Re: [HACKERS] Failing start-up archive recovery at Standby mode in PG9.2.4

2013-04-25 Thread Amit Langote
I also had a similar observation when I could reproduce this. I tried to find why restartpoint causes the recycled segment to be named after timeline 3, but have not been able to determine that. When I looked at the source, I found that, the function XLogFileReadAnyTLI which returns a segment

[HACKERS] Redundancy in comment within lock.c

2013-04-25 Thread Peter Geoghegan
Silly typo report. Line 3774 of lmgr's lock.c says: * Re-acquire a lock belonging to a transaction that was prepared, when * when starting up into hot standby mode. This has been the case since the original hot standby commit. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list

[HACKERS] Re: [BUGS] BUG #8049: Incorrect results when using ORDER BY and query planner options

2013-04-25 Thread Andres Freund
On 2013-04-25 16:27:47 +0800, Jov wrote: I can reproduce on 9.2.4 too. plan, but also wrong plan. I think it is a terrible bug. * Just in case you missed it, there's ongoing work to fix it. For some explanations see: http://www.postgresql.org/message-id/6546.1365701...@sss.pgh.pa.us

Re: [HACKERS] 9.3 Beta1 status report

2013-04-25 Thread Vik Fearing
On 04/24/2013 06:34 PM, Heikki Linnakangas wrote: Let me clarify --- changes to our WAL binary format and source code changes are not really incompatibilities from a user perspective as we never promise to do our best to minimize such changes --- m eaning the fact the WAL format changed is

Re: [HACKERS] Redundancy in comment within lock.c

2013-04-25 Thread Heikki Linnakangas
On 25.04.2013 09:36, Peter Geoghegan wrote: Silly typo report. Line 3774 of lmgr's lock.c says: * Re-acquire a lock belonging to a transaction that was prepared, when * when starting up into hot standby mode. This has been the case since the original hot standby commit. Thanks, fixed. -

Re: [HACKERS] Enabling Checksums

2013-04-25 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: On 24 April 2013 21:06, Jeff Davis pg...@j-davis.com wrote: What goal are you trying to accomplish with this patch? That we might need to patch the checksum version on a production release. I don't actually buy that argument, certainly not as

Re: [HACKERS] Proposal to add --single-row to psql

2013-04-25 Thread Tom Lane
Christopher Manning c...@christophermanning.org writes: Fabrízio and Tom, I know that you can use --variable=FETCH_COUNT=1 from the psql command line, but internally that uses a CURSOR to batch the rows and [Redshift doesn't support CURSOR](

Re: [HACKERS] Failing start-up archive recovery at Standby mode in PG9.2.4

2013-04-25 Thread Kyotaro HORIGUCHI
Hmm. I think that I caught the tail of the problem.. Script with small change reproduced the situation for me. The latest standby uses 3 as its TLI after the history file 0..3.history which could get from the archive. So the WAL files recycled on this standby will have the TLI=3. Nevertheless

Re: [HACKERS] Proposal to add --single-row to psql

2013-04-25 Thread Andrew Dunstan
On 04/25/2013 10:42 AM, Tom Lane wrote: Christopher Manning c...@christophermanning.org writes: Fabrízio and Tom, I know that you can use --variable=FETCH_COUNT=1 from the psql command line, but internally that uses a CURSOR to batch the rows and [Redshift doesn't support CURSOR](

Re: [HACKERS] Proposal to add --single-row to psql

2013-04-25 Thread Joshua D. Drake
On 04/25/2013 07:42 AM, Tom Lane wrote: Christopher Manning c...@christophermanning.org writes: Fabr�zio and Tom, I know that you can use --variable=FETCH_COUNT=1 from the psql command line, but internally that uses a CURSOR to batch the rows and [Redshift doesn't support CURSOR](

Re: [HACKERS] Failing start-up archive recovery at Standby mode in PG9.2.4

2013-04-25 Thread Heikki Linnakangas
On 25.04.2013 17:55, Kyotaro HORIGUCHI wrote: Hmm. I think that I caught the tail of the problem.. Script with small change reproduced the situation for me. Can you share the modified script, please? The latest standby uses 3 as its TLI after the history file 0..3.history which could get

Re: [HACKERS] putting a bgworker to rest

2013-04-25 Thread Peter Eisentraut
On 4/24/13 12:30 PM, Dimitri Fontaine wrote: In Erlang, the lib that cares about such things in called OTP, and that proposes a model of supervisor that knows when to restart a worker. The specs for the restart behaviour are: Restart = permanent | transient | temporary There is also

Re: [HACKERS] Failing start-up archive recovery at Standby mode in PG9.2.4

2013-04-25 Thread Kyotaro HORIGUCHI
I forgot it. In conclusion, the standby should name the recycled WAL segment using the same TLI for the LSN on the master. Or should never recycle WAL files. Or the standby should make the request with correct TLI at first consulting the timeline history. Or the standby should make retry with

Re: [HACKERS] danger of stats_temp_directory = /dev/shm

2013-04-25 Thread Peter Eisentraut
On 4/25/13 12:09 AM, Tom Lane wrote: I think we need it fixed to reject any stats_temp_directory that is not postgres-owned with restrictive permissions. The problem here is not with what it deletes, it's with the insanely insecure configuration. Yeah, the requirements should be similar to

Re: [HACKERS] 9.3 Beta1 status report

2013-04-25 Thread Heikki Linnakangas
On 25.04.2013 12:43, Vik Fearing wrote: On 04/24/2013 06:34 PM, Heikki Linnakangas wrote: Let me clarify --- changes to our WAL binary format and source code changes are not really incompatibilities from a user perspective as we never promise to do our best to minimize such changes --- m

Re: [HACKERS] Failing start-up archive recovery at Standby mode in PG9.2.4

2013-04-25 Thread Kyotaro HORIGUCHI
Can you share the modified script, please? Please find the attached files: test.sh : test script. most significant change is the load. I used simple insert instead of pgbench. It might need some more adjustment for other environment as my usual.

Re: [HACKERS] putting a bgworker to rest

2013-04-25 Thread Alvaro Herrera
Peter Eisentraut wrote: On 4/24/13 12:30 PM, Dimitri Fontaine wrote: In Erlang, the lib that cares about such things in called OTP, and that proposes a model of supervisor that knows when to restart a worker. The specs for the restart behaviour are: Restart = permanent | transient |

Re: [HACKERS] danger of stats_temp_directory = /dev/shm

2013-04-25 Thread Andrew Dunstan
On 04/25/2013 11:24 AM, Peter Eisentraut wrote: On 4/25/13 12:09 AM, Tom Lane wrote: I think we need it fixed to reject any stats_temp_directory that is not postgres-owned with restrictive permissions. The problem here is not with what it deletes, it's with the insanely insecure

Re: [HACKERS] minimizing the target list for foreign data wrappers

2013-04-25 Thread Tom Lane
David Gudeman dave.gude...@gmail.com writes: One of the problems I'm having is that in my application, the foreign tables typically have hundreds of columns while typical queries only access a dozen or so (the foreign server is a columnar SQL database). Furthermore, there is no size

Re: [HACKERS] [ADMIN] Simultaneous index creates on different schemas cause deadlock?

2013-04-25 Thread Tom Lane
Paul Hinze paul.t.hi...@gmail.com writes: [ multiple CREATE INDEX CONCURRENTLY commands will deadlock with each other ] Hm. I guess the reason nobody noticed this before now is that generally the idea with CREATE INDEX CONCURRENTLY is to minimize the impact on system load, hence you wouldn't do

Re: [HACKERS] [ADMIN] Simultaneous index creates on different schemas cause deadlock?

2013-04-25 Thread Andres Freund
On 2013-04-25 13:17:31 -0400, Tom Lane wrote: Paul Hinze paul.t.hi...@gmail.com writes: [ multiple CREATE INDEX CONCURRENTLY commands will deadlock with each other ] Hm. I guess the reason nobody noticed this before now is that generally the idea with CREATE INDEX CONCURRENTLY is to

Re: [HACKERS] Bug Fix: COLLATE with multiple ORDER BYs in aggregates

2013-04-25 Thread Tom Lane
David Fetter da...@fetter.org writes: While testing the upcoming FILTER clause for aggregates, Erik Rijkers uncovered a long-standing bug in $subject, namely that this case wasn't handled. Please find attached a patch by Andrew Gierth and myself which fixes this issue and adds a regression

Re: [HACKERS] Bug Fix: COLLATE with multiple ORDER BYs in aggregates

2013-04-25 Thread Andres Freund
On 2013-04-25 13:42:32 -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: While testing the upcoming FILTER clause for aggregates, Erik Rijkers uncovered a long-standing bug in $subject, namely that this case wasn't handled. Please find attached a patch by Andrew Gierth and

Re: [HACKERS] libpq COPY handling

2013-04-25 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Noah Misch pointed out something interesting to me: /* * PQputCopyEnd - send EOF indication to the backend during COPY IN * * After calling this, use PQgetResult() to check command completion status. * * Returns 1 if successful, 0 if data could

Re: [HACKERS] [ADMIN] Simultaneous index creates on different schemas cause deadlock?

2013-04-25 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2013-04-25 13:17:31 -0400, Tom Lane wrote: Since we know that C.I.C. executes in its own transaction, and there can't be more than one on the same table due to locking, it seems to me that it'd be safe to drop our own snapshot before waiting for

Re: [HACKERS] [ADMIN] Simultaneous index creates on different schemas cause deadlock?

2013-04-25 Thread anara...@anarazel.de
Tom Lane t...@sss.pgh.pa.us schrieb: Andres Freund and...@2ndquadrant.com writes: On 2013-04-25 13:17:31 -0400, Tom Lane wrote: Since we know that C.I.C. executes in its own transaction, and there can't be more than one on the same table due to locking, it seems to me that it'd be safe to

Re: [HACKERS] Substituting Checksum Algorithm (was: Enabling Checksums)

2013-04-25 Thread Jeff Davis
On Tue, 2013-04-23 at 11:44 +0300, Ants Aasma wrote: I will try to reword. Did you have a chance to clarify this, as well as some of the other documentation issues Simon mentioned here? http://www.postgresql.org/message-id/CA+U5nMKVEu8UDXQe +Nk=d7nqm4ypfszaef0esak4j31lyqc...@mail.gmail.com I'm

Re: [HACKERS] Failing start-up archive recovery at Standby mode in PG9.2.4

2013-04-25 Thread Heikki Linnakangas
On 25.04.2013 18:56, Kyotaro HORIGUCHI wrote: Can you share the modified script, please? Please find the attached files: test.sh : test script. most significant change is the load. I used simple insert instead of pgbench. It might need some more adjustment

Re: [HACKERS] [ADMIN] Simultaneous index creates on different schemas cause deadlock?

2013-04-25 Thread Tom Lane
anara...@anarazel.de and...@anarazel.de writes: I don't have access to the code ATM an I wonder whether DROP CONCURRENTLY has a similar problem? Depends a bit on how the waiting is done... It's not a problem --- that code doesn't depend on waiting for snapshots to expire, it just checks for

Re: [HACKERS] Allowing parallel pg_restore from pipe

2013-04-25 Thread Timothy Garnett
As the OP, I'll just note that my organization would definitely find use for a parallel migrator tool as long as it supported doing a selection of tables (i.e. -t / -T) in addition to the whole database and it supported or we were able to patch in an option to cluster as part of the migration (the

Re: [HACKERS] Allowing parallel pg_restore from pipe

2013-04-25 Thread Timothy Garnett
On Wed, Apr 24, 2013 at 5:47 PM, Joachim Wieland j...@mcknight.de wrote: On Wed, Apr 24, 2013 at 4:05 PM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: What might make sense is something like pg_dump_restore which would have no intermediate storage at all, just pump the data etc

Re: [HACKERS] Allowing parallel pg_restore from pipe

2013-04-25 Thread Shaun Thomas
On 04/25/2013 12:56 PM, Timothy Garnett wrote: As the OP, I'll just note that my organization would definitely find use for a parallel migrator tool as long as it supported doing a selection of tables (i.e. -t / -T) in addition to the whole database and it supported or we were able to patch in

Re: [HACKERS] Bug Fix: COLLATE with multiple ORDER BYs in aggregates

2013-04-25 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2013-04-25 13:42:32 -0400, Tom Lane wrote: The argument for it seems to be that array_agg(a COLLATE C ORDER BY b COLLATE POSIX) should not throw an error, but why not? Uh. Why should it? SELECT foo COLLATE C FROM ... ORDER BY bar COLLATE

[HACKERS] Fixing statistics problem related to vacuum truncation termination

2013-04-25 Thread Kevin Grittner
After reviewing the threads and thinking about the various historical behaviors, I'm suggesting that we apply the attached, back-patched to 9.0, to fix the unintended changes from historical behavior related to lack of statistics generation in some cases where statistics were generated before

Re: [HACKERS] 9.3 release notes suggestions

2013-04-25 Thread Daniel Farina
On Wed, Apr 24, 2013 at 6:30 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Apr 23, 2013 at 11:41 PM, Bruce Momjian br...@momjian.us wrote: Thanks for the many suggestions on improving the 9.3 release notes. There were many ideas I would have never thought of. Please keep the

Re: [HACKERS] Bug Fix: COLLATE with multiple ORDER BYs in aggregates

2013-04-25 Thread David Fetter
On Thu, Apr 25, 2013 at 06:04:10PM -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2013-04-25 13:42:32 -0400, Tom Lane wrote: The argument for it seems to be that array_agg(a COLLATE C ORDER BY b COLLATE POSIX) should not throw an error, but why not? Uh. Why

Re: [HACKERS] 9.3 release notes suggestions

2013-04-25 Thread Joshua D. Drake
On 04/25/2013 04:48 PM, Daniel Farina wrote: On Wed, Apr 24, 2013 at 6:30 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Apr 23, 2013 at 11:41 PM, Bruce Momjian br...@momjian.us wrote: Thanks for the many suggestions on improving the 9.3 release notes. There were many ideas I would

Re: [HACKERS] event trigger API documentation?

2013-04-25 Thread Peter Eisentraut
On Thu, 2013-04-18 at 17:31 +0200, Dimitri Fontaine wrote: - what about support for PLs other than C and PLpgSQL? It used to be part of the patch, and I don't understand well enough the development calendar to guess if I'm supposed to extract that from earlier patch or if

[HACKERS] pg_controldata gobbledygook

2013-04-25 Thread Peter Eisentraut
I'm not sure who is supposed to be able to read this sort of stuff: Latest checkpoint's NextXID: 0/7575 Latest checkpoint's NextOID: 49152 Latest checkpoint's NextMultiXactId: 7 Latest checkpoint's NextMultiOffset: 13 Latest checkpoint's oldestXID:1265 Latest

Re: [HACKERS] pg_controldata gobbledygook

2013-04-25 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: The comments in the pg_control.h header file use much more pleasant terms, which when put to use would lead to output similar to this: Latest checkpoint's next free transaction ID: 0/7575 Latest checkpoint's next free OID:

Re: [HACKERS] pg_controldata gobbledygook

2013-04-25 Thread Peter Geoghegan
On Thu, Apr 25, 2013 at 8:07 PM, Peter Eisentraut pete...@gmx.net wrote: Comments? +1 from me. I don't think that these particular changes would break WAL-E, Heroku's continuous archiving tool, which has a class called PgControlDataParser. However, it's possible to imagine someone being

Re: [HACKERS] Failing start-up archive recovery at Standby mode in PG9.2.4

2013-04-25 Thread Kyotaro HORIGUCHI
Thank you for the patch. The test script finishes in success with that. And looks reasonable on a short glance. On Fri, Apr 26, 2013 at 4:34 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: One idea to fix this is to not set curFileTLI, until the page header on the just-opened file has

Re: [HACKERS] pg_controldata gobbledygook

2013-04-25 Thread Alvaro Herrera
Tom Lane wrote: I think I've heard of scripts grepping the output of pg_controldata for this that or the other. Any rewording of the labels would break that. While I'm not opposed to improving the labels, I would vote against your second, abbreviated scheme because it would make things

Re: [HACKERS] pg_controldata gobbledygook

2013-04-25 Thread Fabrízio de Royes Mello
On Fri, Apr 26, 2013 at 12:22 AM, Peter Geoghegan p...@heroku.com wrote: On Thu, Apr 25, 2013 at 8:07 PM, Peter Eisentraut pete...@gmx.net wrote: Comments? +1 from me. I don't think that these particular changes would break WAL-E, Heroku's continuous archiving tool, which has a class

Re: [HACKERS] pg_controldata gobbledygook

2013-04-25 Thread Tom Lane
Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: I think I've heard of scripts grepping the output of pg_controldata for this that or the other. Any rewording of the labels would break that. While I'm not opposed to improving the labels, I would vote against your second,

Re: [HACKERS] Failing start-up archive recovery at Standby mode in PG9.2.4

2013-04-25 Thread Amit Langote
How would code after applying this patch behave if a recycled segment gets renamed using the newest timeline (say 3) while we are still recovering from a lower timeline (say 2)? In that case, since XLogFileReadAnyTLI returns that recycled segment as the next segment to recover from, we get the

Re: [HACKERS] Failing start-up archive recovery at Standby mode in PG9.2.4

2013-04-25 Thread Amit Langote
How would code after applying this patch behave if a recycled segment gets renamed using the newest timeline (say 3) while we are still recovering from a lower timeline (say 2)? In that case, since XLogFileReadAnyTLI returns that recycled segment as the next segment to recover from, we get the