Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Heikki Linnakangas
Tom Lane wrote: So I think that labeling textanycat/anytextcat as immutable was a thinko, and we instead ought to label them volatile so that the planner can inline them no matter what the behavior of the underlying text cast is. That feels backwards, having to label functions as more

Re: [HACKERS] Patch for PKST timezone

2010-05-16 Thread Joachim Wieland
On Wed, May 12, 2010 at 12:39 AM, Tom Lane t...@sss.pgh.pa.us wrote: Joachim Wieland j...@mcknight.de writes: Good we have found that inconsistency now, so let's add PKST. OK, done.  BTW, I notice that PKT was labeled (not in zic), which is not the case, per this discussion.  I seem to recall

Re: [HACKERS] pg_upgrade and extra_float_digits

2010-05-16 Thread Bruce Momjian
Andrew Dunstan wrote: Bruce Momjian wrote: Maybe I have misunderstood. How exactly is the server version being hacked here? I know it's only for testing, but it still seems to me that lying to a program as heavily version dependant as pg_dump is in general a bad idea.

Re: [HACKERS] pg_upgrade and extra_float_digits

2010-05-16 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: Andrew Dunstan wrote: But do earlier server versions accept a value of 3? The 8.4 docs say The value can be set as high as 2. That is the other thing I had to hack --- the 8.4 backend version had to be changed to accept '3'. The good thing is this has

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: So I think that labeling textanycat/anytextcat as immutable was a thinko, and we instead ought to label them volatile so that the planner can inline them no matter what the behavior of the underlying text cast is.

Re: [HACKERS] Keepalive for max_standby_delay

2010-05-16 Thread Simon Riggs
On Sun, 2010-05-16 at 00:05 +0300, Heikki Linnakangas wrote: Heikki Linnakangas wrote: Simon Riggs wrote: WALSender sleeps even when it might have more WAL to send, it doesn't check it just unconditionally sleeps. At least WALReceiver loops until it has no more to receive. I just can't

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Tom Lane
I wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Marking textanycat as not immutable would forbid using it in expression indexes, too. True. On the other hand, the current state of affairs allows one to create an index on expressions that aren't really immutable, with

Re: [HACKERS] [PATCH] Add SIGCHLD catch to psql

2010-05-16 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: A saner approach, which would also help for other corner cases such as out-of-disk-space, would be to check for write failures on the output file and abandon the query if any occur. I had considered this, but I'm not sure we really need to catch *every*

Re: [HACKERS] Keepalive for max_standby_delay

2010-05-16 Thread Simon Riggs
On Sat, 2010-05-15 at 19:50 +0100, Simon Riggs wrote: On Sat, 2010-05-15 at 18:24 +0100, Simon Riggs wrote: I will recode using that concept. Startup gets new pointer when it runs out of data to replay. That might or might not include an updated keepalive timestamp, since there's no exact

Re: [HACKERS] [PATCH] Add SIGCHLD catch to psql

2010-05-16 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: A saner approach, which would also help for other corner cases such as out-of-disk-space, would be to check for write failures on the output file and abandon the query if any occur. I had considered this, but

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Robert Haas
On Sat, May 15, 2010 at 9:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: I noticed by accident that there are some cases where the planner fails to inline the SQL functions that underlie the || operator for text vs non-text cases.  The reason is that these functions are marked immutable, but their

Re: [HACKERS] pg_upgrade and extra_float_digits

2010-05-16 Thread Andrew Dunstan
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Andrew Dunstan wrote: But do earlier server versions accept a value of 3? The 8.4 docs say The value can be set as high as 2. That is the other thing I had to hack --- the 8.4 backend version had to be changed to

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Couldn't you apply this argument to any built-in immutable function whatsoever? No, only the ones that are built on top of other functions that aren't immutable. I did go looking for other potential problems of the same ilk. The only one I can find

Re: [HACKERS] Synchronous replication patch built on SR

2010-05-16 Thread Simon Riggs
On Fri, 2010-05-14 at 15:15 -0400, Robert Haas wrote: On Fri, May 14, 2010 at 9:33 AM, Boszormenyi Zoltan z...@cybertec.at wrote: If min_sync_replication_clients == 0, then the replication is async. If min_sync_replication_clients == max_wal_senders then the replication is fully

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Robert Haas
On Sun, May 16, 2010 at 1:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Couldn't you apply this argument to any built-in immutable function whatsoever? No, only the ones that are built on top of other functions that aren't immutable. Built on top of?

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Jaime Casanova
On Sun, May 16, 2010 at 1:20 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, May 16, 2010 at 1:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Couldn't you apply this argument to any built-in immutable function whatsoever? No, only the ones that are

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Sun, May 16, 2010 at 1:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, only the ones that are built on top of other functions that aren't immutable. Built on top of? I don't get it. It seems like anything of the form

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Robert Haas
On Sun, May 16, 2010 at 2:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sun, May 16, 2010 at 1:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, only the ones that are built on top of other functions that aren't immutable. Built on top of?  I don't get

[HACKERS] Sort of a planner regression 8.3-8.4 (due to EXISTS inlining) and related stuff

2010-05-16 Thread Andres Freund
Hi all, After having received several reports of worse plans on 8.4 compared to 8.3 and recently once more one from 'vaxerdec' on IRC I tried to investigate the difference. Reducing the (large and ugly, automatically generated queries) to a reproducible testcase I ended up with the following

Re: [HACKERS] Sort of a planner regression 8.3-8.4 (due to EXISTS inlining) and related stuff

2010-05-16 Thread Andres Freund
Testschema: ROLLBACK; BEGIN; CREATE TABLE a ( a_id serial PRIMARY KEY NOT NULL, b_id integer ); CREATE INDEX a__b_id ON a USING btree (b_id); CREATE TABLE b ( b_id serial NOT NULL, c_id integer ); CREATE INDEX b__c_id ON b USING btree (c_id); CREATE TABLE c ( c_id serial

Re: [HACKERS] Sort of a planner regression 8.3-8.4 (due to EXISTS inlining) and related stuff

2010-05-16 Thread Robert Haas
On Sun, May 16, 2010 at 7:07 PM, Andres Freund and...@anarazel.de wrote: Reducing the (large and ugly, automatically generated queries) to a reproducible testcase I ended up with the following pattern: explain SELECT 1 FROM    c WHERE    EXISTS (        SELECT *        FROM a            

Re: [HACKERS] pg_upgrade and extra_float_digits

2010-05-16 Thread Bruce Momjian
Andrew Dunstan wrote: Eventually the idea would be to have the build farm run such tests (with a properly created dump file) so we can learn quickly if the backend data format is changed. If we're thinking of doing that, it would be better to back-patch the change that allowed

Re: [HACKERS] pg_upgrade and extra_float_digits

2010-05-16 Thread Andrew Dunstan
Bruce Momjian wrote: Andrew Dunstan wrote: Eventually the idea would be to have the build farm run such tests (with a properly created dump file) so we can learn quickly if the backend data format is changed. If we're thinking of doing that, it would be better to back-patch

Re: [HACKERS] Row-level Locks SERIALIZABLE transactions, postgres vs. Oracle

2010-05-16 Thread Florian Pflug
On May 14, 2010, at 22:54 , Robert Haas wrote: On Thu, May 13, 2010 at 5:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Florian Pflug f...@phlo.org writes: All in all, I believe that SHARE and UPDATE row-level locks should be changed to cause concurrent UPDATEs to fail with a serialization error.

Re: [HACKERS] pg_upgrade and extra_float_digits

2010-05-16 Thread Bruce Momjian
Andrew Dunstan wrote: Uh, that is not actually a problem. You just need to set extra_float_digits=-3 to create the dump file, which is only done once for each major version. You can _load_ that dump file into an unmodified old cluster and test just fine. I will write up some

Re: [HACKERS] Row-level Locks SERIALIZABLE transactions, postgres vs. Oracle

2010-05-16 Thread Florian Pflug
On May 14, 2010, at 16:32 , Kevin Grittner wrote: Florian Pflug f...@phlo.org wrote: I must admit that I wasn't able to find an explicit reference to Oracle's behavior in their docs, so I had to resort to experiments. They do have examples showing how to do FK-like constraints with

Re: [HACKERS] Stefan's bug (was: max_standby_delay considered harmful)

2010-05-16 Thread Robert Haas
On Wed, May 12, 2010 at 10:41 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, May 12, 2010 at 10:36 PM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: Excerpts from Robert Haas's message of mié may 12 20:48:41 -0400 2010: On Wed, May 12, 2010 at 3:55 PM, Robert Haas robertmh...@gmail.com

Re: [HACKERS] pg_upgrade and extra_float_digits

2010-05-16 Thread Andrew Dunstan
Bruce Momjian wrote: Thank you. I understand now. Imagine finding out on the build farm right away when we break binary compatibility --- that would be cool. I'm not saying we can't do that, just that it will not be a trivial change. And yes it would be cool, although I hope we would

Re: [HACKERS] Row-level Locks SERIALIZABLE transactions, postgres vs. Oracle

2010-05-16 Thread Robert Haas
On Sun, May 16, 2010 at 9:07 PM, Florian Pflug f...@phlo.org wrote: On May 14, 2010, at 22:54 , Robert Haas wrote: On Thu, May 13, 2010 at 5:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Florian Pflug f...@phlo.org writes: All in all, I believe that SHARE and UPDATE row-level locks should be

Re: [HACKERS] pg_upgrade and extra_float_digits

2010-05-16 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: Andrew Dunstan wrote: It's going to require some fancy dancing to get the buildfarm to do it. Each buildfarm run is for a specific branch, and all the built artefacts are normally thrown away. Uh, that is not actually a problem. You just need to set

Re: [HACKERS] Performance problem in textanycat/anytextcat

2010-05-16 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: I guess my point is that the actual volatility of an expression is presumably independent of whether it gets inlined. (If inlining is changing the semantics, that's a problem.) So if inlining is changing it's apparent volatility, then there's

Re: [HACKERS] Sort of a planner regression 8.3-8.4 (due to EXISTS inlining) and related stuff

2010-05-16 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: I believe this is a result of a limitation we've discussed previously, namely, that the planner presently uses a limited, special-case kludge to consider partial index scans, and the executor uses another kludge to execute them. Yeah. To restore

Re: [HACKERS] Keepalive for max_standby_delay

2010-05-16 Thread Fujii Masao
On Sun, May 16, 2010 at 6:05 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Heikki Linnakangas wrote: Simon Riggs wrote: WALSender sleeps even when it might have more WAL to send, it doesn't check it just unconditionally sleeps. At least WALReceiver loops until it has no

Re: [HACKERS] pg_upgrade and extra_float_digits

2010-05-16 Thread Andrew Dunstan
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Andrew Dunstan wrote: It's going to require some fancy dancing to get the buildfarm to do it. Each buildfarm run is for a specific branch, and all the built artefacts are normally thrown away. Uh, that is not

Re: [HACKERS] Sort of a planner regression 8.3-8.4 (due to EXISTS inlining) and related stuff

2010-05-16 Thread Andres Freund
On Monday 17 May 2010 04:10:46 Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: I believe this is a result of a limitation we've discussed previously, namely, that the planner presently uses a limited, special-case kludge to consider partial index scans, and the executor uses

Re: [HACKERS] pg_upgrade and extra_float_digits

2010-05-16 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes: This whole discussion leads me to the conclusion that we need to look more imaginatively at our testing regime. When the buildfarm was created it (via pg_regress) covered a lot of what we needed to test, but that is becoming less and less true. Not

Re: [HACKERS] Keepalive for max_standby_delay

2010-05-16 Thread Fujii Masao
On Mon, May 17, 2010 at 1:11 AM, Simon Riggs si...@2ndquadrant.com wrote: On Sat, 2010-05-15 at 19:50 +0100, Simon Riggs wrote: On Sat, 2010-05-15 at 18:24 +0100, Simon Riggs wrote: I will recode using that concept. Startup gets new pointer when it runs out of data to replay. That might or