Re: [HACKERS] Failing start-up archive recovery at Standby mode in PG9.2.4
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 file for reading a XLog page iterates over a list expectedTLIs which starts with 3 in such a case (that is, in case where this error happens). XLogFileReadAnyTLI checks the segment in both archive and pg_xlog. So, even if such a segment (that is with timeline 3) may not be in the archive , it may be in pg_xlog, due to recycling as we have observed. So, such a recycled segment may be returned by XLogFileReadAnyTLI as though it were the next segment to recover from, resulting in the unexpected pageaddr ... error. I could not understand (in case this error happens) how expectedTLIs list had 3 at its head (for example, when XLogFileReadAnyTLI used it as we observed) whereas at other times (when this error does not happen), it has 2 at its head until all the segments of timeline 2 are recovered from? Also, how does recycled segment gets timeline 3 whereas 2 is expected in this case? Is this the right way to look at the problem and its possible fix? I have tried going through the source regarding this but have not been able to determine where this could accidentally happen, partly because I do not understand recovery process (and its code) very well. Will post if find anything useful. regards, Amit Langote -- View this message in context: http://postgresql.1045698.n5.nabble.com/Failing-start-up-archive-recovery-at-Standby-mode-in-PG9-2-4-tp5753110p5753221.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Redundancy in comment within lock.c
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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [BUGS] BUG #8049: Incorrect results when using ORDER BY and query planner options
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 Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] 9.3 Beta1 status report
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 something that would be only in the source code section and not in the incompatibilities section --- incompatibilities are related to change in user experience or documented-API changes. These guidelines makes sense, except I think the change in naming standard of xlog segments is important to document clearly because, even if we have not promised compatibility, people could be relying on it in scripts. I think it makes sense to waste a couple of lines documenting this change, even if we expect the number of people to be bitten by it to be very low. Right. Kevin mentioned he had a script that knew about the numbering: http://www.postgresql.org/message-id/4fd09b5e022500048...@gw.wicourts.gov. We also have scripts that know about the missing FF. How slim are the chances of having pg_xlogdump output the version of the wal file for 9.3? I know we're right on top of the deadline, but that tool and this change are both new to 9.3. That way our scripts could know if a file is missing or not. I talked about this briefly with Andres on IRC and he says a patch to do this would be trivial. Thoughts? -- 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] Redundancy in comment within lock.c
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. - Heikki -- 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] Enabling Checksums
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 something that could happen in 9.3. I'm inclined to think we should forget about this until we have a concrete use-case for it. As Jeff says, there is no need for pg_control contents to be compatible across major releases, so there's no harm in waiting if we have any doubts about how it ought to work. 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] Proposal to add --single-row to psql
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]( https://forums.aws.amazon.com/thread.jspa?threadID=122664tstart=0) so it's not an option when using psql to download data from Redshift. I don't know what redshift is, but I have very little patience with the idea of burdening psql with yet another random feature in order to work around deficiencies in somebody else's software. Considering that the earliest any such thing could reach the field would be 9.4, it seems not unlikely that the need for it would be gone by next year anyway. 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] Failing start-up archive recovery at Standby mode in PG9.2.4
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 the LSN of the segment recycled on standby is on the TLI=2 in the master, the standby makes the first request for each segment with that LSN but TLI = 3 to the master because the standby runs on recoveryTargetTLI=3. The master reasonably doesn't have it and finally the standby finds that wrong WAL file in its pg_xlog directory before the second request with TLI=2 would be made. 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.. regards, -- Kyotaro Horiguchi -- 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] Proposal to add --single-row to psql
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]( https://forums.aws.amazon.com/thread.jspa?threadID=122664tstart=0) so it's not an option when using psql to download data from Redshift. I don't know what redshift is, It's an Amazon product based on release 8.0, but with many many features removed (e.g. Indexes!) but I have very little patience with the idea of burdening psql with yet another random feature in order to work around deficiencies in somebody else's software. Considering that the earliest any such thing could reach the field would be 9.4, it seems not unlikely that the need for it would be gone by next year anyway. Plus there is the fact that we have no way to test it against redshift anyway. It should be up to Amazon to produce a useful psql program that works with redshift, not us. We have enough to do to support our own product. If this is to be justified at all it needs to be without reference to redshift. 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] Proposal to add --single-row to psql
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]( https://forums.aws.amazon.com/thread.jspa?threadID=122664tstart=0) so it's not an option when using psql to download data from Redshift. I don't know what redshift is, It is a PostgreSQL fork based on ancient source code. From Amazon: Amazon Redshift is based on PostgreSQL 8.0.2. Amazon Redshift and PostgreSQL have a number of very important differences that you must be aware of as you design and develop your data warehouse applications. but I have very little patience with the idea of burdening psql with yet another random feature in order to work around deficiencies in somebody else's software. Considering that the earliest any such thing could reach the field would be 9.4, it seems not unlikely that the need for it would be gone by next year anyway. +1 this is really an amazon problem not a postgresql problem. Sincerely, Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- 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] Failing start-up archive recovery at Standby mode in PG9.2.4
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 from the archive. So the WAL files recycled on this standby will have the TLI=3. Nevertheless the LSN of the segment recycled on standby is on the TLI=2 in the master, the standby makes the first request for each segment with that LSN but TLI = 3 to the master because the standby runs on recoveryTargetTLI=3. The master reasonably doesn't have it and finally the standby finds that wrong WAL file in its pg_xlog directory before the second request with TLI=2 would be made. I'm not sure I understand what the problem is, though. When the standby opens the bogus, recycled, file in pg_xlog, it will notice that the header is incorrect, and retry reading the file from the archive. 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.. Perhaps, but it should nevertheless not get confused by recycled segments. - Heikki -- 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] putting a bgworker to rest
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 supervisord; see configuration settings autorestart and exitcodes here: http://supervisord.org/configuration.html#program-x-section-settings Yes, the feature creep is in full progress! -- 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] Failing start-up archive recovery at Standby mode in PG9.2.4
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 the more small TLIs after it gets broken segments. regards, -- Kyotaro Horiguchi -- 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] danger of stats_temp_directory = /dev/shm
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 what initdb requires for PGDATA and pg_xlog. -- 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] 9.3 Beta1 status report
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 eaning the fact the WAL format changed is something that would be only in the source code section and not in the incompatibilities section --- incompatibilities are related to change in user experience or documented-API changes. These guidelines makes sense, except I think the change in naming standard of xlog segments is important to document clearly because, even if we have not promised compatibility, people could be relying on it in scripts. I think it makes sense to waste a couple of lines documenting this change, even if we expect the number of people to be bitten by it to be very low. Right. Kevin mentioned he had a script that knew about the numbering: http://www.postgresql.org/message-id/4fd09b5e022500048...@gw.wicourts.gov. We also have scripts that know about the missing FF. How slim are the chances of having pg_xlogdump output the version of the wal file for 9.3? I know we're right on top of the deadline, but that tool and this change are both new to 9.3. That way our scripts could know if a file is missing or not. I talked about this briefly with Andres on IRC and he says a patch to do this would be trivial. Seems reasonable. Patches are welcome :-). We're not going to guarantee that pg_xlogdump works across versions, but printing out the version that generated the file seems easy enough. If your script has access to the data directory, you could also easily check PG_VERSION. - Heikki -- 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] Failing start-up archive recovery at Standby mode in PG9.2.4
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. xlog.c.diff : Additional log output I thought to be useful to diagnose. I'm not sure I understand what the problem is, though. When the standby opens the bogus, recycled, file in pg_xlog, it will notice that the header is incorrect, and retry reading the file from the archive. It looks the corrupted header only once. It continues to make retry without looking there after that. Perhaps, but it should nevertheless not get confused by recycled segments. regards, and good night. -- Kyotaro Horiguchi promtest_20130426.tar.gz Description: GNU Zip compressed data -- 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] putting a bgworker to rest
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 | temporary There is also supervisord; see configuration settings autorestart and exitcodes here: http://supervisord.org/configuration.html#program-x-section-settings Yes, the feature creep is in full progress! The main missing feature before this can be sensibly implemented, in my view, is some way to make workers start when they are stopped, assuming no intervening postmaster crash. I suppose we could write a SQL-callable function so that a backend can signal postmaster to launch a worker. For this to work, I think we need an SQL-accesible way to list existing registered workers, along with whether they are running or not, and some identifier. However, the list of registered workers and their statuses currently only exists in postmaster local memory; exporting that might be problematic. (Maybe a simple file with a list of registered workers, but not the status, is good enough. Postmaster could write it after registration is done.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] danger of stats_temp_directory = /dev/shm
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 configuration. Yeah, the requirements should be similar to what initdb requires for PGDATA and pg_xlog. Right. I do think that best practice suggests using a dedicated ram drive rather than /dev/shm. Here's an fstab entry I have used at one client's site: tmpfs /var/lib/pgsql/stats_tmp tmpfs size=5G,uid=postgres,gid=postgres 0 0 I guess if we put in the sort of restrictions being suggested above I'd add a mode argument to the mount options. (This drive might seem large, but total RAM on this machine is 512Gb.) 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] minimizing the target list for foreign data wrappers
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 optimization for NULL values passed back from the foreign server, so if I return all of the columns from the table --even as NULLs-- the returned data size will be several times the size that it needs to be. My application cannot tolerate this level of inefficiency, so I need to return minimal columns from the foreign table. That's already possible; see contrib/postgres_fdw in HEAD for an existence proof. The documentation doesn't say how to do this, but looking at the code I think it is possible. In GetForeignPlan() you have to pass on the tlist argument, which I presume means that the query plan will use the tlist that I pass in, right? If so, then it should be possible for me to write a function that takes tlist and baserel-reltargetlist and return a version of tlist that knows which foreign-table columns are actually used, and replaces the rest with a NULL constant. You do not get to editorialize on the tlist that will be computed by the ForeignScan node: in the case of a simple single-table SELECT, that's going to be computing the expressions the user asked for. Nor can you alter the expectation about the rowtype of the scan tuple that's returned by the FDW: that needs to match the declared rowtype of the foreign table. However, you can skip fetching unneeded columns and just set those fields of the scan tuple to nulls. That's cheap enough (particularly if the scan tuple stays virtual) that I'm unconvinced we should contort the APIs to the extent that would be needed to let the FDW change the scan tuple rowtype dynamically. 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] [ADMIN] Simultaneous index creates on different schemas cause deadlock?
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 more than one at a time. Still, it's surely a POLA violation that you *can't* do more than one at a time. The cause is that each one will wait for all older snapshots to be gone --- and it does that before dropping its own snapshot, so that the other ones will see it as something to be waited out too. 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 other xacts to end. That is, we could just rearrange the last few steps in DefineIndex(), taking care to save snapshot-xmin before we destroy the snapshot so that we still have that value to pass to GetCurrentVirtualXIDs(). Anybody see a flaw in that solution? 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] [ADMIN] Simultaneous index creates on different schemas cause deadlock?
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 minimize the impact on system load, hence you wouldn't do more than one at a time. Still, it's surely a POLA violation that you *can't* do more than one at a time. The cause is that each one will wait for all older snapshots to be gone --- and it does that before dropping its own snapshot, so that the other ones will see it as something to be waited out too. Makes sense. 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 other xacts to end. That is, we could just rearrange the last few steps in DefineIndex(), taking care to save snapshot-xmin before we destroy the snapshot so that we still have that value to pass to GetCurrentVirtualXIDs(). Anybody see a flaw in that solution? Except that it still will unnecessarily wait for other CICs, just not deadlock, I don't see a problem. We could have a PROC_IN_CIC flag or something so we can ignore other index creations, but I am not sure if its worth the complication. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Bug Fix: COLLATE with multiple ORDER BYs in aggregates
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 test to ensure it remains fixed. I don't find this patch to be a good idea. 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? And what does that have to do with whacking around the code for CASE? 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] Bug Fix: COLLATE with multiple ORDER BYs in aggregates
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 myself which fixes this issue and adds a regression test to ensure it remains fixed. I don't find this patch to be a good idea. 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 POSIX doesn't throw one either? And what does that have to do with whacking around the code for CASE? I guess that's to avoid to repeat that already triplicated block of code once more. The goal seems to make sense to me, although I am not 100% that thats the nicest solution to get of the repetition. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] libpq COPY handling
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 not be sent (only possible * in nonblock mode), or -1 if an error occurs. */ The comment alleges that 0 is a possible return value, but the only return statements in the code for that function return literal values of either 1 or -1. I'm not sure whether that's a bug in the code or the documentation. Hm. pqFlush has a three-way return value which PQputCopyEnd is only checking as a boolean. Probably the intent was to return data not sent if pqFlush reports that. However, the documentation in libpq.sgml is a bit bogus too, because it counsels trying the PQputCopyEnd() call again, which will not work (since we already changed the asyncStatus). We could make that say a zero result is informational, you might want to try PQflush() later. The trouble with this, though, is that any existing callers that were coded to the old spec would now be broken. It might be better to consider that the code is correct and fix the documentation. I notice that the other places in fe-exec.c that call pqFlush() generally say In nonblock mode, don't complain if we're unable to send it all, which is pretty much the spirit of what this is doing though it lacks that comment. Also, I noticed that there are a few places in fe-protocol3.c that seem not to know about COPY-BOTH mode. I'm not sure that any of these are actually bugs right now given the current very limited use of COPY-BOTH mode, but I'm wondering whether it wouldn't be better to minimize the chance of future surprises. Patch attached. +1 for these changes, anyway. 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] [ADMIN] Simultaneous index creates on different schemas cause deadlock?
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 other xacts to end. That is, we could just rearrange the last few steps in DefineIndex(), taking care to save snapshot-xmin before we destroy the snapshot so that we still have that value to pass to GetCurrentVirtualXIDs(). Anybody see a flaw in that solution? Except that it still will unnecessarily wait for other CICs, just not deadlock, I don't see a problem. We could have a PROC_IN_CIC flag or something so we can ignore other index creations, but I am not sure if its worth the complication. I'm not sure it's a good idea to ignore other CICs altogether --- they could be executing user-defined index functions that do strange things like consult other tables. Since this seems to me to be a bit outside the intended use-case for CIC anyway, I think it's good enough if they just don't deadlock. 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] [ADMIN] Simultaneous index creates on different schemas cause deadlock?
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 drop our own snapshot before waiting for other xacts to end. That is, we could just rearrange the last few steps in DefineIndex(), taking care to save snapshot-xmin before we destroy the snapshot so that we still have that value to pass to GetCurrentVirtualXIDs(). Anybody see a flaw in that solution? Except that it still will unnecessarily wait for other CICs, just not deadlock, I don't see a problem. We could have a PROC_IN_CIC flag or something so we can ignore other index creations, but I am not sure if its worth the complication. I'm not sure it's a good idea to ignore other CICs altogether --- they could be executing user-defined index functions that do strange things like consult other tables. Since this seems to me to be a bit outside the intended use-case for CIC anyway, I think it's good enough if they just don't deadlock Fine with me, especially as nobody seems to have complained so far other than the OP, so it doesn't seem to be to common. 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... Andres --- Please excuse brevity and formatting - I am writing this on my mobile phone. -- 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] Substituting Checksum Algorithm (was: Enabling Checksums)
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 not sure if others are waiting on me for a new patch or not. I can give the documentation issues a try, but I was hesitant to do so because you've done the research. The problems that I can correct are fairly trivial. Regards, Jeff Davis -- 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] Failing start-up archive recovery at Standby mode in PG9.2.4
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 for other environment
as my usual.
xlog.c.diff : Additional log output I thought to be useful to diagnose.
Ok, thanks, I see what's going on now. The problem is that once
XLogFileRead() finds a file with tli X, it immediately sets curFileTLI =
X. XLogFileReadAnyTLI() never tries to read files with tli curFileTLI.
So, if recovery finds a file with the right filename, e.g
00030008, it never tries to open
00020008 anymore, even if the contents of
00030008 later turn out to be bogus.
One idea to fix this is to not set curFileTLI, until the page header on
the just-opened file has been verified. Another idea is to change the
check in XLogFileReadAnyTLI() that currently forbids curFileTLI from
moving backwards. We could allow curFileTLI to move backwards, as long
as the tli is = ThisTimeLineID (ThisTimeLineID is the current timeline
we're recovering records from).
Attached is a patch for the 2nd approach. With the patch, the test
script works for me. Thoughts?
PS. This wasn't caused by the 9.2.4 change to do crash recovery before
entering archive recovery. The test script fails in the same way with
9.2.3 as well.
- Heikki
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 690077c..4ca75d7 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -251,8 +251,7 @@ static bool recoveryStopAfter;
* curFileTLI: the TLI appearing in the name of the current input WAL file.
* (This is not necessarily the same as ThisTimeLineID, because we could
* be scanning data that was copied from an ancestor timeline when the current
- * file was created.) During a sequential scan we do not allow this value
- * to decrease.
+ * file was created.)
*/
static TimeLineID recoveryTargetTLI;
static bool recoveryTargetIsLatest = false;
@@ -657,7 +656,7 @@ static bool InstallXLogFileSegment(uint32 *log, uint32 *seg, char *tmppath,
static int XLogFileRead(uint32 log, uint32 seg, int emode, TimeLineID tli,
int source, bool notexistOk);
static int XLogFileReadAnyTLI(uint32 log, uint32 seg, int emode,
- int sources);
+ int sources, bool randAccess);
static bool XLogPageRead(XLogRecPtr *RecPtr, int emode, bool fetching_ckpt,
bool randAccess);
static int emode_for_corrupt_record(int emode, XLogRecPtr RecPtr);
@@ -2899,7 +2898,8 @@ XLogFileRead(uint32 log, uint32 seg, int emode, TimeLineID tli,
* This version searches for the segment with any TLI listed in expectedTLIs.
*/
static int
-XLogFileReadAnyTLI(uint32 log, uint32 seg, int emode, int sources)
+XLogFileReadAnyTLI(uint32 log, uint32 seg, int emode, int sources,
+ bool randAccess)
{
char path[MAXPGPATH];
ListCell *cell;
@@ -2909,17 +2909,16 @@ XLogFileReadAnyTLI(uint32 log, uint32 seg, int emode, int sources)
* Loop looking for a suitable timeline ID: we might need to read any of
* the timelines listed in expectedTLIs.
*
- * We expect curFileTLI on entry to be the TLI of the preceding file in
- * sequence, or 0 if there was no predecessor. We do not allow curFileTLI
- * to go backwards; this prevents us from picking up the wrong file when a
- * parent timeline extends to higher segment numbers than the child we
- * want to read.
+ * During a sequential read, do not check TLIs smaller than the timeline
+ * we're currently recovering (ThisTimeLineID); this prevents us from
+ * picking up the wrong file when a parent timeline extends to higher
+ * segment numbers than the child we want to read.
*/
foreach(cell, expectedTLIs)
{
TimeLineID tli = (TimeLineID) lfirst_int(cell);
- if (tli curFileTLI)
+ if (!randAccess tli ThisTimeLineID)
break;/* don't bother looking at too-old TLIs */
if (sources XLOG_FROM_ARCHIVE)
@@ -10510,9 +10509,6 @@ retry:
close(readFile);
readFile = -1;
}
- /* Reset curFileTLI if random fetch. */
- if (randAccess)
- curFileTLI = 0;
/*
* Try to restore the file from archive, or read an
@@ -10573,7 +10569,7 @@ retry:
/* Don't try to read from a source that just failed */
sources = ~failedSources;
readFile = XLogFileReadAnyTLI(readId, readSeg, DEBUG2,
- sources);
+ sources, randAccess);
switched_segment = true;
if (readFile = 0)
break;
@@ -10607,16 +10603,12 @@ retry:
{
int sources;
-/* Reset curFileTLI if random fetch. */
-if (randAccess)
- curFileTLI = 0;
-
sources = XLOG_FROM_PG_XLOG;
if (InArchiveRecovery)
sources |= XLOG_FROM_ARCHIVE;
readFile =
Re: [HACKERS] [ADMIN] Simultaneous index creates on different schemas cause deadlock?
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 other sessions holding locks on the target table. (I also did some experimental testing to verify this.) 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] Allowing parallel pg_restore from pipe
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 equivalent of something like https://github.com/tgarnett/postgres/commit/cc320a71 ). Tim 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 from one source to another in parallel. But I pity the poor guy who has to write it :-) hmm pretty sure that Joachims initial patch for parallel dump actually had a PoC for something very similiar to that... That's right, I implemented that as an own output format and named it migrator I think, which wouldn't write each stream to a file as the directory output format does but that instead pumps it back into a restore client. Actually I think the logic was even reversed, it was a parallel restore that got the data from internally calling pg_dump functionality instead of from reading files... The neat thing about this approach was that the order was optimized and correct, i.e. largest tables start first and dependencies get resolved in the right order. I could revisit that patch for 9.4 if enough people are interested. Joachim
Re: [HACKERS] Allowing parallel pg_restore from pipe
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 from one source to another in parallel That's right, I implemented that as an own output format and named it migrator I think, which wouldn't write each stream to a file as the directory output format does but that instead pumps it back into a restore client. I could revisit that patch for 9.4 if enough people are interested. Joachim 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 equivalent of something like https://github.com/tgarnett/postgres/commit/cc320a71 ). Tim
Re: [HACKERS] Allowing parallel pg_restore from pipe
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 an option to cluster as part of
the migration (the equivalent of something like
https://github.com/tgarnett/postgres/commit/cc320a71 ).
If you need something like this short term, we actually found a way to
do it ourselves for a migration we performed back in October. The secret
is xargs with the -P option:
xargs -I{} -P 8 -a table-list.txt \
bash -c pg_dump -Fc -t {} my_db | pg_restore -h remote -d my_db
Fill table-list.txt with as many, or as few tables as you want. The
above example would give you 8 parallel threads. Well equipped systems
may be able to increase this.
Admittedly it's a gross hack, but it works. :)
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com
__
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to
this email
--
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] Bug Fix: COLLATE with multiple ORDER BYs in aggregates
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 POSIX doesn't throw one either? After thinking about it a bit more, this case *should* throw an error: string_agg(a COLLATE C, b COLLATE POSIX) but these should not: array_agg(a COLLATE C ORDER BY b COLLATE POSIX) array_agg(a ORDER BY b COLLATE C, c COLLATE POSIX) that is, the ORDER BY expression(s) ought to be considered independently rather than as part of the agg's argument list. It looks like the proposed patch gets this right, but the proposed test cases really fail to illuminate the problem IMO. 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
[HACKERS] Fixing statistics problem related to vacuum truncation termination
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 b19e4250b45e91c9cbdd18d35ea6391ab5961c8d, and to generate them in some cases where they were historically suppressed. Prior behavior was different between a VACUUM command and autovacuum when there was sufficient empty space at the end of a table to trigger an attempt to truncate the table: * For a VACUUM command, statistics were always generated on a VACUUM ANALYZE, and truncation might or might not occur, depending on whether it was able to immediately get an AccessExclusiveLock on the table when it got to this phase of VACUUM processing. If it was able to get the lock, it would hold it for as long as the truncation took, blocking any other access to the table -- potentially for a very long time. If it was not immediately able to get the lock, it would not attempt any truncation, so truncation was not deterministic before the recent patch. * For autovacuum, if it was initially unable to acquire the AccessExclusiveLock on the table when it got to this phase no truncation was attempted and statistics were generated. If it acquired the lock and blocked any other process for the duration set by deadlock_timeout all work toward truncation was discarded and no statistics were generated. If it was able to complete the truncation, statistics were generated. So before the recent patch neither truncation nor statistics generation were deterministic. Current behavior for both the VACUUM command and autovacuum is to avoid any prolonged holding of AccessExclusiveLock on the table when there is contention for the lock, with limited retries to acquire or reacquire the lock to make incremental progress on truncation. Any progress on truncating is not lost if the lock is relinquished to allow other tasks to proceed. I'm proposing that we don't change that part of it. The problem is that current behavior is to skip statistics generation if the truncation attempt is terminated due to contention for the table lock; whereas historically that was never skipped for the VACUUM ANALYZE command, and only sometimes skipped when autovacuum was intending to analyze the table but was unable to complete the truncation. The attached will not skip the analyze step where it had historically run, and will actually allow autovacuum to run it when the truncation attempt was started but not able to complete. The old mechanism for terminating the truncation attempt (a cancel signal from the blocked process) did not allow this. The attached is along the lines of what Tom suggested was the minimal fix, and less drastic than what I was initially proposing -- which was to also restore historical behavior for the VACUUM command. After seeing how unpredictable that behavior was regarding truncation, it doesn't seem wise to complicate the code to try to go back to that. I also think that the new LOG level entry about giving up on the truncate attempt is too chatty, and we've gotten questions from users who were somewhat alarmed by it, so I toned it down. I'm still not sure that the logging is quite optimal yet, so any suggestions are welcome. The only change outside of local naming, white space, comments, messages, and moving a couple variables into a more local scope is this: - if (!vacrelstats-lock_waiter_detected) - pgstat_report_vacuum(RelationGetRelid(onerel), - onerel-rd_rel-relisshared, - new_rel_tuples); - else - vacstmt-options = ~VACOPT_ANALYZE; + pgstat_report_vacuum(RelationGetRelid(onerel), + onerel-rd_rel-relisshared, + new_rel_tuples); ... which simply reverts this part to match older code. This is being presented for discussion; I have not finished testing it. Comments? -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Companydiff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index d392698..8a1ffcf 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -78,9 +78,9 @@ * that the potential for improvement was great enough to merit the cost of * supporting them. */ -#define AUTOVACUUM_TRUNCATE_LOCK_CHECK_INTERVAL 20 /* ms */ -#define AUTOVACUUM_TRUNCATE_LOCK_WAIT_INTERVAL 50 /* ms */ -#define AUTOVACUUM_TRUNCATE_LOCK_TIMEOUT 5000 /* ms */ +#define VACUUM_TRUNCATE_LOCK_CHECK_INTERVAL 20 /* ms */ +#define VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL 50 /* ms */ +#define VACUUM_TRUNCATE_LOCK_TIMEOUT 5000 /* ms */ /* * Guesstimation of number of dead tuples per page. This is used to @@ -285,17 +285,10 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt, new_frozen_xid,
Re: [HACKERS] 9.3 release notes suggestions
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 suggestions coming. Bruce, Thanks for writing them! Consider the sentiment duplicated. Thank you, Bruce. -- 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] Bug Fix: COLLATE with multiple ORDER BYs in aggregates
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 should it? SELECT foo COLLATE C FROM ... ORDER BY bar COLLATE POSIX doesn't throw one either? After thinking about it a bit more, this case *should* throw an error: string_agg(a COLLATE C, b COLLATE POSIX) but these should not: array_agg(a COLLATE C ORDER BY b COLLATE POSIX) array_agg(a ORDER BY b COLLATE C, c COLLATE POSIX) that is, the ORDER BY expression(s) ought to be considered independently rather than as part of the agg's argument list. It looks like the proposed patch gets this right, but the proposed test cases really fail to illuminate the problem IMO. regards, tom lane Am I understanding correctly that you want the code left alone and the test case expanded as above? 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] 9.3 release notes suggestions
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 have never thought of. Please keep the suggestions coming. Bruce, Thanks for writing them! Consider the sentiment duplicated. Thank you, Bruce. Isn't that a primary key violation? /me runs Sorry it has been a really long two days. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- 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] event trigger API documentation?
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 that's too late for 9.3. I'm not sure
what Peter's idea are wrt to the calendar here.
I added event trigger support to PL/sh, just for some additional
validation:
https://github.com/petere/plsh/tree/event-triggers
It seems pretty straightforward and useful, so I'm not sure where your
hesitation is coming from.
Based in this, I could add some documentation in the coming weeks.
I don't think we have time to add support for this to the in-tree PLs.
But I was thinking we should at least check all PLs out there that they
don't crash if they are presented with an event trigger function,
because if you code a PL like this:
if (CALLED_AS_TRIGGER(fcinfo) {
// it's a trigger
}
else {
// it's a normal call
}
there might be some trouble.
--
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_controldata gobbledygook
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 checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 0 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 1 Note that these symbols don't even correspond to the actual symbols used in the source code in some cases. 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:49152 Latest checkpoint's next free MultiXactId:7 Latest checkpoint's next free MultiXact offset: 13 Latest checkpoint's cluster-wide minimum datfrozenxid:1265 Latest checkpoint's database with cluster-wide minimum datfrozenxid: 1 Latest checkpoint's oldest transaction ID still running: 0 Latest checkpoint's cluster-wide minimum datminmxid: 1 Latest checkpoint's database with cluster-wide minimum datminmxid: 1 One could even rearrange the layout a little bit like this: Control data as of latest checkpoint: next free transaction ID: 0/7575 next free OID:49152 etc. Comments? -- 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_controldata gobbledygook
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:49152 Latest checkpoint's next free MultiXactId:7 Latest checkpoint's next free MultiXact offset: 13 Latest checkpoint's cluster-wide minimum datfrozenxid:1265 Latest checkpoint's database with cluster-wide minimum datfrozenxid: 1 Latest checkpoint's oldest transaction ID still running: 0 Latest checkpoint's cluster-wide minimum datminmxid: 1 Latest checkpoint's database with cluster-wide minimum datminmxid: 1 One could even rearrange the layout a little bit like this: Control data as of latest checkpoint: next free transaction ID: 0/7575 next free OID:49152 etc. Comments? 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 ambiguous for simple grep-based scripts. 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] pg_controldata gobbledygook
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 affected in a similar way. So I'd be sure to document it clearly, and to perhaps preserve the old label names to avoid breaking scripts. -- Peter Geoghegan -- 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] Failing start-up archive recovery at Standby mode in PG9.2.4
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 been verified. Another idea is to change the check in XLogFileReadAnyTLI() that currently forbids curFileTLI from moving backwards. We could allow curFileTLI to move backwards, as long as the tli is = ThisTimeLineID (ThisTimeLineID is the current timeline we're recovering records from). Attached is a patch for the 2nd approach. With the patch, the test script works for me. Thoughts? I am uncertain a bit weather it is necessary to move curFileTLI to anywhere randomly read . On a short glance, the random access occurs also for reading checkpoint-related records. Also I don't have clear distinction between lastSegmentTLI and curFileTLI after the patch applied. Although , I need look closer around them to understand. PS. This wasn't caused by the 9.2.4 change to do crash recovery before entering archive recovery. The test script fails in the same way with 9.2.3 as well. -- Kyotaro Horiguchi
Re: [HACKERS] pg_controldata gobbledygook
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 ambiguous for simple grep-based scripts. We could provide two alternative outputs, one for human consumption with the proposed format and something else that uses, say, shell assignment syntax. (I did propose this years ago and I might have an unfinished patch still lingering about somewhere.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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_controldata gobbledygook
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 called PgControlDataParser. However, it's possible to imagine someone being affected in a similar way. So I'd be sure to document it clearly, and to perhaps preserve the old label names to avoid breaking scripts. Why don't we add options to pg_controldata outputs the info in other several formats like json, yaml, xml or another one? Best regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] pg_controldata gobbledygook
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, abbreviated scheme because it would make things ambiguous for simple grep-based scripts. We could provide two alternative outputs, one for human consumption with the proposed format and something else that uses, say, shell assignment syntax. (I did propose this years ago and I might have an unfinished patch still lingering about somewhere.) And a script would use that how? pg_controldata --machine-friendly would fail outright on older versions. I think it's okay to ask script writers to write pg_controldata | grep -e 'old label|new label' but not okay to ask them to deal with anything as complicated as trying a switch to see if it works or not. 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] Failing start-up archive recovery at Standby mode in PG9.2.4
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 error. And since XLogFileReadAnyTLI iterates over expectedTLIs (whose head seems to be recoveryTargetTLI at all times, is that right?), it will return that wrong (recycled segment) in the first iteration itself. The code for renaming the recycling segment remains unaffected by this patch, right? That is, the code which assigns timelineID to newly created / recycled segments is not changed. (a snippet from CreateRestartPoint() ) /* * Update ThisTimeLineID to the recovery target timeline, so that * we install any recycled segments on the correct timeline. */ ThisTimeLineID = GetRecoveryTargetTLI(); RemoveOldXlogFiles(_logId, _logSeg, endptr); /* * Make more log segments if needed. (Do this after recycling old log * segments, since that may supply some of the needed files.) */ PreallocXlogFiles(endptr); So, would we still get a similar behavior (wherein the wrongly named recycled segments are produced) and cause the error to happen again? Do we need to look here? What do you think? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Failing-start-up-archive-recovery-at-Standby-mode-in-PG9-2-4-tp5753110p5753352.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Failing start-up archive recovery at Standby mode in PG9.2.4
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 error. And since XLogFileReadAnyTLI iterates over expectedTLIs (whose head seems to be recoveryTargetTLI at all times, is that right?), it will return that wrong (recycled segment) in the first iteration itself. The code for renaming the recycling segment remains unaffected by this patch, right? That is, the code which assigns timelineID to newly created / recycled segments is not changed. (a snippet from CreateRestartPoint() ) /* * Update ThisTimeLineID to the recovery target timeline, so that * we install any recycled segments on the correct timeline. */ ThisTimeLineID = GetRecoveryTargetTLI(); RemoveOldXlogFiles(_logId, _logSeg, endptr); /* * Make more log segments if needed. (Do this after recycling old log * segments, since that may supply some of the needed files.) */ PreallocXlogFiles(endptr); So, would we still get a similar behavior (wherein the wrongly named recycled segments are produced) and cause the error to happen again? Do we need to look here? What do you think? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Failing-start-up-archive-recovery-at-Standby-mode-in-PG9-2-4-tp5753110p5753353.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
