Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-26 Thread Heikki Linnakangas
On 25.10.2011 19:37, Jeff Davis wrote: On Mon, 2011-10-24 at 13:15 +0300, Heikki Linnakangas wrote: Hmm, I don't think that's safe. After Oid wraparound, a range type oid might get reused for some other range type, and the cache would return stale values. Extremely unlikely to happen by

Re: [HACKERS] Your review of pg_receivexlog/pg_basebackup

2011-10-26 Thread Heikki Linnakangas
(CC'ing pgsql-hackers, this started as an IM discussion yesterday but really belongs in the archives) On 25.10.2011 23:52, Magnus Hagander wrote: There's a tiny chance to get incomplete xlog files with pg_receivexlog if you crash: 1. pg_receivexlog finishes write()ing a file but system

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2011-10-26 Thread Shigeru Hanada
(2011/10/25 19:15), Magnus Hagander wrote: 2011/10/25 Shigeru Hanadashigeru.han...@gmail.com: I'd like to propose pgsql_fdw, FDW for external PostgreSQL server, as a contrib module. I think that this module would be the basis of further SQL/MED development for core, e.g. join-push-down and

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Simon Riggs
On Tue, Oct 25, 2011 at 10:06 PM, Chris Redekop ch...@replicon.com wrote: Chris, can you rearrange the backup so you copy the pg_control file as the first act after the pg_start_backup? I tried this and it doesn't seem to make any difference. It won't, that was a poor initial diagnosis on my

Re: [HACKERS] TOAST versus VACUUM, or missing chunk number 0 for toast value identified

2011-10-26 Thread Simon Riggs
On Wed, Oct 26, 2011 at 4:24 AM, Tom Lane t...@sss.pgh.pa.us wrote: Even given your recent changes to reduce the overhead of checking for sinval messages, I'm not sure that it'd be practical to move the sinval message processing to just-before-we-look-up-a-cache-entry.  Right now, we do

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Florian Pflug
On Oct25, 2011, at 14:51 , Simon Riggs wrote: On Tue, Oct 25, 2011 at 12:39 PM, Florian Pflug f...@phlo.org wrote: What I don't understand is how this affects the CLOG. How does oldestActiveXID factor into CLOG initialization? It is an entirely different error. Ah, OK. I assumed that

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Florian Pflug
On Oct25, 2011, at 13:39 , Florian Pflug wrote: On Oct25, 2011, at 11:13 , Simon Riggs wrote: On Tue, Oct 25, 2011 at 8:03 AM, Simon Riggs si...@2ndquadrant.com wrote: We are starting recovery at the right place but we are initialising the clog and subtrans incorrectly. Precisely, the

Re: [HACKERS] patch for distinguishing PG instances in event log v2

2011-10-26 Thread MauMau
From: Magnus Hagander mag...@hagander.net 2011/7/16 MauMau maumau...@gmail.com: Hello, The attached file is a revised patch which reflects all review comments by Magnus in: http://archives.postgresql.org/pgsql-hackers/2011-07/msg00839.php I have applied this patch after another round of

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Simon Riggs
On Wed, Oct 26, 2011 at 12:16 PM, Florian Pflug f...@phlo.org wrote: Chris' clog error was caused by a file read error. The file was opened, we did a seek within the file and then the call to read() failed to return a complete page from the file. The xid shown is 22811359, which is the

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Aidan Van Dyk
On Wed, Oct 26, 2011 at 7:43 AM, Simon Riggs si...@2ndquadrant.com wrote: It's very likely that it's a PostgreSQL problem, though. It's probably not a pilot error since it happens even for backups taken with pg_basebackup(), so the only explanation other than a PostgreSQL bug is broken

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Simon Riggs
On Wed, Oct 26, 2011 at 12:54 PM, Aidan Van Dyk ai...@highrise.ca wrote: The read fails because their is no data at the location it's trying to read from, because clog hasn't been extended yet by recovery. You don't actually know that, though I agree it seems a reasonable guess and was my

Re: [HACKERS] TOAST versus VACUUM, or missing chunk number 0 for toast value identified

2011-10-26 Thread Robert Haas
On Tue, Oct 25, 2011 at 11:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: Even given your recent changes to reduce the overhead of checking for sinval messages, I'm not sure that it'd be practical to move the sinval message processing to just-before-we-look-up-a-cache-entry. Wait a minute: I'm

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-26 Thread Robert Haas
On Tue, Oct 25, 2011 at 12:37 PM, Jeff Davis pg...@j-davis.com wrote: On Mon, 2011-10-24 at 13:15 +0300, Heikki Linnakangas wrote: Hmm, I don't think that's safe. After Oid wraparound, a range type oid might get reused for some other range type, and the cache would return stale values.

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2011-10-26 Thread Tom Lane
Shigeru Hanada shigeru.han...@gmail.com writes: (2011/10/25 19:15), Magnus Hagander wrote: I have not looked at the code itself, but I wonder if we shouldn't consider making this a part of core-proper, not just a contrib module. The fact that it isn't *already* available in core surprises a

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Florian Pflug
On Oct26, 2011, at 15:12 , Simon Riggs wrote: On Wed, Oct 26, 2011 at 12:54 PM, Aidan Van Dyk ai...@highrise.ca wrote: The read fails because their is no data at the location it's trying to read from, because clog hasn't been extended yet by recovery. You don't actually know that, though I

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Chris Redekop
And I think they also reported that if they didn't run hot standby, but just normal recovery into a new master, it didn't have the problem either, i.e. without hotstandby, recovery ran, properly extended the clog, and then ran as a new master fine. Yes this is correct...attempting to start as

[HACKERS] cache lookup failed in plpgsql - reason?

2011-10-26 Thread Pavel Stehule
Hello one my customer reported a random issue. He uses a procedure with following fragment IF NOT EXISTS( SELECT relname FROM pg_class WHERE relname = 'tmp_object_state_change' AND relkind = 'r' AND pg_table_is_visible(oid) ) THEN CREATE TEMPORARY TABLE tmp_object_state_change (

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2011-10-26 Thread Robert Haas
2011/10/26 Shigeru Hanada shigeru.han...@gmail.com: (2011/10/25 19:15), Magnus Hagander wrote: 2011/10/25 Shigeru Hanadashigeru.han...@gmail.com: I'd like to propose pgsql_fdw, FDW for external PostgreSQL server, as a contrib module.  I think that this module would be the basis of further

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Florian Pflug
On Oct26, 2011, at 15:57 , Florian Pflug wrote: As you said, the CLOG page corresponding to nextId *should* always be accessible at the start of recovery (Unless whole file has been removed by VACUUM, that is). So we shouldn't need to extends CLOG. Yet the error suggest that the CLOG is, in

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2011-10-26 Thread Dimitri Fontaine
Stephen Frost sfr...@snowman.net writes: I'm in favor of making that distinction. I would still have pgsql_fdw, file_fdw, etc, be packaged more-or-less the same way and still use the CREATE EXTENTION framework, of course. We called that idea “core extension” at the latest hackers meeting, and

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-26 Thread Heikki Linnakangas
On 26.10.2011 18:42, Robert Haas wrote: On Tue, Oct 25, 2011 at 12:37 PM, Jeff Davispg...@j-davis.com wrote: Aren't there a few other cases like this floating around the code? I know the single-xid cache is potentially vulnerable to xid wraparound for the same reason. I believe that we're in

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2011-10-26 Thread Kohei KaiGai
2011/10/26 Robert Haas robertmh...@gmail.com: 2011/10/26 Shigeru Hanada shigeru.han...@gmail.com: (2011/10/25 19:15), Magnus Hagander wrote: 2011/10/25 Shigeru Hanadashigeru.han...@gmail.com: I'd like to propose pgsql_fdw, FDW for external PostgreSQL server, as a contrib module.  I think that

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Aidan Van Dyk
On Wed, Oct 26, 2011 at 9:57 AM, Florian Pflug f...@phlo.org wrote: On Oct26, 2011, at 15:12 , Simon Riggs wrote: On Wed, Oct 26, 2011 at 12:54 PM, Aidan Van Dyk ai...@highrise.ca wrote: The read fails because their is no data at the location it's trying to read from, because clog hasn't been

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Florian Pflug
On Oct26, 2011, at 17:36 , Chris Redekop wrote: And I think they also reported that if they didn't run hot standby, but just normal recovery into a new master, it didn't have the problem either, i.e. without hotstandby, recovery ran, properly extended the clog, and then ran as a new master

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Simon Riggs
On Wed, Oct 26, 2011 at 3:47 PM, Florian Pflug f...@phlo.org wrote: On Oct26, 2011, at 15:57 , Florian Pflug wrote: As you said, the CLOG page corresponding to nextId *should* always be accessible at the start of recovery (Unless whole file has been removed by VACUUM, that is). So we shouldn't

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Simon Riggs
On Wed, Oct 26, 2011 at 5:08 PM, Simon Riggs si...@2ndquadrant.com wrote: Brewing a patch now. Latest thinking... confirmations or other error reports please. This fixes both the subtrans and clog bugs in one patch. --  Simon Riggs   http://www.2ndQuadrant.com/  PostgreSQL

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-26 Thread Robert Haas
On Wed, Oct 26, 2011 at 11:52 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 26.10.2011 18:42, Robert Haas wrote: On Tue, Oct 25, 2011 at 12:37 PM, Jeff Davispg...@j-davis.com  wrote: Aren't there a few other cases like this floating around the code? I know the

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-26 Thread Alvaro Herrera
Excerpts from Robert Haas's message of mié oct 26 13:19:47 -0300 2011: On Wed, Oct 26, 2011 at 11:52 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: 1. In session A: BEGIN; SELECT * FROM foo WHERE id = 1; COMMIT;   The row has xmin = 123456, and it is cached as committed

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2011-10-26 Thread Magnus Hagander
On Wed, Oct 26, 2011 at 16:37, Tom Lane t...@sss.pgh.pa.us wrote: Shigeru Hanada shigeru.han...@gmail.com writes: (2011/10/25 19:15), Magnus Hagander wrote: I have not looked at the code itself, but I wonder if we shouldn't consider making this a part of core-proper, not just a contrib module.

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-26 Thread Jeff Davis
On Wed, 2011-10-26 at 12:19 -0400, Robert Haas wrote: 1. In session A: BEGIN; SELECT * FROM foo WHERE id = 1; COMMIT; The row has xmin = 123456, and it is cached as committed in the one-item cache by TransactionLogFetch. 2. A lot of time passes. Everything is frozen, and XID wrap-around

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Simon Riggs
On Wed, Oct 26, 2011 at 5:16 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Oct 26, 2011 at 5:08 PM, Simon Riggs si...@2ndquadrant.com wrote: Brewing a patch now. Latest thinking... confirmations or other error reports please. This fixes both the subtrans and clog bugs in one patch.

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2011-10-26 Thread Andrew Dunstan
On 10/26/2011 12:47 PM, Magnus Hagander wrote: If that was what he meant, I'd vote against it. There are way too many people who will *not* want their databases configured to be able to reach out onto the net. This feature should be something that has to be installed by explicit user

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-26 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: That's not what Jeff is referring to here, though (correct me if I'm wrong). He's talking about the one-item cache in TransactionIdLogFetch(). You don't need need long-running transactions for that to get confused. Specifically,

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2011-10-26 Thread Magnus Hagander
On Wed, Oct 26, 2011 at 19:25, Andrew Dunstan and...@dunslane.net wrote: On 10/26/2011 12:47 PM, Magnus Hagander wrote: If that was what he meant, I'd vote against it.  There are way too many people who will *not* want their databases configured to be able to reach out onto the net.  This

[HACKERS] autovacuum workers warning

2011-10-26 Thread Euler Taveira de Oliveira
Hi, Some time ago [1], I proposed print a message every time there isn't autovacuum slots available and it asks for another one. It is not a complete solution for autovacuum tuning but it would at least give us a hint that number of workers is insufficient to keep up with the current load.

Re: [HACKERS] pgsql_fdw, FDW for PostgreSQL server

2011-10-26 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes: On Wed, Oct 26, 2011 at 16:37, Tom Lane t...@sss.pgh.pa.us wrote: If that was what he meant, I'd vote against it.  There are way too many people who will *not* want their databases configured to be able to reach out onto the net.  This feature should

Re: [HACKERS] Range Types - typo + NULL string constructor

2011-10-26 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: I believe that we're in trouble with XIDs as soon as you have two active XIDs that are separated by a billion, because then you could have a situation where some people think a given XID is in the future and others think it's in the past. I have been

[HACKERS] Cannot

2011-10-26 Thread David E. Wheeler
Suggested doc “patch”: grep -lri 'can not' doc | xargs perl -i -pe 's/can not/cannot/g' Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Updated version of pg_receivexlog

2011-10-26 Thread Magnus Hagander
On Tue, Oct 25, 2011 at 12:37, Magnus Hagander mag...@hagander.net wrote: On Mon, Oct 24, 2011 at 14:40, Magnus Hagander mag...@hagander.net wrote: On Mon, Oct 24, 2011 at 13:46, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: +               /* +                * Looks like an

Re: [HACKERS] autovacuum workers warning

2011-10-26 Thread Tom Lane
Euler Taveira de Oliveira eu...@timbira.com writes: + if (!can_launch) + ereport(LOG, + (errmsg(maximum number of autovacuum workers reached), + errhint(Consider increasing

Re: [HACKERS] autovacuum workers warning

2011-10-26 Thread Alvaro Herrera
Excerpts from Euler Taveira de Oliveira's message of mar oct 25 16:56:12 -0300 2011: Hi, Some time ago [1], I proposed print a message every time there isn't autovacuum slots available and it asks for another one. It is not a complete solution for autovacuum tuning but it would at least

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Chris Redekop
FYI I have given this patch a good test and can now no longer reproduce either the subtrans nor the clog error. Thanks guys! On Wed, Oct 26, 2011 at 11:09 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Oct 26, 2011 at 5:16 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Oct 26,

Re: [HACKERS] autovacuum workers warning

2011-10-26 Thread Euler Taveira de Oliveira
On 26-10-2011 16:14, Alvaro Herrera wrote: Well, just increasing the number of workers would do nothing to solve the problem, because the more workers there are, the slower they work. The actual solution to the problem would be decreasing autovacuum_vacuum_delay_cost, and/or related knobs. Why

Re: [HACKERS] autovacuum workers warning

2011-10-26 Thread Alvaro Herrera
Excerpts from Euler Taveira de Oliveira's message of mié oct 26 16:57:18 -0300 2011: On 26-10-2011 16:14, Alvaro Herrera wrote: Well, just increasing the number of workers would do nothing to solve the problem, because the more workers there are, the slower they work. The actual

Re: [HACKERS] autovacuum workers warning

2011-10-26 Thread Dickson S. Guedes
2011/10/26 Euler Taveira de Oliveira eu...@timbira.com: I'm not saying that is not the right direction, I'm arguing that a hint is better than nothing. Right now the only way to know if it is out of workers is to query pg_stat_activity frequently. The currently number of autovaccum workers

Re: [HACKERS] Cannot

2011-10-26 Thread Andrew Dunstan
On 10/26/2011 02:16 PM, David E. Wheeler wrote: Suggested doc “patch”: grep -lri 'can not' doc | xargs perl -i -pe 's/can not/cannot/g' Why? can not is perfectly grammatical AFAIK. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] Cannot

2011-10-26 Thread David E. Wheeler
On Oct 26, 2011, at 2:06 PM, Andrew Dunstan wrote: Suggested doc “patch”: grep -lri 'can not' doc | xargs perl -i -pe 's/can not/cannot/g' Why? can not is perfectly grammatical AFAIK. True, but there's a logic issue. Take this example from doc/src/sgml/func.sgml: para

Re: [HACKERS] Cannot

2011-10-26 Thread Andrew Dunstan
On 10/26/2011 05:15 PM, David E. Wheeler wrote: On Oct 26, 2011, at 2:06 PM, Andrew Dunstan wrote: Suggested doc “patch”: grep -lri 'can not' doc | xargs perl -i -pe 's/can not/cannot/g' Why? can not is perfectly grammatical AFAIK. True, but there's a logic issue. Take this example

Re: [HACKERS] Cannot

2011-10-26 Thread Tom Lane
David E. Wheeler da...@kineticode.com writes: On Oct 26, 2011, at 2:06 PM, Andrew Dunstan wrote: Why? can not is perfectly grammatical AFAIK. True, but there's a logic issue. Take this example from doc/src/sgml/func.sgml: para functionpg_advisory_xact_lock/ works the same as

Re: [HACKERS] Cannot

2011-10-26 Thread David E. Wheeler
On Oct 26, 2011, at 2:58 PM, Tom Lane wrote: I read this as equivalent to can be not released. Which of course is silly, so as I read it I realize what it means, but it trips up my overly logical brain. It interrupts the flow. There is no such confusion in cannot be released and thus no

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Florian Pflug
On Oct26, 2011, at 18:08 , Simon Riggs wrote: On Wed, Oct 26, 2011 at 3:47 PM, Florian Pflug f...@phlo.org wrote: On Oct26, 2011, at 15:57 , Florian Pflug wrote: Thus, if the CLOG is extended after (or in the middle of) CheckPointGuts(), but before LogStandbySnapshot(), then we end up with a

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Robert Haas
On Wed, Oct 26, 2011 at 12:16 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Oct 26, 2011 at 5:08 PM, Simon Riggs si...@2ndquadrant.com wrote: Brewing a patch now. Latest thinking... confirmations or other error reports please. This fixes both the subtrans and clog bugs in one patch.

Re: [HACKERS] Hot Backup with rsync fails at pg_clog if under load

2011-10-26 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Wed, Oct 26, 2011 at 12:16 PM, Simon Riggs si...@2ndquadrant.com wrote: This fixes both the subtrans and clog bugs in one patch. I don't see the point of changing StartupCLOG() to be an empty function and adding a new function TrimCLOG() that does