Re: [HACKERS] Automatically setting work_mem
On Tue, 2006-03-21 at 17:47 -0500, Tom Lane wrote:
> I'm fairly unconvinced about Simon's underlying premise --- that we
> can't make good use of work_mem in sorting after the run building phase
> --- anyway.
We can make good use of memory, but there does come a point in final
merging where too much is of no further benefit. That point seems to be
at about 256 blocks per tape; patch enclosed for testing. (256 blocks
per tape roughly doubles performance over 32 blocks at that stage).
That is never the case during run building - more is always better.
> If we cut back our memory usage
Simon inserts the words: "too far"
> then we'll be forcing a
> significantly more-random access pattern to the temp file(s) during
> merging, because we won't be able to pre-read as much at a time.
Yes, thats right.
If we have 512MB of memory that gives us enough for 2000 tapes, yet the
initial runs might only build a few runs. There's just no way that all
512MB of memory is needed to optimise the performance of reading in a
few tapes at time of final merge.
I'm suggesting we always keep 2MB per active tape, or the full
allocation, whichever is lower. In the above example that could release
over 500MB of memory, which more importantly can be reused by subsequent
sorts if/when they occur.
Enclose two patches:
1. mergebuffers.patch allows measurement of the effects of different
merge buffer sizes, current default=32
2. reassign2.patch which implements the two kinds of resource
deallocation/reassignment proposed.
Best Regards, Simon Riggs
Index: src/backend/utils/sort/tuplesort.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/sort/tuplesort.c,v
retrieving revision 1.65
diff -c -r1.65 tuplesort.c
*** src/backend/utils/sort/tuplesort.c 10 Mar 2006 23:19:00 - 1.65
--- src/backend/utils/sort/tuplesort.c 21 Mar 2006 19:20:23 -
***
*** 179,186
*/
#define MINORDER 6 /* minimum merge order */
#define TAPE_BUFFER_OVERHEAD (BLCKSZ * 3)
! #define MERGE_BUFFER_SIZE (BLCKSZ * 32)
!
/*
* Private state of a Tuplesort operation.
*/
--- 179,187
*/
#define MINORDER 6 /* minimum merge order */
#define TAPE_BUFFER_OVERHEAD (BLCKSZ * 3)
! #define OPTIMAL_MERGE_BUFFER_SIZE (BLCKSZ * 32)
! #define PREFERRED_MERGE_BUFFER_SIZE (BLCKSZ * 256)
! #define REUSE_SPACE_LIMIT RELSEG_SIZE
/*
* Private state of a Tuplesort operation.
*/
***
*** 255,260
--- 256,270
*/
int currentRun;
+ /*
+ * These variables are used during final merge to reassign resources
+ * as they become available for each tape
+ */
+ int lastPrereadTape;/* last tape preread from */
+ int numPrereads;/* num times last tape has been selected */
+ int reassignableSlots; /* how many slots can be reassigned */
+ longreassignableMem;/* how much memory can be reassigned */
+
/*
* Unless otherwise noted, all pointer variables below are pointers
* to arrays of length maxTapes, holding per-tape data.
***
*** 398,408
--- 408,422
static Tuplesortstate *tuplesort_begin_common(int workMem, bool randomAccess);
static void puttuple_common(Tuplesortstate *state, SortTuple *tuple);
+ static void grow_memtuples(Tuplesortstate *state);
+ static void shrink_memtuples(Tuplesortstate *state);
static void inittapes(Tuplesortstate *state);
static void selectnewtape(Tuplesortstate *state);
static void mergeruns(Tuplesortstate *state);
static void mergeonerun(Tuplesortstate *state);
static void beginmerge(Tuplesortstate *state);
+ static void assignResourcesUniformly(Tuplesortstate *state, bool initialAssignment);
+ static void reassignresources(Tuplesortstate *state, int srcTape);
static void mergepreread(Tuplesortstate *state);
static void mergeprereadone(Tuplesortstate *state, int srcTape);
static void dumptuples(Tuplesortstate *state, bool alltuples);
***
*** 727,733
* moves around with tuple addition/removal, this might result in thrashing.
* Small increases in the array size are likely to be pretty inefficient.
*/
! static bool
grow_memtuples(Tuplesortstate *state)
{
/*
--- 741,747
* moves around with tuple addition/removal, this might result in thrashing.
* Small increases in the array size are likely to be pretty inefficient.
*/
! static void
grow_memtuples(Tuplesortstate *state)
{
/*
***
*** 740,752
* this assumption should be good. But let's check it.)
*/
if (state->availMem <= (long) (state->memtupsize * sizeof(SortTuple)))
! return false;
/*
* On a 64-bit machine, allowedMem could be high enough to get us into
* trouble with MaxAllocSize, too.
*/
if ((Size) (state->memtupsize * 2) >= MaxAllocSize / sizeof(SortTuple))
! return false;
FREEMEM(state, GetMemoryChunkS
Re: [HACKERS] Question about MemoryContexts and functions that returns
Tom Lane wrote: Thomas Hallgren <[EMAIL PROTECTED]> writes: Is there a difference in how the executor treat a C function and a function using a call handler that can cause this behavior? Can't think of one. You'd better take a closer look at your call handler. gdb'ing with a watchpoint on writes to CurrentMemoryContext might be helpful at seeing whether the context is changing unexpectedly. Yes, that was helpful. My fault of course. I had a comment in place that explained exactly what ought to happen. Then the code did the exact opposite. An excerpt: /* a class loader or other mechanism might have connected already. This * connection must be dropped since its parent context is wrong. */ if(self->isMultiCall && SRF_IS_FIRSTCALL()) Invocation_assertConnect(); The Invocation_assertConnect() performs an SPI_connect(). Sigh... Comments are dangerous :-) Thanks for your help. Kind Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 planning features
On Tue, 21 Mar 2006, Tom Lane wrote: satoshi nagayasu <[EMAIL PROTECTED]> writes: Oleg Bartunov wrote: I and Teodor are going to give a talk on conference "Corporate database systems-2006" here in Moscow and we need info about new features planned in 8.2 release. Bruce, do you have a list of them ? Refer to the Josh's past talk, "PostgreSQL Directions 8.1 and beyond". MP3 and materials of are available: http://www.postgresql.jp/misc/seminar/2006-02-17_18/materials/01_Josh_Berkus.pdf http://www.postgresql.jp/misc/seminar/2006-02-17_18/materials/01_Josh_Berkus.mp3 BTW, pretty pictures such as Josh draws in the above talk should not be confused with reality ;-). The reality of this project is that *there is no overall plan*. Individual developers work on what they choose to. It's true that at the moment there seem to be quite a number of people focusing on performance in enterprise-size applications, but that's not because there's any central plan saying that that's what we're doing. In particular, asking for a list of features that will be done in particular future releases shows a complete lack of understanding of the process ... I didn't expect such a reaction :) I expected people (developers) know what they choose (or plan) to work on and just share their thoughts. If there would be any overall plan I'd not asked -hackers at all. I know, for example, that we're going to work on inverted index support, which scales full text support. regards, tom lane Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Modular Type Libraries: was A real currency type
"Andrew Dunstan" <[EMAIL PROTECTED]> writes: > ... The real issue is what types and type mechanisms should be in the > postgresql core distribution. We won't win any thanks from anyone if we > reduce them. Getting some types right is hard. There is no case that I can > see for pushing timestamps, numerics, bitstrings or geometric or network > types out of the core - they need all the support they can get. I'm also not > sure which of these are required by the SQL spec. Timestamps and numerics are definitely in the spec, geometric and network types are definitely not. IIRC, bitstring types are in SQL99 but for some reason are deprecated in SQL2003 (if anyone knows the reasoning behind the SQL committee's about-face on that, please clue us in). AFAICS, the main part of the type system that isn't modular is the support for type parameters (a/k/a typmod), such as the maximum length for varchar or the precision/scale for numeric. We could certainly invent an API for interpreting such parameters. But in the current state of the system the types that have such parameters have to be hard-wired into the SQL grammar, and I don't see how to get rid of that hard-wiring without breaking a whole lot of stuff. Any bison gurus hanging about? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Modular Type Libraries: was A real currency type
remove my email from the list Regards, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Andrew Dunstan Sent: Wednesday, March 22, 2006 9:05 AM To: [EMAIL PROTECTED] Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Modular Type Libraries: was A real currency type Trent Shipley said: > Without directly addressing the merits of enumerations, enumeration > interfaces, real currency and time zone types, or whether currency and > time zone types should be built using enumerations, I would like to > ask the powers-that-be to seriously consider radically modularizing > Postgresql's type system. > > The core Postgresql installation would come with just those built-in > types needed to bootstrap itself, perhaps just varchar and an integer > type. Everything else would be a contributed module. > > An interface or contract would be described for creating additional > types. It would include things like parameter handlers, how to dump > the type, and how to load the type. (That is, standard housekeeping > functions needed by the Postgresql engine.) > > Other that the tiny number of bootstrap types, Postgresql types would > basically all be contrib modules. > > Types could be bundled into groups such as binary, character, > numerical, 2d-spatial, networking, and so on. > > Then one would not debate whether a type (or meta-type, like an > enumeration) should be put into the core product. Instead, the debate > would be whether or not to grade the type as "mature" and whether or > not to put a given type into pre-packaged type libraries with names > like "legacy", "sql-2003-standard", or "recommended-default". > > Power user DBA's could customize the types offered on their systems. > > In short: > > 1) Types would be modular. This would be elegant, but have no > practical effect on database performance. > > 2) The framework needed to support modular types would encourage type > development. This would enhance Postgresql's adaptability which would > be A Very Good Thing. We already have good support of type development. It's not clear to me that this would buy us anything at all. It seems like modularisation for the sake of it. The real issue is what types and type mechanisms should be in the postgresql core distribution. We won't win any thanks from anyone if we reduce them. Getting some types right is hard. There is no case that I can see for pushing timestamps, numerics, bitstrings or geometric or network types out of the core - they need all the support they can get. I'm also not sure which of these are required by the SQL spec. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Modular Type Libraries: was A real currency type
Trent Shipley <[EMAIL PROTECTED]> writes: > Without directly addressing the merits of enumerations, enumeration > interfaces, real currency and time zone types, or whether currency and time > zone types should be built using enumerations, I would like to ask the > powers-that-be to seriously consider radically modularizing Postgresql's type > system. Er ... what makes you think it's not modular now? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Modular Type Libraries: was A real currency type
Trent Shipley said: > Without directly addressing the merits of enumerations, enumeration > interfaces, real currency and time zone types, or whether currency and > time zone types should be built using enumerations, I would like to > ask the powers-that-be to seriously consider radically modularizing > Postgresql's type system. > > The core Postgresql installation would come with just those built-in > types needed to bootstrap itself, perhaps just varchar and an integer > type. Everything else would be a contributed module. > > An interface or contract would be described for creating additional > types. It would include things like parameter handlers, how to dump > the type, and how to load the type. (That is, standard housekeeping > functions needed by the Postgresql engine.) > > Other that the tiny number of bootstrap types, Postgresql types would > basically all be contrib modules. > > Types could be bundled into groups such as binary, character, > numerical, 2d-spatial, networking, and so on. > > Then one would not debate whether a type (or meta-type, like an > enumeration) should be put into the core product. Instead, the debate > would be whether or not to grade the type as "mature" and whether or > not to put a given type into pre-packaged type libraries with names > like "legacy", "sql-2003-standard", or "recommended-default". > > Power user DBA's could customize the types offered on their systems. > > In short: > > 1) Types would be modular. This would be elegant, but have no > practical effect on database performance. > > 2) The framework needed to support modular types would encourage type > development. This would enhance Postgresql's adaptability which would > be A Very Good Thing. We already have good support of type development. It's not clear to me that this would buy us anything at all. It seems like modularisation for the sake of it. The real issue is what types and type mechanisms should be in the postgresql core distribution. We won't win any thanks from anyone if we reduce them. Getting some types right is hard. There is no case that I can see for pushing timestamps, numerics, bitstrings or geometric or network types out of the core - they need all the support they can get. I'm also not sure which of these are required by the SQL spec. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Modular Type Libraries: was A real currency type
Without directly addressing the merits of enumerations, enumeration interfaces, real currency and time zone types, or whether currency and time zone types should be built using enumerations, I would like to ask the powers-that-be to seriously consider radically modularizing Postgresql's type system. The core Postgresql installation would come with just those built-in types needed to bootstrap itself, perhaps just varchar and an integer type. Everything else would be a contributed module. An interface or contract would be described for creating additional types. It would include things like parameter handlers, how to dump the type, and how to load the type. (That is, standard housekeeping functions needed by the Postgresql engine.) Other that the tiny number of bootstrap types, Postgresql types would basically all be contrib modules. Types could be bundled into groups such as binary, character, numerical, 2d-spatial, networking, and so on. Then one would not debate whether a type (or meta-type, like an enumeration) should be put into the core product. Instead, the debate would be whether or not to grade the type as "mature" and whether or not to put a given type into pre-packaged type libraries with names like "legacy", "sql-2003-standard", or "recommended-default". Power user DBA's could customize the types offered on their systems. In short: 1) Types would be modular. This would be elegant, but have no practical effect on database performance. 2) The framework needed to support modular types would encourage type development. This would enhance Postgresql's adaptability which would be A Very Good Thing. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] A real currency type
Tom Lane said: > I was thinking something more like a CREATE ENUM TYPE command that > specifically lists the enum values, and some extension of that to cater > for tagged types, and the values are put into a system catalog that the > user doesn't manipulate directly. I don't see why it's a good idea to > put control of the backing table in the user's hands. Me either. I like the sound of this. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2 planning features
On 3/21/06, satoshi nagayasu <[EMAIL PROTECTED]> wrote: However, we also need to know why business people wantto know about the future plan. For the business people,the roadmap is used to know the software is fit totheir (growing) business, not only now but in the future. With the current timeline, I'll have hierarchical queries, nonrecursive introspective sort, and insert/update/delete returning ready for 8.2. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [HACKERS] Question about MemoryContexts and functions that returns
Thomas Hallgren <[EMAIL PROTECTED]> writes: > Is there a difference in how the executor treat a C function and a > function using a call handler that can cause this behavior? Can't think of one. You'd better take a closer look at your call handler. gdb'ing with a watchpoint on writes to CurrentMemoryContext might be helpful at seeing whether the context is changing unexpectedly. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 planning features
Tom Lane wrote: > In particular, asking for a list of features that will be done in > particular future releases shows a complete lack of understanding > of the process ... I completely understand. However, we also need to know why business people want to know about the future plan. For the business people, the roadmap is used to know the software is fit to their (growing) business, not only now but in the future. Roadmap can be changed, but still roadmap is necessary for some kind of users. Just my opinion... Thanks, -- NAGAYASU Satoshi <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 8.2 planning features
satoshi nagayasu <[EMAIL PROTECTED]> writes: > Oleg Bartunov wrote: >> I and Teodor are going to give a talk on conference "Corporate database >> systems-2006" here in Moscow and we need info about new features planned >> in 8.2 release. Bruce, do you have a list of them ? > Refer to the Josh's past talk, "PostgreSQL Directions 8.1 and beyond". > MP3 and materials of are available: > http://www.postgresql.jp/misc/seminar/2006-02-17_18/materials/01_Josh_Berkus.pdf > http://www.postgresql.jp/misc/seminar/2006-02-17_18/materials/01_Josh_Berkus.mp3 BTW, pretty pictures such as Josh draws in the above talk should not be confused with reality ;-). The reality of this project is that *there is no overall plan*. Individual developers work on what they choose to. It's true that at the moment there seem to be quite a number of people focusing on performance in enterprise-size applications, but that's not because there's any central plan saying that that's what we're doing. In particular, asking for a list of features that will be done in particular future releases shows a complete lack of understanding of the process ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Question about MemoryContexts and functions that returns
Tom Lane wrote: Thomas Hallgren <[EMAIL PROTECTED]> writes: As it turns out, I'm not supposed to allocate the returned tuple in the caller context. Where do you get that from? plpgsql and plperl both do it that way AFAICS. Are you testing in an --enable-cassert build? The memory-clobber behavior that that turns on is really essential for finding dangling-pointer problems ... I use --enable-cassert. I don't think my problem is a dangling pointer. I just created a dummy C-function that short circuits the java_call_handler. It calls my real java function with the correct parameters. When I register this function with language C and use it instead of the normal function that calls via the java call handler, there's no memory leak. It only leaks memory when I go through the call handler. The call handler doesn't execute many lines of code and from what I can tell, it doesn't manipulate contexts at all. Nor does it allocate anything. Further more, I can prevent the leak by allocating the returned tuple in a context of my own and free it on the next call. Is there a difference in how the executor treat a C function and a function using a call handler that can cause this behavior? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] A real currency type
Martijn van Oosterhout writes: > On Tue, Mar 21, 2006 at 05:55:09PM -0500, Tom Lane wrote: >> Yes, you can >> think of advanced applications where it's useful to have random >> additional stuff in the table, but that's exactly the point at which you >> normally have to get down-and-dirty with some C code --- after all, what >> is standardized code going to *do* with the additional stuff? Nothing, >> that's what. > Umm, actually that extra info is very useful. For example in the > currency type it can store the currency symbol and how many decimal > places, etc. And you do what with that in non-currency-specific code? This argument isn't holding water for me. The "decimal places" info is in the wrong place anyway, it should be attached to the underlying numeric type... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.2 planning features
Oleg, Refer to the Josh's past talk, "PostgreSQL Directions 8.1 and beyond". MP3 and materials of are available: http://www.postgresql.jp/misc/seminar/2006-02-17_18/materials/01_Josh_Berkus.pdf http://www.postgresql.jp/misc/seminar/2006-02-17_18/materials/01_Josh_Berkus.mp3 Thanks, Satoshi Nagayasu Oleg Bartunov wrote: > Hi there, > > I and Teodor are going to give a talk on conference "Corporate database > systems-2006" here in Moscow and we need info about new features planned > in 8.2 release. Bruce, do you have a list of them ? > > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] A real currency type
On Tue, Mar 21, 2006 at 05:55:09PM -0500, Tom Lane wrote: > Yes, you can > think of advanced applications where it's useful to have random > additional stuff in the table, but that's exactly the point at which you > normally have to get down-and-dirty with some C code --- after all, what > is standardized code going to *do* with the additional stuff? Nothing, > that's what. If the argument for this is to make it simple to make > simple enum and tagged types, then I don't think that the design should > be centered on allowing extra stuff. Umm, actually that extra info is very useful. For example in the currency type it can store the currency symbol and how many decimal places, etc. The code uses it by converting the datum into a composite form (base,tag) (the "auxilliary type") or by using the taginfo function return the info tuple directly. These are easily manipulated by straight SQL or pl/pgsql functions. While it may not be critical to the use of then, it's an extremely handy feature. Half the point of a currency type would be the formatting, no? The information needs to be stored somewhere... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Automatically setting work_mem
"Luke Lonergan" <[EMAIL PROTECTED]> writes: > Experiment should take but a minute to validate or disprove the hypothesis. Only if you're prepared to trust the results of one experiment on one platform with a not-very-large amount of data. Otherwise it's going to take quite a few minutes ... The real problem we are facing with a whole lot of our optimization issues (not only sorting) is that it's not all that trivial to get credible experimental results that we can expect will hold up across a range of usage scenarios. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Automatically setting work_mem
Tom, On 3/21/06 2:47 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote: > I'm fairly unconvinced about Simon's underlying premise --- that we > can't make good use of work_mem in sorting after the run building phase > --- anyway. If we cut back our memory usage then we'll be forcing a > significantly more-random access pattern to the temp file(s) during > merging, because we won't be able to pre-read as much at a time. I thought we let the OS do that ;-) Seriously, I've suggested an experiment to evaluate the effectiveness of internal buffering with ridiculously low amounts of RAM (work_mem) compared to bypassing it entirely and preferring the buffer cache and OS I/O cache. I suspect the work_mem caching of merge results, while algorithmically appropriate, may not work effectively with the tiny amount of RAM allocated to it, and could be better left to the OS because of it's liberal use of read-ahead and disk caching. Experiment should take but a minute to validate or disprove the hypothesis. - Luke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] A real currency type
Martijn van Oosterhout writes: > Let me put it this way: if this is to progress beyond just a contrib > module, it needs to go all the way (special syntax, pg_dump, etc). I'm > not sure if I'm that enamoured with it to want all that. My feelings in a nutshell ;-) > The only way to avoid that is if both the type and the backing table > are included in the standard distribution and we forbid user changes. I was thinking something more like a CREATE ENUM TYPE command that specifically lists the enum values, and some extension of that to cater for tagged types, and the values are put into a system catalog that the user doesn't manipulate directly. I don't see why it's a good idea to put control of the backing table in the user's hands. Yes, you can think of advanced applications where it's useful to have random additional stuff in the table, but that's exactly the point at which you normally have to get down-and-dirty with some C code --- after all, what is standardized code going to *do* with the additional stuff? Nothing, that's what. If the argument for this is to make it simple to make simple enum and tagged types, then I don't think that the design should be centered on allowing extra stuff. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Automatically setting work_mem
Martijn van Oosterhout writes: > There is one way to guarentee the memory is released to the OS after > completion. Make the allocator allocate work_mem bytes using mmap() > rather than malloc(). munmap() will then definitly return the memory to > the OS. Unfortunatly, the coding required would probably not be > straight-forward... Nor portable. > Glibc will only convert malloc() to an mmap() on > allocations > 128KB and I don't think PostgreSQL ever does that. Actually, we do: it doesn't take very long for the sequence of block allocations within a context to ramp up to 128K. (And I wouldn't be opposed to tweaking the logic in aset.c to make it happen faster, once an initial small allocation is filled up.) Also, individual chunk requests exceeding 8K or thereabouts are fed directly to malloc, so stuff like the SortTuple array might well be effectively mmap'd. I'm fairly unconvinced about Simon's underlying premise --- that we can't make good use of work_mem in sorting after the run building phase --- anyway. If we cut back our memory usage then we'll be forcing a significantly more-random access pattern to the temp file(s) during merging, because we won't be able to pre-read as much at a time. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] A real currency type
On Tue, Mar 21, 2006 at 05:25:15PM -0500, Tom Lane wrote: > The dump should look the same as the commands originally used to create > the type, which is surely not going to happen with that "SELECT > create_tagged_type()" stuff barring pg_dump modifications. Otherwise > we are nailing down not one but two representations of this feature that > we'll have to support forevermore: what the users see and what's in > pg_dump scripts. Well, I agree with you in principle there. However, when you install tsearch2 and do a pg_dump you don't get just a "\i tsearch.sql" out either (I did propose something like this once though). Now, tsearch2 isn't integrated either, so I guess that the difference. Let me put it this way: if this is to progress beyond just a contrib module, it needs to go all the way (special syntax, pg_dump, etc). I'm not sure if I'm that enamoured with it to want all that. > Now I happen to think that SELECT create_tagged_type() is a horrid kluge > anyway ;-) so I'm not proposing that pg_dump be changed to output that. > What we would need is to design a command syntax that we're actually > prepared to live with for the indefinite future, then implement it in > the backend and teach pg_dump about it. Ack. > What we *don't* want is a pg_dump representation that exposes > implementation details. I would classify both the backing table for a > tagged type's enum values, and the representation of its operators and > functions, as implementation details. One slight problem: the "backing table" is provided by the creator of the type. And in principle they could be altered once the type is created. So if the user creates a new tagged type, the pg_dump output is going to have to output the backing table in some form, if not COPY then something else. The only way to avoid that is if both the type and the backing table are included in the standard distribution and we forbid user changes. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Automatically setting work_mem
On Tue, Mar 21, 2006 at 08:05:50PM +, Simon Riggs wrote: > > Point 2 is actually a serious flaw in Simon's proposal, because there > > is no portable way to make malloc return freed memory to the OS. Some > > mallocs will do that ... in some cases ... but many simply don't ever > > move the brk address down. It's not an easy thing to do when the arena > > gets cluttered with a lot of different alloc chunks and only some of > > them get freed. > The largest requirement for memory is the run building during > performsort. That portion of the code is not concurrently executed > within the same query. If we can reduce memory usage after that phase > completes then we stand a chance of not overusing memory on a big query > and not being able to reclaim it. There is one way to guarentee the memory is released to the OS after completion. Make the allocator allocate work_mem bytes using mmap() rather than malloc(). munmap() will then definitly return the memory to the OS. Unfortunatly, the coding required would probably not be straight-forward... Glibc will only convert malloc() to an mmap() on allocations > 128KB and I don't think PostgreSQL ever does that. Have a ncie day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] [GENERAL] A real currency type
Martijn van Oosterhout writes: > Really? The code creates ordinary types, operators and functions, all > of which are dumped fine by pg_dump. Dependancies are created which > should ensure the parts get dumped in the right order. What special > support in pg_dump were you envisioning? The dump should look the same as the commands originally used to create the type, which is surely not going to happen with that "SELECT create_tagged_type()" stuff barring pg_dump modifications. Otherwise we are nailing down not one but two representations of this feature that we'll have to support forevermore: what the users see and what's in pg_dump scripts. We've already learned that lesson the hard way several times, and are still trying to cope with the fallout in some places (serial columns for instance). Now I happen to think that SELECT create_tagged_type() is a horrid kluge anyway ;-) so I'm not proposing that pg_dump be changed to output that. What we would need is to design a command syntax that we're actually prepared to live with for the indefinite future, then implement it in the backend and teach pg_dump about it. What we *don't* want is a pg_dump representation that exposes implementation details. I would classify both the backing table for a tagged type's enum values, and the representation of its operators and functions, as implementation details. BTW, I share Andrew Dunstan's feeling that there's huge overlap here with support for mysql-like enum types. We ought to try to kill that bird with the same stone. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] A real currency type
On Tue, Mar 21, 2006 at 03:59:31PM -0500, Andrew Dunstan wrote: > >I think such types would be better implemented as some sort of > >structured type, possibly with constructors and methods and all the > >other stuff that SQL talks about. We don't have all of that yet -- > >maybe we don't need all of it immediately -- but before we start > >endorsing many of these types I'd like to see some consideration given > >to this issue. > > Yeah, looks interesting but let's find the wrinkles. I was wondering if > it might have relevance to what I wanted to do with enumeration types, > i.e. we would tag each one with its particular enumeration id. I have considered making enumeration types with this. It would probably work but kind of overkill. The main problem being that this code is seriously aimed at creating new types that you intend to do operations on, create operators for. An enumeration is just that, an enumeration. > I also don't like the idea of it being done with user tables - if this > is core material then the tags should go in the catalog, ISTM. But the user is providing the list of tags when creating the type. Unless you are proposing copying the tag table to the catalog when the type is created? You can't store all the tags for all tagged types in one tag table either. At least, that wasn't what I was envisiging. See the currency type where the tag-table also stores the format string for output. No other tagged type is going to need that information. SQL types with constructors and methods might provide this also, I don't know. My purpose with this was allowing people to simply create new types with specific behaviour without writing functions in C. Right now type input/output functions cannot be written in anything other than C. Here are a few C functions that handle these new types in a generic way and user can write little pl/pgsql wrappers to customise the output. Have a ncie day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Automatically setting work_mem
On Sat, 2006-03-18 at 13:21 -0800, Luke Lonergan wrote: > In short, faster performance through more aggressive runtime compilation. A > JIT for the database kernel. It's not like I'm on shaky ground here - other > commercial DBMS have done it for over a decade. I think what Luke may be referring to is the ability to compile WHERE clauses to remove the bottleneck around Eval for complex searches. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] A real currency type
On Tue, Mar 21, 2006 at 04:10:21PM -0500, Tom Lane wrote: > Martijn van Oosterhout writes: > > I'd like it to be considered for inclusion. > > The description page lists sufficiently many unresolved issues that > I'd have to call it "not ready for prime time" ... even assuming there > are not any issues you failed to identify. One showstopper I can cite > immediately is the lack of any pg_dump support. Really? The code creates ordinary types, operators and functions, all of which are dumped fine by pg_dump. Dependancies are created which should ensure the parts get dumped in the right order. What special support in pg_dump were you envisioning? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] qsort, once again
Greg Stark <[EMAIL PROTECTED]> writes: > My question explicitly recognized that possibility. I'm just a little > skeptical since the comparison function in Postgres is often not some simple > bit of tightly optimized C code, but rather a complex locale sensitive > comparison function or even a bit of SQL expression to evaluate. Yeah, I'd guess the same way, but OTOH at least a few people have reported that our qsort code is consistently faster than glibc's (and that was before this fix). See this thread: http://archives.postgresql.org/pgsql-hackers/2005-12/msg00610.php Currently I believe that we only use our qsort on Solaris, not any other platform, so if you think that glibc's qsort is better then you've already got your wish. It seems to need more investigation though. In particular, I'm thinking that the various adjustments we've made to the sort support code over the past month probably invalidate any previous testing of the point, and that we ought to go back and redo those comparisons. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] qsort, once again
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > That looks better both on average and in the worst case. Are the time > > constants that much worse that the merge sort still takes longer? > > Keep in mind that this is only counting the number of > comparison-function calls; it's not accounting for any other effects. > In particular, for a large sort operation quicksort might win because of > its more cache-friendly memory access patterns. My question explicitly recognized that possibility. I'm just a little skeptical since the comparison function in Postgres is often not some simple bit of tightly optimized C code, but rather a complex locale sensitive comparison function or even a bit of SQL expression to evaluate. Cache effectiveness is may be a minimal factor anyways when the comparison is executing more than a minimal amount of code. And one extra comparison is going to cost a lot more too. -- greg ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Question about MemoryContexts and functions that returns
Thomas Hallgren <[EMAIL PROTECTED]> writes: > As it turns out, I'm not supposed to allocate the returned tuple in the > caller context. Where do you get that from? plpgsql and plperl both do it that way AFAICS. Are you testing in an --enable-cassert build? The memory-clobber behavior that that turns on is really essential for finding dangling-pointer problems ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] A real currency type
Martijn van Oosterhout writes: > I'd like it to be considered for inclusion. The description page lists sufficiently many unresolved issues that I'd have to call it "not ready for prime time" ... even assuming there are not any issues you failed to identify. One showstopper I can cite immediately is the lack of any pg_dump support. I could see putting this in contrib, perhaps, but to become part of core it'd need to be better designed and better integrated. That probably means fixing some limitations in the current core code (for instance the need for the auxiliary-type kluge). Peter's point that there may exist SQL2003 features that cover the same ground is also well taken ... we should investigate that before inventing nonstandard stuff that we'll be stuck with supporting forevermore. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] A real currency type
Peter Eisentraut wrote: Bruce Momjian wrote: This looks very interesting. Should we add it to the core distribution? I think such types would be better implemented as some sort of structured type, possibly with constructors and methods and all the other stuff that SQL talks about. We don't have all of that yet -- maybe we don't need all of it immediately -- but before we start endorsing many of these types I'd like to see some consideration given to this issue. Yeah, looks interesting but let's find the wrinkles. I was wondering if it might have relevance to what I wanted to do with enumeration types, i.e. we would tag each one with its particular enumeration id. I also don't like the idea of it being done with user tables - if this is core material then the tags should go in the catalog, ISTM. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] qsort, once again
Greg Stark <[EMAIL PROTECTED]> writes: > That looks better both on average and in the worst case. Are the time > constants that much worse that the merge sort still takes longer? Keep in mind that this is only counting the number of comparison-function calls; it's not accounting for any other effects. In particular, for a large sort operation quicksort might win because of its more cache-friendly memory access patterns. The whole question of our qsort vs the system library's qsort probably needs to be revisited, however, now that we've identified and fixed this particular performance issue. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] A real currency type
Bruce Momjian wrote: > This looks very interesting. Should we add it to the core > distribution? I think such types would be better implemented as some sort of structured type, possibly with constructors and methods and all the other stuff that SQL talks about. We don't have all of that yet -- maybe we don't need all of it immediately -- but before we start endorsing many of these types I'd like to see some consideration given to this issue. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] qsort, once again
Tom Lane <[EMAIL PROTECTED]> writes: > and here are the results using glibc's qsort, which of course isn't > quicksort at all but some kind of merge sort: > ... > Overall: average cratio 0.63 over 525 tests That looks better both on average and in the worst case. Are the time constants that much worse that the merge sort still takes longer? -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create Index behaviour)
Last month I wrote: > It seems clear that our qsort.c is doing a pretty awful job of picking > qsort pivots, while glibc is mostly managing not to make that mistake. I re-ran Gary's test script using the just-committed improvements to qsort.c, and got pretty nice numbers (attached --- compare to http://archives.postgresql.org/pgsql-performance/2006-02/msg00227.php). So it was wrong to blame his problems on the pivot selection --- the culprit was that ill-considered switch to insertion sort. regards, tom lane 100 runtimes for latest port/qsort.c, sorted ascending: Time: 335.481 ms Time: 335.606 ms Time: 335.932 ms Time: 336.039 ms Time: 336.182 ms Time: 336.231 ms Time: 336.711 ms Time: 336.721 ms Time: 336.971 ms Time: 336.982 ms Time: 337.036 ms Time: 337.190 ms Time: 337.223 ms Time: 337.312 ms Time: 337.350 ms Time: 337.423 ms Time: 337.523 ms Time: 337.528 ms Time: 337.565 ms Time: 337.566 ms Time: 337.732 ms Time: 337.741 ms Time: 337.744 ms Time: 337.786 ms Time: 337.790 ms Time: 337.898 ms Time: 337.905 ms Time: 337.952 ms Time: 337.976 ms Time: 338.017 ms Time: 338.123 ms Time: 338.206 ms Time: 338.306 ms Time: 338.514 ms Time: 338.594 ms Time: 338.597 ms Time: 338.683 ms Time: 338.705 ms Time: 338.729 ms Time: 338.748 ms Time: 338.816 ms Time: 338.958 ms Time: 338.963 ms Time: 338.997 ms Time: 339.074 ms Time: 339.106 ms Time: 339.134 ms Time: 339.159 ms Time: 339.226 ms Time: 339.260 ms Time: 339.289 ms Time: 339.341 ms Time: 339.500 ms Time: 339.585 ms Time: 339.595 ms Time: 339.774 ms Time: 339.897 ms Time: 339.927 ms Time: 340.064 ms Time: 340.133 ms Time: 340.172 ms Time: 340.219 ms Time: 340.261 ms Time: 340.323 ms Time: 340.708 ms Time: 340.761 ms Time: 340.785 ms Time: 340.900 ms Time: 340.986 ms Time: 341.339 ms Time: 341.564 ms Time: 341.707 ms Time: 342.155 ms Time: 342.213 ms Time: 342.452 ms Time: 342.515 ms Time: 342.540 ms Time: 342.928 ms Time: 343.548 ms Time: 343.663 ms Time: 344.192 ms Time: 344.952 ms Time: 345.152 ms Time: 345.174 ms Time: 345.444 ms Time: 346.848 ms Time: 348.144 ms Time: 348.842 ms Time: 354.550 ms Time: 356.877 ms Time: 357.475 ms Time: 358.487 ms Time: 364.178 ms Time: 370.730 ms Time: 493.098 ms Time: 648.009 ms Time: 849.345 ms Time: 860.616 ms Time: 936.800 ms Time: 1727.085 ms ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Automatically setting work_mem
On Fri, 2006-03-17 at 09:46 -0500, Tom Lane wrote: > "Qingqing Zhou" <[EMAIL PROTECTED]> writes: > > So what's the difference between these two strategy? > > (1) Running time: do they use the same amount of memory? Why option 2 is > > better than 1? > > (2) Idle time: after sort done, option 1 will return all 1024 to the OS and > > 2 will still keep 512? > > Point 2 is actually a serious flaw in Simon's proposal, because there > is no portable way to make malloc return freed memory to the OS. Some > mallocs will do that ... in some cases ... but many simply don't ever > move the brk address down. It's not an easy thing to do when the arena > gets cluttered with a lot of different alloc chunks and only some of > them get freed. I'm aware of that objection and agree its an issue... One of the situations I am looking at is larger queries with multiple sorts in them. I'm getting some reasonable results for final merge even after releasing lots of memory (say 50-90%). memtuples array is not required at all for randomAccess sorts (100% reduction). The largest requirement for memory is the run building during performsort. That portion of the code is not concurrently executed within the same query. If we can reduce memory usage after that phase completes then we stand a chance of not overusing memory on a big query and not being able to reclaim it. So overall memory usage could be as low as work_mem + (numsorts * 0.1 * work_mem) which is a lot less than numsorts * work_mem. (e.g. 130% of work_mem rather than 300% work_mem). > So the semantics we'd have to adopt is that once a backend claims some > "shared work mem", it keeps it until process exit. I don't think that > makes the idea worthless, because there's usually a clear distinction > between processes doing expensive stuff and processes doing cheap > stuff. But it's definitely a limitation. ...Hopefully less so with mem reduction changes. The other way is of course to allocate *all* sort/hash/big stuff space out of shared memory and then let all the backends fight it out (somehow...) to see who gets access to it. That way backends stay small and we have well bounded memory usage. > Also, if you've got a process > doing expensive stuff, it's certainly possible to expect the user to > just increase work_mem locally. Doing that is fine, but you have to retune the system every time the memory usage changes for any reason. So most of the time manual tuning has to be very conservative to avoid getting it wrong. > My own thoughts about the problems with our work_mem arrangement are > that the real problem is the rule that we can allocate work_mem per sort > or hash operation; this makes the actual total memory use per backend > pretty unpredictable for nontrivial queries. I don't know how to fix > this though. The planner needs to know the work_mem that will be used > for any one of these operations in order to estimate costs, so simply > trying to divide up work_mem among the operations of a completed plan > tree is not going to improve matters. Spent about 5 hours discussing that and the best answer was "use queuing" = = = = Anyway, thinking just about sort, I've got the following concrete suggestions (first two of which coded and tested): 1. I originally picked MERGE_BUFFER_SIZE at 32 blocks as a guess. Better test results show that is indeed the optimum when we take into account both intermediate and final merging. However, the preferred buffer size would be about 256 blocks in the case that Nruns << Ntapes i.e. when work_mem is set high. In this case memory is reallocated to reduce the overall usage after "performsort done" has happened. 2. When a tape runs out of tuples, its memory is reallocated to remaining tapes to increase their I/O efficiency. This should help to increase performance for smaller work_mem settings with large sorts, or anything where the final merge Nruns is close to Ntapes. You can see this occurring in the example below. The reallocation is either done uniformly or all onto a single tape, depending upon the preread pattern. This mostly doesn't occur with well sorted output, so there is little overhead from doing this in the general case. Right now, large sort performance is very good, whereas smaller sort perfomance is still fairly bad. 3. We implement new merge alogorithm as Tom suggested... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] A real currency type
On Tue, Mar 21, 2006 at 02:00:14PM -0500, Gregory Maxwell wrote: > On 3/21/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > ISTM that having a currency type is pretty common for most databases; I > > don't really see any reason not to just include it. Likewise for a type > > that actually stores timezone info with a timestamp. > > This really should be generalized to work with all the base types > because there are strong use cases for each. (timezones with > timestamps, currencies with numeric, physical units with floats and > ints) Have you looked at the code? It *is* generalised. All you need to do is provide a table of tags and indicate the base-type. Hence: currency type = table of currencies + numeric base type timestamp+zone type = table of timezones + timestamp base type physical unit = table of units + numeric base type These are examples already included in the code, but I'm sure people can come up with more. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
[HACKERS] [GENERAL] A real currency type
On 3/21/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > ISTM that having a currency type is pretty common for most databases; I > don't really see any reason not to just include it. Likewise for a type > that actually stores timezone info with a timestamp. This really should be generalized to work with all the base types because there are strong use cases for each. (timezones with timestamps, currencies with numeric, physical units with floats and ints) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Question about MemoryContexts and functions that returns
David, Thanks for the tip. A diff on the plperl source was really helpful. As it turns out, I'm not supposed to allocate the returned tuple in the caller context. Apparently, PostgreSQL will always make a copy of it. I find this a bit inconsistent with how other return values are handled. PL/Java initially had some problems when I trusted that values where copied when in fact they where not. Has the function call semantics changed in this respect? Kind Regards, Thomas Hallgren David Fetter wrote: On Mon, Mar 20, 2006 at 11:47:41AM +0100, Thomas Hallgren wrote: Hi, A PL/Java user reports that his backend runs out of memory when he uses PL/Java to execute huge queries towards a remote database and return the result. PL/Java is designed not to collect data in memory when it returns result sets. Each call to the function handler will be dispatched to the corresponding 'ResultSet.next()' in order to retrieve and propagate one row at a time. Yet, it seems the data is collected somewhere. An excerpt from the user at the time he runs out of memory looks like this: A similar thing happened in PL/Perl up until recently. Check Neil Conway's patches to that for hints :) Cheers, D ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] qsort, once again
"Dann Corbit" <[EMAIL PROTECTED]> writes:
> Well, my point was that it is a snap to implement and test.
Well, having done this, I have to eat my words: it does seem to be a
pretty good idea.
The following test numbers are using Bentley & McIlroy's test framework,
but modified to test only the case N=1 rather than the four smaller
N values they originally used. I did that because it exposes quadratic
behavior more obviously, and the variance in N made it harder to compare
comparison ratios for different cases. I also added a "NEARSORT" test
method, which sorts the input distribution and then exchanges two
elements chosen at random. I did that because I was concerned that
nearly sorted input would be the worst case for the presorted-input
check, as it would waste the most cycles before failing on such input.
With our existing qsort code, the results look like
distribution SAWTOOTH: max cratio 94.17, min 0.08, average 1.56 over 105 tests
distribution RAND: max cratio 1.06, min 0.08, average 0.51 over 105 tests
distribution STAGGER: max cratio 6.08, min 0.23, average 1.01 over 105 tests
distribution PLATEAU: max cratio 94.17, min 0.08, average 2.12 over 105 tests
distribution SHUFFLE: max cratio 94.17, min 0.23, average 1.92 over 105 tests
method COPY: max cratio 6.08, min 0.08, average 0.72 over 75 tests
method REVERSE: max cratio 5.34, min 0.08, average 0.69 over 75 tests
method FREVERSE: max cratio 94.17, min 0.08, average 5.71 over 75 tests
method BREVERSE: max cratio 3.86, min 0.08, average 1.41 over 75 tests
method SORT: max cratio 0.82, min 0.08, average 0.31 over 75 tests
method NEARSORT: max cratio 0.82, min 0.08, average 0.36 over 75 tests
method DITHER: max cratio 5.52, min 0.18, average 0.77 over 75 tests
Overall: average cratio 1.42 over 525 tests
("cratio" is the ratio of the actual number of comparison function calls
to the theoretical expectation, N log2(N))
That's pretty awful: there are several test cases that make it use
nearly 100 times the expected number of comparisons.
Removing the swap_cnt test to bring it close to B&M's original
recommendations, we get
distribution SAWTOOTH: max cratio 3.85, min 0.08, average 0.70 over 105 tests
distribution RAND: max cratio 1.06, min 0.08, average 0.52 over 105 tests
distribution STAGGER: max cratio 6.08, min 0.58, average 1.12 over 105 tests
distribution PLATEAU: max cratio 3.70, min 0.08, average 0.34 over 105 tests
distribution SHUFFLE: max cratio 3.86, min 0.86, average 1.24 over 105 tests
method COPY: max cratio 6.08, min 0.08, average 0.76 over 75 tests
method REVERSE: max cratio 5.34, min 0.08, average 0.75 over 75 tests
method FREVERSE: max cratio 4.56, min 0.08, average 0.73 over 75 tests
method BREVERSE: max cratio 3.86, min 0.08, average 1.41 over 75 tests
method SORT: max cratio 0.86, min 0.08, average 0.56 over 75 tests
method NEARSORT: max cratio 0.86, min 0.08, average 0.56 over 75 tests
method DITHER: max cratio 3.73, min 0.18, average 0.72 over 75 tests
Overall: average cratio 0.78 over 525 tests
which is a whole lot better as to both average and worst cases.
I then added some code to check for presorted input (just after the
n<7 insertion sort code):
#ifdef CHECK_SORTED
presorted = 1;
for (pm = (char *) a + es; pm < (char *) a + n * es; pm += es)
{
if (cmp(pm - es, pm) > 0)
{
presorted = 0;
break;
}
}
if (presorted)
return;
#endif
This gives
distribution SAWTOOTH: max cratio 3.88, min 0.08, average 0.62 over 105 tests
distribution RAND: max cratio 1.06, min 0.08, average 0.46 over 105 tests
distribution STAGGER: max cratio 6.15, min 0.08, average 0.98 over 105 tests
distribution PLATEAU: max cratio 3.79, min 0.08, average 0.31 over 105 tests
distribution SHUFFLE: max cratio 3.91, min 0.08, average 1.09 over 105 tests
method COPY: max cratio 6.15, min 0.08, average 0.72 over 75 tests
method REVERSE: max cratio 5.34, min 0.08, average 0.76 over 75 tests
method FREVERSE: max cratio 4.58, min 0.08, average 0.73 over 75 tests
method BREVERSE: max cratio 3.91, min 0.08, average 1.44 over 75 tests
method SORT: max cratio 0.08, min 0.08, average 0.08 over 75 tests
method NEARSORT: max cratio 0.89, min 0.08, average 0.39 over 75 tests
method DITHER: max cratio 3.73, min 0.18, average 0.72 over 75 tests
Overall: average cratio 0.69 over 525 tests
So the worst case seems only very marginally worse, and there is a
definite improvement in the average case, even for inputs that aren't
entirely sorted. Importantly, the "near sorted" case that I thought
might send it into quadratic behavior doesn't seem to do that.
So, unless anyone wants to do further testing, I'll go ahead and commit
these changes.
regards, tom lane
PS: Just as a comparison point, here are the results when testing HPUX's
library qsort:
distribution SAWTOOTH: max cratio 7.00, min 0.0
Re: [HACKERS] [GENERAL] A real currency type
On Tue, Mar 21, 2006 at 09:39:09AM -0800, Josh Berkus wrote: > Bruce, Martijin, > > > > This looks very interesting. Should we add it to the core distribution? > > > > Excellent question. As yet I have received very little feedback on it, > > though it does work as advertised. I have had some people complain that > > while they'd like to use it (especially the timestamp-that-remembers- > > the-timezone), they don't like the idea of an external module. > > So, how about adding it to contrib for one version? ISTM that having a currency type is pretty common for most databases; I don't really see any reason not to just include it. Likewise for a type that actually stores timezone info with a timestamp. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] A real currency type
On Tue, Mar 21, 2006 at 06:15:29PM +0100, Pailloncy Jean-Gerard wrote: > >>This looks very interesting. Should we add it to the core > >>distribution? > > > >Excellent question. As yet I have received very little feedback on it, > >though it does work as advertised. I have had some people complain > >that > >while they'd like to use it (especially the timestamp-that-remembers- > >the-timezone), they don't like the idea of an external module. > > > >I suppose the are a few technical issues that could be raised, like > >the > >fact that it searches user tables during the parse phase, but this > >is a > >generic problem with non-immutable type input functions. > > > >I'd like it to be considered for inclusion. If the interest is there I > >can make any changes people suggest. > There was an old thread on the list about "extended type". > > The general idea was to add a attribute to each column to represent > the "unit" (as for physical measure). > > I had worked on a development system that use this kind of "extended > type" and it was very good to catch errors by checking the > compatibility of extended type. While I'm not sure it makes sense to include such an 'extended type' in the base system, it would be a neat project to have on pgFoundry. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] A real currency type
Bruce, Martijin, > > This looks very interesting. Should we add it to the core distribution? > > Excellent question. As yet I have received very little feedback on it, > though it does work as advertised. I have had some people complain that > while they'd like to use it (especially the timestamp-that-remembers- > the-timezone), they don't like the idea of an external module. So, how about adding it to contrib for one version? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] A real currency type
This looks very interesting. Should we add it to the core distribution? Excellent question. As yet I have received very little feedback on it, though it does work as advertised. I have had some people complain that while they'd like to use it (especially the timestamp-that-remembers- the-timezone), they don't like the idea of an external module. I suppose the are a few technical issues that could be raised, like the fact that it searches user tables during the parse phase, but this is a generic problem with non-immutable type input functions. I'd like it to be considered for inclusion. If the interest is there I can make any changes people suggest. There was an old thread on the list about "extended type". The general idea was to add a attribute to each column to represent the "unit" (as for physical measure). I had worked on a development system that use this kind of "extended type" and it was very good to catch errors by checking the compatibility of extended type. Cordialement, Jean-Gérard Pailloncy ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Problems with CREATE AGGREGATE and user defined state type.
Magnus Jonsson <[EMAIL PROTECTED]> writes: > I discoverd what seems to be a bug in postgresql 7.4.12 (also on > 7.4.7). AFAIR, 7.4 and earlier didn't really have a sane way of representing NULL composite-type values, so this aggregate isn't going to work before 8.0. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Pgbuildfarm-members] guppie: 64MB RAM too small?
On Tue, Mar 21, 2006 at 09:59:40AM -0500, Tom Lane wrote: > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > At the time it sets max_connections there is no server to test against. > > initdb in fact never uses a standard client connection at all, and never > > starts postmaster. To do a check on max_connections you would have to > > start postmaster and then try to start that many client connections. > > max_connections *is* checked by initdb ... although only to the extent > of verifying we can make that many semaphores. Ok, I thought there was at least some kind of check in there. Maybe it should try and determine how many processes the postmaster user is allowed as well; presumably something like ulimit would show this. > The parallel regression tests are not a particularly great reference > point for this anyway, because for each parallel test case you have not > only a server process, but a psql process, and in most shells a parent > shell process for the psql, ie 3x the nominal level of parallelism, > all running under the postgres userid. This isn't the normal usage > scenario, so it would not be reasonable to restrict max_connections to > 1/3 the number of user processes per userid. Certainly; it doesn't make sense to be mucking around just for the sake of make check. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Problems with CREATE AGGREGATE and user defined state type.
Hello, I discoverd what seems to be a bug in postgresql 7.4.12 (also on 7.4.7). I have an example located at http://foo.fot.nu/psql/ with a simple example that delevers the correct result on 8.1 (tested by some nice person at #postgresql). It seems like the state variable is reseted every time a new grouped value pops up. /M -- Magnus Jonsson <[EMAIL PROTECTED]> I'm here looking for an answer, but I'm not sure about the question... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [Pgbuildfarm-members] guppie: 64MB RAM too small?
Andrew Dunstan <[EMAIL PROTECTED]> writes: > At the time it sets max_connections there is no server to test against. > initdb in fact never uses a standard client connection at all, and never > starts postmaster. To do a check on max_connections you would have to > start postmaster and then try to start that many client connections. max_connections *is* checked by initdb ... although only to the extent of verifying we can make that many semaphores. The parallel regression tests are not a particularly great reference point for this anyway, because for each parallel test case you have not only a server process, but a psql process, and in most shells a parent shell process for the psql, ie 3x the nominal level of parallelism, all running under the postgres userid. This isn't the normal usage scenario, so it would not be reasonable to restrict max_connections to 1/3 the number of user processes per userid. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] A real currency type
On Tue, Mar 21, 2006 at 08:49:18AM -0500, Bruce Momjian wrote:
>
> This looks very interesting. Should we add it to the core distribution?
Excellent question. As yet I have received very little feedback on it,
though it does work as advertised. I have had some people complain that
while they'd like to use it (especially the timestamp-that-remembers-
the-timezone), they don't like the idea of an external module.
I suppose the are a few technical issues that could be raised, like the
fact that it searches user tables during the parse phase, but this is a
generic problem with non-immutable type input functions.
I'd like it to be considered for inclusion. If the interest is there I
can make any changes people suggest.
Have a nice day,
>
> ---
>
> Martijn van Oosterhout wrote:
> -- Start of PGP signed section.
> > For a while I've been wondering about making a type that was really a
> > shell around a base type that tagged the type in some way. For example,
> > associating a currency with a numeric and complaining about additions
> > between mismatches.
> >
> > Well, I did it and it's available here:
> > http://svana.org/kleptog/pgsql/taggedtypes.html
> >
> > Below some examples of it in action. Yes, that's a timestamp that
> > remembers the timezone. Neat huh?
> >
> > Tested on 7.4 and a recent 8.1devel so it should work for most people.
> > Installation reports welcome. Note, this is beta software, don't run it
> > on your production server. Thanks.
> >
> > Have a nice day,
> >
> >
> > test=# select '5.6 USD'::currency + '4.5 USD'::currency;;
> > ?column?
> > ---
> > 10.10 USD
> > (1 row)
> >
> > test=# select '5.6 USD'::currency + '4.5 AUD'::currency;;
> > ERROR: Using operator +(currency,currency) with incompatable tags (USD,AUD)
> > test=# select c1, print_currency(c1) from c;
> > c1 | print_currency
> > +
> > 232.44 USD | US$232.44
> > 21.20 EUR | ? 21.20
> > -13.44 AUD | AU$-13.44
> > 0.01 USD | US$ 0.01
> > 14.00 AUD | AU$ 14.00
> > (5 rows)
> >
> > test=# select 5.4*c1 from c where tag(c1) = 'AUD';
> > ?column?
> >
> > -72.58 AUD
> > 75.60 AUD
> > (2 rows)
> >
> > test=# select t, "timestamp"(t), date_part('hour',t) from c;
> > t| timestamp | date_part
> > -+-+---
> > 2005-08-14 02:00:00+02 Europe/Amsterdam | 2005-08-14 02:00:00 | 2
> > 2005-08-14 02:00:00+02 Australia/Sydney | 2005-08-14 10:00:00 |10
> > 2005-08-14 02:00:00+02 Asia/Hong_Kong | 2005-08-14 08:00:00 | 8
> > 2005-08-14 02:00:00+02 America/New_York | 2005-08-13 20:00:00 |20
> > 2005-08-14 02:00:00+02 Asia/Kuwait | 2005-08-14 03:00:00 | 3
> > (5 rows)
> >
> > --
> > Martijn van Oosterhout http://svana.org/kleptog/
> > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > > tool for doing 5% of the work and then sitting around waiting for someone
> > > else to do the other 95% so you can sue them.
> -- End of PGP section, PGP failed!
>
> --
> Bruce Momjian http://candle.pha.pa.us
> SRA OSS, Inc. http://www.sraoss.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
--
Martijn van Oosterhout http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
signature.asc
Description: Digital signature
Re: [HACKERS] Problems with CREATE AGGREGATE and user defined state type.
delete my email from the list Regards, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Magnus Jonsson Sent: Tuesday, March 21, 2006 8:39 PM To: pgsql-hackers@postgresql.org Subject: [HACKERS] Problems with CREATE AGGREGATE and user defined state type. Hello, I discoverd what seems to be a bug in postgresql 7.4.12 (also on 7.4.7). I have an example located at http://foo.fot.nu/psql/ with a simple example that delevers the correct result on 8.1 (tested by some nice person at #postgresql). It seems like the state variable is reseted every time a new grouped value pops up. /M -- Magnus Jonsson <[EMAIL PROTECTED]> I'm here looking for an answer, but I'm not sure about the question... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL/XML extension
Pavel Stehule wrote: > Hello, > > This patch is well, I hope. I didn't look on it half year. Contains: SQL/XML > support + doc by D.Fetter > http://candle.pha.pa.us/mhonarc/patches_hold/msg00134.html Yes, I am just looking at it now. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Pgbuildfarm-members] guppie: 64MB RAM too small?
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Ok, lets go back to my original point then: initdb should be made to > check that you can actually open as many connections as it's trying to > set max_connections to. Apparently you are unaware that it's done that for a long time. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [Pgbuildfarm-members] guppie: 64MB RAM too small?
Jim C. Nasby wrote: Adding -hackers. [removing -buildfarm :-) ] Ok, lets go back to my original point then: initdb should be made to check that you can actually open as many connections as it's trying to set max_connections to. If it can't, it should drop max_connections down (and possibly add a note to postgresql.conf indicating that it did so). At the time it sets max_connections there is no server to test against. initdb in fact never uses a standard client connection at all, and never starts postmaster. To do a check on max_connections you would have to start postmaster and then try to start that many client connections. That's a lot of extra lifting to put into initdb for what is arguably at worst a rare problem. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [Pgbuildfarm-members] guppie: 64MB RAM too small?
Adding -hackers. On Tue, Mar 21, 2006 at 02:20:09PM +0100, Christian Mair wrote: > > > No, let's start again. > > > > The user's machine ran out of resources. That can't be because inbitdb set > > max_connections too low - if anything it has probably set them too high. I > > suggested that he could possibly limit resource use by limiting the number > > of concurrent connections "make check" would use, by using the *UNRELATED* > > MAX_CONNECTIONS=n make flag. This flag is not part of buildfarm - it > > predates buildfarm in fact. It's part of the postgres build system - look in > > pg_regress.sh and the associated make file. Buildfarm has support for it as > > shown in the sample config file. > > Yes, > Neither PostgreSQL nor the buildfarm code is to blame. > The problem was OpenBSD's restrictive default limit of max 64 > user processes. I raised that on OpenBSD's side and as you can see > guppie turned green :) Ok, lets go back to my original point then: initdb should be made to check that you can actually open as many connections as it's trying to set max_connections to. If it can't, it should drop max_connections down (and possibly add a note to postgresql.conf indicating that it did so). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2 planning features
Oleg Bartunov said: > Hi there, > > I and Teodor are going to give a talk on conference > "Corporate database systems-2006" here in Moscow and we need info about > new features planned in 8.2 release. Bruce, do you have a list of them > ? > > Speaking of which, I previously said that I intended to work on providing first class enumeration types for postgres for 8.2. It's now looking very unlikely that I'll have time to do that, so if any adventurous soul feels like doing this or is just looking for a project please speak up - I'm quite prepared to offer help and suggestions. You'll make thousands of migrating MySQL users happy. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] A real currency type
This looks very interesting. Should we add it to the core distribution?
---
Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> For a while I've been wondering about making a type that was really a
> shell around a base type that tagged the type in some way. For example,
> associating a currency with a numeric and complaining about additions
> between mismatches.
>
> Well, I did it and it's available here:
> http://svana.org/kleptog/pgsql/taggedtypes.html
>
> Below some examples of it in action. Yes, that's a timestamp that
> remembers the timezone. Neat huh?
>
> Tested on 7.4 and a recent 8.1devel so it should work for most people.
> Installation reports welcome. Note, this is beta software, don't run it
> on your production server. Thanks.
>
> Have a nice day,
>
>
> test=# select '5.6 USD'::currency + '4.5 USD'::currency;;
> ?column?
> ---
> 10.10 USD
> (1 row)
>
> test=# select '5.6 USD'::currency + '4.5 AUD'::currency;;
> ERROR: Using operator +(currency,currency) with incompatable tags (USD,AUD)
> test=# select c1, print_currency(c1) from c;
> c1 | print_currency
> +
> 232.44 USD | US$232.44
> 21.20 EUR | ? 21.20
> -13.44 AUD | AU$-13.44
> 0.01 USD | US$ 0.01
> 14.00 AUD | AU$ 14.00
> (5 rows)
>
> test=# select 5.4*c1 from c where tag(c1) = 'AUD';
> ?column?
>
> -72.58 AUD
> 75.60 AUD
> (2 rows)
>
> test=# select t, "timestamp"(t), date_part('hour',t) from c;
> t| timestamp | date_part
> -+-+---
> 2005-08-14 02:00:00+02 Europe/Amsterdam | 2005-08-14 02:00:00 | 2
> 2005-08-14 02:00:00+02 Australia/Sydney | 2005-08-14 10:00:00 |10
> 2005-08-14 02:00:00+02 Asia/Hong_Kong | 2005-08-14 08:00:00 | 8
> 2005-08-14 02:00:00+02 America/New_York | 2005-08-13 20:00:00 |20
> 2005-08-14 02:00:00+02 Asia/Kuwait | 2005-08-14 03:00:00 | 3
> (5 rows)
>
> --
> Martijn van Oosterhout http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
-- End of PGP section, PGP failed!
--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] 8.2 planning features
Hi there, I and Teodor are going to give a talk on conference "Corporate database systems-2006" here in Moscow and we need info about new features planned in 8.2 release. Bruce, do you have a list of them ? Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] obtaining row locking information
I have attached pgrowlocks tested under current. -- Tatsuo Ishii SRA OSS, Inc. Japan > Tatsuo, have you developed a new version of this for 8.2? > > --- > > Tatsuo Ishii wrote: > > > Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > > > To accomplish this I need to add following function into > > > > storage/ipc/procarray.c. This is similar to BackendPidGetProc() except > > > > that it accepts xid as an argument. Any objection? > > > > > > > if (xid == 0) /* never match dummy > > > > PGPROCs */ > > > > return NULL; > > > > > > I think this test should be against InvalidTransactionId, not "0", and > > > the comment is wrong (you are suppressing matches against idle PGPROCs). > > > > > > Also note the comment at the top of the function: once you release > > > ProcArrayLock you have no guarantee that the result means anything at > > > all; and unlike ProcSendSignal, you have no reason to think that the > > > target backend can't quit before you get another cycle. It might be > > > better to return the pid directly rather than assuming it'll still be > > > meaningful to indirect through a returned pointer. > > > > Agreed. > > > > > Also, what are you going to do about prepared transactions? They can > > > hold locks but they don't have PIDs. On the whole, I'm not sure this > > > is a good idea at all, because of that. > > > > For prepared transactions, just showing "0" pids are enough, I > > think. Assuming that in practice most transactions are not prepared > > ones, I think the function is not perfect, but is usefull enough for > > most DBAs. > > -- > > Tatsuo Ishii > > > > -- > Bruce Momjian http://candle.pha.pa.us > SRA OSS, Inc. http://www.sraoss.com > > + If your life is a hard drive, Christ can be your backup. + > pgrowlocks.tar.gz Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
