Re: [HACKERS] Performance Enhancement/Fix for Array Utility Functions
The existence and naming of ARR_MAX_HEADER_SIZE is somewhat dubious, as it is: Thanks you for the feedback. I cleaned up the patch. * Used in exactly one place (not necessarily a reason why it should not be reified into a stand-alone definition, though, but something to consider) Moved it to one definition * The array header refers to the NULL bitmap as well, but the interpretation used by the patch does not. I renamed the macros to have NONULL in the name (hopefully it doesn't make them too long). I also added a comment. Not quite sure if it's the appropriate format, but I didn't feel it warranted 3 lines. Thanks, Mike Lewis detoast-headers-for-array-functions-003.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Add a new backend process
Hello, I want to add a new backend process to postgres, to include my own auditing modules. How can i do that, also how can i signal it after! Sorry if this is very general question! I didn't find any source to learn these things in postgres. thanks in advance
Re: [HACKERS] New PGXN Extension site
David E. Wheeler wrote: On Jun 15, 2010, at 3:23 PM, Robert Haas wrote: I think this project is a great idea, and I think as a community we ought to be behind it 100%. However, I do wonder what happened to the original name, which IIRC was PGAN. That seems easier to pronounce, remember, ... I didn't care for it, personally. Pee-Gan sounds weird to my ear. I prefer pee-gee-ex-en. But you can go for pixin or pigskin if you'd rather. ;-) My bike shed is chartreuse, heh I'm with Robert on that PGXN just sounds and speels weird - PGAN was much easier ;) Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GetOldestWALSendPointer() in header
Hi, GetOldestWALSendPointer() is commented out in the source code with NOT_USED block, but is still declared in the header file. Should we remove the function prototype from walsender.h ? [walsender.h] extern XLogRecPtr GetOldestWALSendPointer(void); Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
On Tue, 2010-06-15 at 16:12 -0400, Bruce Momjian wrote: This was just posted to announce. I notice you mention that this was just posted to the ANNOUNCE list. Who is it that moderates the announce list? The postings made by David Fetter on 13 June and postings by David Wheeler on 15 June were both approved within an hour of posting. Critically important posting by Lacey Powers on Monday, approximately 40 hours early was held in the queue. Also, an item by Koichi Suzuki was held in the queue since 8 June, and also an item by myself was held in the queue since 11 June. Why is there significant delay on important posts, yet some posts go almost straight though? Every time I use Announce my posts are delayed for about 4-5 days. Why do some posts jump the queue, appearing to imply the moderator is being selective in releasing some, yet not others? Do we need some more moderators? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and 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] New PGXN Extension site
On Wed, Jun 16, 2010 at 7:03 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: David E. Wheeler wrote: On Jun 15, 2010, at 3:23 PM, Robert Haas wrote: I think this project is a great idea, and I think as a community we ought to be behind it 100%. However, I do wonder what happened to the original name, which IIRC was PGAN. That seems easier to pronounce, remember, ... I didn't care for it, personally. Pee-Gan sounds weird to my ear. I prefer pee-gee-ex-en. But you can go for pixin or pigskin if you'd rather. ;-) My bike shed is chartreuse, heh I'm with Robert on that PGXN just sounds and speels weird - PGAN was much easier ;) +1 -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add a new backend process
On Jun 16, 2010, at 8:47 , Amir Abdollahi wrote: I want to add a new backend process to postgres, to include my own auditing modules. How can i do that, also how can i signal it after! The existing auxiliary processes (in 8.4) and their entry points are autovacuum (autovacuum.c, AutoVacLauncherMain) bgwriter (bgwriter.c, BackgroundWriterMain) walwriter (walwriter.c, WalWriterMain) startup/recovery (xlog.c, StartupProcessMain) You should probably compare the characteristics of these processes (when are they launched, how do they interface with the database, ...) to your requirements. Pick the best-matching candidate and start by copying what it does. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
On Wed, Jun 16, 2010 at 12:27, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2010-06-15 at 16:12 -0400, Bruce Momjian wrote: This was just posted to announce. I notice you mention that this was just posted to the ANNOUNCE list. Who is it that moderates the announce list? I can't answer this part, just wanted to add a comment below. The postings made by David Fetter on 13 June and postings by David Wheeler on 15 June were both approved within an hour of posting. In the case of Fetter at least, I think he's whitelisted and his posts are never moderated - since he posts the PWN every week. Critically important posting by Lacey Powers on Monday, approximately 40 hours early was held in the queue. Also, an item by Koichi Suzuki was held in the queue since 8 June, and also an item by myself was held in the queue since 11 June. Why is there significant delay on important posts, yet some posts go almost straight though? Every time I use Announce my posts are delayed for about 4-5 days. Why do some posts jump the queue, appearing to imply the moderator is being selective in releasing some, yet not others? Do we need some more moderators? Probably - someone else can hopefully comment on who moderates that one now. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TABLE...ALTER COLUMN vs inheritance
On Wed, Jun 16, 2010 at 5:31 AM, Bernd Helmle maili...@oopsware.de wrote: --On 15. Juni 2010 20:51:21 -0700 Selena Deckelmann selenama...@gmail.com wrote: Confirmed that this tests fine against commit id 0dca7d2f70872a242d4430c4c3aa01ba8dbd4a8c I also wrote a little test script and verified that it throws an error when I try to remove a constraint from the parent table. Thanks for looking at this. Please note that the main purpose of this patch is to protect *child* tables against dropping NOT NULL constraints inherited from a parent table. This could lead to unrestorable dumps formerly. Yes! I didn't say that right earlier -- sorry I should have attached the test. I'll just try to add it and send it to you in patch form. Should an explicit test be added for this? I think so, yes. There are some spelling and grammar errors in comments that I can help fix if you want the help. You're welcome. I have pushed my branch to my git repos as well, if you like to start from there: http://git.postgresql.org/gitweb?p=bernd_pg.git;a=shortlog;h=refs/heads/notnull_constraint Awesome! I'll have a look this afternoon. And, I didn't really know what to say about the rest of the issues you brought up around structuring the code, and the couple TODOs still left in the patch. -selena -- http://chesnok.com/daily - me -- 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] PL/Perl function naming
Tim Bunce wrote: If the feature is not any use should we rip it out? We pretty much included it because you said it was what you needed for the profiler. Yes, it can go. Done. 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] ALTER TABLE...ALTER COLUMN vs inheritance
--On 15. Juni 2010 20:51:21 -0700 Selena Deckelmann selenama...@gmail.com wrote: Confirmed that this tests fine against commit id 0dca7d2f70872a242d4430c4c3aa01ba8dbd4a8c I also wrote a little test script and verified that it throws an error when I try to remove a constraint from the parent table. Thanks for looking at this. Please note that the main purpose of this patch is to protect *child* tables against dropping NOT NULL constraints inherited from a parent table. This could lead to unrestorable dumps formerly. Should an explicit test be added for this? I think so, yes. There are some spelling and grammar errors in comments that I can help fix if you want the help. You're welcome. I have pushed my branch to my git repos as well, if you like to start from there: http://git.postgresql.org/gitweb?p=bernd_pg.git;a=shortlog;h=refs/heads/notnull_constraint -- Thanks Bernd -- 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] GetOldestWALSendPointer() in header
Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp writes: GetOldestWALSendPointer() is commented out in the source code with NOT_USED block, but is still declared in the header file. Should we remove the function prototype from walsender.h ? Yes, that's our usual convention. 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] New PGXN Extension site
On Wed, 2010-06-16 at 09:03 +0200, Stefan Kaltenbrunner wrote: David E. Wheeler wrote: On Jun 15, 2010, at 3:23 PM, Robert Haas wrote: I think this project is a great idea, and I think as a community we ought to be behind it 100%. However, I do wonder what happened to the original name, which IIRC was PGAN. That seems easier to pronounce, remember, ... I didn't care for it, personally. Pee-Gan sounds weird to my ear. I prefer pee-gee-ex-en. But you can go for pixin or pigskin if you'd rather. ;-) My bike shed is chartreuse, heh I'm with Robert on that PGXN just sounds and speels weird - PGAN was much easier ;) I actually like PGXN. PGXN is marketable. Yeah that may not be what -hackers are after but if I stand up in front of a Fortune 500 company and say, We have PGXN it sounds a heck of a lot better that PGAN. Joshua D. Drake Stefan -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] New PGXN Extension site
David E. Wheeler wrote: Honestly, I didn't realize anyone was attached to ?PGAN.? Frankly, I blame whoever named PostgreSQL itself and came up with the short version, ?PG.? Nothing but pigs out of that. I finally understand how pig-squeal is a short-form of PostgreSQL (PG-SQL). :-O Yet another variation. I guess it could be worse. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- 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] New PGXN Extension site
Joshua D. Drake wrote: On Wed, 2010-06-16 at 09:03 +0200, Stefan Kaltenbrunner wrote: David E. Wheeler wrote: On Jun 15, 2010, at 3:23 PM, Robert Haas wrote: I think this project is a great idea, and I think as a community we ought to be behind it 100%. However, I do wonder what happened to the original name, which IIRC was PGAN. That seems easier to pronounce, remember, ... I didn't care for it, personally. Pee-Gan sounds weird to my ear. I prefer pee-gee-ex-en. But you can go for pixin or pigskin if you'd rather. ;-) My bike shed is chartreuse, heh I'm with Robert on that PGXN just sounds and speels weird - PGAN was much easier ;) I actually like PGXN. PGXN is marketable. Yeah that may not be what -hackers are after but if I stand up in front of a Fortune 500 company and say, We have PGXN it sounds a heck of a lot better that PGAN. I think the attraction of PGAN is that people have some hope of guessing what it means (CPAN/PGAN), and because C and G look similar, there is even more an association, e.g. swap C and P, change C to G, and viola. The attraction of PGXN is that it looks like PGXS. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- 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] [v9.1] Add security hook on initialization of instance
KaiGai, * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: On the other hand, a security feature have to identify the client and assign an appropriate set of privileges on the session prior to it being available for users. [...] However, here is no hooks available for the purpose. I believe we understand the issue now, my point was that in the future let's have this discussion first. One idea is, as Robert suggested, that we can invoke getpeercon() at the first call of SELinux module and store it on the local variable. It will work well as long as getpeercon() does not cause an error. Let's work with this approach to build a proof-of-concept that at least the DML hook will work as advertised. We've got alot of time till 9.1 and I think that if we can show that a module exists that implements SELinux using the DML hook, and that a few other hooks are needed to address short-comings in that module, adding them won't be a huge issue. Thanks, Stephen signature.asc Description: Digital signature
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
Why is there significant delay on important posts, yet some posts go almost straight though? Every time I use Announce my posts are delayed for about 4-5 days. Why do some posts jump the queue, appearing to imply the moderator is being selective in releasing some, yet not others? Do we need some more moderators? Yes. Currently the only moderators for -announce are Marc and Greg S-M. This means that you can get your announce through quickly if you follow up a posting to that list with a private e-mail to one of them; otherwise, stuff tends to lag for several days. Or there are a couple of pass-throughs, for release announcements and PWN, which are not moderated. I've asked several times that we add additional moderators for -announce. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New PGXN Extension site
On Wed, 2010-06-16 at 13:22 -0400, Bruce Momjian wrote: I actually like PGXN. PGXN is marketable. Yeah that may not be what -hackers are after but if I stand up in front of a Fortune 500 company and say, We have PGXN it sounds a heck of a lot better that PGAN. I think the attraction of PGAN is that people have some hope of guessing what it means (CPAN/PGAN), and because C and G look similar, there is even more an association, e.g. swap C and P, change C to G, and viola. The attraction of PGXN is that it looks like PGXS. Again, to hackers :). I am looking at this differently. If I stand up and say, PostgreSQL has PGXN, the PostgreSQL Extension Network Versus PostgreSQL has PGAN, P can that Can, I am Pee gannning. What? Anyway, a name is a name. We are PostgreSQL after all. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] Patch to show individual statement latencies in pgbench output
On Jun 14, 2010, at 2:22 , Greg Smith wrote: Florian Pflug wrote: To be able to asses the performance characteristics of the different wal-related options, I patched pgbench to show the average latency of each individual statement. The idea is to be able to compare the latency of the COMMIT with the ones of the other statements. That's an interesting idea, particularly given that people don't really understand where the time is going in the standard pgbench test. Your first bit of review feedback is that this would have to be something you could toggle on and off, there's no way most people want to pay this penalty. If you submit a new patch with a command line option to enable this alternate logging format and add the result to https://commitfest.postgresql.org/action/commitfest_view?id=6 , you can put my name down as a reviewer and I'll take a deeper look at it as part of that. Thank for offering to review this! Here is an updated patch that adds a command-line option (-r) to enable/disable per-command latency reporting. It also uses the INSTR_TIME infrastructure that 9.0 provides, and should work correctly in multi-threaded mode. Data is collected per-thread and summarized only for reporting to avoid locking overhead. It now shows all lines for the SQL scripts together with their latencies (zero for comments), not only those containing SQL commands. I'll add this patch to the next commitfest, and put you down as a reviewer, as you suggested. best regards, Florian Pflug pgbench_statementlatency.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 9.0 beta2 pg_upgrade: malloc 0 bytes patch
Running pg_upgrade against an unmodified (the output of initdb) cluster on AIX is giving me pg_alloc: Out of memory errors. On some non-linux platforms (including AIX) malloc(0) returns 0. with the attached patch to pg_upgrade I am now able to get pg_upgrade to convert an 8.3 database consisting of a single table to 9.0 on an AIX server. -- Steve Singer Afilias Canada Data Services Developer 416-673-1142 diff --git a/contrib/pg_upgrade/tablespace.c b/contrib/pg_upgrade/tablespace.c index 302eb0d..99a97e4 100644 --- a/contrib/pg_upgrade/tablespace.c +++ b/contrib/pg_upgrade/tablespace.c @@ -49,7 +49,10 @@ get_tablespace_paths(migratorContext *ctx) spcname != 'pg_global'); ctx-num_tablespaces = ntups = PQntuples(res); - ctx-tablespaces = (char **) pg_malloc(ctx, ntups * sizeof(char *)); + if( ntups 0 ) + ctx-tablespaces = (char **) pg_malloc(ctx, ntups * sizeof(char *)); + else + ctx-tablespaces=0; i_spclocation = PQfnumber(res, spclocation); -- 1.6.3.3 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
On Wed, 2010-06-16 at 13:05 +0200, Magnus Hagander wrote: On Wed, Jun 16, 2010 at 12:27, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2010-06-15 at 16:12 -0400, Bruce Momjian wrote: This was just posted to announce. I notice you mention that this was just posted to the ANNOUNCE list. Who is it that moderates the announce list? I can't answer this part, just wanted to add a comment below. Well I am one. I can tell you that one of the problems I run into is that if I don't see it during my normal business day, I won't moderate it. So if you send during your timezone, and I am the only one watching... If it comes through during my business day, I moderate immediately (assuming I am reading email) Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] New PGXN Extension site
2010/6/16 David E. Wheeler david.whee...@pgexperts.com: On Jun 15, 2010, at 3:23 PM, Robert Haas wrote: I think this project is a great idea, and I think as a community we ought to be behind it 100%. However, I do wonder what happened to the original name, which IIRC was PGAN. That seems easier to pronounce, remember, ... I didn't care for it, personally. Pee-Gan sounds weird to my ear. I prefer pee-gee-ex-en. But you can go for pixin or pigskin if you'd rather. ;-) PGAN is very sweet in French, where PGXN is an horror My bike shed is chartreuse, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0 beta2 pg_upgrade: malloc 0 bytes patch
Steve Singer wrote: Running pg_upgrade against an unmodified (the output of initdb) cluster on AIX is giving me pg_alloc: Out of memory errors. On some non-linux platforms (including AIX) malloc(0) returns 0. with the attached patch to pg_upgrade I am now able to get pg_upgrade to convert an 8.3 database consisting of a single table to 9.0 on an AIX server. Great, thanks for your testing. I have applied a modified version of the patch, attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: contrib/pg_upgrade/tablespace.c === RCS file: /cvsroot/pgsql/contrib/pg_upgrade/tablespace.c,v retrieving revision 1.1 diff -c -c -r1.1 tablespace.c *** contrib/pg_upgrade/tablespace.c 12 May 2010 02:19:11 - 1.1 --- contrib/pg_upgrade/tablespace.c 16 Jun 2010 19:37:23 - *** *** 38,44 { PGconn *conn = connectToServer(ctx, template1, CLUSTER_OLD); PGresult *res; - int ntups; int tblnum; int i_spclocation; --- 38,43 *** *** 48,59 WHERE spcname != 'pg_default' AND spcname != 'pg_global'); ! ctx-num_tablespaces = ntups = PQntuples(res); ! ctx-tablespaces = (char **) pg_malloc(ctx, ntups * sizeof(char *)); i_spclocation = PQfnumber(res, spclocation); ! for (tblnum = 0; tblnum ntups; tblnum++) ctx-tablespaces[tblnum] = pg_strdup(ctx, PQgetvalue(res, tblnum, i_spclocation)); --- 47,61 WHERE spcname != 'pg_default' AND spcname != 'pg_global'); ! if ((ctx-num_tablespaces = PQntuples(res)) != 0) ! ctx-tablespaces = (char **) pg_malloc(ctx, ! ctx-num_tablespaces * sizeof(char *)); ! else ! ctx-tablespaces = NULL; i_spclocation = PQfnumber(res, spclocation); ! for (tblnum = 0; tblnum ctx-num_tablespaces; tblnum++) ctx-tablespaces[tblnum] = pg_strdup(ctx, PQgetvalue(res, tblnum, i_spclocation)); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] streaming replication breaks horribly if master crashes
On Mon, Jun 14, 2010 at 7:55 AM, Simon Riggs si...@2ndquadrant.com wrote: But that change would cause the problem that Robert pointed out. http://archives.postgresql.org/pgsql-hackers/2010-06/msg00670.php Presumably this means that if synchronous_commit = off on primary that SR in 9.0 will no longer work correctly if the primary crashes? I spent some time investigating this today and have come to the conclusion that streaming replication is really, really broken in the face of potential crashes on the master. Using a copy of VMware parallels provided by $EMPLOYER, I set up two Fedora 12 virtual machines on my MacBook in a master/slave configuration. Then I crashed the master repeatedly using 'echo b /proc/sysrq-trigger', which causes an immediate reboot (without syncing the disks, closing network connections, etc.) while running pgbench or other stuff against it. The first problem I noticed is that the slave never seems to realize that the master has gone away. Every time I crashed the master, I had to kill the wal receiver process on the slave to get it to reconnect; otherwise it just sat there waiting, either forever or at least for longer than I was willing to wait. More seriously, I was able to demonstrate that the problem linked in the thread above is real: if the master crashes after streaming WAL that it hasn't yet fsync'd, then on recovery the slave's xlog position is ahead of the master. So far I've only been able to reproduce this with fsync=off, but I believe it's possible anyway, and this just makes it more likely. After the most recent crash, the master thought pg_current_xlog_location() was 1/86CD4000; the slave thought pg_last_xlog_receive_location() was 1/8733C000. After reconnecting to the master, the slave then thought that pg_last_xlog_receive_location() was 1/8700. The slave didn't think this was a problem yet, though. When I then restarted a pgbench run against the master, the slave pretty quickly started spewing an endless stream of messages complaining of LOG: invalid record length at 1/8733A828. So, obviously at this point my slave database is corrupted beyond repair due to nothing more than an unexpected crash on the master. That's bad. What is worse is that the system only detected the corruption because the slave had crossed an xlog segment boundary which the master had not crossed. Had it been otherwise, when the slave rewound to the beginning of the current segment, it would have had no trouble getting back in sync with the master - but it would have done this after having replayed WAL that, from the master's point of view, doesn't exist. In other words, the database on the slave would be silently corrupted. I don't know what to do about this, but I'm pretty sure we can't ship it as-is. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] streaming replication breaks horribly if master crashes
On Wed, 2010-06-16 at 15:47 -0400, Robert Haas wrote: So, obviously at this point my slave database is corrupted beyond repair due to nothing more than an unexpected crash on the master. That's bad. What is worse is that the system only detected the corruption because the slave had crossed an xlog segment boundary which the master had not crossed. Had it been otherwise, when the slave rewound to the beginning of the current segment, it would have had no trouble getting back in sync with the master - but it would have done this after having replayed WAL that, from the master's point of view, doesn't exist. In other words, the database on the slave would be silently corrupted. I don't know what to do about this, but I'm pretty sure we can't ship it as-is. The slave must be able to survive a master crash. Joshua D. Drake -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] streaming replication breaks horribly if master crashes
Robert Haas robertmh...@gmail.com wrote: So, obviously at this point my slave database is corrupted beyond repair due to nothing more than an unexpected crash on the master. Certainly that's true for resuming replication. From your description it sounds as though the slave would be usable for purposes of taking over for an unrecoverable master. Or am I misunderstanding? had no trouble getting back in sync with the master - but it would have done this after having replayed WAL that, from the master's point of view, doesn't exist. In other words, the database on the slave would be silently corrupted. I don't know what to do about this, but I'm pretty sure we can't ship it as-is. I'm sure we can't. -Kevin -- 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] streaming replication breaks horribly if master crashes
On 06/16/2010 09:47 PM, Robert Haas wrote: On Mon, Jun 14, 2010 at 7:55 AM, Simon Riggssi...@2ndquadrant.com wrote: But that change would cause the problem that Robert pointed out. http://archives.postgresql.org/pgsql-hackers/2010-06/msg00670.php Presumably this means that if synchronous_commit = off on primary that SR in 9.0 will no longer work correctly if the primary crashes? I spent some time investigating this today and have come to the conclusion that streaming replication is really, really broken in the face of potential crashes on the master. Using a copy of VMware parallels provided by $EMPLOYER, I set up two Fedora 12 virtual machines on my MacBook in a master/slave configuration. Then I crashed the master repeatedly using 'echo b /proc/sysrq-trigger', which causes an immediate reboot (without syncing the disks, closing network connections, etc.) while running pgbench or other stuff against it. The first problem I noticed is that the slave never seems to realize that the master has gone away. Every time I crashed the master, I had to kill the wal receiver process on the slave to get it to reconnect; otherwise it just sat there waiting, either forever or at least for longer than I was willing to wait. well this is likely caused by the OS not noticing that the connections went away (linux has really long timeouts here) - maybe we should unconditionally enable keepalive on systems that support that for replication connections (if that is possible in the current design anyway) More seriously, I was able to demonstrate that the problem linked in the thread above is real: if the master crashes after streaming WAL that it hasn't yet fsync'd, then on recovery the slave's xlog position is ahead of the master. So far I've only been able to reproduce this with fsync=off, but I believe it's possible anyway, and this just makes it more likely. After the most recent crash, the master thought pg_current_xlog_location() was 1/86CD4000; the slave thought pg_last_xlog_receive_location() was 1/8733C000. After reconnecting to the master, the slave then thought that pg_last_xlog_receive_location() was 1/8700. The slave didn't think this was a problem yet, though. When I then restarted a pgbench run against the master, the slave pretty quickly started spewing an endless stream of messages complaining of LOG: invalid record length at 1/8733A828. this is obviously bad but with fsync=off(or sync_commit=off?) it is probably impossible to prevent... Stefan -- 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] streaming replication breaks horribly if master crashes
Robert Haas robertmh...@gmail.com wrote: I don't know what to do about this This probably is out of the question for 9.0 based on scale of change, and maybe forever based on the impact of WAL volume, but -- if we logged before images along with the after, we could undo the work of the over-eager transactions on the slave upon reconnect. -Kevin -- 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] streaming replication breaks horribly if master crashes
Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: well this is likely caused by the OS not noticing that the connections went away (linux has really long timeouts here) - maybe we should unconditionally enable keepalive on systems that support that for replication connections (if that is possible in the current design anyway) Yeah, in similar situations I've had good results with a keepalive timeout of a minute or two. -Kevin -- 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] streaming replication breaks horribly if master crashes
The first problem I noticed is that the slave never seems to realize that the master has gone away. Every time I crashed the master, I had to kill the wal receiver process on the slave to get it to reconnect; otherwise it just sat there waiting, either forever or at least for longer than I was willing to wait. Yes, I've noticed this. That was the reason for forcing walreceiver to shut down on a restart per prior discussion and patches. This needs to be on the open items list ... possibly it'll be fixed by Simon's keepalive patch? Or is it just a tcp_keeplalive issue? More seriously, I was able to demonstrate that the problem linked in the thread above is real: if the master crashes after streaming WAL that it hasn't yet fsync'd, then on recovery the slave's xlog position is ahead of the master. So far I've only been able to reproduce this with fsync=off, but I believe it's possible anyway, ... and some users will turn fsync off. This is, in fact, one of the primary uses for streaming replication: Durability via replicas. and this just makes it more likely. After the most recent crash, the master thought pg_current_xlog_location() was 1/86CD4000; the slave thought pg_last_xlog_receive_location() was 1/8733C000. After reconnecting to the master, the slave then thought that pg_last_xlog_receive_location() was 1/8700. So, *in this case*, detecting out-of-sequence xlogs (and PANICing) would have actually prevented the slave from being corrupted. My question, though, is detecting out-of-sequence xlogs *enough*? Are there any crash conditions on the master which would cause the master to reuse the same locations for different records, for example? I don't think so, but I'd like to be certain. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] streaming replication breaks horribly if master crashes
On Wed, Jun 16, 2010 at 4:00 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: So, obviously at this point my slave database is corrupted beyond repair due to nothing more than an unexpected crash on the master. Certainly that's true for resuming replication. From your description it sounds as though the slave would be usable for purposes of taking over for an unrecoverable master. Or am I misunderstanding? It depends on what you mean. If you can prevent the slave from ever reconnecting to the master, then it's still safe to promote it. But if the master comes up and starts generating WAL again, and the slave ever sees any of that WAL (either via SR or via the archive) then you're toast. In my case, the slave was irrecoverably out of sync with the master as soon as the crash happened, but it still could have been promoted at that point if you killed the old master. It became corrupted as soon as it replayed the first WAL record starting beyond 1/8700. At that point it's potentially got arbitrary corruption; you need a new base backup (but this may not be immediately obvious; it may look OK even if it isn't). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] streaming replication breaks horribly if master crashes
On Wed, Jun 16, 2010 at 4:14 PM, Josh Berkus j...@agliodbs.com wrote: The first problem I noticed is that the slave never seems to realize that the master has gone away. Every time I crashed the master, I had to kill the wal receiver process on the slave to get it to reconnect; otherwise it just sat there waiting, either forever or at least for longer than I was willing to wait. Yes, I've noticed this. That was the reason for forcing walreceiver to shut down on a restart per prior discussion and patches. This needs to be on the open items list ... possibly it'll be fixed by Simon's keepalive patch? Or is it just a tcp_keeplalive issue? I think a TCP keepalive might be enough, but I have not tried to code or test it. More seriously, I was able to demonstrate that the problem linked in the thread above is real: if the master crashes after streaming WAL that it hasn't yet fsync'd, then on recovery the slave's xlog position is ahead of the master. So far I've only been able to reproduce this with fsync=off, but I believe it's possible anyway, ... and some users will turn fsync off. This is, in fact, one of the primary uses for streaming replication: Durability via replicas. Yep. and this just makes it more likely. After the most recent crash, the master thought pg_current_xlog_location() was 1/86CD4000; the slave thought pg_last_xlog_receive_location() was 1/8733C000. After reconnecting to the master, the slave then thought that pg_last_xlog_receive_location() was 1/8700. So, *in this case*, detecting out-of-sequence xlogs (and PANICing) would have actually prevented the slave from being corrupted. My question, though, is detecting out-of-sequence xlogs *enough*? Are there any crash conditions on the master which would cause the master to reuse the same locations for different records, for example? I don't think so, but I'd like to be certain. The real problem here is that we're sending records to the slave which might cease to exist on the master if it unexpectedly reboots. I believe that what we need to do is make sure that the master only sends WAL it has already fsync'd (Tom suggested on another thread that this might be necessary, and I think it's now clear that it is 100% necessary). But I'm not sure how this will play with fsync=off - if we never fsync, then we can't ever really send any WAL without risking this failure mode. Similarly with synchronous_commit=off, I believe that the next checkpoint will still fsync WAL, but the lag might be long. I think we should also change the slave to panic and shut down immediately if its xlog position is ahead of the master. That can never be a watertight solution because you can always advance the xlog position on them master and mask the problem. But I think we should do it anyway, so that we at least have a chance of noticing that we're hosed. I wish I could think of something a little more watertight... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] streaming replication breaks horribly if master crashes
Robert Haas robertmh...@gmail.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: So, obviously at this point my slave database is corrupted beyond repair due to nothing more than an unexpected crash on the master. Certainly that's true for resuming replication. From your description it sounds as though the slave would be usable for purposes of taking over for an unrecoverable master. Or am I misunderstanding? It depends on what you mean. If you can prevent the slave from ever reconnecting to the master, then it's still safe to promote it. Yeah, that's what I meant. But if the master comes up and starts generating WAL again, and the slave ever sees any of that WAL (either via SR or via the archive) then you're toast. Well, if it *applies* what it sees, yes. Effectively you've got transactions from two alternative timelines applied in the same database, which is not going to work. At a minimum we need some way to reliably detect that the incoming WAL stream is starting before some applied WAL record and isn't a match. -Kevin -- 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] streaming replication breaks horribly if master crashes
On Wed, Jun 16, 2010 at 22:26, Robert Haas robertmh...@gmail.com wrote: and this just makes it more likely. After the most recent crash, the master thought pg_current_xlog_location() was 1/86CD4000; the slave thought pg_last_xlog_receive_location() was 1/8733C000. After reconnecting to the master, the slave then thought that pg_last_xlog_receive_location() was 1/8700. So, *in this case*, detecting out-of-sequence xlogs (and PANICing) would have actually prevented the slave from being corrupted. My question, though, is detecting out-of-sequence xlogs *enough*? Are there any crash conditions on the master which would cause the master to reuse the same locations for different records, for example? I don't think so, but I'd like to be certain. The real problem here is that we're sending records to the slave which might cease to exist on the master if it unexpectedly reboots. I believe that what we need to do is make sure that the master only sends WAL it has already fsync'd (Tom suggested on another thread that this might be necessary, and I think it's now clear that it is 100% necessary). But I'm not sure how this will play with fsync=off - if we never fsync, then we can't ever really send any WAL without risking Well, at this point we can just prevent streaming replication with fsync=off if we can't think of an easy fix, and then design a proper fix for 9.1. Given how late we are in the cycle. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] streaming replication breaks horribly if master crashes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Robert Haas wrote: The first problem I noticed is that the slave never seems to realize that the master has gone away. Every time I crashed the master, I had to kill the wal receiver process on the slave to get it to reconnect; otherwise it just sat there waiting, either forever or at least for longer than I was willing to wait. Hei Robert I have seen two different behaviors in my tests. a) If I crash the server , the wal receiver process will wait forever and the only way to get it working again is to restart postgres in the slave after the master is back online. I have not been able to get the slave database corrupted (I am running with fsync=on). b) If I kill all postgres processes in the master with kill -9, the wal receiver will start trying to reconnect automatically and it will success in the moment postgres gets startet in the master. The only different I can see at the OS level is that in a) the connection continues to have the status ESTABLISHED forever, and in b) it gets status TIME_WAIT in the moment postgres is down in the master. regards, - -- Rafael Martinez, r.m.guerr...@usit.uio.no Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkwZNiMACgkQBhuKQurGihQ3CQCaAhKcLkur6MO0/F7RqD6OWbv2 R/IAnjj4SrgiwkD6qKodJxrFHCODAEuh =qHlh -END PGP SIGNATURE- -- 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] streaming replication breaks horribly if master crashes
Robert Haas robertmh...@gmail.com writes: The first problem I noticed is that the slave never seems to realize that the master has gone away. Every time I crashed the master, I had to kill the wal receiver process on the slave to get it to reconnect; otherwise it just sat there waiting, either forever or at least for longer than I was willing to wait. TCP timeout is the answer there. More seriously, I was able to demonstrate that the problem linked in the thread above is real: if the master crashes after streaming WAL that it hasn't yet fsync'd, then on recovery the slave's xlog position is ahead of the master. So indeed we'd better change walsender to not get ahead of the fsync'd position. And probably also warn people to not disable fsync on the master, unless they're willing to write it off and fail over at any system crash. I don't know what to do about this, but I'm pretty sure we can't ship it as-is. Doesn't seem tremendously insoluble from here ... 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] Add new backend process
Hello, I want to add a new backend process to postgres, to include my own auditing modules. How can i do that, also how can i signal it after! Sorry if this is very general question! I didn't find any source to learn these things in postgres. thanks in advance
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
On Wed, 2010-06-16 at 10:34 -0700, Josh Berkus wrote: Why is there significant delay on important posts, yet some posts go almost straight though? Every time I use Announce my posts are delayed for about 4-5 days. Why do some posts jump the queue, appearing to imply the moderator is being selective in releasing some, yet not others? Do we need some more moderators? Yes. Currently the only moderators for -announce are Marc and Greg S-M. And me, and devrim and a number of others. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
And me, and devrim and a number of others. Hmmm. Yet nothing seems to get approved unless I personal e-mail Marc. Why? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] streaming replication breaks horribly if master crashes
On 6/16/10 1:26 PM, Robert Haas wrote: Similarly with synchronous_commit=off, I believe that the next checkpoint will still fsync WAL, but the lag might be long. That's not a showstopper. Just tell people that having synch_commit=off on the master might increase the lag to the slave, and leave it alone. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ANNOUNCE list
On Wed, 2010-06-16 at 10:34 -0700, Josh Berkus wrote: Why is there significant delay on important posts, yet some posts go almost straight though? Every time I use Announce my posts are delayed for about 4-5 days. Why do some posts jump the queue, appearing to imply the moderator is being selective in releasing some, yet not others? Do we need some more moderators? Yes. Currently the only moderators for -announce are Marc and Greg S-M. And me, and devrim and a number of others. I think adding new moderators who are regualy reading emails and living in different time zones is an idea. If nobody in +0900 tinme zone(Japan), I'd like to be an additional moderator. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
I don't moderate -announce. -- Devrim GÜNDÜZ PostgreSQL DBA @ Akinon/Markafoni, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz 17.Haz.2010 tarihinde 00:58 saatinde, Joshua D. Drake j...@commandprompt.com şunları yazdı: On Wed, 2010-06-16 at 10:34 -0700, Josh Berkus wrote: Why is there significant delay on important posts, yet some posts go almost straight though? Every time I use Announce my posts are delayed for about 4-5 days. Why do some posts jump the queue, appearing to imply the moderator is being selective in releasing some, yet not others? Do we need some more moderators? Yes. Currently the only moderators for -announce are Marc and Greg S-M. And me, and devrim and a number of others. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] streaming replication breaks horribly if master crashes
The real problem here is that we're sending records to the slave which might cease to exist on the master if it unexpectedly reboots. I believe that what we need to do is make sure that the master only sends WAL it has already fsync'd How about this : - pg records somewhere the xlog position of the last record synced to disk. I dont remember the variable name, let's just say xlog_synced_recptr - pg always writes the xlog first, ie. before writing any page it checks that the page's xlog recptr xlog_synced_recptr and if it's not the case it has to wait before it can write the page. Now : - master sends messages to slave with the xlog_synced_recptr after each fsync - slave gets these messages and records the master_xlog_synced_recptr - slave doesn't write any page to disk until BOTH the slave's local WAL copy AND the master's WAL have reached the recptr of this page If a master crashes or the slave loses connection, then the in-memory pages of the slave could be in a state that is in the future compared to the master's state when it comes up. Therefore when a slave detects that the master has crashed, it could shoot itself and recover from WAL, at which point the slave will not be in the future anymore from the master, rather it would be in the past, which is a lot less problematic... Of course this wouldn't speed up the failover process !... I think we should also change the slave to panic and shut down immediately if its xlog position is ahead of the master. That can never be a watertight solution because you can always advance the xlog position on them master and mask the problem. But I think we should do it anyway, so that we at least have a chance of noticing that we're hosed. I wish I could think of something a little more watertight... If a slave is in the future relative to the master, then the only way to keep using this slave could be to make it the new master... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
On Wed, 2010-06-16 at 15:01 -0700, Josh Berkus wrote: And me, and devrim and a number of others. Hmmm. Yet nothing seems to get approved unless I personal e-mail Marc. Why? I approved stuff today and yesterday. I didn't the week before because I was in Chicago. I also normally don't moderate on the weekends. I was the one that approved the PGXN email for example. Joshua D. Drake -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
On Thu, 2010-06-17 at 01:17 +0300, Devrim GUNDUZ wrote: I don't moderate -announce. Sorry. I thought you did. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] streaming replication breaks horribly if master crashes
On Wed, Jun 16, 2010 at 9:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: The first problem I noticed is that the slave never seems to realize that the master has gone away. Every time I crashed the master, I had to kill the wal receiver process on the slave to get it to reconnect; otherwise it just sat there waiting, either forever or at least for longer than I was willing to wait. TCP timeout is the answer there. If you mean TCP Keepalives, I disagree quite strongly. If you want the application to guarantee any particular timing constraints then you have to implement that in the application using timers and data packets. TCP keepalives are for detecting broken network connections, not enforcing application rules. Using TCP timeouts would have a number of problems: On many systems they are impossible or difficult to adjust and worse, it would make it impossible to distinguish an postgres master crash from a transient or permanent network outage. More seriously, I was able to demonstrate that the problem linked in the thread above is real: if the master crashes after streaming WAL that it hasn't yet fsync'd, then on recovery the slave's xlog position is ahead of the master. So indeed we'd better change walsender to not get ahead of the fsync'd position. And probably also warn people to not disable fsync on the master, unless they're willing to write it off and fail over at any system crash. I don't know what to do about this, but I'm pretty sure we can't ship it as-is. Doesn't seem tremendously insoluble from here ... For the case of fsync=off I can't get terribly excited about the slave being ahead of the master after a crash. After all the master is toast anyways. It seems to me in this situation the slave should detect that the master has failed and automatically come up in master mode. Or perhaps it should just shut down and then refuse to come up as a slave again on the basis that it would be unsafe precisely because it might be ahead of the (corrupt) master. At some point we should consider having a server set to fsync=off refuse to come back up unless it was shut down cleanly anyways. Perhaps we should put a strongly worded warning now. For the case of fsync=on it does seem to me to be terribly obvious that the master should never send records to the slave that aren't fsynced on the master. For 9.1 the other option proposed would work as well but would be more complex -- to send and store records immediately but not replay them on the slave until they're either fsynced on the master or failover occurs. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
Hmmm. Yet nothing seems to get approved unless I personal e-mail Marc. Why? I approved stuff today and yesterday. I didn't the week before because I was in Chicago. I also normally don't moderate on the weekends. I was the one that approved the PGXN email for example. This week isn't special. Stuff going to -announce frequently gets held for days. I know, people bug me about getting it approved. So there's clearly an issue either with the number of moderators, their time zones, or coordination. I'm thinking I need to be on the moderator list just because I'm one of the people who get personal e-mail when something is delayed. However, that *won't* solve the delay issue; I already moderate 3 other lists and won't be very attentive. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ANNOUNCE list (was Re: [HACKERS] New PGXN Extension site)
On Wed, 2010-06-16 at 16:08 -0700, Josh Berkus wrote: Hmmm. Yet nothing seems to get approved unless I personal e-mail Marc. Why? I approved stuff today and yesterday. I didn't the week before because I was in Chicago. I also normally don't moderate on the weekends. I was the one that approved the PGXN email for example. This week isn't special. Stuff going to -announce frequently gets held for days. I know, people bug me about getting it approved. So there's clearly an issue either with the number of moderators, their time zones, or coordination. I am not arguing delays here. :) I am just saying that I regularly moderate but there are rules around my moderation just like anyone else. I like Tatuso's idea. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] streaming replication breaks horribly if master crashes
Greg Stark gsst...@mit.edu wrote: TCP keepalives are for detecting broken network connections Yeah. That seems like what we have here. If you shoot the OS in the head, the network connection is broken rather abruptly, without the normal packets exchanged to close the TCP connection. It sounds like it behaves just fine except for not detecting a broken connection. -Kevin -- 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] streaming replication breaks horribly if master crashes
Kevin Grittner kevin.gritt...@wicourts.gov wrote: It sounds like it behaves just fine except for not detecting a broken connection. Of course I meant in terms of the slave's attempts at retrieving more WAL, not in terms of it applying a second time line. TCP keepalive timeouts don't help with that part of it, just the failure to recognize the broken connection. I suppose someone could argue that's a *feature*, since it gives you two hours to manually intervene before it does something stupid, but that hardly seems like a solution -Kevin -- 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] hstore == and deprecate =
On Tue, Jun 15, 2010 at 10:55 PM, David E. Wheeler da...@kineticode.com wrote: On Jun 15, 2010, at 6:58 PM, Robert Haas wrote: Well, the idea is it's like logical-and - give me only those keys that appear on both sides... Yeah, but = doesn't return the keys, - does. = returns an hstore. If there is a critical mass of votes for one of these options, I'm fine with whatever. Put me down for +. Since there are no other votes for that option (or, indeed, any other option), I'm going to go with my original instinct and change hstore = text[] to hstore text[]. Patch to do that is attached. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company hstore_arrow_text_to_logical_and.patch Description: Binary 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] streaming replication breaks horribly if master crashes
On Thu, Jun 17, 2010 at 12:22 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: It sounds like it behaves just fine except for not detecting a broken connection. Of course I meant in terms of the slave's attempts at retrieving more WAL, not in terms of it applying a second time line. TCP keepalive timeouts don't help with that part of it, just the failure to recognize the broken connection. I suppose someone could argue that's a *feature*, since it gives you two hours to manually intervene before it does something stupid, but that hardly seems like a solution It's certainly a design goal of TCP that you should be able to disconnect the network and reconnect it everything should recover. If no data was sent it should be able to withstand arbitrarily long disconnections. TCP Keepalives break that but they should only break it in the case where the network connection has definitely exceeded the retry timeouts, not when it merely hasn't responded fast enough for the application requirements. -- greg -- 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] [v9.1] Add security hook on initialization of instance
(2010/06/16 21:37), Stephen Frost wrote: KaiGai, * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: On the other hand, a security feature have to identify the client and assign an appropriate set of privileges on the session prior to it being available for users. [...] However, here is no hooks available for the purpose. I believe we understand the issue now, my point was that in the future let's have this discussion first. One idea is, as Robert suggested, that we can invoke getpeercon() at the first call of SELinux module and store it on the local variable. It will work well as long as getpeercon() does not cause an error. Let's work with this approach to build a proof-of-concept that at least the DML hook will work as advertised. We've got alot of time till 9.1 and I think that if we can show that a module exists that implements SELinux using the DML hook, and that a few other hooks are needed to address short-comings in that module, adding them won't be a huge issue. OK, fair enough. Please wait for a few days. I'll introduce the proof-of-concept module until this week. Thanks, -- KaiGai Kohei kai...@ak.jp.nec.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] hstore == and deprecate =
On Jun 16, 2010, at 4:24 PM, Robert Haas wrote: Put me down for +. Since there are no other votes for that option (or, indeed, any other option), I'm going to go with my original instinct and change hstore = text[] to hstore text[]. Patch to do that is attached. Damn. My other argument is that looks like boolean or bitwise AND, so the return of an hstore might be unexpected. + looks more like an arrow (sort of). But it doesn't much matter, as long as it works. 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] streaming replication breaks horribly if master crashes
On Thu, Jun 17, 2010 at 12:16 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Greg Stark gsst...@mit.edu wrote: TCP keepalives are for detecting broken network connections Yeah. That seems like what we have here. If you shoot the OS in the head, the network connection is broken rather abruptly, without the normal packets exchanged to close the TCP connection. It sounds like it behaves just fine except for not detecting a broken connection. So I think there are two things happening here. If you shut down the master and don't replace it then you'll get no network errors until TCP gives up entirely. Similarly if you pull the network cable or your switch powers off or your routing table becomes messed up, or anything else occurs which prevents packets from getting through then you'll see similar breakage. You wouldn't want your database to suddenly come up as master in such circumstances though when you'll have to fix the problem anyways, doing so won't solve any problems it would just create a second problem. But there's a second case. The Postgres master just stops responding -- perhaps it starts seeing disk errors and becomes stuck in disk-wait or the machine just becomes very heaviliy loaded and Postgres can't get any cycles, or someone attaches to it with gdb, or one of any number of things happen which cause it to stop sending data. In that case replication will not see any data from the master but TCP will never time out because the network is just fine. That's why there needs to be an application level health check if you want to have timeouts. You can't depend on the network layer to detect problems between the application. -- greg -- 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] hstore == and deprecate =
Robert Haas robertmh...@gmail.com writes: Since there are no other votes for that option (or, indeed, any other option), I'm going to go with my original instinct and change hstore = text[] to hstore text[]. Patch to do that is attached. Um ... wait a minute. What happened to backwards compatibility? I thought the idea was to deprecate = for a release or so, not kill it on the spot. 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] hstore == and deprecate =
On Jun 16, 2010, at 4:53 PM, Tom Lane wrote: Um ... wait a minute. What happened to backwards compatibility? I thought the idea was to deprecate = for a release or so, not kill it on the spot. hstore = text[] is new in 9.0. 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] hstore == and deprecate =
David E. Wheeler da...@kineticode.com writes: On Jun 16, 2010, at 4:53 PM, Tom Lane wrote: Um ... wait a minute. What happened to backwards compatibility? I thought the idea was to deprecate = for a release or so, not kill it on the spot. hstore = text[] is new in 9.0. Wup, sorry, I read this as being the other operator. Nevermind ... (FWIW, I share your dislike of for this operator. I just haven't got a better idea.) 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] [PATCH] elimination of code duplication in DefineOpFamily()
I have not been able to find any comments or discussion on this patch. Contents and Purpose: This patch removes duplicate code in opclasscmds.c. It removes the duplicate code from DefineOpFamily by calling CreateOpFamily. No new regression test or documentation are included with the patch. Since it is not adding, removing, or changing any functionality, they are most likely not necessary. The function call parameters remain the same. Initial Run The patch applied cleanly to HEAD. The regression tests pass both before and after the patch. Performance = No changes in performance were observed. I do not have an appropriate setup to properly test for performance impacts. Since this changes a define function, I would not expect it to be frequently used. Conclusion The patch eliminates duplicate code as expected. Brent Dombrowski -- 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] InvalidXLogRecPtr in docs
On Tue, Jun 15, 2010 at 4:22 AM, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Jun 15, 2010 at 2:41 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 15/06/10 08:23, Fujii Masao wrote: On Thu, Jun 10, 2010 at 11:06 PM, Tom Lanet...@sss.pgh.pa.us wrote: I'm not sure if it's worth the trouble, or even a particularly smart idea, to force the output of the status function to be monotonic regardless of what happens underneath. I think removing that claim from the docs altogether is the easiest answer. We should (1) just remove While streaming replication is in progress this will increase monotonically. from the description about pg_last_xlog_receive_location()? or (2) add But if streaming replication is restarted this will back off to the beginning of current WAL file into there? I'm for (2) since it's more informative. Thought? Something like (2) seems better, because even if we remove the note that it increases monotonically, people might still assume that. The attached patch adds the following: - But when streaming replication is restarted this will back off to the replication starting position, which typically indicates the beginning of the WAL file including the record in the position which functionpg_last_xlog_replay_location/ points to at the moment. - Applied with some additional wordsmithing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalive for max_standby_delay
On Wed, Jun 9, 2010 at 8:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Thu, 2010-06-03 at 19:02 -0400, Tom Lane wrote: I decided there wasn't time to get anything useful done on it before the beta2 deadline (which is, more or less, right now). I will take another look over the next few days. We all really need you to fix up max_standby_delay, or, let me do it. Yes, I'll get with it ... Tom, Any update on this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Explicit psqlrc
Hi David, At a pdxpug gathering, we took a look at your patch to psql for supporting multiple -f's and put together some feedback: REVIEW: Patch: support multiple -f options https://commitfest.postgresql.org/action/patch_view?id=286 ==Submission review== Is the patch in context diff format? yes Does it apply cleanly to the current CVS HEAD? yes Do all tests pass? yes Does it include reasonable tests, necessary doc patches, etc? - tests: inconclusive - docs: no: psql --help does not mention that you can use multiple -f switches; do we want it to? also, no doc update was included in the patch. ==Usability review== Read what the patch is supposed to do, and consider: Does the patch actually implement that? yes Do we want that? sure! Do we already have it? no Does it follow SQL spec, or the community-agreed behavior? NA Does it include pg_dump support (if applicable)? NA Are there dangers? - subject to the usual Dumb Mistakes (see have all the bases been covered) Have all the bases been covered? Scenarios we came up with: - how does it handle wildcards, eg psql -f *.sql? Does not - this is a shell issue. psql is designed to take the database as the last argument; giving the -f option a wildcard expands the list, but does not assign multiple -f switches...so if you name one of your files the same as one of your databases, you could accidentally run updates you don't want to do. This is a known feature of psql, and has already bitten these reviewers in the butt on other occasions, so nothing to worry about here. - how does it handle the lack of a file? as expected: gabrie...@princess~/tmp/bin/:::-- ./psql -f ./psql: option requires an argument -- 'f' Try psql --help for more information. - how does it handle a non-existent file? as expected: gabrie...@princess~/tmp/bin/:::-- ./psql -f beer.sql beer.sql: No such file or directory - how does it handle files that don't contain valid sql? as expected, logs an error carries on with the next file. ==Feature test== Apply the patch, compile it and test: Does the feature work as advertised? - Yes; and BEGIN-COMMIT statements within the files cause warnings when the command is wrapped in a transaction with the -1 switch (as specified in the patch submission). Are there corner cases the author has failed to consider? - none that we can think of Are there any assertion failures or crashes? - Mark got it to segfault due to bug in logic for allocating pointers for filenames. It appears the order of operations between '!' and '%' was not as intended, thus realloc() is never called and a seg fault may occur after 16 files are passed. There are a few ways to fix it, the one we played with to make minimum changes to the patch is to change: if (!options-nm_files % FILE_ALLOC_BLOCKS) to if (options-num_files 1 !((options-num_files - 1) % FILE_ALLOC_BLOCKS)) ==Performance review== Does the patch slow down simple tests? - not that we can tell. If it claims to improve performance, does it? N/A Does it slow down other things? - not that we can tell. ==Coding review== Read the changes to the code in detail and consider: Does it follow the project coding guidelines? - unnecessary whitespace on line 251? - inconsistent spacing between operators Are there portability issues? - untested Will it work on Windows/BSD etc? - untested Are the comments sufficient and accurate? Does it do what it says, correctly? - yes Does it produce compiler warnings? - no Can you make it crash? - See above about the segfault. ==Architecture review== Consider the changes to the code in the context of the project as a whole: Is everything done in a way that fits together coherently with other features/modules? - yes Are there interdependencies that can cause problems? - not that we are aware of ==Review review== Did the reviewer cover all the things that kind of reviewer is supposed to do? - AFAWK. Regards, Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] modular se-pgsql as proof-of-concept
I tried to implement a modular se-pgsql as proof-of-concept, using the DML permission check hook which was proposed by Robert Haas. At first, please build and install the latest PostgreSQL with this patch to add a hook on DML permission checks. http://archives.postgresql.org/pgsql-hackers/2010-05/msg01095.php Then, check out the modular se-pgsql, as follows: % svn co http://sepgsql.googlecode.com/svn/trunk/ sepgsql Build and install: % cd sepgsql % make % su -c 'make install' Setting it up. % initdb -D $PGDATA % vi $PGDATA/postgresql.conf --- add 'sepgsql' for the 'shared_preload_libraries' % pg_ctl -l /path/to/logfile Limitations: - It does not check anything except for regular DML statements (SELECT, INSERT, UPDATE and DELETE). - No security label support, so it assumes pg_description stores security label of tables/columns instead. - No default labeling support, so we have to label tables/columns prior to accesses by hand. - No access control decision cache. - and so many limitations now... Example usage: [kai...@saba ~]$ id -Z unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 [kai...@saba ~]$ psql postgres psql (9.0beta2) Type help for help. postgres=# CREATE OR REPLACE FUNCTION sepgsql_getcon() RETURNS text AS 'sepgsql','sepgsql_getcon' LANGUAGE 'C'; CREATE FUNCTION postgres=# SELECT sepgsql_getcon(); sepgsql_getcon --- unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 (1 row) = It means it can obtain security context of the peer process correctly. Please confirm it is same as the result of 'id -Z'. postgres=# CREATE TABLE t1 (a int, b text); CREATE TABLE postgres=# CREATE TABLE t2 (x int, y text); CREATE TABLE = No DDL support now, so SELinux does not prevent anything. postgres=# INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); ERROR: SELinux: security policy violation = Because no labels are assigned on the table and columns, SELinux raises an access control violation error. postgres=# COMMENT ON TABLE t1 IS 'system_u:object_r:sepgsql_table_t:s0'; COMMENT postgres=# COMMENT ON COLUMN t1.a IS 'system_u:object_r:sepgsql_table_t:s0'; COMMENT postgres=# COMMENT ON COLUMN t1.b IS 'system_u:object_r:sepgsql_table_t:s0'; COMMENT = In this stage, it uses pg_description to store the security label of database objects, instead of the upcoming facilities. postgres=# INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); INSERT 0 3 = Because these are labeled correctly, SELinux allows to execute INSERT statement on the table/columns. postgres=# SET client_min_messages = LOG; SET postgres=# SET sepgsql_debug_audit = ON; SET postgres=# SELECT * FROM t1; LOG: SELinux: allowed { select } scontext=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 tcontext=system_u:object_r:sepgsql_table_t:s0 tclass=db_table name=t1 LOG: SELinux: allowed { select } scontext=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 tcontext=system_u:object_r:sepgsql_table_t:s0 tclass=db_column name=t1.a LOG: SELinux: allowed { select } scontext=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 tcontext=system_u:object_r:sepgsql_table_t:s0 tclass=db_column name=t1.b a | b ---+- 1 | aaa 2 | bbb 3 | ccc (3 rows) = We can observe what permissions were evaluated using 'sepgsql_debug_audit', even if required permissions were allowed. ('denied actions' will be logged in the default.) postgres=# CREATE TABLE t2 (x int, y text); CREATE TABLE postgres=# COMMENT ON TABLE t2 IS 'system_u:object_r:sepgsql_table_t:s0'; COMMENT postgres=# COMMENT ON COLUMN t2.x IS 'system_u:object_r:sepgsql_table_t:s0:c0'; COMMENT postgres=# COMMENT ON COLUMN t2.y IS 'system_u:object_r:sepgsql_table_t:s0:c1'; COMMENT postgres=# INSERT INTO t2 VALUES (1,'xxx'), (2,'yyy'); INSERT 0 2 postgres=# SELECT sepgsql_getcon(); sepgsql_getcon --- unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 (1 row) postgres=# SELECT * FROM t2; x | y ---+- 1 | xxx 2 | yyy (2 rows) = Note that ':c0' was appended on the security label of t2.x, and ':c1' was appended on the security label of t2.y. It means the 'c' of categories. In this example, the client has privileges to access whole of the categories from c0 to c1023, so SELinux does not prevent accesses. Then, let's try to log in with more restricted privileges. [kai...@saba ~]$ runcon -l s0:c1 psql postgres psql (9.0beta2) Type help for help. postgres=# SET client_min_messages = LOG; SET postgres=# SELECT sepgsql_getcon(); sepgsql_getcon -- unconfined_u:unconfined_r:unconfined_t:s0:c1 (1 row) postgres=# SELECT * FROM t2; LOG:
Re: [HACKERS] streaming replication breaks horribly if master crashes
On Thu, Jun 17, 2010 at 5:26 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 16, 2010 at 4:14 PM, Josh Berkus j...@agliodbs.com wrote: The first problem I noticed is that the slave never seems to realize that the master has gone away. Every time I crashed the master, I had to kill the wal receiver process on the slave to get it to reconnect; otherwise it just sat there waiting, either forever or at least for longer than I was willing to wait. Yes, I've noticed this. That was the reason for forcing walreceiver to shut down on a restart per prior discussion and patches. This needs to be on the open items list ... possibly it'll be fixed by Simon's keepalive patch? Or is it just a tcp_keeplalive issue? I think a TCP keepalive might be enough, but I have not tried to code or test it. The keepalive on libpq patch would help. https://commitfest.postgresql.org/action/patch_view?id=281 and this just makes it more likely. After the most recent crash, the master thought pg_current_xlog_location() was 1/86CD4000; the slave thought pg_last_xlog_receive_location() was 1/8733C000. After reconnecting to the master, the slave then thought that pg_last_xlog_receive_location() was 1/8700. So, *in this case*, detecting out-of-sequence xlogs (and PANICing) would have actually prevented the slave from being corrupted. My question, though, is detecting out-of-sequence xlogs *enough*? Are there any crash conditions on the master which would cause the master to reuse the same locations for different records, for example? I don't think so, but I'd like to be certain. The real problem here is that we're sending records to the slave which might cease to exist on the master if it unexpectedly reboots. I believe that what we need to do is make sure that the master only sends WAL it has already fsync'd (Tom suggested on another thread that this might be necessary, and I think it's now clear that it is 100% necessary). The attached patch changes walsender so that it always sends WAL up to LogwrtResult.Flush instead of LogwrtResult.Write. But I'm not sure how this will play with fsync=off - if we never fsync, then we can't ever really send any WAL without risking this failure mode. Similarly with synchronous_commit=off, I believe that the next checkpoint will still fsync WAL, but the lag might be long. First of all, we should not restart the master after the crash in fsync=off case. That would cause the corruption of the master database itself. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center send_after_fsync_v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers