Re: [HACKERS] Draft release notes
Ühel kenal päeval, N, 2006-09-14 kell 01:12, kirjutas Bruce Momjian: Here is an early draft of the release notes. It needs more polish and review: http://momjian.us/cgi-bin/pgrelease I will catch up on my email tomorrow, update the open items list for 8.2, and then return to the release notes for cleanup. You should leave my name out from plpython enchancements, the actual programming was all done by Sven. * Add named parameters to plpython args[] array (Hannu Krosing, Sven Suursoho) * Return composite-types from plpython as dictionary (Hannu Krosing, Sven Suursoho) * Return result-set from plpython as list, iterator or generator (Hannu Krosing, Sven Suursoho) -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Draft release notes
On 9/14/06, Bruce Momjian [EMAIL PROTECTED] wrote: Here is an early draft of the release notes. It needs more polish and review: http://momjian.us/cgi-bin/pgrelease AFAICS the log_duration behaviour change made by Tom a few days ago is not there. -- Guillaume ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Draft release notes
* Improve multicolumn GiST index (oleg,teodor) * GiST indexes now are clusterable (teodor) * tsearch2 improvements (oleg, teodor): - multibyte encoding support (including UTF8) - query rewriting support - improve ranking functions - thesaurus dictionary - Ispell dictionary now recognize MySpell format, used by OpenOffice. - support of GIN * new operators for one-dimensional array (@, @, ) with GIN support (teodor) Bruce Momjian wrote: Here is an early draft of the release notes. It needs more polish and review: http://momjian.us/cgi-bin/pgrelease I will catch up on my email tomorrow, update the open items list for 8.2, and then return to the release notes for cleanup. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Draft release notes
Here is an early draft of the release notes. It needs more polish and review: http://momjian.us/cgi-bin/pgrelease I will catch up on my email tomorrow, update the open items list for 8.2, and then return to the release notes for cleanup. * Allow regression tests to be run on Win32 without MinGW (Magnus, Tom) This is not entirsely correect. What was done was a re-implementation of pg_regress in C from shellscript. This is a prerequisite for running the tests completely without mingw, but you can't do that yet (there needs to be rules in the VC build stuff to deal with the regression output files that need to be edited, for example - this is done with sed from the Makefile today. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] AIX shared libraries
Tom Lane wrote: I think there's a reasonable argument that by installing a .a file that isn't a shared library, we are violating the platform's conventions. Hm. This seems possible with some moderate hacking on Makefile.shlib (certainly it'd be no more invasive than the existing Windows-specific platform variants). [...] Another issue with installing only .a is that there's no provision for versioning in .a library names ... what happens to someone who needs two generations of libpq on his machine? Ok, I have spent some time researching and thinking, and I have three proposals how to deal with linking on AIX. 1) Leave everything as it is and add the LDAP libraries to the AIX hack in Makefile.shlib. Pros: - Little work. Cons: - PostgreSQL will continue to be statically linked on AIX (unless somebody feeds configure the right LDFLAGS). 2) Remove the AIX hack from Makefile.shlib, add -brtl and -blibpath:$(rpathdir):*-L directories in LDPATH*:/usr/lib:/lib (this sets the AIX equivalent for RPATH) to LDFLAGS for AIX. Pros: - Dynamic linking on AIX. - The organization of the libraries (libpq.a static, libpq.so dynamic) is similar to other operating systems. Cons: - The library organization is counter-intuitive to AIX people, and most people will inadvertedly link statically when linking against libpq. - According to Rocco Altier it will not work on historic versions of AIX (no -brtl flag). 3) Major hacking in Makefile.shlib to achieve the following: - libXX.so.n is built from libXX.a in the traditional way. Then libXX.a is deleted, and recreated as archive containing libXX.so.n. - Linking takes place withOUT -brtl, but with -blibpath:... as in 2). - When the shared libs are installed, I see two options: a) copy (and overwrite) libXX.a to libdir, do not install libXX.so.n b) Look for existing libXX.a in libdir, extract all libXX.so.k from it, mark them LOADONLY with 'strip -e libXX.so.k', create a new libXX.a with these objects and the new libXX.so.n Pros: - Dynamic linking on AIX. - AIX-conforming organization of libraries. - In the case of 3)b), multiple versions of the library can be retained in the same archive. Linking is only possible with the latest versions, but old programs continue to work. - 3)a) will probably work on older versions of AIX (I hope there's a -blibpath flag). Cons: - Much work, particularly with 3)b). - Library organization on AIX will be different from other platforms. - 3)b) will probably not work on old versions of AIX (I read a posting that makes me believe that 'strip -e' was not around before 4.3.3. I am willing to implement whatever solution we decide upon. I personally would prefer 3)a), but am happy with anything except 1). Yours, Laurenz Albe I personally would prefer 3)a) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Draft release notes
The list of functions for: * Add SQL2003-standard statistical aggregates (Sergey Koposov) regr_intercept, regr_slope, regr_r2, corr, covar_samp, covar_pop, regr_avgx, regr_avgy, regr_sxy, regr_sxx, regr_syy, regr_count Also, I guess that the point * Aggregate functions now support multiple input arguments (Tom) should be * Aggregate functions now support multiple input arguments (Sergey Koposov, Tom) instead Regards, Sergey *** Sergey E. Koposov Max Planck Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Not-so-open items
Tom Lane [EMAIL PROTECTED] writes: I changed the locking thing I was worried about. Unless Greg wants to do some real-world performance measurements to confirm or refute that change, I think this can be closed. I could do some if you're curious but my feeling is that the conservative choice is the right choice here regardless of what those numbers would show. So yeah, it should be closed. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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
[HACKERS] Is there any utility to update the table whenever text file gets changed?
Is there any utility in postgresql which can do the following? The utility must update the table whenever there is any change in the text file. COPY command helps to do that, though this is not straight forward. Can it be automated? Thanks Dhanaraj ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Getting a move on for 8.2 beta
Hi, Jeremy, Jeremy Drake wrote: Another possibility would be to test these patches in some kind of virtual machine that gets blown away every X days, so that even if someone did get something malicious in there it wouldn't last long. Or just have a snapshot which is reverted after each run, and read-only access to files used to do the build. I know vmware supports this, probably others too... A chroot / fakeroot combined with unionfs should do the same, probably with less effort. There are other user-mode jail projects that also block networking. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Fixed length data types issue
Hi, Jim, Jim Nasby wrote: I'd love to have the ability to control toasting thresholds manually. This could result in a lot of speed improvements in cases where a varlena field isn't frequently accessed and will be fairly large, yet not large enough to normally trigger toasting. An address field would be a good example. Being able to force a field to be toasted before it normally would could drastically improve tuple density without requiring the developer to use a 'side table' to store the data. Sounds good. But I remember that the query planner underestimated sequential scans when lots of TOAST data was in the table. IIRC, The specific case (that was discussent on pgperform) was about 70 PostGIS geometries, amounting to about 35MB of TOAST data and only 2 or 3 pages in the actual table. The query planner used an sequential scan instead of an GIST index scan ( operator), leading to deTOASTing and processing all 35 MB of geometries, instead of just those 2 small ones that matched the index condition. So I think before we start toasting more, we should check whether the query planner could be affected negatively. It should have statistics about TOAST data, and then see whether he'd need to detoast for condition checking and for actual data fetching. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Interesting tight loop
Theo Schlossnagle [EMAIL PROTECTED] writes: But the interesting thing is that there were 4.6 million elements in the s-childXids list. Which is why it took so damn long. I can't quite figure out how I induced this state. It is an OLAP server with about 10-20 connection that run long queries (from 5 seconds to 24 hours). Wow, I think that means you've had 4.6 million subtransactions within the current transaction. Is this a PL/PgSQL function or a script that uses SAVEPOINT a lot? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] CSStorm occurred again by postgreSQL8.2
Tom Lane [EMAIL PROTECTED] writes: Anyway, given that there's this one nonobvious gotcha, there might be others. My recommendation is that we take this off the open-items list for 8.2 and revisit it in the 8.3 cycle when there's more time. I wonder if Theo's recent reported problem with 4.3M child xids changes the calculus on this. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Interesting tight loop
On Sep 14, 2006, at 7:03 AM, Gregory Stark wrote: Theo Schlossnagle [EMAIL PROTECTED] writes: But the interesting thing is that there were 4.6 million elements in the s-childXids list. Which is why it took so damn long. I can't quite figure out how I induced this state. It is an OLAP server with about 10-20 connection that run long queries (from 5 seconds to 24 hours). Wow, I think that means you've had 4.6 million subtransactions within the current transaction. Is this a PL/PgSQL function or a script that uses SAVEPOINT a lot? We don't use savepoint's too much. Maybe one or two across out 1k or so pl/pgsql procs. We use dbi-link which is plperl. Perhaps that is somehow creating subtransactions? // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.com/ ---(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] Draft release notes
On Thu, 2006-09-14 at 01:12 -0400, Bruce Momjian wrote: Here is an early draft of the release notes. It needs more polish and review: http://momjian.us/cgi-bin/pgrelease I will catch up on my email tomorrow, update the open items list for 8.2, and then return to the release notes for cleanup. A few changes: - Improve COPY performance (Alon Goldshuv) - Optimize the locking used by sequential scans (Qingqing Zhou) - Re-enable full_page_writes (Tom) This flag can now be used even with PITR. It is turned **on** (not off!) automatically between pg_start_backup() and pg_stop_backup() calls. - Add support for forcing a switch to a new xlog file (Simon Riggs) ...should include Tom - Improve performance of replaying WAL logs on a backup server (Simon Riggs) ...I describe this as Restartable Recovery... don't think it improves performance exactly, just avoids needing to re-run it from scratch My memory says this was eventually removed, even though it was committed for a time. Am I wrong? - Make EXPLAIN sampling smarter, to avoid excessive sampling delay (Martijn van Oosterhout) -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Interesting tight loop
Theo Schlossnagle [EMAIL PROTECTED] writes: We don't use savepoint's too much. Maybe one or two across out 1k or so pl/pgsql procs. Well if they're in a loop... We use dbi-link which is plperl. Perhaps that is somehow creating subtransactions? Ok, I more or less see what's going on. plperl creates a subtransaction whenever you execute an SPI query from inside a perl function. That's so that errors in the query can throw perl exceptions and be caught in the perl code. So if your DBI source is an SPI connection (and not a connection to some other database source) you will get a subtransaction for every remote_select() call. In addition, dbi-link seems to do its work by creating a trigger which fires once for every record you modify in its shadow table. I'm not sure what you're doing with those records but if your sending them on via an SPI connection to another table you'll get a subtransaction every time the trigger fires. It would be interesting to know which of these it is because in the former case it may be something that could be fixed. We only really need to remember subtransactions that have hit disk. But I rather suspect it's the latter case since it's easy to see you firing a trigger 4.3M times. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] CSStorm occurred again by postgreSQL8.2
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: Anyway, given that there's this one nonobvious gotcha, there might be others. My recommendation is that we take this off the open-items list for 8.2 and revisit it in the 8.3 cycle when there's more time. I wonder if Theo's recent reported problem with 4.3M child xids changes the calculus on this. Yeah, I was just looking at that. Removing useless entries from the child-xid list would presumably help him. Considering we're not even formally in beta yet, I'm probably being too conservative to recommend we not touch it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Interesting tight loop
Gregory Stark [EMAIL PROTECTED] writes: Ok, I more or less see what's going on. plperl creates a subtransaction whenever you execute an SPI query from inside a perl function. That's so that errors in the query can throw perl exceptions and be caught in the perl code. It might also be worthwhile modifying plperl to check $^S which indicates whether exceptions are going to be caught anywhere. If they're not going to be caught would it be possible to skip creating the nested transaction and allow them to force the whole transaction to be rolled back? I'm not clear though if there would there be any internal perl interpreter state that would get messed up if we just longjmp out from within perl code rather than allow perl exceptions to get us out. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Interesting tight loop
On Sep 14, 2006, at 8:19 AM, Gregory Stark wrote:
Theo Schlossnagle [EMAIL PROTECTED] writes:
We don't use savepoint's too much. Maybe one or two across out 1k
or so
pl/pgsql procs.
Well if they're in a loop...
We use dbi-link which is plperl. Perhaps that is somehow creating
subtransactions?
Ok, I more or less see what's going on. plperl creates a
subtransaction
whenever you execute an SPI query from inside a perl function.
That's so that
errors in the query can throw perl exceptions and be caught in the
perl code.
So if your DBI source is an SPI connection (and not a connection to
some other
database source) you will get a subtransaction for every
remote_select() call.
In addition, dbi-link seems to do its work by creating a trigger
which fires
once for every record you modify in its shadow table. I'm not
sure what
you're doing with those records but if your sending them on via an SPI
connection to another table you'll get a subtransaction every time
the trigger
fires.
It would be interesting to know which of these it is because in the
former
case it may be something that could be fixed. We only really need
to remember
subtransactions that have hit disk. But I rather suspect it's the
latter case
since it's easy to see you firing a trigger 4.3M times.
My remote_select() in DBI does a RETURN NEXT $row; You think that
might be the problem? If that's the case -- that needs to be fixed.
The metalevel of the remote_select is:
remote_select(query) {
handle = remote.prepare(query)
handle.execute;
while(row = handle.fetchrow_hashref) {
return_next $row;
}
handle.close;
return;
}
If that return_next is causing an subtransaction that would explain
my world of pain well.
// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] CSStorm occurred again by postgreSQL8.2
I wrote: Yeah, I was just looking at that. Removing useless entries from the child-xid list would presumably help him. Considering we're not even formally in beta yet, I'm probably being too conservative to recommend we not touch it. Actually ... wait a minute. We do not assign an XID to a subtransaction at all unless it writes a tuple to disk (see GetCurrentTransactionId and its callers). So this whole optimization idea is redundant. I see a bug though, which is that RecordSubTransactionAbort() calls GetCurrentTransactionId() before having verified that it needs to do anything. This means that we'll generate and then discard an XID uselessly in a failed subxact that didn't touch disk. Worth fixing, but it doesn't look like this is Theo's problem. Unless I'm missing something, Theo's problem must involve having done tuple updates in 4.6M different subtransactions. 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] Interesting tight loop
Theo Schlossnagle [EMAIL PROTECTED] writes: In production today (8.1.4), I ran into a backend process that wouldn't cancel right away -- minutes went by. It was in [0] TransactionIdIsCurrentTransactionId [1] HeapTupleSatisfiesSnapshot ... But the interesting thing is that there were 4.6 million elements in the s-childXids list. Which is why it took so damn long. Well, I don't think TransactionIdIsCurrentTransactionId() itself is to blame --- the loop over childXids is about as tight as such a loop could possibly be (two indirect fetches and a comparison per iteration). Even with 4.6M child Xids it could hardly take a second on any modern machine. I'm not inclined to add a CHECK_FOR_INTERRUPTS there. The problem should instead be blamed on something further down the call stack ... did you save the full stack by any chance? regards, tom lane ---(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] CSStorm occurred again by postgreSQL8.2
I wrote: I see a bug though, which is that RecordSubTransactionAbort() calls GetCurrentTransactionId() before having verified that it needs to do anything. This means that we'll generate and then discard an XID uselessly in a failed subxact that didn't touch disk. Well, it would be a bug except that RecordSubTransactionAbort isn't called unless the current subxact has an XID. Perhaps a comment would be appropriate but there's nothing to fix here. I think Theo's problem is probably somewhere else, too --- apparently it's not so much that TransactionIdIsCurrentTransactionId takes a long time as that something is calling it lots of times with no check for interrupt. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] AIX shared libraries
Albe Laurenz [EMAIL PROTECTED] writes: I personally would prefer 3)a) 3) Major hacking in Makefile.shlib to achieve the following: - libXX.so.n is built from libXX.a in the traditional way. Then libXX.a is deleted, and recreated as archive containing libXX.so.n. - Linking takes place withOUT -brtl, but with -blibpath:... as in 2). - When the shared libs are installed, I see two options: a) copy (and overwrite) libXX.a to libdir, do not install libXX.so.n Hm. The objection I see to this is that it will not support concurrent installation of multiple libpq versions. What about 4) Build and install only libXX.so.n, don't install libXX.a at all 5) As 4), plus actively remove any libXX.a seen in the install directory regards, tom lane ---(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] Release notes
On Tuesday 12 September 2006 14:49, Bruce Momjian wrote:
Andrew Dunstan wrote:
Bruce Momjian wrote:
I again will not be able to complete the release notes today as
promised. My next target date is Monday, August 18. Sorry.
Will that be in a few years, or are you traveling backwards in time? ;-)
Sorry, September 18. I will probably be done before then, but it seems
best to set a date I know I will hit.
Here we go again with another developer who keeps making endless promises for
vaporware patches that never show up. We've already set on-disk bit-map
indexes straight on this and I think giving you special treatment sets a bad
tone for the project. At this point I think we have to cut the release notes
from this release... maybe they can be added back in for 8.3.
;^)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(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] Release notes
Robert Treat wrote: On Tuesday 12 September 2006 14:49, Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I again will not be able to complete the release notes today as promised. My next target date is Monday, August 18. Sorry. Will that be in a few years, or are you traveling backwards in time? ;-) Sorry, September 18. I will probably be done before then, but it seems best to set a date I know I will hit. Here we go again with another developer who keeps making endless promises for vaporware patches that never show up. We've already set on-disk bit-map indexes straight on this and I think giving you special treatment sets a bad tone for the project. At this point I think we have to cut the release notes from this release... maybe they can be added back in for 8.3. Very good one! -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] CSStorm occurred again by postgreSQL8.2
Tom Lane wrote: I wrote: I see a bug though, which is that RecordSubTransactionAbort() calls GetCurrentTransactionId() before having verified that it needs to do anything. This means that we'll generate and then discard an XID uselessly in a failed subxact that didn't touch disk. Well, it would be a bug except that RecordSubTransactionAbort isn't called unless the current subxact has an XID. Perhaps a comment would be appropriate but there's nothing to fix here. I think Theo's problem is probably somewhere else, too --- apparently it's not so much that TransactionIdIsCurrentTransactionId takes a long time as that something is calling it lots of times with no check for interrupt. Could it be something like heap_lock_tuple? It calls MultiXactIdWait, which calls GetMultXactIdMembers and TransactionIdIsCurrentTransactionId on each member. (heap_update and heap_delete do the same thing). I must admit I didn't read Theo's description on his scenario though. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] AIX shared libraries
Tom Lane wrote: 3) Major hacking in Makefile.shlib to achieve the following: - libXX.so.n is built from libXX.a in the traditional way. Then libXX.a is deleted, and recreated as archive containing libXX.so.n. - Linking takes place withOUT -brtl, but with -blibpath:... as in 2). - When the shared libs are installed, I see two options: a) copy (and overwrite) libXX.a to libdir, do not install libXX.so.n Hm. The objection I see to this is that it will not support concurrent installation of multiple libpq versions. What about 4) Build and install only libXX.so.n, don't install libXX.a at all Won't work - the linker looks for libXX.so and won't find libXX.so.n. If you create a symbolic link libXX.so -- libXX.so.n, you can link, but the executable will depend on libXX.so and not on libXX.so.n. Moreover, you cannot link statically any more because in a static link only libXX.a files will be searched... 5) As 4), plus actively remove any libXX.a seen in the install directory Same problem. Yours, Laurenz Albe ---(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] Release notes
Bruce Momjian wrote: Robert Treat wrote: On Tuesday 12 September 2006 14:49, Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I again will not be able to complete the release notes today as promised. My next target date is Monday, August 18. Sorry. Will that be in a few years, or are you traveling backwards in time? ;-) Sorry, September 18. I will probably be done before then, but it seems best to set a date I know I will hit. Here we go again with another developer who keeps making endless promises for vaporware patches that never show up. We've already set on-disk bit-map indexes straight on this and I think giving you special treatment sets a bad tone for the project. At this point I think we have to cut the release notes from this release... maybe they can be added back in for 8.3. Very good one! Yeah, it was funny, but it points a problem which is that we are overloading you to do the release notes thing. I agree that we should push individual developers to include release notes updates on the patches they submit. They are easier to write than the documentation update in any case (which as you say, not everyone submits), mainly because they are way shorter. (Or maybe not _push_ them to do that, but at least not forbid updating the release notes which AFAIK is the current policy.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] New version of money type
For years I have been promising that a 64 bit version of the money type
was on the way. Here it is. So far it compiles and I have done some
basic testing on it and it seems to work fine. Note that the currency
symbol is also dropped on output as well but it is accepted on input.
darcy=# select '$92,233,720,368,547,758.07'::money;
money
92,233,720,368,547,758.07
(1 row)
Index: src/backend/utils/adt/cash.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/cash.c,v
retrieving revision 1.68
diff -u -p -u -r1.68 cash.c
--- src/backend/utils/adt/cash.c14 Jul 2006 14:52:23
- 1.68 +++ src/backend/utils/adt/cash.c 14 Sep 2006
14:28:30 - @@ -1,13 +1,17 @@
/*
* cash.c
* Written by D'Arcy J.M. Cain
+ * darcy@druid.net
+ * http://www.druid.net/darcy/
*
* Functions to allow input and output of money normally but store
- * and handle it as int4s
+ * and handle it as 64 bit ints
*
* A slightly modified version of this file and a discussion of the
* workings can be found in the book Software Solutions in C by
- * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7.
+ * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7 except that
+ * this version handles 64 bit numbers and so can hold values up to
+ * $92,233,720,368,547,758.07.
*
* $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.68 2006/07/14
14:52:23 momjian Exp $ */
@@ -23,17 +27,12 @@
#include utils/cash.h
#include utils/pg_locale.h
-
-static const char *num_word(Cash value);
-
-/* when we go to 64 bit values we will have to modify this */
-#define CASH_BUFSZ 24
+#define CASH_BUFSZ 36
#define TERMINATOR (CASH_BUFSZ - 1)
#define LAST_PAREN (TERMINATOR - 1)
#define LAST_DIGIT (LAST_PAREN - 1)
-
/*
* Cash is a pass-by-ref SQL type, so we must pass and return pointers.
* These macros and support routine hide the pass-by-refness.
@@ -41,6 +40,65 @@ static const char *num_word(Cash value);
#define PG_GETARG_CASH(n) (* ((Cash *) PG_GETARG_POINTER(n)))
#define PG_RETURN_CASH(x) return CashGetDatum(x)
+
+
+/*
+ * Private routines
+
/
+ +static const char *
+num_word(Cash value)
+{
+ static char buf[128];
+ static const char *small[] = {
+ zero, one, two, three, four, five, six,
seven,
+ eight, nine, ten, eleven, twelve,
thirteen, fourteen,
+ fifteen, sixteen, seventeen, eighteen,
nineteen, twenty,
+ thirty, forty, fifty, sixty, seventy,
eighty, ninety
+ };
+ const char **big = small + 18;
+ int tu = value % 100;
+
+ /* deal with the simple cases first */
+ if (value = 20)
+ return small[value];
+
+ /* is it an even multiple of 100? */
+ if (!tu)
+ {
+ sprintf(buf, %s hundred, small[value / 100]);
+ return buf;
+ }
+
+ /* more than 99? */
+ if (value 99)
+ {
+ /* is it an even multiple of 10 other than 10? */
+ if (value % 10 == 0 tu 10)
+ sprintf(buf, %s hundred %s,
+ small[value / 100], big[tu /
10]);
+ else if (tu 20)
+ sprintf(buf, %s hundred and %s,
+ small[value / 100], small[tu]);
+ else
+ sprintf(buf, %s hundred %s %s,
+ small[value / 100], big[tu /
10], small[tu % 10]); +
+ }
+ else
+ {
+ /* is it an even multiple of 10 other than 10? */
+ if (value % 10 == 0 tu 10)
+ sprintf(buf, %s, big[tu / 10]);
+ else if (tu 20)
+ sprintf(buf, %s, small[tu]);
+ else
+ sprintf(buf, %s %s, big[tu / 10], small[tu %
10]);
+ }
+
+ return buf;
+} /* num_word() */
+
static Datum
CashGetDatum(Cash value)
{
@@ -56,12 +114,6 @@ CashGetDatum(Cash value)
* Format is [$]###[,]###[.##]
* Examples: 123.45 $123.45 $123,456.78
*
- * This is currently implemented as a 32-bit integer.
- * XXX HACK It looks as though some of the symbols for
- * monetary values returned by localeconv() can be multiple
- * bytes/characters. This code assumes one byte only. - tgl
97/04/14
- * XXX UNHACK Allow the currency symbol to be multibyte.
- * - thomas 1998-03-01
*/
Datum
cash_in(PG_FUNCTION_ARGS)
@@ -74,11 +126,11 @@ cash_in(PG_FUNCTION_ARGS)
int seen_dot = 0;
const char *s = str;
int fpoint;
- char *csymbol;
+ const char *csymbol,
+
Re: [HACKERS] CSStorm occurred again by postgreSQL8.2
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: I think Theo's problem is probably somewhere else, too --- apparently it's not so much that TransactionIdIsCurrentTransactionId takes a long time as that something is calling it lots of times with no check for interrupt. Could it be something like heap_lock_tuple? It calls MultiXactIdWait, which calls GetMultXactIdMembers and TransactionIdIsCurrentTransactionId on each member. (heap_update and heap_delete do the same thing). I must admit I didn't read Theo's description on his scenario though. He shows HeapTupleSatisfiesSnapshot as the next thing down the call stack, so those scenarios don't seem quite right. I'm wondering about a CHECK_FOR_INTERRUPTS-free loop in either plperl or trigger handling, myself. Anyway, I was thinking some more about Theo's original suggestion that the linked-list representation of childXids was too inefficient. I'm disinclined to use a hash as he suggests, but it strikes me that we could very easily change the list into a dynamically extended array --- and because the entries are surely added in increasing XID order, such an array could be binary-searched. This wouldn't be a win for very small numbers of child XIDs, but for large numbers it would. OTOH, there are probably enough other inefficiencies in handling large numbers of subxact XIDs that speeding up TransactionIdIsCurrentTransactionId might be a useless exercise. It would be good to profile a test case before spending much effort here. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] AIX shared libraries
Albe Laurenz [EMAIL PROTECTED] writes: Tom Lane wrote: Hm. The objection I see to this is that it will not support concurrent installation of multiple libpq versions. What about 4) Build and install only libXX.so.n, don't install libXX.a at all Won't work - the linker looks for libXX.so and won't find libXX.so.n. If you create a symbolic link libXX.so -- libXX.so.n, you can link, but the executable will depend on libXX.so and not on libXX.so.n. Ugh. So given that linker behavior, it's basically impossible to support multiple libpq versions in the same directory anyway on AIX. I concur with your 3a) then. Do you have time to do that now? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New version of money type
D'Arcy J.M. Cain wrote:
For years I have been promising that a 64 bit version of the money type
was on the way. Here it is. So far it compiles and I have done some
basic testing on it and it seems to work fine. Note that the currency
symbol is also dropped on output as well but it is accepted on input.
Not to come down on your hard work, but isn't the money type deprecated?
Joshua D. Drake
darcy=# select '$92,233,720,368,547,758.07'::money;
money
92,233,720,368,547,758.07
(1 row)
Index: src/backend/utils/adt/cash.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/cash.c,v
retrieving revision 1.68
diff -u -p -u -r1.68 cash.c
--- src/backend/utils/adt/cash.c14 Jul 2006 14:52:23
- 1.68 +++ src/backend/utils/adt/cash.c 14 Sep 2006
14:28:30 - @@ -1,13 +1,17 @@
/*
* cash.c
* Written by D'Arcy J.M. Cain
+ * darcy@druid.net
+ * http://www.druid.net/darcy/
*
* Functions to allow input and output of money normally but store
- * and handle it as int4s
+ * and handle it as 64 bit ints
*
* A slightly modified version of this file and a discussion of the
* workings can be found in the book Software Solutions in C by
- * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7.
+ * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7 except that
+ * this version handles 64 bit numbers and so can hold values up to
+ * $92,233,720,368,547,758.07.
*
* $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.68 2006/07/14
14:52:23 momjian Exp $ */
@@ -23,17 +27,12 @@
#include utils/cash.h
#include utils/pg_locale.h
-
-static const char *num_word(Cash value);
-
-/* when we go to 64 bit values we will have to modify this */
-#define CASH_BUFSZ 24
+#define CASH_BUFSZ 36
#define TERMINATOR (CASH_BUFSZ - 1)
#define LAST_PAREN (TERMINATOR - 1)
#define LAST_DIGIT (LAST_PAREN - 1)
-
/*
* Cash is a pass-by-ref SQL type, so we must pass and return pointers.
* These macros and support routine hide the pass-by-refness.
@@ -41,6 +40,65 @@ static const char *num_word(Cash value);
#define PG_GETARG_CASH(n) (* ((Cash *) PG_GETARG_POINTER(n)))
#define PG_RETURN_CASH(x) return CashGetDatum(x)
+
+
+/*
+ * Private routines
+
/
+ +static const char *
+num_word(Cash value)
+{
+ static char buf[128];
+ static const char *small[] = {
+ zero, one, two, three, four, five, six,
seven,
+ eight, nine, ten, eleven, twelve,
thirteen, fourteen,
+ fifteen, sixteen, seventeen, eighteen,
nineteen, twenty,
+ thirty, forty, fifty, sixty, seventy,
eighty, ninety
+ };
+ const char **big = small + 18;
+ int tu = value % 100;
+
+ /* deal with the simple cases first */
+ if (value = 20)
+ return small[value];
+
+ /* is it an even multiple of 100? */
+ if (!tu)
+ {
+ sprintf(buf, %s hundred, small[value / 100]);
+ return buf;
+ }
+
+ /* more than 99? */
+ if (value 99)
+ {
+ /* is it an even multiple of 10 other than 10? */
+ if (value % 10 == 0 tu 10)
+ sprintf(buf, %s hundred %s,
+ small[value / 100], big[tu /
10]);
+ else if (tu 20)
+ sprintf(buf, %s hundred and %s,
+ small[value / 100], small[tu]);
+ else
+ sprintf(buf, %s hundred %s %s,
+ small[value / 100], big[tu /
10], small[tu % 10]); +
+ }
+ else
+ {
+ /* is it an even multiple of 10 other than 10? */
+ if (value % 10 == 0 tu 10)
+ sprintf(buf, %s, big[tu / 10]);
+ else if (tu 20)
+ sprintf(buf, %s, small[tu]);
+ else
+ sprintf(buf, %s %s, big[tu / 10], small[tu %
10]);
+ }
+
+ return buf;
+} /* num_word() */
+
static Datum
CashGetDatum(Cash value)
{
@@ -56,12 +114,6 @@ CashGetDatum(Cash value)
* Format is [$]###[,]###[.##]
* Examples: 123.45 $123.45 $123,456.78
*
- * This is currently implemented as a 32-bit integer.
- * XXX HACK It looks as though some of the symbols for
- * monetary values returned by localeconv() can be multiple
- * bytes/characters. This code assumes one byte only. - tgl
97/04/14
- * XXX UNHACK Allow the currency symbol to be multibyte.
- * - thomas 1998-03-01
*/
Datum
cash_in(PG_FUNCTION_ARGS)
@@ -74,11 +126,11 @@ cash_in(PG_FUNCTION_ARGS)
int seen_dot = 0;
const char
Re: [HACKERS] Release notes
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Robert Treat) belched out: On Tuesday 12 September 2006 14:49, Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I again will not be able to complete the release notes today as promised. My next target date is Monday, August 18. Sorry. Will that be in a few years, or are you traveling backwards in time? ;-) Sorry, September 18. I will probably be done before then, but it seems best to set a date I know I will hit. Here we go again with another developer who keeps making endless promises for vaporware patches that never show up. We've already set on-disk bit-map indexes straight on this and I think giving you special treatment sets a bad tone for the project. At this point I think we have to cut the release notes from this release... maybe they can be added back in for 8.3. ;^) I'm happy they're available; I'm prepping a talk on new stuff for Ohio LinuxFest, and for the notes to be available now is pretty ideal. Seems to me that what I mostly do is print off a copy, show how thick it is, and say There are a really a lot of things improved, as visible on this list; alas, few are obviously 'sexy' new things... In seriousness, that is somewhat troublesome with this release, and that's a challenge for the press release. (Work on that can presumably proceed, now, in that there is a feature list to try to distill.) There are lots of little things that I like; it's just hard to point to any big, easily identifiable things, like PITR, 2PC, recursive queries, and such. -- output = (cbbrowne @ gmail.com) http://linuxdatabases.info/info/lsf.html Well, I wish you'd just tell me rather than trying to engage my enthusiasm, because I haven't got one. -- Marvin the Paranoid Android ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New version of money type
On Thu, 14 Sep 2006 07:59:07 -0700 Joshua D. Drake [EMAIL PROTECTED] wrote: D'Arcy J.M. Cain wrote: For years I have been promising that a 64 bit version of the money type was on the way. Here it is. So far it compiles and I have done some basic testing on it and it seems to work fine. Note that the currency symbol is also dropped on output as well but it is accepted on input. Not to come down on your hard work, but isn't the money type deprecated? Not by me. :-) The biggest argument about the money type is that it has an unrealistic limit. With this change we can go to almost one hundred thousand trillion dollars. That should handle even the US federal budget for a few more years. The benefit of the money type is speed. Because internal operations are done on integers they can generally be handled by single CPU ops. My tests on the 64 bit version show 10% to 25% improvement over numeric for many operations. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(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] CSStorm occurred again by postgreSQL8.2
Tom Lane [EMAIL PROTECTED] writes: --- and because the entries are surely added in increasing XID order, such an array could be binary-searched. If they're only added if they write to disk then isn't it possible to add them out of order? Start a child transaction, start a child of that one and write to disk, then exit the grandchild and write to disk in the first child? I'm just going on your description, I'm not familiar with this part of the code at all. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New version of money type
D'Arcy J.M. Cain wrote: On Thu, 14 Sep 2006 07:59:07 -0700 Joshua D. Drake [EMAIL PROTECTED] wrote: D'Arcy J.M. Cain wrote: For years I have been promising that a 64 bit version of the money type was on the way. Here it is. So far it compiles and I have done some basic testing on it and it seems to work fine. Note that the currency symbol is also dropped on output as well but it is accepted on input. Not to come down on your hard work, but isn't the money type deprecated? Not by me. :-) Obviously ;), but it is deprecated by the project. The biggest argument about the money type is that it has an unrealistic limit. With this change we can go to almost one hundred thousand trillion dollars. That should handle even the US federal budget for a few more years. Isn't that what numeric is for? The benefit of the money type is speed. Because internal operations are done on integers they can generally be handled by single CPU ops. My tests on the 64 bit version show 10% to 25% improvement over numeric for many operations. Well that is certainly cool :) I will leave it to others to determine if we should include it. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [ADMIN] Vacuum error on database postgres
andy [EMAIL PROTECTED] writes: Tom Lane wrote: andy [EMAIL PROTECTED] writes: So I'm ok, but I tried it again, by dropping the database and re-running both scripts and got the same error again. So thought I'd offer a test case if there was interest. Absolutely. I've seen just enough of these reports to make me think there's an underlying bug. Here are some urls: ... Doh ... I think the critical bit is here: autovacuum = on # enable autovacuum subprocess? The problem is that ANALYZE takes only AccessShareLock on a table, so it's entirely possible for two backends to try to ANALYZE the same table concurrently, and in particular for autovacuum to try to do so while your foreground VACUUM ANALYZE is running. That leads to concurrent insertion attempts into pg_statistic for the same key. This behavior dates from a time when there was no good alternative. One possible fix today would be to make ANALYZE take ShareUpdateExclusive lock instead, thus ensuring there is only one ANALYZE at a time on a table. However I'm a bit concerned by the possibility that ANALYZE-inside-a-transaction could accumulate a whole bunch of such locks in a random order, leading at least to a risk of deadlocks against other ANALYZEs. (We have to hold the lock till commit, else we aren't fixing the problem.) Do we need a specialized lock type just for ANALYZE? Would sorting the target list of rel OIDs be enough? Perhaps it's not worth worrying 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] CSStorm occurred again by postgreSQL8.2
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: --- and because the entries are surely added in increasing XID order, such an array could be binary-searched. If they're only added if they write to disk then isn't it possible to add them out of order? Start a child transaction, start a child of that one and write to disk, then exit the grandchild and write to disk in the first child? No, because we enforce child XID parent XID. In the case above, the child xact would be given an XID when the grandchild needs one --- see recursion in AssignSubTransactionId(). The actually slightly shaky assumption above is that children of the same parent xact must subcommit in numerical order ... but as long as we have strict nesting of subxacts I think this must be so. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Opinion wanted on UUID/GUID datatype output formats.
Folks,
I would like to have your opinion on the following:
At this moment we (almost) have a uuid/guid datatype.
As suggested in earlier discussion we provide a raw/plain output of the
uuid type:
devdb=# select * from tbluuid;
pk|
--+
6b13c5a1afb4dcf5ce8f8b4656b6c93c |
01e40a79b55b6e226bffb577e960453d |
(2 rows)
I was wondering if we want to have a formatting function to be able to
provide other common formats of the uuid/guid?
something like:
select format_uuid(mypk,'format2') from tbluuid;
and then get: 6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c
or
select format_uuid(mypk,'format3') from tbluuid;
and then get: {6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c}
(which would be MSSQL compatible)
Do we want such a function added to the core or we let the application
handle the formatting if ever needed.
What do the PostgreSQL masters think? :)
Regards,
Gevik.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Lock partitions
Tom, Taking the 4 lock vs 8 lock partitions, 4 LockMgr lock partitions spent a total of 652 seconds in lock management (acquiring/releasing) and 8 LockMgr lock partitions spent a total of 536 in lock management. This is an improvement of 116 seconds, but the TPS didn't improve by much - only a 1.21 TPS improvement. The improvement in the LockMgr processing is consumed by the next system bottleneck downstream as more work is being let through. In this particular case it's the WALInsertLock lock. The 4 LockMgr lock partition test spent a total of 5868 seconds in WALInsertLock lock management whereas the 8 LockMgr partition test spent 5945 seconds in WALInsertLock lock management which is an increase of 77 seconds. But, that's not the only static lock that increased in time, it's just the most significant increase. The WALWriteLock lock increased by 12 seconds, ProcArrayLock increased by 8 seconds and SInvalLock increased by 5 seconds. This takes the total time flowing to other locks to 102 seconds. The locks are not the only part of the puzzle. As improvements are made to various areas like the BufMapping and LockMgr lock partitions, other parts of the system start to get exercised in ways that were not possible in previous releases. We're still trying to get our arms around all the functions that might become bottlenecks when other lock contention is minimized. And, improvements are being made. The locking changes from 8.0.x to 8.1.x made a significant difference in scalability. Again, the current lock improvements in 8.2 have realized ~20% improvement over 8.1.x, based on our testing. We added monitoring code to the LWLockAcquire and LWLockRelease functions. We track the total time taken to pass through LWLockAcquire and LWLockRelease. So, if a particular backend process takes 1 second to run through LWLockAcquire, we will track that as 1 second in lock acquisition. Irrespective of whether my backend process was spinning or in a semaphore wait, it's 1 second that was taken away from processing a statement/request. We could also add timing for semaphore waits within LWLockAcquire, if that would be a useful statistic. Let me know if there are any other tests or metrics that would be useful. David -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 13, 2006 1:36 PM To: Strong, David Cc: PostgreSQL-development Subject: Re: [HACKERS] Lock partitions Strong, David [EMAIL PROTECTED] writes: We have some results for you. We left the buffer partition locks at 128 as this did not seem to be a concern and we're still using 25 backend processes. We ran tests for 4, 8 and 16 lock partitions. For 4 lock partitions, it took 620 seconds to acquire locks and 32 seconds to release locks. The test produced 199.95 TPS. For 8 lock partitions, it took 505 seconds to acquire locks and 31 seconds to release locks. The test produced 201.16 TPS. For 16 lock partitions, it took 362 seconds to acquire locks and 22 seconds to release locks. The test produced 200.75 TPS. And, just for grins, using 128 buffer and 128 lock partitions, took 235 seconds to acquire locks and 22 seconds to release locks. The test produced 203.24 TPS. [ itch... ] I can't help thinking there's something wrong with this; the wait-time measurements seem sane, but why is there essentially no change in the TPS result? The above numbers are only for the lock-partition LWLocks, right? What are the totals --- that is, how much time is spent blocked vs. processing overall? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Opinion wanted on UUID/GUID datatype output formats.
select format_uuid(mypk,'format2') from tbluuid; and then get: 6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c How about instead of fixed formats, you allow a format string using the diverse parts of the GUID a la time formatting functions ? Then everybody can format it as they want. Just an idea. Cheers, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [ADMIN] Vacuum error on database postgres
This behavior dates from a time when there was no good alternative. One possible fix today would be to make ANALYZE take ShareUpdateExclusive lock instead, thus ensuring there is only one ANALYZE at a time on a table. However I'm a bit concerned by the possibility that ANALYZE-inside-a-transaction could accumulate a whole bunch of such locks in a random order, leading at least to a risk of deadlocks against other ANALYZEs. (We have to hold the lock till commit, else we aren't fixing the problem.) Do we need a specialized lock type just for ANALYZE? Would sorting the target list of rel OIDs be enough? Perhaps it's not worth worrying about? Why not an internal lock that people don't see? The behavior would the following: conn1: analyze foo; conn2: analyze foo; ERROR: analyze already running on foo conn1: analyze foo; conn2: analyze; NOTICE: analyze full started, analyze running on foo, skipping foo Sincerely, Joshua D. Drake 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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Fixed length data types issue
Gregory Stark wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Gregory Stark wrote: Well char doesn't have quite the same semantics as CHAR(1). If that's the consensus though then I can work on either fixing char semantics to match CHAR(1) or adding a separate type instead. What semantics? The main bit that comes to mind is 32::CHAR(1) give you '3' but 32::char gives you ' '. Really it makes more sense if you think of char is a 1 byte integer type with some extra text casts and operators to make C programmers happy, not a 1 byte character type. One very nifty trick would be to fix char to act as CHAR(), and map CHAR(1) automatically to char. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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
[HACKERS] Mid cycle release?
Hello, I know that this would be completely out of the norm. However, would it be worth considering having a mid cycle release for 8.3? Basically the release would focus on: Updateable views Bitmap indexes Recursive queries We would release in June? Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Mid cycle release?
Joshua D. Drake wrote: Hello, I know that this would be completely out of the norm. However, would it be worth considering having a mid cycle release for 8.3? Basically the release would focus on: Updateable views Bitmap indexes Recursive queries We would release in June? Interesting idea but we already have one of the fastest release cycles of all database systems and some people would like to see a larger cycle. In addition to that this plan might hold back some people from upgrading to 8.2 which solves quite a few critical issues with features we marketed/introduced during the past 8.x cycles and are really getting polished and usable now (partitioning,pitr,...) and 8.2 gives quite a nice performance boost for a lot of workloads too. On a personal note - while those features might be nice to market for some of use others would like to see very different things (like proper encoding/character set/collation support or plan invalidation). That might lead to a more that and this feature must be in based release cycle which might not work out that well in practise ... Stefan ---(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] Mid cycle release?
Stefan Kaltenbrunner wrote: Joshua D. Drake wrote: Hello, I know that this would be completely out of the norm. However, would it be worth considering having a mid cycle release for 8.3? Basically the release would focus on: Updateable views Bitmap indexes Recursive queries We would release in June? Interesting idea but we already have one of the fastest release cycles of all database systems and some people would like to see a larger cycle. I really don't care about other database systems. I care about postgresql :). That is also why I wanted to limit the features set specifically. In addition to that this plan might hold back some people from upgrading to 8.2 which solves quite a few critical issues with features we marketed/introduced during the past 8.x cycles and are really getting polished and usable now (partitioning,pitr,...) and 8.2 gives quite a nice performance boost for a lot of workloads too. I frankly won't see many people migrate to 8.2. Most of my customers will wait for 8.3 anyway. (except new business of course). Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(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] Fixed length data types issue
My apologies if you are seeing this twice. I posted it last night, but it still does not appear to have made it to the group. Mark Dilger wrote: Tom Lane wrote: Mark Dilger [EMAIL PROTECTED] writes: Tom Lane wrote: Please provide a stack trace --- AFAIK there shouldn't be any reason why a pass-by-ref 3-byte type wouldn't work. (gdb) bt #0 0xb7e01d45 in memcpy () from /lib/libc.so.6 #1 0x08077ece in heap_fill_tuple (tupleDesc=0x83c2ef7, values=0x83c2e84, isnull=0x83c2e98 , data=0x83c2ef4 , infomask=0x83c2ef0, bit=0x0) at heaptuple.c:181 Hm, are you sure you provided a valid pointer (not the integer value itself) as the Datum output from int3_in? (Looks at patch ... ) Um, I think you didn't, although that coding is far too cute to be actually readable ... regards, tom lane Ok, I have it working on my intel architecture machine. Here are some of my findings. Disk usage is calculated by running 'du -b' in /usr/local/pgsql/data before and after loading the table, and taking the difference. That directory is deleted, recreated, and initdb rerun between each test. The host system is a dual processor, dual core 2.4 GHz system, 2 GB DDR400 memory, 10,000 RPM SCSI ultra160 hard drive with the default postgresql.conf file as created by initdb. The code is the stock postgresql-8.1.4 release tarball compiled with gcc and configured without debug or cassert options enabled. INT3 VS INT4 Using a table of 8 integers per row and 16777216 rows, I can drop the disk usage from 1.2 GB down to 1.0 GB by defining those integers as int3 rather than int4. (It works out to about 70.5 bytes per row vs. 62.5 bytes per row.) However, the load time actually increases, probably due to CPU/memory usage. The time increased from 197 seconds to 213 seconds. Note that int3 is defined pass-by-reference due to a limitation in the code that prevents pass-by-value for any datasize other than 1, 2, or 4 bytes. Using a table of only one integer per row, the table size is exactly the same (down to the byte) whether I use int3 or int4. I suspect this is due to data alignment for the row being on at least a 4 byte boundary. Creating an index on a single column of the 8-integer-per-row table, the index size is exactly the same whether the integers are int3 or int4. Once again, I suspect that data alignment is eliminating the space savings. I haven't tested this, but I suspect that if the column following an int3 is aligned on 4 or 8 byte boundaries, that the int3 column will have an extra byte padded and hence will have no performance gain. INT1 VS INT2 Once again using a table of 8 integers per row and 16777216 rows, I can drop the disk usage from 909 MB down to 774 MB by defining those integers as int1 rather than int2. (54 bytes per row vs 46 bytes per row.) The load time also drops, from 179 seconds to 159 seconds. Note that int1 is defined pass-by-value. mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Mid cycle release?
Joshua D. Drake wrote: Stefan Kaltenbrunner wrote: Joshua D. Drake wrote: Hello, I know that this would be completely out of the norm. However, would it be worth considering having a mid cycle release for 8.3? Basically the release would focus on: Updateable views Bitmap indexes Recursive queries We would release in June? Interesting idea but we already have one of the fastest release cycles of all database systems and some people would like to see a larger cycle. I really don't care about other database systems. I care about postgresql :). That is also why I wanted to limit the features set specifically. hmm yeah but as I said - probably not everybody has an immediate demand or is so fixated on those .. In addition to that this plan might hold back some people from upgrading to 8.2 which solves quite a few critical issues with features we marketed/introduced during the past 8.x cycles and are really getting polished and usable now (partitioning,pitr,...) and 8.2 gives quite a nice performance boost for a lot of workloads too. I frankly won't see many people migrate to 8.2. Most of my customers will wait for 8.3 anyway. (except new business of course). I disagree - 8.2 is much more attractive for us then say 8.0 or 8.1 was and we will probably adopt it rather aggressively ... Stefan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Mid cycle release?
In addition to that this plan might hold back some people from upgrading to 8.2 which solves quite a few critical issues with features we marketed/introduced during the past 8.x cycles and are really getting polished and usable now (partitioning,pitr,...) and 8.2 gives quite a nice performance boost for a lot of workloads too. I frankly won't see many people migrate to 8.2. Most of my customers will wait for 8.3 anyway. (except new business of course). I disagree - 8.2 is much more attractive for us then say 8.0 or 8.1 was and we will probably adopt it rather aggressively ... That's why I said I frankly won't. I have customers with multi terrabyte datasets. 8.1 performs wonderfully for them. It would be a hard push to initiate an 8.2 outage for that. Joshua D. Drake Stefan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(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] Mid cycle release?
Joshua D. Drake wrote: In addition to that this plan might hold back some people from upgrading to 8.2 which solves quite a few critical issues with features we marketed/introduced during the past 8.x cycles and are really getting polished and usable now (partitioning,pitr,...) and 8.2 gives quite a nice performance boost for a lot of workloads too. I frankly won't see many people migrate to 8.2. Most of my customers will wait for 8.3 anyway. (except new business of course). I disagree - 8.2 is much more attractive for us then say 8.0 or 8.1 was and we will probably adopt it rather aggressively ... That's why I said I frankly won't. I have customers with multi terrabyte datasets. 8.1 performs wonderfully for them. It would be a hard push to initiate an 8.2 outage for that. maybe - we have mostly OLTP style databases in the 2-3 figure gigabyte range and none of the features you want to see an entire major release done for would be a reason to upgrade for us. Things 30% overall performance increase for a large set of queries (in our apps) due to planner improvements and things like restartable recovery and reduced dump restore times (due to the sorting fixes) however are :-) Point I want to make is - all those are cool features(and might be critical for some) but I don't think they warrant a dramatic change in the release cycle policy ... Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Draft release notes
Bruce Momjian wrote: Here is an early draft of the release notes. It needs more polish and review: http://momjian.us/cgi-bin/pgrelease I will catch up on my email tomorrow, update the open items list for 8.2, and then return to the release notes for cleanup. Add support for Windows codepages 1253, 1254, 1255, and 1257 (Peter) This was actually me. http://archives.postgresql.org/pgsql-patches/2006-02/msg00039.php Kris Jurka ---(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] New version of money type
On Thu, 14 Sep 2006 08:17:29 -0700 Joshua D. Drake [EMAIL PROTECTED] wrote: Obviously ;), but it is deprecated by the project. I keep hearing that but no action is ever taken. I think that there are too many people who still find it useful. By the way, I removed the currency symbol from the output. Would removing the commas also make sense? These are the sorts of things that can be added by applications. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Lock partitions
Tom Lane wrote:
Mark Wong [EMAIL PROTECTED] writes:
Tom Lane wrote:
It would be nice to see some results from the OSDL tests with, say, 4,
8, and 16 lock partitions before we forget about the point though.
Anybody know whether OSDL is in a position to run tests for us?
Yeah, I can run some dbt2 tests in the lab. I'll get started on it.
We're still a little bit away from getting the automated testing for
PostgreSQL going again though.
Great, thanks. The thing to twiddle is LOG2_NUM_LOCK_PARTITIONS in
src/include/storage/lwlock.h. You need a full backend recompile
after changing it, but you shouldn't need to initdb, if that helps.
Sorry for the delay but looks like there's some data coming in. It also
looks like my kit is starting to be a little dated. My stored libpq
calls are failing. I'm getting this message:
ERROR: record type has not been registered
From PQerrorMessage() on line 41 from this bit of code:
/* Create the query and execute it. */
sprintf(stmt, SELECT * FROM order_status(%d, %d, %d, '%s'),
data-c_id, data-c_w_id, data-c_d_id, data-c_last);
res = PQexec(dbc-conn, stmt);
if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK
PQresultStatus(res) != PGRES_TUPLES_OK)) {
LOG_ERROR_MESSAGE(%s, PQerrorMessage(dbc-conn));
PQclear(res);
return ERROR;
}
LOG_ERROR_MESSAGE() is just a macro for a function that does a printf().
Any suggestions?
Thanks,
Mark
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] New version of money type
D'Arcy J.M. Cain wrote: On Thu, 14 Sep 2006 08:17:29 -0700 Joshua D. Drake [EMAIL PROTECTED] wrote: Obviously ;), but it is deprecated by the project. I keep hearing that but no action is ever taken. I think that there are too many people who still find it useful. By the way, I removed the currency symbol from the output. Would removing the commas also make sense? These are the sorts of things that can be added by applications. I don't think that we should be providing *any* presentation beyond the actual representation of the data. What if it is not US dollars? :) Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Fixed length data types issue
Bruce Momjian wrote: Gregory Stark wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Gregory Stark wrote: Well char doesn't have quite the same semantics as CHAR(1). If that's the consensus though then I can work on either fixing char semantics to match CHAR(1) or adding a separate type instead. What semantics? The main bit that comes to mind is 32::CHAR(1) give you '3' but 32::char gives you ' '. Really it makes more sense if you think of char is a 1 byte integer type with some extra text casts and operators to make C programmers happy, not a 1 byte character type. One very nifty trick would be to fix char to act as CHAR(), and map CHAR(1) automatically to char. Sorry, probably a stupid idea considering multi-byte encodings. I suppose it could be an optimization for single-byte encodings, but that seems very limiting. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] New version of money type
On Thu, 14 Sep 2006 10:33:19 -0700 Joshua D. Drake [EMAIL PROTECTED] wrote: By the way, I removed the currency symbol from the output. Would removing the commas also make sense? These are the sorts of things that can be added by applications. I don't think that we should be providing *any* presentation beyond the actual representation of the data. What if it is not US dollars? :) That's what locale is for. It looks at that to determine that sort of stuff including currency symbol before I removed it. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(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] Lock partitions
Mark Wong [EMAIL PROTECTED] writes: Sorry for the delay but looks like there's some data coming in. It also looks like my kit is starting to be a little dated. My stored libpq calls are failing. I'm getting this message: ERROR: record type has not been registered This is a server-side failure --- could we see how order_status() is defined? What PG version are you testing exactly? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] contrib/xml2 and PG_MODULE_MAGIC
URL added to TODO list. --- Peter Eisentraut wrote: Tom Lane wrote: Even more interesting would be to fix things so that xml2 gets built as part of the regular contrib build, but I'm not sure if we're ready to add stuff to the configure script for the sole benefit of a contrib module. It might be good to get the configury code out in this release for advance testing. I'd be more willing to do it if there were a long-term plan for putting libxml-dependent code into the core. Do we have a roadmap yet for XML development? I thought there was a session at the anniversary conference devoted to sketching one, but if anything came out of that I missed it. I put a write-up here: http://developer.postgresql.org/index.php/XML_Support -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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 -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] New version of money type
On Sep 14, 2006, at 14:04 , D'Arcy J.M. Cain wrote: On Thu, 14 Sep 2006 10:33:19 -0700 Joshua D. Drake [EMAIL PROTECTED] wrote: By the way, I removed the currency symbol from the output. Would removing the commas also make sense? These are the sorts of things that can be added by applications. I don't think that we should be providing *any* presentation beyond the actual representation of the data. What if it is not US dollars? :) That's what locale is for. It looks at that to determine that sort of stuff including currency symbol before I removed it. If you force the locale into the money type, then the entire column must be of the same currency. That seems like an unnecessary limitation. Does your type support banker's rounding? -M ---(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] Lock partitions
Tom Lane wrote: Mark Wong [EMAIL PROTECTED] writes: Sorry for the delay but looks like there's some data coming in. It also looks like my kit is starting to be a little dated. My stored libpq calls are failing. I'm getting this message: ERROR: record type has not been registered This is a server-side failure --- could we see how order_status() is defined? What PG version are you testing exactly? I took pgsqsl snapshot from cvs on Sept 11. Due to the length of the file that order_status() is in and of order_status() itself, here's is a url for the file in the svn repository. order_status() is defined starting on line 710: http://svn.sourceforge.net/viewvc/osdldbt/trunk/dbt2/storedproc/pgsql/c/funcs.c?view=markup Thanks, Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Mid cycle release?
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: Point I want to make is - all those are cool features(and might be critical for some) but I don't think they warrant a dramatic change in the release cycle policy ... Any release is going to have some things that are compelling and some that aren't, for any particular person ... it's just that those things vary depending on who you are ... I was heard to gripe not long ago that feature freeze during August was bad timing. It would be interesting to try to do it during the spring instead, just to see if people have more free time then. So for me, +1 for a shorter-than-a-year cycle this time, independently of what features make it or don't. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Mid cycle release?
Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: Point I want to make is - all those are cool features(and might be critical for some) but I don't think they warrant a dramatic change in the release cycle policy ... Any release is going to have some things that are compelling and some that aren't, for any particular person ... it's just that those things vary depending on who you are ... no doubt on that :-) I was heard to gripe not long ago that feature freeze during August was bad timing. It would be interesting to try to do it during the spring instead, just to see if people have more free time then. So for me, +1 for a shorter-than-a-year cycle this time, independently of what features make it or don't. well that is something I can agree too - it is mostly the do a special release for exactly those 3 features I don't like that much ... Stefan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Mid cycle release?
Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: Point I want to make is - all those are cool features(and might be critical for some) but I don't think they warrant a dramatic change in the release cycle policy ... Any release is going to have some things that are compelling and some that aren't, for any particular person ... it's just that those things vary depending on who you are ... I was heard to gripe not long ago that feature freeze during August was bad timing. It would be interesting to try to do it during the spring instead, just to see if people have more free time then. So for me, +1 for a shorter-than-a-year cycle this time, independently of what features make it or don't. Well on that same vein (with a +1), I know that we lost at least 8 weeks of productivity from various vacations etc.. during the summer. When you incorporate everyone else that is involved with postgresql, I could easily see almost a full man year lost, by having freeze where it is now. I think having a freeze more toward march/april makes a heck of a lot of sense. Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Lock partitions
Mark Wong [EMAIL PROTECTED] writes: Tom Lane wrote: This is a server-side failure --- could we see how order_status() is defined? What PG version are you testing exactly? I took pgsqsl snapshot from cvs on Sept 11. Due to the length of the file that order_status() is in and of order_status() itself, here's is a url for the file in the svn repository. order_status() is defined starting on line 710: http://svn.sourceforge.net/viewvc/osdldbt/trunk/dbt2/storedproc/pgsql/c/funcs.c?view=markup Hmph. I think we broke something --- the error implies that some function tried to return a tuple that hadn't been properly blessed, but I can't see that order_status or any of the other functions in that file are doing anything wrong. In any case, if it used to work for you, we had better figure out exactly why it stopped working. I know you've posted info before on how to set up and run the dbt code, but could you refresh my memory? Is there a URL somewhere with the info? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Lock partitions
Tom Lane wrote: Mark Wong [EMAIL PROTECTED] writes: Tom Lane wrote: This is a server-side failure --- could we see how order_status() is defined? What PG version are you testing exactly? I took pgsqsl snapshot from cvs on Sept 11. Due to the length of the file that order_status() is in and of order_status() itself, here's is a url for the file in the svn repository. order_status() is defined starting on line 710: http://svn.sourceforge.net/viewvc/osdldbt/trunk/dbt2/storedproc/pgsql/c/funcs.c?view=markup Hmph. I think we broke something --- the error implies that some function tried to return a tuple that hadn't been properly blessed, but I can't see that order_status or any of the other functions in that file are doing anything wrong. In any case, if it used to work for you, we had better figure out exactly why it stopped working. I know you've posted info before on how to set up and run the dbt code, but could you refresh my memory? Is there a URL somewhere with the info? Here's a readme: http://svn.sourceforge.net/viewvc/osdldbt/trunk/dbt2/README-POSTGRESQL?view=markup But perhaps something much easier, using subversion: mkdir /mnt/dbt2 # for pgdata svn co https://svn.sourceforge.net/svnroot/osdldbt/trunk/dbt2 dbt2 cd dbt2 ./configure --with-postgresql=pgsql_dir make cd scripts/pgsql/ ./build_db -g -w 1 cd .. ./run_workload -w 1 -d 120 -c 20 -n I think that should work. That will create a 1 warehouse database and run a 120 second test with no-thinktimes. That should be sufficient to run through each transaction more than several times. Thanks, Mark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Lock partitions
Mark Wong [EMAIL PROTECTED] writes: But perhaps something much easier, using subversion: mkdir /mnt/dbt2 # for pgdata svn co https://svn.sourceforge.net/svnroot/osdldbt/trunk/dbt2 dbt2 cd dbt2 ./configure --with-postgresql=pgsql_dir configure is not in the svn checkout. I guessed that I needed to do aclocal/automake/autoconf, but automake fails: $ automake configure.ac:11: required file `config.h.in' not found $ and I don't see anyplace to get config.h.in from. 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] Lock partitions
Tom Lane wrote: Mark Wong [EMAIL PROTECTED] writes: But perhaps something much easier, using subversion: mkdir /mnt/dbt2 # for pgdata svn co https://svn.sourceforge.net/svnroot/osdldbt/trunk/dbt2 dbt2 cd dbt2 ./configure --with-postgresql=pgsql_dir configure is not in the svn checkout. I guessed that I needed to do aclocal/automake/autoconf, but automake fails: $ automake configure.ac:11: required file `config.h.in' not found $ and I don't see anyplace to get config.h.in from. Oops! 'autoreconf --install' is what I run to generate all that stuff. Sorry about that. Thanks, Mark ---(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] New version of money type
* D'Arcy J.M. Cain (darcy@druid.net) wrote: The benefit of the money type is speed. Because internal operations are done on integers they can generally be handled by single CPU ops. My tests on the 64 bit version show 10% to 25% improvement over numeric for many operations. Erm, the numeric is doing integer ops too mostly, no? Perhaps I'm missing something here.. What *exactly* makes it faster than numeric, and why couldn't numeric use that improvement? The one thing I can think of right off would be having a 64bit-base numeric type instead of the current 32bit-base (which limits us to base-10,000 while 64bit would give us base-1,000,000,000, which means more done in one operation and so less work overall- *if* you can do fast 64bit integer operations, which isn't necessairly the case on all architectures...). If that's where the improvment is then let's add a 'numeric64' type. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Lock partitions
Mark Wong [EMAIL PROTECTED] writes: Oops! 'autoreconf --install' is what I run to generate all that stuff. Ah, better. I see at least part of the problem: CREATE OR REPLACE FUNCTION stock_level (INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS '/home/tgl/dbt2/storedproc/pgsql/c/../../../storedproc/pgsql/c/funcs' LANGUAGE C STRICT; psql:/home/tgl/dbt2/scripts/pgsql/../../storedproc/pgsql/c/stock_level.sql:7: ERROR: incompatible library /home/tgl/dbt2/storedproc/pgsql/c/../../../storedproc/pgsql/c/funcs.so: missing magic block HINT: Extension libraries are now required to use the PG_MODULE_MAGIC macro. You need to add something like this to funcs.c: #include executor/spi.h /* this should include most necessary APIs */ #include executor/executor.h /* for GetAttributeByName() */ #include funcapi.h /* for returning set of rows in order_status */ + + #ifdef PG_MODULE_MAGIC + PG_MODULE_MAGIC; + #endif /* #define DEBUG With that change, I didn't see run_workload report any errors, but maybe I don't know where to look. I'm not sure how this bug could have led to a type not registered error ... the query should've just failed outright. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] XML support wiki page
Peter Eisentraut wrote: For those who don't read all the threads, I'll repeat it here. I've put up a wiki page working out the mysterious XML support: http://developer.postgresql.org/index.php/XML_Support This is pretty much my talk from the conference. The short status is that we have quite a bit of code ready and willing for 8.3. Some factions are working on sneaking some of that into 8.2, but not me. :) We have decided against any XML additions for 8.2, based on your suggestions. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Lock partitions
Tom Lane wrote: Mark Wong [EMAIL PROTECTED] writes: Oops! 'autoreconf --install' is what I run to generate all that stuff. Ah, better. I see at least part of the problem: CREATE OR REPLACE FUNCTION stock_level (INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS '/home/tgl/dbt2/storedproc/pgsql/c/../../../storedproc/pgsql/c/funcs' LANGUAGE C STRICT; psql:/home/tgl/dbt2/scripts/pgsql/../../storedproc/pgsql/c/stock_level.sql:7: ERROR: incompatible library /home/tgl/dbt2/storedproc/pgsql/c/../../../storedproc/pgsql/c/funcs.so: missing magic block HINT: Extension libraries are now required to use the PG_MODULE_MAGIC macro. You need to add something like this to funcs.c: #include executor/spi.h /* this should include most necessary APIs */ #include executor/executor.h /* for GetAttributeByName() */ #include funcapi.h /* for returning set of rows in order_status */ + + #ifdef PG_MODULE_MAGIC + PG_MODULE_MAGIC; + #endif /* #define DEBUG With that change, I didn't see run_workload report any errors, but maybe I don't know where to look. I'm not sure how this bug could have led to a type not registered error ... the query should've just failed outright. The error is captured in dbt2/scripts/output/*/client/error.log, where * is the run directory. Ok, I'll give it a shot on my system. Thanks, Mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Mid cycle release?
* Joshua D. Drake ([EMAIL PROTECTED]) wrote: Updateable views Bitmap indexes Recursive queries We would release in June? Could we get autovacuum enabled by default too? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Mid cycle release?
Stephen Frost wrote: * Joshua D. Drake ([EMAIL PROTECTED]) wrote: Updateable views Bitmap indexes Recursive queries We would release in June? Could we get autovacuum enabled by default too? I certainly hope not... I don't really feel like turning it off all the time. Joshua D. Drake Thanks, Stephen -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Lock partitions
Mark Wong [EMAIL PROTECTED] writes: Tom Lane wrote: With that change, I didn't see run_workload report any errors, but maybe I don't know where to look. The error is captured in dbt2/scripts/output/*/client/error.log, where * is the run directory. Hm ... here's what I see in there: Thu Sep 14 15:19:16 2006 tid:-1430387232 client.c:129 20 DB worker threads have started Thu Sep 14 15:19:31 2006 tid:1087957312 libpq/dbc_new_order.c:111 ERROR: deadlock detected DETAIL: Process 5334 waits for ShareLock on transaction 3505055; blocked by process 5363. Process 5363 waits for ShareLock on transaction 3505049; blocked by process 5334. CONTEXT: SQL statement UPDATE stock SET s_quantity = s_quantity - 10 WHERE s_i_id = 48368 AND s_w_id = 1 Thu Sep 14 15:21:18 2006 tid:1089960256 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1089689920 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090636096 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090230592 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090365760 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090095424 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1089825088 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090500928 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090906432 client_interface.c:33 socket closed on _receive Thu Sep 14 15:21:18 2006 tid:1090771264 client_interface.c:33 socket closed on _receive Is the deadlock failure expected? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Lock partitions
Tom Lane wrote: Mark Wong [EMAIL PROTECTED] writes: Tom Lane wrote: With that change, I didn't see run_workload report any errors, but maybe I don't know where to look. The error is captured in dbt2/scripts/output/*/client/error.log, where * is the run directory. Hm ... here's what I see in there: Thu Sep 14 15:19:16 2006 tid:-1430387232 client.c:129 20 DB worker threads have started Thu Sep 14 15:19:31 2006 tid:1087957312 libpq/dbc_new_order.c:111 ERROR: deadlock detected DETAIL: Process 5334 waits for ShareLock on transaction 3505055; blocked by process 5363. Process 5363 waits for ShareLock on transaction 3505049; blocked by process 5334. CONTEXT: SQL statement UPDATE stock SET s_quantity = s_quantity - 10 WHERE s_i_id = 48368 AND s_w_id = 1 [snip] Is the deadlock failure expected? Ooh, that's interesting. Deadlock failure is possible although I think we would all prefer that it didn't happen. In the scheme of the workload having failed transactions is ok. So with respect to having an invalid test run it's something I wouldn't worry about too much if it's infrequent. Mark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Mid cycle release?
* Joshua D. Drake ([EMAIL PROTECTED]) wrote: Stephen Frost wrote: * Joshua D. Drake ([EMAIL PROTECTED]) wrote: Updateable views Bitmap indexes Recursive queries We would release in June? Could we get autovacuum enabled by default too? I certainly hope not... I don't really feel like turning it off all the time. The change had been put into CVS at one point as a pretty-much agreed-upon thing to do, aiui. It was removed mainly because it caused problems for the regression tests which were enough that it was going to take a while to fix them all so the change was postponed to 8.3... Quite a few people (myself included) had really been hoping to see it in 8.2 and it's been the going-forward plan ever since autovacuum was put into the backend (in fact, iirc, having it in the backend was a prerequisite to having it on by default). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Mid cycle release?
Quite a few people (myself included) had really been hoping to see it in 8.2 and it's been the going-forward plan ever since autovacuum was put into the backend (in fact, iirc, having it in the backend was a prerequisite to having it on by default). w00t, more processes doing things they shouldn't be doing, but doing them automatically at times when they shouldn't be done because of some arbitrary calculation that really isn't documented that well in some conf file! O.k. that was negative, sorry. Frankly I think that turning autovacuum on by default pretty much equates to, I am lazy, and I don't want to actually evaluate my needs. Lets just go with MS Access Joshua D. Drake Thanks, Stephen -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(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] Mid cycle release?
O.k. that was negative, sorry. Frankly I think that turning autovacuum on by default pretty much equates to, I am lazy, and I don't want to actually evaluate my needs. Lets just go with MS Access Please ignore my negativity today. I apologize. I do not want autovacuum turned on by default but it isn't that big of a deal. Take care. Joshua D. Drake Joshua D. Drake Thanks, Stephen -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(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] dump / restore functionality
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Naz Gassiep wrote: Did this make it into the to-do list for 8.3 ? Don't worry about the to-do list too much. In particular, if you're imagining that being in the TODO list will in itself cause anyone to work on it, you're much mistaken about this community operates. Scratching your own itch is the general rule. I can add it to the TODO list if people want it. The original discussion seemed rather unfocused for me to add it to TODO. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Mid cycle release?
* Joshua D. Drake ([EMAIL PROTECTED]) wrote: w00t, more processes doing things they shouldn't be doing, but doing them automatically at times when they shouldn't be done because of some arbitrary calculation that really isn't documented that well in some conf file! I'd love for it to be improved. If you've got specific suggestions on improvments which could be made then please bring them up on the list. In general I've been reasonably happy with it and it *is* improving as people work on it. Having it enabled by default may get more people interested in improving it too. O.k. that was negative, sorry. Frankly I think that turning autovacuum on by default pretty much equates to, I am lazy, and I don't want to actually evaluate my needs. Lets just go with MS Access It would be kind of nice to have internal database processes, you know, handled *internally*. While autovacuum might not be configured perfectly for a given situation at the outset in the ideal world it would be able to essentially self-configure itself over time. There have been ideas floated to get us closer to that such as the dead-tuple (or dead-page?) list. Unfortunately I'm not really keen on the we use MVCC internally, and MVCC needs it, therefore you as the admin have to deal with it argument. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Inconsistency in extended-query-protocol
Tom Lane wrote: Guillaume Smet [EMAIL PROTECTED] writes: On 9/13/06, Tom Lane [EMAIL PROTECTED] wrote: statement: querystring Simple Query parse stmt: querystring Parse bind stmt/portal: querystring Bind execute stmt/portal: querystringExecute I agree with that. OK, Bruce hasn't said anything so I went ahead and changed it, as per attached patch. Sure, whatever people using it like. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] ECPG connection target formats
On Tue, Aug 29, 2006 at 02:36:21PM +0200, Michael Meskes wrote: [Various ECPG connection string problems.] Fixed. Are any of these changes considered bug fixes that will be backpatched, or should I prepare different documentation patches for different versions? With the recent talk about releasing 8.1.5 soon I'm hoping to submit a patch before that happens. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Release notes
Christopher Browne wrote: Seems to me that what I mostly do is print off a copy, show how thick it is, and say There are a really a lot of things improved, as visible on this list; alas, few are obviously 'sexy' new things... Think marshmallow explosion. Lots of white, fluffy stuff everywhere. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Mid cycle release?
Stefan Kaltenbrunner wrote: Interesting idea but we already have one of the fastest release cycles of all database systems and some people would like to see a larger cycle. I think the key complaint is about how painful the upgrade process is and if you still get fixes for previous releases if you are not willing to make the big jump. regards, Lukas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Release notes
Alvaro Herrera wrote: Bruce Momjian wrote: Robert Treat wrote: On Tuesday 12 September 2006 14:49, Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I again will not be able to complete the release notes today as promised. My next target date is Monday, August 18. Sorry. Will that be in a few years, or are you traveling backwards in time? ;-) Sorry, September 18. I will probably be done before then, but it seems best to set a date I know I will hit. Here we go again with another developer who keeps making endless promises for vaporware patches that never show up. We've already set on-disk bit-map indexes straight on this and I think giving you special treatment sets a bad tone for the project. At this point I think we have to cut the release notes from this release... maybe they can be added back in for 8.3. Very good one! Yeah, it was funny, but it points a problem which is that we are overloading you to do the release notes thing. I agree that we should push individual developers to include release notes updates on the patches they submit. They are easier to write than the documentation update in any case (which as you say, not everyone submits), mainly because they are way shorter. (Or maybe not _push_ them to do that, but at least not forbid updating the release notes which AFAIK is the current policy.) There are problems with this. First, since everyone isn't going to do it, I still have to go through all the CVS logs, and then I have to merge the created list to avoid duplicates. Then there is the problem that we need consistent wording through the release notes, so again, I have to wack around some more text. Doing it in one pass is the most reliable, and efficient. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Not-so-open items
Tom Lane wrote: There are several entries on the 8.2 open-items list that I think can be removed: Fix backward array comparison - subset Done (this was redundant with the containment-operator item) OK, that wasn't clear to me. Store only active XIDs in subtransaction cache Per my note just now, this probably should wait for 8.3. OK, added to TODO. Double vacuum speed on tables with no indexes I changed the locking thing I was worried about. Unless Greg wants to do some real-world performance measurements to confirm or refute that change, I think this can be closed. OK, removed. Fix excessive page checking for new btree split code Per my note yesterday, I can't reproduce the misbehavior I saw six weeks ago, so I recommend we leave the code alone. OK, removed. Suppress error on bind parameters of unknown types I haven't heard one single person speak up to say yeah, that's a good idea, so I conclude it probably isn't. Recommend we not change it. OK, removed. BTW, pushing out an 8.1.5 is probably a good idea, but what's it doing in the 8.2 open-items list? Especially in the documentation section? It is something that has to happen before final release, but is not a _code_ item, so I threw it there. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Draft release notes
Hannu Krosing wrote: ?hel kenal p?eval, N, 2006-09-14 kell 01:12, kirjutas Bruce Momjian: Here is an early draft of the release notes. It needs more polish and review: http://momjian.us/cgi-bin/pgrelease I will catch up on my email tomorrow, update the open items list for 8.2, and then return to the release notes for cleanup. You should leave my name out from plpython enchancements, the actual programming was all done by Sven. * Add named parameters to plpython args[] array (Hannu Krosing, Sven Suursoho) * Return composite-types from plpython as dictionary (Hannu Krosing, Sven Suursoho) * Return result-set from plpython as list, iterator or generator (Hannu Krosing, Sven Suursoho) OK, updated. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Draft release notes
Guillaume Smet wrote: On 9/14/06, Bruce Momjian [EMAIL PROTECTED] wrote: Here is an early draft of the release notes. It needs more polish and review: http://momjian.us/cgi-bin/pgrelease AFAICS the log_duration behaviour change made by Tom a few days ago is not there. The top says: [ CURRENT AS OF 2006-09-07 ] -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.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] [ADMIN] Vacuum error on database postgres
Joshua D. Drake [EMAIL PROTECTED] writes: One possible fix today would be to make ANALYZE take ShareUpdateExclusive lock instead, thus ensuring there is only one ANALYZE at a time on a table. Why not an internal lock that people don't see? We could add another LockTagType just for ANALYZE, but that seems like rather a lot of infrastructure to support an extremely narrow corner case, namely two people doing database-wide ANALYZE at the same time inside transaction blocks. (If they do it outside a transaction block then the ANALYZE is divided into multiple xacts and so doesn't try to hold locks on multiple tables concurrently. autovacuum won't try to do that either.) There's no such animal as an internal lock people don't see --- if we went this way it'd propagate into user-visible entries in pg_locks, for example. ISTM it should be sufficient to use ShareUpdateExclusiveLock. The only real argument I can see against it is you couldn't ANALYZE and VACUUM a table at the same time ... but that's probably a bad idea anyway, especially if we extend ANALYZE to estimate dead-tuple statistics. regards, tom lane ---(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] Draft release notes
Great. Added: http://momjian.postgresql.org/cgi-bin/pgrelease --- Teodor Sigaev wrote: * Improve multicolumn GiST index (oleg,teodor) * GiST indexes now are clusterable (teodor) * tsearch2 improvements (oleg, teodor): - multibyte encoding support (including UTF8) - query rewriting support - improve ranking functions - thesaurus dictionary - Ispell dictionary now recognize MySpell format, used by OpenOffice. - support of GIN * new operators for one-dimensional array (@, @, ) with GIN support (teodor) Bruce Momjian wrote: Here is an early draft of the release notes. It needs more polish and review: http://momjian.us/cgi-bin/pgrelease I will catch up on my email tomorrow, update the open items list for 8.2, and then return to the release notes for cleanup. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Not-so-open items
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Store only active XIDs in subtransaction cache Per my note just now, this probably should wait for 8.3. OK, added to TODO. Actually, I realized this morning that there isn't anything there that the current code doesn't do already. A subxact will never be assigned an XID in the first place unless it stores tuples into the database. (This means the do-I-need-to-do-something tests in RecordSubTransactionCommit and RecordSubTransactionAbort are pretty much redundant...) So you might as well drop it from TODO --- perhaps there are variant ideas we could use, but I don't know what they are. regards, tom lane ---(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] Draft release notes
Magnus Hagander wrote: Here is an early draft of the release notes. It needs more polish and review: http://momjian.us/cgi-bin/pgrelease I will catch up on my email tomorrow, update the open items list for 8.2, and then return to the release notes for cleanup. * Allow regression tests to be run on Win32 without MinGW (Magnus, Tom) This is not entirsely correect. What was done was a re-implementation of pg_regress in C from shellscript. This is a prerequisite for running the tests completely without mingw, but you can't do that yet (there needs to be rules in the VC build stuff to deal with the regression output files that need to be edited, for example - this is done with sed from the Makefile today. OK, updated wording: * Reimplement the regression test as a C program (Magnus, Tom) -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Draft release notes
OK, all updated. Thanks. --- Sergey E. Koposov wrote: The list of functions for: * Add SQL2003-standard statistical aggregates (Sergey Koposov) regr_intercept, regr_slope, regr_r2, corr, covar_samp, covar_pop, regr_avgx, regr_avgy, regr_sxy, regr_sxx, regr_syy, regr_count Also, I guess that the point * Aggregate functions now support multiple input arguments (Tom) should be * Aggregate functions now support multiple input arguments (Sergey Koposov, Tom) instead Regards, Sergey *** Sergey E. Koposov Max Planck Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.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] Not-so-open items
OK, removed. --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Store only active XIDs in subtransaction cache Per my note just now, this probably should wait for 8.3. OK, added to TODO. Actually, I realized this morning that there isn't anything there that the current code doesn't do already. A subxact will never be assigned an XID in the first place unless it stores tuples into the database. (This means the do-I-need-to-do-something tests in RecordSubTransactionCommit and RecordSubTransactionAbort are pretty much redundant...) So you might as well drop it from TODO --- perhaps there are variant ideas we could use, but I don't know what they are. regards, tom lane ---(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 -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Opinion wanted on UUID/GUID datatype output formats.
On 9/14/06, Gevik Babakhani [EMAIL PROTECTED] wrote: At this moment we (almost) have a uuid/guid datatype. As suggested in earlier discussion we provide a raw/plain output of the uuid type: devdb=# select * from tbluuid; pk| --+ 6b13c5a1afb4dcf5ce8f8b4656b6c93c | 01e40a79b55b6e226bffb577e960453d | (2 rows) Which is a Good Format. I was wondering if we want to have a formatting function to be able to provide other common formats of the uuid/guid? something like: select format_uuid(mypk,'format2') from tbluuid; and then get: 6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c Ehm, I would strongly suggest rather something similar to to_char() family of date-and-other-stuff formatting function, in the above example: SELECT to_char(mypk,'----') FROM tbluuid; ...or maybe some shorter syntax, like '8N-4N-4N-4N-12N'). This way it gains both flexibility (ANY format user wants is possible, say using slashes as separator (great for hash-like filename generator) and readability (no need to look for 'formatN' definition). Regards, Dawid ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] New version of money type
Darcy, The biggest argument about the money type is that it has an unrealistic limit. Funny, I thought it was the lack of operators, conversions and any clear plan on how to have a money type that supports multiple currencies. Or are you working on those? That would be keen ... -- Josh Berkus PostgreSQL @ Sun San Francisco ---(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] Draft release notes
Simon Riggs wrote: On Thu, 2006-09-14 at 01:12 -0400, Bruce Momjian wrote: Here is an early draft of the release notes. It needs more polish and review: http://momjian.us/cgi-bin/pgrelease I will catch up on my email tomorrow, update the open items list for 8.2, and then return to the release notes for cleanup. A few changes: - Improve COPY performance (Alon Goldshuv) - Optimize the locking used by sequential scans (Qingqing Zhou) - Re-enable full_page_writes (Tom) This flag can now be used even with PITR. It is turned **on** (not off!) automatically between pg_start_backup() and pg_stop_backup() calls. - Add support for forcing a switch to a new xlog file (Simon Riggs) ...should include Tom OK, done. - Improve performance of replaying WAL logs on a backup server (Simon Riggs) ...I describe this as Restartable Recovery... don't think it improves performance exactly, just avoids needing to re-run it from scratch OK, new wording: * Allow WAL replay to be restared quicker in case of a crash (Simon Riggs) The improvement is to do periodic checkpoints during WAL recovery, so if there is a crash, future WAL recovery is shortened. My memory says this was eventually removed, even though it was committed for a time. Am I wrong? - Make EXPLAIN sampling smarter, to avoid excessive sampling delay (Martijn van Oosterhout) I see a reversion for EXPLAIN ANALYZE only: date: 2006/06/09 19:30:56; author: tgl; state: Exp; lines: +27 -172 Revert sampling patch for EXPLAIN ANALYZE; it turns out to be too unreliable because node timing is much less predictable than the patch expects. I kept the API change for InstrStopNode, however. so the item is probably still OK. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Opinion wanted on UUID/GUID datatype output formats.
Gevik,
select format_uuid(mypk,'format3') from tbluuid;
and then get: {6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c}
(which would be MSSQL compatible)
What do the PostgreSQL masters think? :)
There are no masters here. Except in replication.
I think that we should have a formatting function, but it should be developer
defined rather than pre-set, like to_char is. For example, instead of:
select format_uuid(mypk,'format3') from tbluuid;
and then get: {6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c}
Have:
select format_uuid(mypk,'HH----HHH')
... to get the same result. Or you could even support regexes:
select format_uuid(mypk,'[0-9a-f]{6}-[0-9a-f]{6}-[0-9a-f]{6}-[0-9a-f]{6}')
... but something which allows the definition of ad-hoc formating masks so
that we can cover compatibility with products of which we're not yet aware.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Draft release notes
Bruce Momjian [EMAIL PROTECTED] writes: Simon Riggs wrote: My memory says this was eventually removed, even though it was committed for a time. Am I wrong? - Make EXPLAIN sampling smarter, to avoid excessive sampling delay (Martijn van Oosterhout) I see a reversion for EXPLAIN ANALYZE only: date: 2006/06/09 19:30:56; author: tgl; state: Exp; lines: +27 -172 Revert sampling patch for EXPLAIN ANALYZE; it turns out to be too unreliable because node timing is much less predictable than the patch expects. I kept the API change for InstrStopNode, however. so the item is probably still OK. No, Simon is right, that entry should be removed --- there's nothing interesting left of the patch :-( regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Mid cycle release?
No one would expect Oracle to install Oracle and walk away. We are not MySQL, nor MS Access. I can definitely see where you're coming from, it's a sort of tough-love scenario. There are legitimate counter arguments, though. The most obvious is that anyone who *does* evaluate their needs properly shouldn't have too much trouble turning it off, whereas there are lots of small database users out there who find having to set up a vacuum cron a pain. Example: I'm in the process of setting up a typo blog, using postgresql of course, but the database setup was secondary to the main thing that I was doing, and I'd completely forgotten about setting up a cron. Now I'm unlikely to produce blog posts at a rate that will cause the database to grow out of the minuscule range, but it should still be done, right? I have to ask, what's wrong with lazy users? Software which allows you to be lazy gives you a warm tingly feeling, and you install it on your intranet server when no-one's looking. We want people to think of postgresql that way. There are lots of MySQL specific pieces of software out there that started out as some guy/girl with a PHP and MySQL type of book. We can't turn that clock back, but making postgresql easier for the masses has to be a good thing for its adoption. The native win32 port is the poster child for this. It was a big PR win, no? I would argue that leaving autovacuum off is only justifiable if we feel that it's going to be a bad choice for the majority of users. Many of the users who frequent postgresql lists understand the trade-off, but the ones that we're trying to attract don't. Is it better for them to discover manual vacuums when they're trying to incrementally improve performance (with the risk that they never discover them at all), or when their database is running like a dog because they've never vacuumed it at all? One solution might be to turn it on in turn-key solutions: linux distro RPMs, Win32 installer (is it on there already?) etc, but leave it turned off in the source release. Would that help you, or are your clients using RPMs or whatever? Cheers Tom -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Fixed length data types issue
Bruce Momjian [EMAIL PROTECTED] writes: One very nifty trick would be to fix char to act as CHAR(), and map CHAR(1) automatically to char. Sorry, probably a stupid idea considering multi-byte encodings. I suppose it could be an optimization for single-byte encodings, but that seems very limiting. No, there are lots of single-byte encoding databases. And one day we'll have per-column encoding anyways and there are lots of databases that have columns that want to be one-character ascii encoded fields. It's limited but I wouldn't say it's very limiting. In the cases where it doesn't apply there's no way out anyways. A UTF8 field will need a length header in some form. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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
