Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)
Here's a 2nd version of the latch patch. Now with a Windows implementation. Comments welcome. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/configure b/configure index bd9b347..432cd58 100755 --- a/configure +++ b/configure @@ -27773,6 +27773,13 @@ _ACEOF SHMEM_IMPLEMENTATION=src/backend/port/win32_shmem.c fi +# Select latch implementation type. +if test $PORTNAME != win32; then + LATCH_IMPLEMENTATION=src/backend/port/unix_latch.c +else + LATCH_IMPLEMENTATION=src/backend/port/win32_latch.c +fi + # If not set in template file, set bytes to use libc memset() if test x$MEMSET_LOOP_LIMIT = x ; then MEMSET_LOOP_LIMIT=1024 @@ -29098,7 +29105,7 @@ fi ac_config_files=$ac_config_files GNUmakefile src/Makefile.global -ac_config_links=$ac_config_links src/backend/port/dynloader.c:src/backend/port/dynloader/${template}.c src/backend/port/pg_sema.c:${SEMA_IMPLEMENTATION} src/backend/port/pg_shmem.c:${SHMEM_IMPLEMENTATION} src/include/dynloader.h:src/backend/port/dynloader/${template}.h src/include/pg_config_os.h:src/include/port/${template}.h src/Makefile.port:src/makefiles/Makefile.${template} +ac_config_links=$ac_config_links src/backend/port/dynloader.c:src/backend/port/dynloader/${template}.c src/backend/port/pg_sema.c:${SEMA_IMPLEMENTATION} src/backend/port/pg_shmem.c:${SHMEM_IMPLEMENTATION} src/backend/port/pg_latch.c:${LATCH_IMPLEMENTATION} src/include/dynloader.h:src/backend/port/dynloader/${template}.h src/include/pg_config_os.h:src/include/port/${template}.h src/Makefile.port:src/makefiles/Makefile.${template} if test $PORTNAME = win32; then @@ -29722,6 +29729,7 @@ do src/backend/port/dynloader.c) CONFIG_LINKS=$CONFIG_LINKS src/backend/port/dynloader.c:src/backend/port/dynloader/${template}.c ;; src/backend/port/pg_sema.c) CONFIG_LINKS=$CONFIG_LINKS src/backend/port/pg_sema.c:${SEMA_IMPLEMENTATION} ;; src/backend/port/pg_shmem.c) CONFIG_LINKS=$CONFIG_LINKS src/backend/port/pg_shmem.c:${SHMEM_IMPLEMENTATION} ;; +src/backend/port/pg_latch.c) CONFIG_LINKS=$CONFIG_LINKS src/backend/port/pg_latch.c:${LATCH_IMPLEMENTATION} ;; src/include/dynloader.h) CONFIG_LINKS=$CONFIG_LINKS src/include/dynloader.h:src/backend/port/dynloader/${template}.h ;; src/include/pg_config_os.h) CONFIG_LINKS=$CONFIG_LINKS src/include/pg_config_os.h:src/include/port/${template}.h ;; src/Makefile.port) CONFIG_LINKS=$CONFIG_LINKS src/Makefile.port:src/makefiles/Makefile.${template} ;; diff --git a/configure.in b/configure.in index 7b09986..7f84cea 100644 --- a/configure.in +++ b/configure.in @@ -1700,6 +1700,13 @@ else SHMEM_IMPLEMENTATION=src/backend/port/win32_shmem.c fi +# Select latch implementation type. +if test $PORTNAME != win32; then + LATCH_IMPLEMENTATION=src/backend/port/unix_latch.c +else + LATCH_IMPLEMENTATION=src/backend/port/win32_latch.c +fi + # If not set in template file, set bytes to use libc memset() if test x$MEMSET_LOOP_LIMIT = x ; then MEMSET_LOOP_LIMIT=1024 @@ -1841,6 +1848,7 @@ AC_CONFIG_LINKS([ src/backend/port/dynloader.c:src/backend/port/dynloader/${template}.c src/backend/port/pg_sema.c:${SEMA_IMPLEMENTATION} src/backend/port/pg_shmem.c:${SHMEM_IMPLEMENTATION} + src/backend/port/pg_latch.c:${LATCH_IMPLEMENTATION} src/include/dynloader.h:src/backend/port/dynloader/${template}.h src/include/pg_config_os.h:src/include/port/${template}.h src/Makefile.port:src/makefiles/Makefile.${template} diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c index 615a7fa..094d0c9 100644 --- a/src/backend/access/transam/twophase.c +++ b/src/backend/access/transam/twophase.c @@ -55,6 +55,7 @@ #include miscadmin.h #include pg_trace.h #include pgstat.h +#include replication/walsender.h #include storage/fd.h #include storage/procarray.h #include storage/sinvaladt.h @@ -1025,6 +1026,13 @@ EndPrepare(GlobalTransaction gxact) /* If we crash now, we have prepared: WAL replay will fix things */ + /* + * Wake up all walsenders to send WAL up to the PREPARE record + * immediately if replication is enabled + */ + if (max_wal_senders 0) + WalSndWakeup(); + /* write correct CRC and close file */ if ((write(fd, statefile_crc, sizeof(pg_crc32))) != sizeof(pg_crc32)) { @@ -2005,6 +2013,13 @@ RecordTransactionCommitPrepared(TransactionId xid, /* Flush XLOG to disk */ XLogFlush(recptr); + /* + * Wake up all walsenders to send WAL up to the COMMIT PREPARED record + * immediately if replication is enabled + */ + if (max_wal_senders 0) + WalSndWakeup(); + /* Mark the transaction committed in pg_clog */ TransactionIdCommitTree(xid, nchildren, children); @@ -2078,6 +2093,13 @@ RecordTransactionAbortPrepared(TransactionId xid, XLogFlush(recptr); /* + * Wake up all walsenders to send WAL up to the ABORT PREPARED record + * immediately if replication is enabled + */ + if (max_wal_senders 0) + WalSndWakeup(); + + /* * Mark the transaction
Re: [HACKERS] How to construct an exact plan
hello ... here is the ultimate revelation of planner hints in postgres ... let us praise oleg and teodor for solving a practical problem for practical people ... http://www.sai.msu.su/~megera/wiki/plantuner try this one ... it is excellent and definitely helpful for many many people out there. unfortunately this code is not too well known. many thanks, hans On Aug 30, 2010, at 9:41 PM, Pavel Stehule wrote: 2010/8/30 Pei He hepeim...@gmail.com: Hi, I am hacking postgresql 8.2.5. a) and b) do not work for me. The situation is that I made a join operator, and a scan operator. And, The join operator requires the scan operator as the inner. So, I need to have the full control of the join plan. I am not ready to provide the optimization support for the two new operators. And, I want to run some performance tests before working on the optimization part. So, I want to know if it is possible to directly create a path or a plan, and do a unit test for the operators. yes, it is possible - but it isn't simple. I thing, so better is simple implementation of all parts and then runtime blocking some (for you not interesting) buildin methods via SET enable_ to off. Regards Pavel Stehule Thanks -- Pei On Mon, Aug 30, 2010 at 1:59 PM, Josh Berkus j...@agliodbs.com wrote: I have developed a new operators, and I want to do some tests on it. I do not want the optimizer to choose the plan for me, and I need to construct a plan as exact as I want. Can anyone provide me a way to achieve that? a) easy: choose a simple enough query that its plan is always predictable. b) moderate: choose a query whose plan is predictable if you manipulate the enable_* configuration settings c) hard: hack the PostgreSQL planner to choose a specific execution plan, and recompile Postgres. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Performance Farm Release
Stephen Frost wrote: You can certainly run it yourself locally w/o setting it up to report back to the build or performance farm.. So, yes, you can, you'll just have to look through the outputs yourself and it won't necessairly make much sense unless you've been doing those runs for a period of time to get a feel for how volatile the speed is on your system.. Thanks again to Scott for working on this all summer, and to Stephen and Noblis for helping fund it. There were a lot of small pieces that needed to assemble just right to make this all fit together in a way I hope will make it integrate into the core infrastructure soon. It's probably not obvious to everyone else where this stands as far as reporting results goes though, so let me expand on what Stephen said here. When you run performance tests with this, those are stored locally. Scott demonstrated that you can get basic reports out of that, and I'm content the right initial tests are being run and the most useful data to record is being saved. But none of the performance numbers are sent anywhere yet--they're just stored as CSV files. From the README: WHENEVER YOU RUN A PERFORMANCE TEST, RUN WITH --test, so not to spam the buildfarm server. That's the bad news that warning is conveying: you can't upload results yet. I expect the next steps here to look like this: 1) Nail down what subset of the information gathered locally should be uploaded to the buildfarm master server. Probably just the same columns of data already being saved for each test, but perhaps with some extra metadata. The local animal will also have graphs and such, but unrealistic to upload all of those for a number of reasons I think. Only really useful to drill down when there is some sort of regression I expect, and hopefully the animal is still alive when that happens. 2) Update the buildfarm server code to accept and store that data. 3) Update this perffarm client to talk to that. 4) Merge the perfarm fork changes into the mainline buildfarm code. I expect continued bitrot of this code as changes are made to the regular buildfarm client, so it might be worth considering that sooner rather than later. My understanding is that the code for the server side of the buildfarm isn't public to everyone right now, just because of time limitations getting it cleaned up for that. So a couple of parts here are being funneled through how much spare time Andrew has, and there are more important git and buildfarm related things for him to worry about right now. Presuming nothing exciting on this happens before then, I'm hoping that I can catch up with Andrew at PG West and map out how to get the rest of this done, so it goes live somewhere during 9.1 development. Now that the code has been released from the Noblis fortress, I can start cleaning up some of the little details on it before then too (i.e. not working for anything but 9.0 yet). -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Estimation of Plan quality
Dear all Can someone tell me how to estimate the quality of my plan. For example if the ideal query plan generated by Dynamic Programming has a plan quality of 1.0, and suppose I generate my query plan with some greedy method or something, it will be worse (not as optimal as) than ideal plan by, say, 1.5 or 2.0 times , how can I know this factor? Should it be estimated from the execution time? In the plan generated by PostgreSQL when I give explain query, I see Plan cost at the top of the plan with some exponential value. What does it indicate? Is it the estimated execution time? or Is it the search space that has been considered during plan execution? Thanks Vamsi
Re: [HACKERS] huia and moa versus old PG branches
On Mon, Aug 30, 2010 at 3:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: The new buildfarm machines huia and moa aren't doing terribly well with the older PG branches. This isn't really those machines' fault; what I find after a bit of digging is that we just didn't have good support for 64-bit Solaris until relatively recently. In particular: Yeah, sorry - been working on that but keep getting distracted. * There was no 64-bit spinlock assembler code that worked with Sun's compiler until 8.2. The first attempt to support it was here: http://archives.postgresql.org/pgsql-committers/2005-12/msg00507.php although that got whacked around quite a bit before 8.2 final. Right - that's why I'm using --disable-spinlocks for 8.0/8.1 on moa. Unfortunately, that doesn't seem to work either. * gcc builds didn't fully work in 64-bit Solaris either until 8.3: http://archives.postgresql.org/pgsql-committers/2007-01/msg00154.php Before that patch, contrib didn't build because pgcrypto needs BYTE_ORDER to be defined. OK. huia, which is claimed on the buildfarm dashboard to be using Sun Studio but is actually using gcc, thus fails at the contrib make stage before 8.3. grumble. We had an issue with the keys which got switched around when these were setup - clearly it wasn't the keys, but the names. Andrew, can you swap the descriptions over please? moa, which is claimed on the buildfarm dashboard to be using gcc but is actually using cc, hits the spinlock problem in 8.0 and 8.1 and the BYTE_ORDER problem in 8.2. Per above, moa is configured with --disable-spinlocks for 8.1/8.0. Is something else broken in the configure code - or am I missing the point of --disable-spinlocks? This file was created by PostgreSQL configure 8.0.25, which was generated by GNU Autoconf 2.53. Invocation command line was $ ./configure --enable-cassert --enable-debug --enable-nls --enable-integer-datetimes \ --with-gssapi --with-pam --without-readline \ --enable-thread-safety --disable-spinlocks --prefix=/export/home/dpage/pgbuildfarm/moa/REL8_0_STABLE/inst \ --with-pgport=5688 That BYTE_ORDER patch is pretty small and safe, so I think it would be reasonable to back-patch it into 8.2 so that we have a uniform story that 64-bit Solaris is supported in 8.2 and up. The spinlock changes were significantly more invasive, so my feeling is we should not try to back-patch them, but just turn off moa for pre-8.2 branches. OK, I'll disable moa for pre-8.2. Also, although moa is actually green for 8.3, it's showing an initdb failure in 8.4 and up (cache lookup failed for type 0 while processing system_views.sql). I'm betting this is some sort of over-aggressive-optimization problem, but it's hard to tell much from the buildfarm logs. Could you look into that and find out exactly where it's failing? Yup. Thanks for the hint - I wasn't sure where to start with that one. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] huia and moa versus old PG branches
On Tue, Aug 31, 2010 at 10:28 AM, Dave Page dp...@pgadmin.org wrote: On Mon, Aug 30, 2010 at 3:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: Also, although moa is actually green for 8.3, it's showing an initdb failure in 8.4 and up (cache lookup failed for type 0 while processing system_views.sql). I'm betting this is some sort of over-aggressive-optimization problem, but it's hard to tell much from the buildfarm logs. Could you look into that and find out exactly where it's failing? Yup. Thanks for the hint - I wasn't sure where to start with that one. Looks like -xO4 caused this. -xO3 seems to be OK in a test. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)
On Tue, Aug 31, 2010 at 4:06 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Here's a 2nd version of the latch patch. Now with a Windows implementation. Comments welcome. Seems good. Two minor comments: rc = WaitForSingleObject(latch-event, timeout / 1000); if (rc == WAIT_FAILED) { ereport(ERROR, (errcode_for_socket_access(), errmsg(WaitForSingleObject() failed: error code %d, (int) GetLastError(; } if (rc == WAIT_TIMEOUT) break; /* timeout exceeded */ We should also check rc == WAIT_OBJECT_0? static volatile HANDLE waitingEvent = false; s/false/NULL? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: I: [HACKERS] About Our CLUSTER implementation is pessimal patch
Sorry for the very delayed review. On Wed, Jul 21, 2010 at 11:15 PM, Leonardo Francalanci m_li...@yahoo.it wrote: 2) what other areas can I comment more? I think the patch is almost ready to commit, but still have some comments for the usability and documentations. I hope native English speakers would help improving docs. * Documentation could be a bit more simplified like as CLUSTER requires twice disk spaces of your original table. The added description seems too difficult for standard users. * How to determine which method was used? We can get some information from trace_sort logs, but CLUSTER VERBOSE would be better to log which CLUSTER method was used. * How to control which method will be used? It might be good to explain we can control the method with enable_seqscan/indexscan. -- Itagaki Takahiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Git conversion progress report and call for testing assistance
On Sat, Aug 28, 2010 at 16:30, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Should we consider actually removing the Log Message header and just put the message right at the start? If there's no link first, that would be fairly obvious, I think.. Either way (header or not) is OK by me. Updated, with the removal of the header to help MUA previews. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] git: uh-oh
On Mon, Aug 30, 2010 at 05:03, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 25, 2010 at 2:39 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 25, 2010 at 1:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: The fact that the file was modified twice after being removed at rev 2.88 seems really wacko. Are you sure that's not contributing to what we're seeing here? Yeah, that was discussed in the earlier git-conversion thread that I pointed to. We never did figure out how that happened, though I speculated it might have been due to weirdness in Vadim's local checkout. Is it possible to just delete those two revisions from the CVS repository, and if so would it help? We certainly don't need 'em. cvs admin -o ? Magnus, is this something that you can try? Prune those could of wonky revisions after the delete and before the re-add prior to running the conversion, and see how that comes out? Yes, definitely. Do we have list of exactly which revisions it is, or a good way to find it? Other than random browsing of the history? :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] thousand unrelated data files in pg_default tablespace
Hello there is a dump from 8KB files Regard Pavel Stehule *** * PostgreSQL File/Block Formatted Dump Utility - Version 8.3.0 * * File: /srv/postgresql/data/base/3400014/27059918 * Options used: None * * Dump created on: Tue Aug 31 12:57:23 2010 *** Block 0 Header - Block Offset: 0x Offsets: Lower 40 (0x0028) Block: Size 8192 Version 4 Upper 8000 (0x1f40) LSN: logid 0 recoff 0x Special 8192 (0x2000) Items: 4 Free Space: 7960 TLI: 0x Prune XID: 0x Flags: 0x () Length (including item array): 40 Data -- Item 1 -- Length: 47 Offset: 8144 (0x1fd0) Flags: NORMAL Item 2 -- Length: 47 Offset: 8096 (0x1fa0) Flags: NORMAL Item 3 -- Length: 47 Offset: 8048 (0x1f70) Flags: NORMAL Item 4 -- Length: 47 Offset: 8000 (0x1f40) Flags: NORMAL *** End of File Encountered. Last Block Read: 0 *** $ ls -l /srv/postgresql/data/base/3400014/27059918 -rw--- 1 postgres postgres 8192 Jul 1 06:28 /srv/postgresql/data/base/3400014/27059918 $ ./pg_filedump /srv/postgresql/data/base/3400014/27059926 *** * PostgreSQL File/Block Formatted Dump Utility - Version 8.3.0 * * File: /srv/postgresql/data/base/3400014/27059926 * Options used: None * * Dump created on: Tue Aug 31 13:00:17 2010 *** Block 0 Header - Block Offset: 0x Offsets: Lower 48 (0x0030) Block: Size 8192 Version 4 Upper 8176 (0x1ff0) LSN: logid 0 recoff 0x Special 8176 (0x1ff0) Items: 6 Free Space: 8128 TLI: 0x0001 Prune XID: 0x Flags: 0x () Length (including item array): 48 BTree Meta Data: Magic (0x00053162) Version (2) Root: Block (0) Level (0) FastRoot: Block (0) Level (0) Special Section - BTree Index Section: Flags: 0x0008 (META) Blocks: Previous (0) Next (0) Level (0) CycleId (0) *** End of File Encountered. Last Block Read: 0 *** $ ls -l /srv/postgresql/data/base/3400014/27059918 -rw--- 1 postgres postgres 8192 Jul 1 06:28 /srv/postgresql/data/base/3400014/27059918 Pavel Stehule pavel.steh...@gmail.com 31.08.2010 09:32 To robert.mou...@lmc.eu cc Subject Re: [HACKERS] thousand unrelated data files in pg_default tablespace Zdar, preposilam ti report 2010/8/30 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I found a PostgreSQL 8.3 server (Linux) used for large OLAP where the data directory is bloating. There are more than one hundred thousand files - 8KB or 0KB long. The filenames are not transformable to names via oid2name. Does somebody know about similar bug? 1. 8.3.what? 2. Any signs of distress in the postmaster log? I'm wondering about being unable to complete checkpoints, or repeated backend crashes that might cause leakage of temp tables. 3. What's in the files --- do they appear to be tables, indexes, random temp files from sorts/hashes, or what? pg_filedump might help you here. muzes se na ten pg_filedump podivat a projet to tim, myslim, ale ze se to bude muset od nekud stahnout a prelozit Pavel regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)
On Fri, Aug 27, 2010 at 4:39 PM, Fujii Masao masao.fu...@gmail.com wrote: /* * XXX: Should we invent an API to wait for data coming from the * client connection too? It's not critical, but we could then * eliminate the timeout altogether and go to sleep for good. */ Yes, it would be very helpful when walsender waits for the ACK from the standby in upcoming synchronous replication. I propose to change WaitLatch() so that it accepts the socket descriptor as an argument, to wait for data coming from the client connection. WaitLatch() monitors not only the self-pipe but also the given socket. If -1 is supplied, it checks only the self-pipe. The socket should be monitored by using poll() if the platform has it, since poll() is usually more efficient. So I'd like to change Unix implementation of WaitLatch() as follows. Thought? --- define WaitLatch(latch, timeout) WaitLatchAndSocket(latch, -1, timeout) void WaitLatchAndSocket(Latch *latch, int sock, long timeout); { ... FD_SET(selfpipe_readfd, input_mask); if (sock != -1) FD_SET(sock, input_mask); #ifdef HAVE_POLL poll(...) #else select(...) #endif /* HAVE_POLL */ ... } --- Windows implementation of WaitLatchAndSocket() seems not to be so simple. We would need to wait for both the latch event and the packet from the socket by using WaitForMultipleObjectsEx(). Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5305: Postgres service stops when closing Windows session
On Sun, Aug 29, 2010 at 12:05 PM, Magnus Hagander mag...@hagander.net wrote: On Thu, Aug 26, 2010 at 22:59, Cristian Bittel cbit...@gmail.com wrote: I still believe this exit code 128 is related to pgAdmin opened during the clossing session on Remote Desktop. I have a Windows user login wich is not administrator just no privileged user, it cannot start/stop services, just monitoring. With pgAdmin window opened inside my disconected session, as Administrator if I close the another disconnected session, Postgres exit with 128 code. If the closing of a session on the remote desktop can affect a *service* then frankly that sounds like a serious isolation bug in Windows itself. The postmaster grabs the handle of the process when it's started and waits on that - that should never be affected by something in a different session. I think it's more likely that Windows just looses track when you terminate a lot of processes at once, and randomly kills off something - or at least *indicates* that something has been killed off. Did you reproduce this behavior? No, AFAIK nobody has managed to reproduce this behavior in any kind of consistent way. It's certainly been seen more than once in many places, but not consistently reproducible. This behaviour, no - but desktop heap exhaustion is very easy to reproduce. That's because the heap usage is caused by user32.dll which uses a consistent amount with each process started, which is allocated as the process is created. When I was working on the issue a couple of years ago, it was entirely predictable - user32.dll allocates N bytes and as soon as N * numbackends exceeds the allocated heap size, we fall over. It shouldn't matter as desktop heap is allocated on a per-session basis, but are you logging on using the service account to run your admin tasks Cristian? If so, do you see the problem if you login interactively using a different account? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] huia and moa versus old PG branches
Dave Page dp...@pgadmin.org writes: On Mon, Aug 30, 2010 at 3:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: Also, although moa is actually green for 8.3, it's showing an initdb failure in 8.4 and up (cache lookup failed for type 0 while processing system_views.sql). I'm betting this is some sort of over-aggressive-optimization problem, but it's hard to tell much from the buildfarm logs. Could you look into that and find out exactly where it's failing? Looks like -xO4 caused this. -xO3 seems to be OK in a test. Hmm. I wonder whether -xO4 invokes something comparable to strict aliasing optimizations. Does Sun Studio have anything corresponding to -fno-strict-aliasing ? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] huia and moa versus old PG branches
Dave Page dp...@pgadmin.org writes: On Mon, Aug 30, 2010 at 3:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: moa, which is claimed on the buildfarm dashboard to be using gcc but is actually using cc, hits the spinlock problem in 8.0 and 8.1 and the BYTE_ORDER problem in 8.2. Per above, moa is configured with --disable-spinlocks for 8.1/8.0. Is something else broken in the configure code - or am I missing the point of --disable-spinlocks? Hum ... you are right, there is something else broken there. It looks like, on platforms where we have an out-of-line assembler file, we attempt to assemble it whether or not --disable-spinlocks is selected (because configure sets TAS anyway). This is clearly something we should fix, but I've got mixed feelings about whether to back-patch it all the way, in view of the fact that nobody complained before. If you would like to keep running moa with --disable-spinlocks in the old branches, I'll do it. However, it looks like what you did amounts to --disable-spinlocks in *all* branches, which is definitely not what we want to test. If you can fix the buildfarm configuration so that that switch is really applied selectively to just pre-8.2 branches, then I think it'd be good to make that happen. If you can't, I think it'd be best to just drop the switch and forget about pre-8.2 on moa. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] huia and moa versus old PG branches
On Tue, Aug 31, 2010 at 2:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: On Mon, Aug 30, 2010 at 3:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: Also, although moa is actually green for 8.3, it's showing an initdb failure in 8.4 and up (cache lookup failed for type 0 while processing system_views.sql). I'm betting this is some sort of over-aggressive-optimization problem, but it's hard to tell much from the buildfarm logs. Could you look into that and find out exactly where it's failing? Looks like -xO4 caused this. -xO3 seems to be OK in a test. Hmm. I wonder whether -xO4 invokes something comparable to strict aliasing optimizations. Does Sun Studio have anything corresponding to -fno-strict-aliasing ? A quick Google gives: -xalias_level[=l] http://docs.sun.com/app/docs/doc/819-5265/bjaso?a=view -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] huia and moa versus old PG branches
On Tue, Aug 31, 2010 at 3:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: On Mon, Aug 30, 2010 at 3:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: moa, which is claimed on the buildfarm dashboard to be using gcc but is actually using cc, hits the spinlock problem in 8.0 and 8.1 and the BYTE_ORDER problem in 8.2. Per above, moa is configured with --disable-spinlocks for 8.1/8.0. Is something else broken in the configure code - or am I missing the point of --disable-spinlocks? Hum ... you are right, there is something else broken there. It looks like, on platforms where we have an out-of-line assembler file, we attempt to assemble it whether or not --disable-spinlocks is selected (because configure sets TAS anyway). This is clearly something we should fix, but I've got mixed feelings about whether to back-patch it all the way, in view of the fact that nobody complained before. If you would like to keep running moa with --disable-spinlocks in the old branches, I'll do it. Personally I have no interest in Solaris - I'm just providing animals because I can. I'll do whatever makes most sense for the project. However, it looks like what you did amounts to --disable-spinlocks in *all* branches, which is definitely not what we want to test. If you can fix the buildfarm configuration so that that switch is really applied selectively to just pre-8.2 branches, then I think it'd be good to make that happen. If you can't, I think it'd be best to just drop the switch and forget about pre-8.2 on moa. I did this already, based on other similar code in the default config file. My perl-fu is weak though - maybe screwed something up? # Add --disable-spinlocks on 8.1 and below if ($branch le 'REL8_1') { push(@{$conf{config_opts}},--disable-spinlocks) } -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] thousand unrelated data files in pg_default tablespace
Pavel Stehule pavel.steh...@gmail.com writes: there is a dump from 8KB files Well, those certainly look like tables/indexes not temp files. So we can rule out one theory. You're *certain* these aren't referenced from pg_class.relfilenode of any of the databases in the server? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5305: Postgres service stops when closing Windows session
On Tue, Aug 31, 2010 at 3:40 PM, Cristian Bittel cbit...@gmail.com wrote: To Dave's question, this behavior occurs on all Windows Server interactive sessions, no matter if Administrators or underpriviledge users, but is related to closing Windows interactive session while pgAdmin window is opened and connected to service. Nobody logon to Windows using postgres service user. Thanks Cristian. Can you reproduce the problem if you use psql instead of pgAdmin? Both use libpq to talk to the server, so if your theory is correct, I would expect to see the same crash. It's hard to see what would bring the server down though... -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] git: uh-oh
Magnus Hagander mag...@hagander.net writes: On Mon, Aug 30, 2010 at 05:03, Robert Haas robertmh...@gmail.com wrote: cvs admin -o ? Magnus, is this something that you can try? Prune those could of wonky revisions after the delete and before the re-add prior to running the conversion, and see how that comes out? Yes, definitely. Do we have list of exactly which revisions it is, or a good way to find it? Other than random browsing of the history? :-) I think the files in question are these: 2007-04-16 21:05 tgl * src/: backend/parser/gram.c, interfaces/ecpg/preproc/pgc.c, interfaces/ecpg/preproc/preproc.c: Temporarily re-add derived files, in hopes of straightening out their CVS status. 2007-04-16 21:06 tgl * src/: backend/parser/gram.c, interfaces/ecpg/preproc/pgc.c, interfaces/ecpg/preproc/preproc.c: And remove 'em again ... Look at the histories of these in cvsweb, and try to zap the versions that are later than the first FILE REMOVED event. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5305: Postgres service stops when closing Windows session
Dave Page wrote: On Tue, Aug 31, 2010 at 3:40 PM, Cristian Bittel cbit...@gmail.com wrote: To Dave's question, this behavior occurs on all Windows Server interactive sessions, no matter if Administrators or underpriviledge users, but is related to closing Windows interactive session while pgAdmin window is opened and connected to service. Nobody logon to Windows using postgres service user. Thanks Cristian. Can you reproduce the problem if you use psql instead of pgAdmin? Both use libpq to talk to the server, so if your theory is correct, I would expect to see the same crash. It's hard to see what would bring the server down though... We have already found that exceeding desktop heap might cause a CreateProcess to return success but later fail with a return code of 128, which causes a server restart. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5305: Postgres service stops when closing Windows session
On Tue, Aug 31, 2010 at 4:27 PM, Bruce Momjian br...@momjian.us wrote: Dave Page wrote: On Tue, Aug 31, 2010 at 3:40 PM, Cristian Bittel cbit...@gmail.com wrote: To Dave's question, this behavior occurs on all Windows Server interactive sessions, no matter if Administrators or underpriviledge users, but is related to closing Windows interactive session while pgAdmin window is opened and connected to service. Nobody logon to Windows using postgres service user. Thanks Cristian. Can you reproduce the problem if you use psql instead of pgAdmin? Both use libpq to talk to the server, so if your theory is correct, I would expect to see the same crash. It's hard to see what would bring the server down though... We have already found that exceeding desktop heap might cause a CreateProcess to return success but later fail with a return code of 128, which causes a server restart. That doesn't mean that this is desktop heap exhaustion though - just that it can cause the same effect. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5305: Postgres service stops when closing Windows session
Dave Page wrote: On Tue, Aug 31, 2010 at 4:27 PM, Bruce Momjian br...@momjian.us wrote: Dave Page wrote: On Tue, Aug 31, 2010 at 3:40 PM, Cristian Bittel cbit...@gmail.com wrote: To Dave's question, this behavior occurs on all Windows Server interactive sessions, no matter if Administrators or underpriviledge users, but is related to closing Windows interactive session while pgAdmin window is opened and connected to service. Nobody logon to Windows using postgres service user. Thanks Cristian. Can you reproduce the problem if you use psql instead of pgAdmin? Both use libpq to talk to the server, so if your theory is correct, I would expect to see the same crash. It's hard to see what would bring the server down though... We have already found that exceeding desktop heap might cause a CreateProcess to return success but later fail with a return code of 128, which causes a server restart. That doesn't mean that this is desktop heap exhaustion though - just that it can cause the same effect. Right, but it is the only possible server crash cause we have come up with so far. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)
Tom Lane wrote: Greg Smith g...@2ndquadrant.com writes: Tom Lane wrote: Well, yes they are. They cause unnecessary process wakeups and thereby consume cycles even when the database is idle. See for example a longstanding complaint here: https://bugzilla.redhat.com/show_bug.cgi?id=252129 ... The only clear case where this is always a win is when the system it totally idle. If you'll climb down off that horse for a moment: yeah, the idle case is *exactly* what they're complaining about. In particular, the complaint is that it's unreasonable to have Postgres running on a machine at all unless it's actively being used, because it forces significant CPU power drain anyway. That gets in the way of our plan for world domination, no? If you can't have a PG sitting unobtrusively in the background, waiting for you to have a need for it, it won't get installed in the first place. People will pick mysql, or something else with a smaller footprint, to put on their laptops, and then we lose some more mindshare. I see this as just another facet of the argument about whether it's okay to have default settings that try to take over the entire machine. FYI, last week I was running PG 8.4.X with default settings on a T43 laptop running XP with 1 Gig of RAM and it caused a racing game I was playing to run jerkily. When I shut down the Postgres server, the problem was fixed. I was kind of surprised that an idle PG server could cause that. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5305: Postgres service stops when closing Windows session
I am the remote support guy for a web developed application (Apache+PHP+Pg. Postgres is isolated on a server, Apache runs on another server), and installed at our client, our client is the Administrator user on Windows Server, I just have a limited privileges Windows user for monitoring. I have my own support superuser (not postgres user) for Postgres database to monitor the status, logs and to perform stats queries. To Windows Server I just can login using remote desktop, my interactive user cannot start or stop the PostgreSQL service or other services, just Administrators users can do it. From inside my underprivileged session on the Windows server I can open pgAdmin and connect to Postgres service. When I left the pgAdmin connected to Postgres service opened into the Windows session (session connected or disconnected) and I or someone else (Administrators) close my session, then is when PostgreSQL service crash. If inside the remote session I normally close pgAdmin using the X button or FileExit or Ctrl+Q, that not affect PostgreSQL service. This is the major reason to think is pgAdmin.exe when forced shutdown by terminating Windows session which sends abnormal signal to PostgreSQL service. Besides the abnormal signal that pgAdmin forced shutingdown could being send to PostgreSQL service, the service itself also could catch that behavior in any of the aproaches you are discussing for the service itself to ignore that signal. To Dave's question, this behavior occurs on all Windows Server interactive sessions, no matter if Administrators or underpriviledge users, but is related to closing Windows interactive session while pgAdmin window is opened and connected to service. Nobody logon to Windows using postgres service user. Regards, Cristian. 2010/8/31 Dave Page dp...@pgadmin.org On Sun, Aug 29, 2010 at 12:05 PM, Magnus Hagander mag...@hagander.net wrote: On Thu, Aug 26, 2010 at 22:59, Cristian Bittel cbit...@gmail.com wrote: I still believe this exit code 128 is related to pgAdmin opened during the clossing session on Remote Desktop. I have a Windows user login wich is not administrator just no privileged user, it cannot start/stop services, just monitoring. With pgAdmin window opened inside my disconected session, as Administrator if I close the another disconnected session, Postgres exit with 128 code. If the closing of a session on the remote desktop can affect a *service* then frankly that sounds like a serious isolation bug in Windows itself. The postmaster grabs the handle of the process when it's started and waits on that - that should never be affected by something in a different session. I think it's more likely that Windows just looses track when you terminate a lot of processes at once, and randomly kills off something - or at least *indicates* that something has been killed off. Did you reproduce this behavior? No, AFAIK nobody has managed to reproduce this behavior in any kind of consistent way. It's certainly been seen more than once in many places, but not consistently reproducible. This behaviour, no - but desktop heap exhaustion is very easy to reproduce. That's because the heap usage is caused by user32.dll which uses a consistent amount with each process started, which is allocated as the process is created. When I was working on the issue a couple of years ago, it was entirely predictable - user32.dll allocates N bytes and as soon as N * numbackends exceeds the allocated heap size, we fall over. It shouldn't matter as desktop heap is allocated on a per-session basis, but are you logging on using the service account to run your admin tasks Cristian? If so, do you see the problem if you login interactively using a different account? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company
Re: [HACKERS] [BUGS] BUG #5305: Postgres service stops when closing Windows session
On Tue, Aug 31, 2010 at 4:35 PM, Bruce Momjian br...@momjian.us wrote: Dave Page wrote: On Tue, Aug 31, 2010 at 4:27 PM, Bruce Momjian br...@momjian.us wrote: We have already found that exceeding desktop heap might cause a CreateProcess to return success but later fail with a return code of 128, which causes a server restart. That doesn't mean that this is desktop heap exhaustion though - just that it can cause the same effect. Right, but it is the only possible server crash cause we have come up with so far. Understood - I'm just unconvinced it's the cause - aside from the point I made earlier about heap exhaustion being very predictable and reproducible (which this issue apparently is not), when the server is run under the SCM, it creates a logon session for that service alone which has it's own heap allocation which is entirely independent of the allocation used by any interactive logon sessions. So unless there's a major isolation bug in Windows, any desktop heap usage in an interactive session for one user should have zero effect on a non-interactive session for another user. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Performance Farm Release
On Tue, 2010-08-31 at 03:28 -0400, Greg Smith wrote: 4) Merge the perfarm fork changes into the mainline buildfarm code. I expect continued bitrot of this code as changes are made to the regular buildfarm client, so it might be worth considering that sooner rather than later. As Andrew is currently on vacation, *now* is the time to do that. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5305: Postgres service stops when closing Windows session
Dave Page wrote: On Tue, Aug 31, 2010 at 4:35 PM, Bruce Momjian br...@momjian.us wrote: Dave Page wrote: On Tue, Aug 31, 2010 at 4:27 PM, Bruce Momjian br...@momjian.us wrote: We have already found that exceeding desktop heap might cause a CreateProcess to return success but later fail with a return code of 128, which causes a server restart. That doesn't mean that this is desktop heap exhaustion though - just that it can cause the same effect. Right, but it is the only possible server crash cause we have come up with so far. Understood - I'm just unconvinced it's the cause - aside from the point I made earlier about heap exhaustion being very predictable and reproducible (which this issue apparently is not), when the server is run under the SCM, it creates a logon session for that service alone which has it's own heap allocation which is entirely independent of the allocation used by any interactive logon sessions. So unless there's a major isolation bug in Windows, any desktop heap usage in an interactive session for one user should have zero effect on a non-interactive session for another user. Well, the only description that we have ever heard that makes sense is some kind of heap exhaustion, perhaps triggered by a Windows bug that doesn't properly track heap allocations sometimes. Of course, the cause might be aliens, but we don't have any evidence of that either. :-| What we do know is that CreateProcess is returning success, and the child is exiting with 128 no_such_child, and that logging out can trigger it sometimes. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] git: uh-oh
On Tue, Aug 31, 2010 at 17:12, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Mon, Aug 30, 2010 at 05:03, Robert Haas robertmh...@gmail.com wrote: cvs admin -o ? Magnus, is this something that you can try? Prune those could of wonky revisions after the delete and before the re-add prior to running the conversion, and see how that comes out? Yes, definitely. Do we have list of exactly which revisions it is, or a good way to find it? Other than random browsing of the history? :-) I think the files in question are these: 2007-04-16 21:05 tgl * src/: backend/parser/gram.c, interfaces/ecpg/preproc/pgc.c, interfaces/ecpg/preproc/preproc.c: Temporarily re-add derived files, in hopes of straightening out their CVS status. 2007-04-16 21:06 tgl * src/: backend/parser/gram.c, interfaces/ecpg/preproc/pgc.c, interfaces/ecpg/preproc/preproc.c: And remove 'em again ... Look at the histories of these in cvsweb, and try to zap the versions that are later than the first FILE REMOVED event. Ok. I've got a new migration runinng. Here's the revisions removed: RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/Attic/gram.c,v deleting revision 2.90.2.1 deleting revision 2.90.2.2 done RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/Attic/gram.c,v deleting revision 2.92 deleting revision 2.91 deleting revision 2.90 deleting revision 2.89 deleting revision 2.88 done RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/pgc.c,v deleting revision 1.5.2.1 deleting revision 1.5.2.2 done RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/pgc.c,v deleting revision 1.7 deleting revision 1.6 deleting revision 1.5 deleting revision 1.4 deleting revision 1.3 deleting revision 1.2 done RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/preproc.c,v deleting revision 1.11.2.1 deleting revision 1.11.2.2 done RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/preproc.c,v deleting revision 1.13 deleting revision 1.12 deleting revision 1.11 deleting revision 1.10 deleting revision 1.9 deleting revision 1.8 deleting revision 1.7 deleting revision 1.6 done -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] git: uh-oh
Magnus Hagander mag...@hagander.net writes: Ok. I've got a new migration runinng. Here's the revisions removed: RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/Attic/gram.c,v deleting revision 2.88 RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/pgc.c,v deleting revision 1.2 RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/preproc.c,v deleting revision 1.6 Hmm, it looks like you deleted the file deletion events (the versions cited above). Not sure this is the right thing. Check to see if the files are still there according to the converted git history ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] git: uh-oh
On Tue, Aug 31, 2010 at 19:44, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Ok. I've got a new migration runinng. Here's the revisions removed: RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/Attic/gram.c,v deleting revision 2.88 RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/pgc.c,v deleting revision 1.2 RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/preproc.c,v deleting revision 1.6 Hmm, it looks like you deleted the file deletion events (the versions cited above). Not sure this is the right thing. Check to see if the files are still there according to the converted git history ... Oh, drat. That's right. It shouldn't have been inclusive :S I'll abort the conversion and run it again :) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_resetxlog display bogosity
I just noticed that if I specify pg_resetxlog a timeline ID with the -l switch, it will display this value as TimeLineID of latest checkpoint. Which is not really the truth. I wonder if pg_resetxlog should display the actual pg_control values in one section, and the values that would be set after a reset in a different section, so that it is extra clear. So it would look like pg_control values: pg_control version number:903 Catalog version number: 201004261 Database system identifier: 5509100787461288958 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/667 Latest checkpoint's NextOID: 16390 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID:654 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 0 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value Values to be used after reset: First log file ID:14 First log file segment: 28 TimeLineID: 57 (I'd also like to point out that the Latest checkpoint's phrasing is awkward and cumbersome for translated output, but I'm refraining from suggest a reword because it'd complicate matters for programs that try to read the output) -- Álvaro Herrera alvhe...@alvh.no-ip.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Assertion failure on HEAD (or at least git copy of it)
On Mon, Aug 30, 2010 at 12:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: I guess that something isn't properly setting up rnode.backend in recovery processing, but didn't find it yet. CreateFakeRelcacheEntry is the culprit ... Thanks for the fix. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_resetxlog display bogosity
Alvaro Herrera alvhe...@alvh.no-ip.org writes: I wonder if pg_resetxlog should display the actual pg_control values in one section, and the values that would be set after a reset in a different section, so that it is extra clear. Seems reasonable, although I'd suggest labeling the first section as Current pg_control values or some such, if you want clarity. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] git: uh-oh
On Tue, Aug 31, 2010 at 19:46, Magnus Hagander mag...@hagander.net wrote: On Tue, Aug 31, 2010 at 19:44, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Ok. I've got a new migration runinng. Here's the revisions removed: RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/Attic/gram.c,v deleting revision 2.88 RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/pgc.c,v deleting revision 1.2 RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/preproc.c,v deleting revision 1.6 Hmm, it looks like you deleted the file deletion events (the versions cited above). Not sure this is the right thing. Check to see if the files are still there according to the converted git history ... Oh, drat. That's right. It shouldn't have been inclusive :S I'll abort the conversion and run it again :) Ok, I've pushed a clone of the new repository with these modifications to: http://git.postgresql.org/gitweb?p=git-migration-test.git;a=summary Haven't had the time to dig into it yet, so please go ahead anybody who wants to :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] string function - format function proposal
Hello attached WIP patch. I implement only basic format's tags related to SQL: string, value, literal, sql identifier. These tags are basic, but there are not any break to implement any other formats or enhance a syntax. The mix with to_char function is more complex then I expected - so I don't thinking about it for now (there are more then one to_char function). I don't found a nice mix for placeholders and positional placeholders - so I propose a new special function substitute (in contrib) where placeholders are positional. More - we check in function format if all parameters are used - this check isn't related to positional placeholders, this is reason for separate implementation too: so some examples: postgres=# select substitute('second parameter is $2 and first parameter is $1', 'first parameter', 'second parameter'); substitute ─ second parameter is second parameter and first parameter is first parameter (1 row) postgres=# select format('INSERT INTO %i (c1, c2, c3, c4) VALUES (%v,%v,%v,%v)', 'my tab',1, NULL, true, 'hello'); format INSERT INTO my tab (c1, c2, c3, c4) VALUES (1,NULL,t,'hello') (1 row) postgres=# select format('SQL identifier %i cannot be a NULL', NULL); ERROR: SQL identifier cannot be a NULL postgres=# select format('NULL is %v or empty string %s', NULL, NULL); format ─ NULL is NULL or empty string (1 row) %i ... sql identifier %v ... sql value %s ... string --- the most used tag I expect %l ... literal I hope so this system is clean, simple, readable and extensible Regards Pavel 2010/8/30 Pavel Stehule pavel.steh...@gmail.com: 2010/8/30 Alvaro Herrera alvhe...@commandprompt.com: Excerpts from Pavel Stehule's message of lun ago 30 07:51:55 -0400 2010: 2010/8/30 Itagaki Takahiro itagaki.takah...@gmail.com: On Mon, Aug 30, 2010 at 7:58 PM, Pavel Stehule pavel.steh...@gmail.com wrote: propsals: * format function - uses same formatting as PL/pgSQL RAISE statement * sprintf function Now I propose a compromise - format function with only three tags: %s .. some string %i .. SQL identifier %l .. string literal These are just ideas: * Use $n, as like as PREPARE command. It allows for us to swap arguments in any order. SELECT format('$2 before $1', 'aaa', 'bbb') what is use case for this feature? I don't see it. Translations :-) I haven't had a use for that but I've heard people implements gettext of sorts in database tables. Maybe that kind of thing would be of use here. * Call to_char() functions for each placeholder. For example, format('=={-MM-DD}==', tm::timestamp) is equivalent to '==' || to_char(tm, '-MM-DD') || '==' '{}' prints the input with the default format. New languages' libraries might be of some help. LLs, C#, etc. I though about integration with to_char function too. There are not technical barrier. And I can live with just {to_char_format} too. It can be or cannot be mixed with basic tags together - there is specified a NULL value behave. If we allow {format} syntax, then we have to specify a escape syntax for { and }. Do you have a some idea? What about %{sth}? That way you don't need to escape {. The closing } would need escaping only inside the %{} specifier, so {%{{\}MM}} prints {2010{}08} So the above example is: then you need escaping too :) format('==%{-MM-DD}==', tm::timestamp); I am not sure if this is correct -but why not so there are possible combinations %s .. no quoting, NULL is '' %{} .. no quoting, NULL is NULL .. like output from to_char %{}s .. no quoting with formatting, NULL is '' now I have not idea about nice syntax for positional parameters - maybe %{...}$1s or we can use a two variants for tags - not positional '%' and positional '%', so $1{...}s, %{...}s, $1, %s, $1s, $1{...}, %{...} can be valid tags Regards Pavel Stehule Not sure about this to_char stuff though, seems too cute. You can do the case above like this: format('==%s==', to_char(tm::timestamp, '-MM-DD')) I like an using a format like tag - there are not technical problem - format can be taken from string and data type parameter can be known too. But this feature can be some enhancing. The basic features are NULL handling and right quoting. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] string function - format function proposal
On Aug 31, 2010, at 5:07 PM, Pavel Stehule wrote: Hello attached WIP patch. I implement only basic format's tags related to SQL: string, value, literal, sql identifier. These tags are basic, but there are not any break to implement any other formats or enhance a syntax. The mix with to_char function is more complex then I expected - so I don't thinking about it for now (there are more then one to_char function). snip It would be pretty handy if plpgsql EXECUTE could operate like this with USING to support identifiers. Cheers, M -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Synchronous replication - patch status inquiry
Hello everyone, I'm interested in benchmarking synchronous replication, to see how performance degrades compared to asynchronous streaming replication. I browsed through the archive of emails, but things still seem unclear. Do we have a final agreed upon patch that I can use? Any links for that? Thanks. OS = Linux Suse, sles 11, 64-bit Postgres version = 9.0 beta-4
Re: [HACKERS] Synchronous replication - patch status inquiry
fazool mein wrote: Hello everyone, I'm interested in benchmarking synchronous replication, to see how performance degrades compared to asynchronous streaming replication. I browsed through the archive of emails, but things still seem unclear. Do we have a final agreed upon patch that I can use? Any links for that? No. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] experimental: TSearch dictionary [de]serialization
Hello I wrote a some very primitive code for testing serialization and de serialization of TSearch ISpell dictionary. This code working - but it is useful only for speed test now. Czech fulltext dictionary is serialized to cca 9MB long file. Saving needs about 90ms and reading needs same time. postgres=# select * from ts_debug('cs','příliš žluťoučký kůň se napil žluté vody'); alias │description│ token │ dictionaries │ dictionary │ lexemes ───┼───┼───┼─┼┼─ word │ Word, all letters │ příliš│ {cspell,simple} │ cspell │ {příliš} blank │ Space symbols │ │ {} │ [null] │ [null] word │ Word, all letters │ žluťoučký │ {cspell,simple} │ cspell │ {žluťoučký} blank │ Space symbols │ │ {} │ [null] │ [null] word │ Word, all letters │ kůň │ {cspell,simple} │ cspell │ {kůň} blank │ Space symbols │ │ {} │ [null] │ [null] asciiword │ Word, all ASCII │ se│ {cspell,simple} │ cspell │ {} blank │ Space symbols │ │ {} │ [null] │ [null] asciiword │ Word, all ASCII │ napil │ {cspell,simple} │ cspell │ {napít} blank │ Space symbols │ │ {} │ [null] │ [null] word │ Word, all letters │ žluté │ {cspell,simple} │ cspell │ {žlutý} blank │ Space symbols │ │ {} │ [null] │ [null] asciiword │ Word, all ASCII │ vody │ {cspell,simple} │ cspell │ {voda} (13 rows) Time: 92.708 ms -- with using a preprocessed dictionary postgres=# select * from ts_debug('cs','příliš žluťoučký kůň se napil žluté vody'); alias │description│ token │ dictionaries │ dictionary │ lexemes ───┼───┼───┼─┼┼─ word │ Word, all letters │ příliš│ {cspell,simple} │ cspell │ {příliš} blank │ Space symbols │ │ {} │ [null] │ [null] word │ Word, all letters │ žluťoučký │ {cspell,simple} │ cspell │ {žluťoučký} blank │ Space symbols │ │ {} │ [null] │ [null] word │ Word, all letters │ kůň │ {cspell,simple} │ cspell │ {kůň} blank │ Space symbols │ │ {} │ [null] │ [null] asciiword │ Word, all ASCII │ se│ {cspell,simple} │ cspell │ {} blank │ Space symbols │ │ {} │ [null] │ [null] asciiword │ Word, all ASCII │ napil │ {cspell,simple} │ cspell │ {napít} blank │ Space symbols │ │ {} │ [null] │ [null] word │ Word, all letters │ žluté │ {cspell,simple} │ cspell │ {žlutý} blank │ Space symbols │ │ {} │ [null] │ [null] asciiword │ Word, all ASCII │ vody │ {cspell,simple} │ cspell │ {voda} (13 rows) Time: 3.758 ms -- standard time (dictionary is loaded) postgres=# select * from ts_debug('cs','příliš žluťoučký kůň se napil žluté vody'); alias │description│ token │ dictionaries │ dictionary │ lexemes ───┼───┼───┼─┼┼─ word │ Word, all letters │ příliš│ {cspell,simple} │ cspell │ {příliš} blank │ Space symbols │ │ {} │ [null] │ [null] word │ Word, all letters │ žluťoučký │ {cspell,simple} │ cspell │ {žluťoučký} blank │ Space symbols │ │ {} │ [null] │ [null] word │ Word, all letters │ kůň │ {cspell,simple} │ cspell │ {kůň} blank │ Space symbols │ │ {} │ [null] │ [null] asciiword │ Word, all ASCII │ se│ {cspell,simple} │ cspell │ {} blank │ Space symbols │ │ {} │ [null] │ [null] asciiword │ Word, all ASCII │ napil │ {cspell,simple} │ cspell │ {napít} blank │ Space symbols │ │ {} │ [null] │ [null] word │ Word, all letters │ žluté │ {cspell,simple} │ cspell │ {žlutý} blank │ Space symbols │ │ {} │ [null] │ [null] asciiword │ Word, all ASCII │ vody │ {cspell,simple} │ cspell │ {voda} (13 rows) Time: 518.528 ms --- typical first evaluation time So using a preprocessed file helps - the time of first processing is about 4x better. But still this time is 20x slower than using a loaded dictionary. I found a one issue - I am not able to serialize a full regexp. Czech dictionary doesn't use it, so I didn't solve this task. I would to like implement a few hooks to ISpellDictionary to be possible implement own memory management for ispell dictionaries. I understand to problems with shared memory or mmap - but I don't see any different way, than use a
Re: [HACKERS] Synchronous replication - patch status inquiry
On Tue, Aug 31, 2010 at 05:44:15PM -0400, Bruce Momjian wrote: fazool mein wrote: Hello everyone, I'm interested in benchmarking synchronous replication, to see how performance degrades compared to asynchronous streaming replication. I browsed through the archive of emails, but things still seem unclear. Do we have a final agreed upon patch that I can use? Any links for that? No. That was a mite brusque and not super informative. There are patches, and the latest from Fujii Masao is probably worth looking at :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] string function - format function proposal
On Tue, Aug 31, 2010 at 11:07:40PM +0200, Pavel Stehule wrote: Hello attached WIP patch. I don't see it attached. Is it just me? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Performance Farm Release
On 08/31/2010 03:28 AM, Greg Smith wrote: 1) Nail down what subset of the information gathered locally should be uploaded to the buildfarm master server. Probably just the same columns of data already being saved for each test, but perhaps with some extra metadata. The local animal will also have graphs and such, but unrealistic to upload all of those for a number of reasons I think. Only really useful to drill down when there is some sort of regression I expect, and hopefully the animal is still alive when that happens. 2) Update the buildfarm server code to accept and store that data. The server code is in fact very generic. There is no hard coding of step names or anything like that. The major piece I think we'd need to add in here is somewhere to store performance test scores, as opposed to the success/failure of normal buildfarm steps. That's not going to be terrible difficult. 3) Update this perffarm client to talk to that. 4) Merge the perfarm fork changes into the mainline buildfarm code. I expect continued bitrot of this code as changes are made to the regular buildfarm client, so it might be worth considering that sooner rather than later. OK, but the client code is hardly in a state of violent flux. In the last year there have been eight commits, including the git changes. My understanding is that the code for the server side of the buildfarm isn't public to everyone right now, just because of time limitations getting it cleaned up for that. So a couple of parts here are being funneled through how much spare time Andrew has, and there are more important git and buildfarm related things for him to worry about right now. The git buildfarm changes are pretty much bedded down now, I think. I have broken the back of extensible enums a bit more quickly than I expected, so I might have some flying time in the next week or so to devote to cleaning stuff up in the server code, while taking a break from COPY as a FROM target (which will undoubtedly turn my brain to mush if I work on it for more than a couple of hours at a time). Presuming nothing exciting on this happens before then, I'm hoping that I can catch up with Andrew at PG West and map out how to get the rest of this done, so it goes live somewhere during 9.1 development. Now that the code has been released from the Noblis fortress, I can start cleaning up some of the little details on it before then too (i.e. not working for anything but 9.0 yet). Sounds good. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] git: uh-oh
On Tue, Aug 31, 2010 at 5:07 PM, Magnus Hagander mag...@hagander.net wrote: On Tue, Aug 31, 2010 at 19:46, Magnus Hagander mag...@hagander.net wrote: On Tue, Aug 31, 2010 at 19:44, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Ok. I've got a new migration runinng. Here's the revisions removed: RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/Attic/gram.c,v deleting revision 2.88 RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/pgc.c,v deleting revision 1.2 RCS file: /usr/local/cvsroot/pgsql/src/interfaces/ecpg/preproc/Attic/preproc.c,v deleting revision 1.6 Hmm, it looks like you deleted the file deletion events (the versions cited above). Not sure this is the right thing. Check to see if the files are still there according to the converted git history ... Oh, drat. That's right. It shouldn't have been inclusive :S I'll abort the conversion and run it again :) Ok, I've pushed a clone of the new repository with these modifications to: http://git.postgresql.org/gitweb?p=git-migration-test.git;a=summary Haven't had the time to dig into it yet, so please go ahead anybody who wants to :-) That definitely didn't fix it, although I'm not quite sure why. Can you throw the modified CVS you ran this off of up somewhere I can rsync it? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous replication - patch status inquiry
On Tue, Aug 31, 2010 at 6:24 PM, David Fetter da...@fetter.org wrote: On Tue, Aug 31, 2010 at 05:44:15PM -0400, Bruce Momjian wrote: fazool mein wrote: Hello everyone, I'm interested in benchmarking synchronous replication, to see how performance degrades compared to asynchronous streaming replication. I browsed through the archive of emails, but things still seem unclear. Do we have a final agreed upon patch that I can use? Any links for that? No. That was a mite brusque and not super informative. There are patches, and the latest from Fujii Masao is probably worth looking at :) I am pretty sure, however, that the performance will be terrible at this point. Heikki is working on fixing that, but it ain't done yet. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous replication - patch status inquiry
On Tue, Aug 31, 2010 at 6:24 PM, David Fetter da...@fetter.org wrote: On Tue, Aug 31, 2010 at 05:44:15PM -0400, Bruce Momjian wrote: fazool mein wrote: Hello everyone, I'm interested in benchmarking synchronous replication, to see how performance degrades compared to asynchronous streaming replication. I browsed through the archive of emails, but things still seem unclear. Do we have a final agreed upon patch that I can use? Any links for that? No. That was a mite brusque and not super informative. There are patches, and the latest from Fujii Masao is probably worth looking at :) I am pretty sure, however, that the performance will be terrible at this point. Heikki is working on fixing that, but it ain't done yet. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous replication - patch status inquiry
On Tue, Aug 31, 2010 at 08:34:31PM -0400, Robert Haas wrote: On Tue, Aug 31, 2010 at 6:24 PM, David Fetter da...@fetter.org wrote: On Tue, Aug 31, 2010 at 05:44:15PM -0400, Bruce Momjian wrote: fazool mein wrote: Hello everyone, I'm interested in benchmarking synchronous replication, to see how performance degrades compared to asynchronous streaming replication. I browsed through the archive of emails, but things still seem unclear. Do we have a final agreed upon patch that I can use? Any links for that? No. That was a mite brusque and not super informative. There are patches, and the latest from Fujii Masao is probably worth looking at :) I am pretty sure, however, that the performance will be terrible at this point. Heikki is working on fixing that, but it ain't done yet. Is this something for an eDB feature, or for community PostgreSQL, or...? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] string function - format function proposal
On Wed, Sep 1, 2010 at 6:07 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I don't found a nice mix for placeholders and positional placeholders How about %pos$format, used in C-printf()? It might be only in Linux's libc. printf(%2$s %1$d\n, 123, abc); = abc 123 http://linux.die.net/man/3/printf %i ... sql identifier %v ... sql value %s ... string --- the most used tag I expect %l ... literal Looks good designed. I have a couple of comments and questions: * There is no examples for %l. What's the difference from %v and %s? If it always quotes, how does it work? Like as quote_literal() or quote_nullable()? * %v quotes text values (and maybe all non-numeric values) with single quotes, but doesn't numeric values. How do we determine the difference? By type oid? * %v also doesn't quote boolean values, but t and f are not valid. You should use true and false (or 't' and 'f') for the cases. (So, your INSERT INTO example is broken.) -- Itagaki Takahiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous replication - patch status inquiry
On Tue, Aug 31, 2010 at 8:45 PM, David Fetter da...@fetter.org wrote: I am pretty sure, however, that the performance will be terrible at this point. Heikki is working on fixing that, but it ain't done yet. Is this something for an eDB feature, or for community PostgreSQL, or...? It's an EDB feature in the sense that Heikki is developing it as part of his employment with EDB, but it will be committed to community PostgreSQL. See the thread on interruptible sleeps. The problem right now is that there are some polling loops that act to throttle the maximum rate at which a node doing sync rep can make forward progress, independent of the capabilities of the hardware. Those need to be replaced with a system that doesn't inject unnecessary delays into the process, which is what Heikki is working on. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous replication - patch status inquiry
On Wed, Sep 1, 2010 at 9:34 AM, Robert Haas robertmh...@gmail.com wrote: There are patches, and the latest from Fujii Masao is probably worth looking at :) I am pretty sure, however, that the performance will be terrible at this point. Heikki is working on fixing that, but it ain't done yet. Yep. The latest WIP code is available in my git repository, but it's not worth benchmarking yet. I'll need to merge Heikki's effort and the synchronous replication patch. git://git.postgresql.org/git/users/fujii/postgres.git branch: synchrep Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Estimation of Plan quality
First, this is not a bug. Don't post to pgsql-bugs unless you're reporting a bug. On Tue, Aug 31, 2010 at 4:38 AM, vamsi krishna vamsikrishna1...@gmail.com wrote: Can someone tell me how to estimate the quality of my plan. For example if the ideal query plan generated by Dynamic Programming has a plan quality of 1.0, and suppose I generate my query plan with some greedy method or something, it will be worse (not as optimal as) than ideal plan by, say, 1.5 or 2.0 times , how can I know this factor? Should it be estimated from the execution time? I'm not sure there's one right way to do this. If you know what the ideal plan is and how fast it runs, comparing that to the actual plan seems like a reasonable thing to do... In the plan generated by PostgreSQL when I give explain query, I see Plan cost at the top of the plan with some exponential value. What does it indicate? Is it the estimated execution time? or Is it the search space that has been considered during plan execution? ...but if you haven't used the planner enough to understand the EXPLAIN output, there is a good chance you don't understand it well enough to experiment with modifications, either. The answers to the questions above can be found in our fine documentation, specifically the second paragraph of the EXPLAIN documentation: http://www.postgresql.org/docs/8.4/static/sql-explain.html -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous replication - patch status inquiry
Thanks! I'll wait for the merging then; there is no point in benchmarking otherwise. Regards On Tue, Aug 31, 2010 at 6:06 PM, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Sep 1, 2010 at 9:34 AM, Robert Haas robertmh...@gmail.com wrote: There are patches, and the latest from Fujii Masao is probably worth looking at :) I am pretty sure, however, that the performance will be terrible at this point. Heikki is working on fixing that, but it ain't done yet. Yep. The latest WIP code is available in my git repository, but it's not worth benchmarking yet. I'll need to merge Heikki's effort and the synchronous replication patch. git://git.postgresql.org/git/users/fujii/postgres.git branch: synchrep Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Re: [HACKERS] string function - format function proposal
2010/9/1 David Fetter da...@fetter.org: On Tue, Aug 31, 2010 at 11:07:40PM +0200, Pavel Stehule wrote: Hello attached WIP patch. I don't see it attached. Is it just me? sorry, it was at 1 ofter midnight Regards Pavel Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate *** ./src/backend/tsearch/dict_ispell.c.orig 2010-08-23 09:16:49.0 +0200 --- ./src/backend/tsearch/dict_ispell.c 2010-08-31 23:46:00.178669635 +0200 *** *** 37,113 dictloaded = false, stoploaded = false; ListCell *l; d = (DictISpell *) palloc0(sizeof(DictISpell)); ! foreach(l, dictoptions) { ! DefElem*defel = (DefElem *) lfirst(l); ! ! if (pg_strcasecmp(defel-defname, DictFile) == 0) { ! if (dictloaded) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg(multiple DictFile parameters))); ! NIImportDictionary((d-obj), ! get_tsearch_config_filename(defGetString(defel), ! dict)); ! dictloaded = true; } ! else if (pg_strcasecmp(defel-defname, AffFile) == 0) { ! if (affloaded) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg(multiple AffFile parameters))); ! NIImportAffixes((d-obj), ! get_tsearch_config_filename(defGetString(defel), ! affix)); ! affloaded = true; } ! else if (pg_strcasecmp(defel-defname, StopWords) == 0) { ! if (stoploaded) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg(multiple StopWords parameters))); ! readstoplist(defGetString(defel), (d-stoplist), lowerstr); ! stoploaded = true; } else { ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg(unrecognized Ispell parameter: \%s\, ! defel-defname))); } ! } - if (affloaded dictloaded) - { - NISortDictionary((d-obj)); - NISortAffixes((d-obj)); - } - else if (!affloaded) - { - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg(missing AffFile parameter))); - } - else - { - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg(missing DictFile parameter))); } MemoryContextDeleteChildren(CurrentMemoryContext); MemoryContextStats(CurrentMemoryContext); - PG_RETURN_POINTER(d); } --- 37,132 dictloaded = false, stoploaded = false; ListCell *l; + int i; d = (DictISpell *) palloc0(sizeof(DictISpell)); + + d-obj.stream = fopen(/tmp/xxx.ft, r); + d-obj.mode = 'r'; ! if (d-obj.mode == 'r') { ! readSPDict(d-obj.stream, d-obj); ! readAffix(d-obj.stream, d-obj); ! postProcessAffixes(d-obj); ! readStopList(d-obj.stream, d-stoplist); ! } ! else ! { ! foreach(l, dictoptions) { ! DefElem*defel = (DefElem *) lfirst(l); ! ! if (pg_strcasecmp(defel-defname, DictFile) == 0) ! { ! if (dictloaded) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg(multiple DictFile parameters))); ! NIImportDictionary((d-obj), ! get_tsearch_config_filename(defGetString(defel), ! dict)); ! dictloaded = true; ! } ! else if (pg_strcasecmp(defel-defname, AffFile) == 0) ! { ! if (affloaded) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg(multiple AffFile parameters))); ! NIImportAffixes((d-obj), ! get_tsearch_config_filename(defGetString(defel), ! affix)); ! affloaded = true; ! } ! else if (pg_strcasecmp(defel-defname, StopWords) == 0) ! { ! if (stoploaded) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg(multiple StopWords parameters))); ! readstoplist(defGetString(defel), (d-stoplist), lowerstr); ! stoploaded = true; ! ! } ! else ! { ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg(unrecognized Ispell parameter: \%s\, ! defel-defname))); ! } } ! ! if (affloaded dictloaded) { ! NISortDictionary((d-obj)); ! NISortAffixes((d-obj)); } ! else if (!affloaded) { ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg(missing AffFile parameter))); } else { ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg(missing DictFile parameter))); } ! ! if (d-obj.stream != NULL d-obj.mode == 'w') ! outStopList(d-obj.stream, d-stoplist); } MemoryContextDeleteChildren(CurrentMemoryContext); MemoryContextStats(CurrentMemoryContext); +
Re: [HACKERS] string function - format function proposal
2010/9/1 Itagaki Takahiro itagaki.takah...@gmail.com: On Wed, Sep 1, 2010 at 6:07 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I don't found a nice mix for placeholders and positional placeholders How about %pos$format, used in C-printf()? It might be only in Linux's libc. printf(%2$s %1$d\n, 123, abc); = abc 123 http://linux.die.net/man/3/printf same syntax I designed and didn't implement, because it isn't too readable (my opinion). But I am not against - just thinking so separate function can be better. %i ... sql identifier %v ... sql value %s ... string --- the most used tag I expect %l ... literal Looks good designed. I have a couple of comments and questions: * There is no examples for %l. What's the difference from %v and %s? If it always quotes, how does it work? Like as quote_literal() or quote_nullable()? %l is always quoted it is designed for syntax: SELECT integer '10'; %s versus %v ... %s is never quoted X %v is quoted when it is necessary, NULL is showed as empty string for %s and as NULL for %v. %s is used for messages (the behave is same like concat), %v is used for SQL statement building * %v quotes text values (and maybe all non-numeric values) with single quotes, but doesn't numeric values. How do we determine the difference? By type oid? every datatype has typecategory attribute typname │ typcategory ─┼─ int8│ N int2│ N int4│ N regproc │ N oid │ N float4 │ N float8 │ N money │ N numeric │ N regprocedure│ N regoper │ N regoperator │ N regclass│ N regtype │ N regconfig │ N regdictionary │ N cardinal_number │ N so these types are unquoted * %v also doesn't quote boolean values, but t and f are not valid. You should use true and false (or 't' and 'f') for the cases. (So, your INSERT INTO example is broken.) you have a true - it should be fixed Regards Pavel -- Itagaki Takahiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous replication - patch status inquiry
On 01/09/10 04:02, Robert Haas wrote: See the thread on interruptible sleeps. The problem right now is that there are some polling loops that act to throttle the maximum rate at which a node doing sync rep can make forward progress, independent of the capabilities of the hardware. To be precise, the polling doesn't affect the bandwidth the replication can handle, but it introduces a delay wh Those need to be replaced with a system that doesn't inject unnecessary delays into the process, which is what Heikki is working on. Right. Once we're done with that, all the big questions are still left. How to configure it? What does synchronous replication mean, when is a transaction acknowledged as committed? What to do if a standby server dies and never acknowledges a commit? All these issues have been discussed, but there is no consensus yet. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] array_agg() NULL Handling
The aggregate docs say: The first form of aggregate expression invokes the aggregate across all input rows for which the given expression(s) yield non-null values. (Actually, it is up to the aggregate function whether to ignore null values or not — but all the standard ones do.) -- http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES That, however, is not true of array_agg(): try=# CREATE TABLE foo(id int); CREATE TABLE try=# INSERT INTO foo values(1), (2), (NULL), (3); INSERT 0 4 try=# select array_agg(id) from foo; array_agg ── {1,2,NULL,3} (1 row) So are the docs right, or is array_agg() right? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers