Re: [HACKERS] [RFC][PATCH]: CRC32 is limiting at COPY/CTAS/INSERT ... SELECT + speeding it up
The Saturday 30 October 2010 11:05:17, Andres Freund wrote : Hi, This thread died after me not implementing a new version and some potential license problems. I still think its worthwile (and I used it in production for some time) so I would like to implement a version fit for the next commitfest. The code where I started out from is under the zlib license - which is to my knowledge compatible with PGs licence. Whats the position of HACKERS there? There already is some separately licenced code around and were already linking to zlib licenced code... For simplicitly I asked Mark Adler (the original Copyright Owner) if he would be willing to relicence - he is not. For anybody not hording all old mail like me here is a link to the archives about my old patch: http://archives.postgresql.org/message- id/201005202227.49990.and...@anarazel.de Andres I forgot to report this a few months ago: I had a very intensive COPY load, and this patch helped. The context was a server that was CPU bound on loading data (8 COPY on the same table in parallel, not indexed). This patch gave me a 10% boost in load time. I don't have the figures right now, but I could try to do this test again if this can help. At that time, I just tried it out of curiosity, but the load time was sufficient without it, so I didn't spend more time on it. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hash support for arrays
2010/11/2 Kenneth Marshall k...@rice.edu: Given that our hash implimentation mixes the input data well (It does. I tested it.) then a simple rotate-and-xor method is all that should be needed to maintain all of the needed information. The original hash function has done the heavy lifting in this case. Even with the perfect hash function for the elements, certain combinations of elements could still lead to massive collisions. E.g., if repeated values are typical in the input data we are talking about, then the rotate-and-xor method would still lead to collisions between any array of the same values of certain lengths, regardless of the value. In Tom's implementation, as he mentioned before, those problematical lengths would be multiples of 32 (e.g., an array of 32 1s would collide with an array of 32 2s would collide with an array of 32 3s, etc). Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsql: Bootstrap WAL to begin at segment logid=0 logseg=1 (000000010000
On Tue, Nov 2, 2010 at 2:40 AM, Heikki Linnakangas heikki.linnakan...@iki.fi wrote: Back-patch to 9.0. Since this only affects bootstrapping, it makes no difference to existing installations. We don't need to worry about the bug in existing installations, because if you've managed to get past the initial base backup already, you won't hit the bug in the future either. I'm actually not nearly so sanguine about this not affecting existing installations. It means, for example, that anyone who has written monitoring scripts that watch the wal position will see behaviour they're not familiar with. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsql: Bootstrap WAL to begin at segment logid=0 logseg=1 (000000010000
On 03.11.2010 11:34, Greg Stark wrote: On Tue, Nov 2, 2010 at 2:40 AM, Heikki Linnakangas heikki.linnakan...@iki.fi wrote: Back-patch to 9.0. Since this only affects bootstrapping, it makes no difference to existing installations. We don't need to worry about the bug in existing installations, because if you've managed to get past the initial base backup already, you won't hit the bug in the future either. I'm actually not nearly so sanguine about this not affecting existing installations. It means, for example, that anyone who has written monitoring scripts that watch the wal position will see behaviour they're not familiar with. You mean, they will see an unfamiliar wal position right after initdb? I guess, but who runs monitoring scripts on a freshly initdb'd database before doing anything on it? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hash support for arrays
On Wed, Nov 03, 2010 at 10:24:16AM +0100, Nicolas Barbier wrote:
2010/11/2 Kenneth Marshall k...@rice.edu:
Given that our hash implimentation mixes the input data well (It does.
I tested it.) then a simple rotate-and-xor method is all that should
be needed to maintain all of the needed information. The original
hash function has done the heavy lifting in this case.
Even with the perfect hash function for the elements, certain
combinations of elements could still lead to massive collisions. E.g.,
if repeated values are typical in the input data we are talking about,
then the rotate-and-xor method would still lead to collisions between
any array of the same values of certain lengths, regardless of the
value. In Tom's implementation, as he mentioned before, those
problematical lengths would be multiples of 32 (e.g., an array of 32
1s would collide with an array of 32 2s would collide with an array of
32 3s, etc).
Nicolas
True. I just took another look at our defined hash functions and it
looks like we can make a simple variant of hash_uint32() that we
can use as a stream checksum. The only thing missing is that ability
to pass in the current 32-bit hash value as a starting seed to add
the next 32-bit value. Something like this should work:
Datum
hash_uint32(uint32 k, uint32 initval)
{
register uint32 a,
b,
c;
a = b = c = 0x9e3779b9 + (uint32) sizeof(uint32) + 3923095 + initval;
a += k;
final(a, b, c);
/* report the result */
return UInt32GetDatum(c);
}
Then if you pass in the current value as the initval, it should mix
well each additional 32-bit hash value.
Regards,
Ken
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsql: Bootstrap WAL to begin at segment logid=0 logseg=1 (000000010000
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 03.11.2010 11:34, Greg Stark wrote: I'm actually not nearly so sanguine about this not affecting existing installations. It means, for example, that anyone who has written monitoring scripts that watch the wal position will see behaviour they're not familiar with. You mean, they will see an unfamiliar wal position right after initdb? I guess, but who runs monitoring scripts on a freshly initdb'd database before doing anything on it? The WAL position immediately after initdb is unspecified, and definitely NOT 0/0, in any case. From this perspective initdb will merely seem to have emitted more WAL than it used to. A possibly more realistic objection is that a slave freshly initdb'd with 9.0.2 might have trouble syncing up with a master using 9.0.1, if the master is so new it hasn't chewed a segment's worth of WAL yet. Not sure if this is actually a problem. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] improved parallel make support
On tis, 2010-11-02 at 10:21 -0400, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: This patch requires GNU make 3.80, because of the above | feature and the $(eval) function. Version 3.80 is dated October 2002, so it should be no problem, but I do occasionally read of make 3.79 around here; maybe it's time to get rid of that. I did put in a check that makes the build fail right away if a wrong version of make is used. Do we have a handle on how many buildfarm members this will break? I suppose we don't. One way to find out would be to commit just this bit +# We need the $(eval) function, which is available in GNU make 3.80. +# That also happens to be the version where the .VARIABLES variable +# was introduced, so this is a simple check. +ifndef .VARIABLES +$(error GNU make 3.80 or newer is required) +endif with a $(warning) instead, and let it run for a bit. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] improved parallel make support
Peter Eisentraut pete...@gmx.net writes: On tis, 2010-11-02 at 10:21 -0400, Tom Lane wrote: Do we have a handle on how many buildfarm members this will break? I suppose we don't. One way to find out would be to commit just this bit +# We need the $(eval) function, which is available in GNU make 3.80. +# That also happens to be the version where the .VARIABLES variable +# was introduced, so this is a simple check. +ifndef .VARIABLES +$(error GNU make 3.80 or newer is required) +endif with a $(warning) instead, and let it run for a bit. +1 regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger
Alex Hunsaker bada...@gmail.com writes: On Mon, Nov 1, 2010 at 16:59, Tom Lane t...@sss.pgh.pa.us wrote: Surely, removing the internal name's dependency on the istrigger flag is wrong. Â If you're going to maintain separate hash entries at the pltcl level, why would you want to risk collisions underneath that? Good catch. I was basing it off plperl which uses the same proname for both (sprintf(subname, %s__%u, prodesc-proname, fn_oid)). Its OK for plperl because when we compile we save a reference to it and use that directly (more or less). The name does not really matter. OK, applied. I notice that plpython is also using the trigger relation's OID, but I don't know that language well enough to tell whether it really needs to. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Fwd: Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name
Sorry, I messed up and emailed this only to Dimitri. --- Begin forwarded message from Alvaro Herrera --- From: Alvaro Herrera alvhe...@commandprompt.com To: Dimitri Fontaine dimi...@2ndquadrant.fr Date: Wed, 03 Nov 2010 14:13:58 -0300 Subject: Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name Excerpts from Dimitri Fontaine's message of mié nov 03 13:10:12 -0300 2010: Then, I think the ALTER EXTENSION foo SET SCHEMA name still has a use case, so I've prepared a simple patch to show the API usage before we get to refactor it all following Tom's asking. So there's a initial patch to see that in action. I had to rework AlterFunctionNamespace() API so that I can call it from elsewhere in the backend where I have Oids, so here's an updated set_schema.4.patch. We will have to extend the APIs for relations and types the same way, but it's already possible to test the patch with some extensions this way. Before I noticed that you were going to rework this patch completely, I cleaned it up a bit; attached (probably just for your amusement) I was looking at this patch 'cause someone asked for the ability to do CREATE TEMP OBJECT for objects that currently don't support it. I thought this might be related. --- End forwarded message --- -- Álvaro Herrera alvhe...@alvh.no-ip.org 0010-Clean-up-ALTER-OBJECT-SET-SCHEMA-patch.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Improving planner's handling of min/max aggregate optimization
On Mon, Nov 1, 2010 at 8:16 PM, Tom Lane t...@sss.pgh.pa.us wrote: This will make the min/max optimization code more visible to the rest of the planner in a couple of ways: aside from being called at two places not one, it will have some intermediate state that'll have to be kept in PlannerInfo, and the useful pathkeys logic will have to be complicit in letting paths that match the aggregates' requirements survive. But it's not real bad, and it seems a lot better than continuing with the fully-at-arms-length approach. Comments? +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_wal_senders must die
[going back on list with this] Selena Deckelmann selenama...@gmail.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: the other three DBAs here implemented the HS/SR while I was out They told me that it was working great once they figured it out, but it was confusing; it took them a lot of time and a few false starts to get it working. I've been trying to get details to support an improvement in documentation Just curious -- did they use the wiki documentation at all? Was any of that more or less helpful? I finally got a chance to chat with the other DBAs about this -- they actually *just* looked at the Wiki to get through it, and didn't go to the manual at all. They eventually concluded that the problems were all because this was done on a machine where we had multiple major releases of PostgreSQL running for different database clusters, and the version on $PATH was not changed to 9.0. Even though they started PostgreSQL on the standby server with an explicit path to the 9.0 version, it seemed to find some executable or library from 8.4 on $PATH, resulting in bizarre and unhelpful error messages. We do our own builds with a prefix like /usr/local/pgsql-9.0.1 and create a symlink from /usr/local/pgsql to what we want as the default on the machine, when an explicit path is not specified. When they pointed the symlink to 9.0.1 everything worked as expected. They said that except for the quirky path behavior, the installation went fine; the Wiki page instructions were clear and adequate and that installation process was not difficult or confusing. This path issue sounds like a bug to me -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_wal_senders must die
Hi, On Wednesday 03 November 2010 20:28:03 Kevin Grittner wrote: They said that except for the quirky path behavior, the installation went fine; the Wiki page instructions were clear and adequate and that installation process was not difficult or confusing. This path issue sounds like a bug to me I guess you built both in the same place and just prefix installed it to different directories? That can cause issues like that unless you use --disable-rpath. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger
On Wed, Nov 3, 2010 at 10:28, Tom Lane t...@sss.pgh.pa.us wrote: OK, applied. Thanks! I notice that plpython is also using the trigger relation's OID, but I don't know that language well enough to tell whether it really needs to. This thread was started by someone working a plpython a validator, I figured the two areas overlap somewhat and did not want to step on any toes. Anyhow the patch is tiny. So toes should remain intact. Find it attached. Given that plpython can only return None/OK, MODIFY or SKIP and looking around the code for a bit, I don't see any reason it needs it. I only tried plpython3, but it passed an installcheck and some additional testing (two tables with the same column name and different types using the same trigger). [ Aside ] I almost thought using tgreloid was required as PLy_modify_tuple has: plpython.c:748 modvalues[i] = InputFunctionCall(proc-result.out.r.atts[atti].typfunc, ^ NULL, proc-result.out.r.atts[atti].typioparam ^ But Ply_procedure_get has this bit which gets run every time the function is called: plpython.c: 1336 /* * Input/output conversion for trigger tuples. Use the result * TypeInfo variable to store the tuple conversion info. We do this * over again on each call to cover the possibility that the * relation's tupdesc changed since the trigger was last called. * PLy_input_tuple_funcs and PLy_output_tuple_funcs are responsible * for not doing repetitive work. */ PLy_input_tuple_funcs((proc-result), tdata-tg_relation-rd_att); PLy_output_tuple_funcs((proc-result), tdata-tg_relation-rd_att); I double checked the other pls just for my sanity. They get it right, that is look it up instead of using anything cached in proc_desc. *** a/src/pl/plpython/plpython.c --- b/src/pl/plpython/plpython.c *** *** 1298,1304 PLy_procedure_get(FunctionCallInfo fcinfo, Oid tgreloid) if (!HeapTupleIsValid(procTup)) elog(ERROR, cache lookup failed for function %u, fn_oid); ! rv = snprintf(key, sizeof(key), %u_%u, fn_oid, tgreloid); if (rv = sizeof(key) || rv 0) elog(ERROR, key too long); --- 1298,1308 if (!HeapTupleIsValid(procTup)) elog(ERROR, cache lookup failed for function %u, fn_oid); ! if(OidIsValid(tgreloid)) ! rv = snprintf(key, sizeof(key), %u_trigger, fn_oid); ! else ! rv = snprintf(key, sizeof(key), %u, fn_oid); ! if (rv = sizeof(key) || rv 0) elog(ERROR, key too long); *** *** 1365,1374 PLy_procedure_create(HeapTuple procTup, Oid tgreloid, char *key) if (OidIsValid(tgreloid)) rv = snprintf(procName, sizeof(procName), ! __plpython_procedure_%s_%u_trigger_%u, NameStr(procStruct-proname), ! HeapTupleGetOid(procTup), ! tgreloid); else rv = snprintf(procName, sizeof(procName), __plpython_procedure_%s_%u, --- 1369,1377 if (OidIsValid(tgreloid)) rv = snprintf(procName, sizeof(procName), ! __plpython_procedure_%s_%u_trigger, NameStr(procStruct-proname), ! HeapTupleGetOid(procTup)); else rv = snprintf(procName, sizeof(procName), __plpython_procedure_%s_%u, -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_wal_senders must die
Andres Freund and...@anarazel.de wrote: I guess you built both in the same place and just prefix installed it to different directories? We always build in a directory tree with a name based on the version, with a prefix based on the version. This is routine for us. I have a hard time believing that they made an error on that, but I'll try to re-create in a controlled way so that I can report specific error messages and confirm my assumptions. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SQL/MED estimated time of arrival?
Hi SQL/MED developers, Our company has just finished development of a database extension for Informix that provides tabular access to various types of structured files (NetCDF and HDF5, with more types to come). We would like to port this logic to run on PostgreSQL, since many of our potential customers use PostgreSQL. On Informix, we were able to take advantage of the VTI (Virtual Table Interface) feature to support table scans and indexing. (See http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/db_vti.html .) Do you have any idea of how long it will be before SQL/MED on PostgreSQL will be available, and perhaps how similar it will be to Informix VTI? Thanks, Eric. ** Eric Davies, M.Sc. Senior Programmer Analyst Barrodale Computing Services Ltd. 1095 McKenzie Ave., Suite 418 Victoria BC V8P 2L5 Canada Tel: (250) 704-4428 Web: http://www.barrodale.com Email: e...@barrodale.com **
Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger
On 03/11/10 20:57, Alex Hunsaker wrote: On Wed, Nov 3, 2010 at 10:28, Tom Lane t...@sss.pgh.pa.us wrote: OK, applied. Thanks! I notice that plpython is also using the trigger relation's OID, but I don't know that language well enough to tell whether it really needs to. This thread was started by someone working a plpython a validator, I figured the two areas overlap somewhat and did not want to step on any toes. Anyhow the patch is tiny. So toes should remain intact. Find it attached. Yeah, it just needs a flag to say trigger/not (but it does need a flag, for the same reason plperl needs it). By the way, I'm leaning in the direction of not using a Python dictionary for the cache, but a standard Postgres HTAB instead. It's more like other pls this way, and you can get rid of PyCObjects (which are deprecated BTW) and messing around with reference counting the cached procedures. I was even thinking about having *two* hash tables, for trigger and nontrigger procedures. This way you can make the function OID (which is useful to hav anyway) be the first field of the structure being cached and make both hash tables keyed by OIDs. Saves you the trouble of defining a structure for the key... Not sure if it'll turn out for the better, but I'm definitely for not using a Python dictionary for the cache. The validator is ready, once I'm done with the hash tables I'll try to fix up the error checking (get rid of the global error state) and finally do what started it all, that is make plpythonu use subtransactions for SPI and be able to do: try: plpy.execute(insert into foo values(1)) except plpy.UniqueViolation, e: plpy.notice(Ooops, you got yourself a SQLSTATE %d, e.sqlstate) Cheers, Jan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger
On Wed, Nov 3, 2010 at 14:43, Jan Urbański wulc...@wulczer.org wrote:
By the way, I'm leaning in the direction of not using a Python
dictionary for the cache, but a standard Postgres HTAB instead. It's
more like other pls this way, and you can get rid of PyCObjects (which
are deprecated BTW) and messing around with reference counting the
cached procedures.
Well if they are deprecated and there is an arguably cleaner way to do
it... might as well.
I was even thinking about having *two* hash tables, for trigger and
nontrigger procedures...snip... Saves you the trouble of
defining a structure for the key... Not sure if it'll turn out for the
better, but I'm definitely for not using a Python dictionary for the cache.
*shrug*
make plpythonu use
subtransactions for SPI and be able to do:
try:
plpy.execute(insert into foo values(1))
except plpy.UniqueViolation, e:
plpy.notice(Ooops, you got yourself a SQLSTATE %d, e.sqlstate)
Ouuu googly eyes.
[ now that eval { }, thanks to Tim Bunce, works with plperl it should
be possible to do something similar there as well. Just noting the
possibility... not volunteering :) ]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger
On Nov 3, 2010, at 2:06 PM, Alex Hunsaker wrote:
try:
plpy.execute(insert into foo values(1))
except plpy.UniqueViolation, e:
plpy.notice(Ooops, you got yourself a SQLSTATE %d, e.sqlstate)
Ouuu googly eyes.
[ now that eval { }, thanks to Tim Bunce, works with plperl it should
be possible to do something similar there as well. Just noting the
possibility... not volunteering :) ]
/me wants a global $dbh that mimics the DBI interface but just uses SPI under
the hood. Not volunteering, either…
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name
Excerpts from Dimitri Fontaine's message of mié nov 03 13:10:12 -0300 2010: Then, I think the ALTER EXTENSION foo SET SCHEMA name still has a use case, so I've prepared a simple patch to show the API usage before we get to refactor it all following Tom's asking. So there's a initial patch to see that in action. FWIW I think you should use getObjectDescription, as in the attached patch. (Note the patch is incomplete and does not compile because only one caller to CheckSetNamespace has been fixed). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support 0001-Use-getObjectDescription.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fix for seg picksplit function
Hackers,
Seg contrib module contains the same bug in picksplit function as cube
contrib module.
Also, Guttman's split algorithm is not needed in unidimensional case,
because sorting based algorithm is good in this case. I propose the patch
which replace current picksplit implementation with sorting based algorithm.
test=# create table seg_test(a seg);
test=# insert into seg_test (select (a || ' .. ' || a + 0.5*b)::seg from
(select random() as a, random() as b from generate_series(1,100)) x);
Before the patch.
test=# create index seg_test_idx on seg_test using gist (a);
CREATE INDEX
Time: 263981,639 ms
test=# explain (buffers, analyze) select * from seg_test where a @ '0.5 ..
0.5'::seg;
QUERY PLAN
Bitmap Heap Scan on seg_test (cost=36.28..2508.41 rows=1000 width=12)
(actual time=36.909..36.981 rows=23 loops=1)
Recheck Cond: (a @ '0.5'::seg)
Buffers: shared hit=1341
- Bitmap Index Scan on seg_test_idx (cost=0.00..36.03 rows=1000
width=0) (actual time=36.889..36.889 rows=23 loops=1)
Index Cond: (a @ '0.5'::seg)
Buffers: shared hit=1318
Total runtime: 37.066 ms
(7 rows)
Time: 37,842 ms
After the patch.
test=# create index seg_test_idx on seg_test using gist (a);
CREATE INDEX
Time: 205476,854 ms
test=# explain (buffers, analyze) select * from seg_test where a @ '0.5 ..
0.5'::seg;
QUERY PLAN
--
Bitmap Heap Scan on seg_test (cost=28.18..2500.31 rows=1000 width=12)
(actual time=0.283..0.397 rows=23 loops=1)
Recheck Cond: (a @ '0.5'::seg)
Buffers: shared hit=27
- Bitmap Index Scan on seg_test_idx (cost=0.00..27.93 rows=1000
width=0) (actual time=0.261..0.261 rows=23 loops=1)
Index Cond: (a @ '0.5'::seg)
Buffers: shared hit=4
Total runtime: 0.503 ms
(7 rows)
Time: 1,530 ms
Number of pages, which was used for index scan, decreased from 1318 to 4.
I'm going to add this patch to commitfest.
Pg_temporal project contain same bug. If this patch will be accepted by
community, then I'll prepare similar patch for pg_temporal.
With best regards,
Alexander Korotkov.
*** a/contrib/seg/seg.c
--- b/contrib/seg/seg.c
***
*** 292,329 gseg_penalty(GISTENTRY *origentry, GISTENTRY *newentry, float *result)
return (result);
}
/*
** The GiST PickSplit method for segments
! ** We use Guttman's poly time split algorithm
*/
GIST_SPLITVEC *
gseg_picksplit(GistEntryVector *entryvec,
GIST_SPLITVEC *v)
{
! OffsetNumber i,
! j;
! SEG *datum_alpha,
! *datum_beta;
SEG *datum_l,
*datum_r;
! SEG *union_d,
! *union_dl,
! *union_dr;
! SEG *inter_d;
bool firsttime;
! float size_alpha,
! size_beta,
! size_union,
! size_inter;
! float size_waste,
! waste;
! float size_l,
! size_r;
int nbytes;
! OffsetNumber seed_1 = 1,
! seed_2 = 2;
OffsetNumber *left,
*right;
OffsetNumber maxoff;
--- 292,335
return (result);
}
+ /*
+ * Auxiliary structure for picksplit method.
+ */
+ typedef struct
+ {
+ int index;
+ SEG *data;
+ } PickSplitSortItem;
+ /*
+ * Compare function for PickSplitSortItem based on seg_cmp.
+ */
+ static int
+ sort_item_cmp(const void *a, const void *b)
+ {
+ PickSplitSortItem *i1 = (PickSplitSortItem *)a;
+ PickSplitSortItem *i2 = (PickSplitSortItem *)b;
+ return seg_cmp(i1-data, i2-data);
+ }
/*
** The GiST PickSplit method for segments
! ** Algorithm based on sorting. Incoming array of segs is sorting using seg_cmp
! ** function. After that first half of segs goes to the left datum, and the
! ** second half of segs goes to the right datum.
*/
GIST_SPLITVEC *
gseg_picksplit(GistEntryVector *entryvec,
GIST_SPLITVEC *v)
{
! OffsetNumber i;
SEG *datum_l,
*datum_r;
! PickSplitSortItem *sortItems;
bool firsttime;
! float waste;
int nbytes;
! OffsetNumber seed_2;
OffsetNumber *left,
*right;
OffsetNumber maxoff;
***
*** 332,442 gseg_picksplit(GistEntryVector *entryvec,
fprintf(stderr, picksplit\n);
#endif
! maxoff = entryvec-n - 2;
! nbytes = (maxoff + 2) * sizeof(OffsetNumber);
v-spl_left = (OffsetNumber *) palloc(nbytes);
v-spl_right = (OffsetNumber *) palloc(nbytes);
firsttime = true;
waste = 0.0;
! for (i = FirstOffsetNumber; i maxoff; i = OffsetNumberNext(i))
{
! datum_alpha = (SEG *) DatumGetPointer(entryvec-vector[i].key);
! for (j = OffsetNumberNext(i); j = maxoff; j = OffsetNumberNext(j))
! {
! datum_beta = (SEG *) DatumGetPointer(entryvec-vector[j].key);
!
! /* compute the wasted space by
Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name
Alvaro Herrera alvhe...@commandprompt.com writes: FWIW I think you should use getObjectDescription, as in the attached patch. (Note the patch is incomplete and does not compile because only one caller to CheckSetNamespace has been fixed). That a very good idea, will apply (cherry-pick -n) and finish it tomorrow, thanks! Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] timestamp of the last replayed transaction
On Tue, Nov 2, 2010 at 10:38 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Fujii Masao masao.fu...@gmail.com writes: After 9.0 release, I've often heard that some people want to know how far transactions have been replayed in the standby in timestamp rather than LSN. So I'm thinking to include the function which returns the timestamp of the last applied transaction (i.e., commit/abort WAL record) in the core. Name: pg_last_replay_xact_timestamp (better name?) Return Type: timestamp with time zone Thought? How do you want to implement the tracking? I'm thinking to just expose GetLatestXTime(), i.e., XLogCtl-recoveryLastXTime. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Comparison with true in source code
On Wed, Nov 3, 2010 at 2:19 AM, Michael Meskes mes...@postgresql.org wrote: On Mon, Nov 01, 2010 at 12:17:02PM +0900, Itagaki Takahiro wrote: There are some == true in the codes, but they might not be safe because all non-zero values are true in C. Is it worth cleaning up them? Here is a proposed cleanup that replaces boolean == true with boolean. I didn't touch == false unless they are not in pairs of comparisons with true because comparison with false is a valid C code. Note that I also changed boolean != true in pg_upgrade, but I didn't change ones in xlog.c because it might check corrupted fields in control files. src/interfaces/ecpg/preproc/ecpg.c(310): ptr2ext[3] = (header_mode == true) ? 'h' : 'c'; I actually see no reason why these variables are not defined as bool instead of int, so I changed this. Hopefully I found all of them. I added an additional cleanup to 'header_mode' in ecpg; I changed the type from bool to char to hold 'h' or 'c'. Do you think it is reasonable? -- Itagaki Takahiro bool_eq_true_cleanup.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First patch proposal
On 14/10/10 15:53, Alastair Turner wrote: It isn't a TODO item, or related to any previous thread I could find. It's certainly something I can see a use for. When I'm having a bad typing day I get annoyed that I find I've made a mistake after I've typed the password. To me this is a feature that will just make life a little more pleasant for command line junkies like me. Regards, -- Mike Fowler Registered Linux user: 379787 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Can we talk about a version which has already been developed?
I read a few things about development in the open source area on different websites, had a talk with a few friends and thought that I cannot work on the development branch of postgres right now. So I have this one single question: Would I be able to get the answers if I asked questions about the last version developed (e.g. for now, that would mean version 9.0.1)? Regards, Vaibhav (*_*)
Re: [HACKERS] Can we talk about a version which has already been developed?
On Wed, Nov 3, 2010 at 9:30 PM, Vaibhav Kaushal vaibhavkaushal...@gmail.com wrote: I read a few things about development in the open source area on different websites, had a talk with a few friends and thought that I cannot work on the development branch of postgres right now. ok So I have this one single question: Would I be able to get the answers if I asked questions about the last version developed (e.g. for now, that would mean version 9.0.1)? if you make a question in the appropiate list (which probably this isn't, use pgsql-admin or pgsql-performance or pgsql-sql) probably yes -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
