Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Fujii Masao wrote: On Fri, Apr 23, 2010 at 1:04 AM, Robert Haas robertmh...@gmail.com wrote: One way we could fix this is use 2 bits rather than 1 for XLogStandbyInfoMode. One bit could indicate that either archive_mode=on or max_wal_senders0, and the second bit could indicate that recovery_connections=on. If the second bit is unset, we could emit the existing complaint: recovery connections cannot start because the recovery_connections parameter is disabled on the WAL source server If the other bit is unset, then we could instead complain: recovery connections cannot start because archive_mode=off and max_wal_senders=0 on the WAL source server If we don't want to use two bits there, it's hard to really describe all the possibilities in a reasonable number of characters. The only thing I can think of is to print a message and a hint: recovery_connections cannot start due to incorrect settings on the WAL source server HINT: make sure recovery_connections=on and either archive_mode=on or max_wal_senders0 I haven't checked whether the hint would be displayed in the log on the standby, but presumably we could make that be the case if it's not already. I think the first way is better because it gives the user more specific information about what they need to fix. Thinking about how each case might happen, since the default for recovery_connections is 'on', it seems that recovery_connections=off will likely only be an issue if the user has explicitly turned it off. The other case, where archive_mode=off and max_wal_senders=0, will likely only occur if someone takes a snapshot of the master without first setting up archiving or SR. Both of these will probably happen relatively rarely, but since we're burning a whole byte for XLogStandbyInfoMode (plus 3 more bytes of padding?), it seems like we might as well snag one more bit for clarity. Thoughts? I like the second choice since it's simpler and enough for me. But I have no objection to the first. When we encounter the error, we would need to not only change those parameter values but also take a fresh base backup and restart the standby using it. The description of this required procedure needs to be in the document or error message, I think. I quite liked Robert's proposal to add an explicit GUC to control what extra information is logged (http://archives.postgresql.org/pgsql-hackers/2010-04/msg00509.php). It is quite difficult to explain the current behavior, a simple explicit wal_mode GUC would be a lot simpler. It wouldn't add any extra steps to setting the system up, you currently need to set archive_mode='on' anyway to enable archiving. You would just set wal_mode='archive' or wal_mode='standby' instead, depending on what you want to do with the WAL. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Issue with ReRaise in PG
Hi, Please consider the following test case CREATE OR REPLACE FUNCTION raisetest() returns void AS $$ BEGIN BEGIN RAISE syntax_error; EXCEPTION WHEN syntax_error THEN BEGIN raise notice 'exception thrown in inner block, reraising'; RAISE; EXCEPTION WHEN OTHERS THEN raise notice 'RIGHT - exception caught in innermost block'; END; END; EXCEPTION WHEN OTHERS THEN raise notice 'WRONG - exception caught in outer block'; END; $$ LANGUAGE plpgsql; select raisetest(); NOTICE: exception thrown in inner block, reraising NOTICE: WRONG - exception caught in outer block block raisetest --- (1 row) The output of the above function seems to be wrong. Ideally the Exception should have caught in the inner most block instead of the outer block. Below I am sharing my obsevation while debuging this issue. When we give RAISE without the exception name statement, it is internally returning PLPGSQL_RC_RERAISE instead of jumping to the EXCEPTION block of the current Begin-End Block. This will force engine to eliminate/skip the current block's EXCEPTION block. This is the reason its got caught in the next exception block. To fix this, instead of returning PLPGSQL_RC_RERAISE from the function, we will rethrow the exception if their is no EXCEPTION name given to the RAISE statement. When their is RAISE (without exception name) statement, it is been assume that their must be some exception already raised earlier. We are now storing the 'errordata' into the 'estate' structure, while raising the exception. Now since we are not returning PLPGSQL_RC_RERAISE statement, I have also removed the related redundunt code in the pl_exec.c. The testcase mentioned above is behaving correctly like postgres=# select raisetest(); NOTICE: exception thrown in inner block, reraising NOTICE: RIGHT - exception caught in innermost block raisetest --- (1 row) Please find attached patch generated on the current branch to fix the problem. -- Piyush S Newe Principal Engineer EnterpriseDB office: +91 20 3058 9500 www.enterprisedb.com Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message. Index: src/pl/plpgsql/src/pl_exec.c === RCS file: /repositories/postgreshome/cvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v retrieving revision 1.257 diff -c -p -r1.257 pl_exec.c *** src/pl/plpgsql/src/pl_exec.c 14 Apr 2010 23:52:10 - 1.257 --- src/pl/plpgsql/src/pl_exec.c 23 Apr 2010 09:51:08 - *** plpgsql_exec_function(PLpgSQL_function * *** 327,336 ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg(CONTINUE cannot be used outside a loop))); - else if (rc == PLPGSQL_RC_RERAISE) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg(RAISE without parameters cannot be used outside an exception handler))); else ereport(ERROR, (errcode(ERRCODE_S_R_E_FUNCTION_EXECUTED_NO_RETURN_STATEMENT), --- 327,332 *** plpgsql_exec_trigger(PLpgSQL_function *f *** 695,704 ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg(CONTINUE cannot be used outside a loop))); - else if (rc == PLPGSQL_RC_RERAISE) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg(RAISE without parameters cannot be used outside an exception handler))); else ereport(ERROR, (errcode(ERRCODE_S_R_E_FUNCTION_EXECUTED_NO_RETURN_STATEMENT), --- 691,696 *** exec_stmt_block(PLpgSQL_execstate *estat *** 1132,1138 --- 1124,1136 estate-err_text = NULL; + /* + * Set last_caught_error for the duration of the + * exception handler, so that RAISE; can rethrow it. + */ + estate-last_caught_error = edata; rc = exec_stmts(estate, exception-action); + estate-last_caught_error = NULL; free_var(state_var); state_var-value = (Datum) 0; *** exec_stmt_block(PLpgSQL_execstate *estat *** 1141,1150 errm_var-value = (Datum) 0; errm_var-isnull = true; - /* re-throw error if requested by handler */ - if (rc == PLPGSQL_RC_RERAISE) -
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 5:24 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Fujii Masao wrote: On Fri, Apr 23, 2010 at 1:04 AM, Robert Haas robertmh...@gmail.com wrote: One way we could fix this is use 2 bits rather than 1 for XLogStandbyInfoMode. One bit could indicate that either archive_mode=on or max_wal_senders0, and the second bit could indicate that recovery_connections=on. If the second bit is unset, we could emit the existing complaint: recovery connections cannot start because the recovery_connections parameter is disabled on the WAL source server If the other bit is unset, then we could instead complain: recovery connections cannot start because archive_mode=off and max_wal_senders=0 on the WAL source server If we don't want to use two bits there, it's hard to really describe all the possibilities in a reasonable number of characters. The only thing I can think of is to print a message and a hint: recovery_connections cannot start due to incorrect settings on the WAL source server HINT: make sure recovery_connections=on and either archive_mode=on or max_wal_senders0 I haven't checked whether the hint would be displayed in the log on the standby, but presumably we could make that be the case if it's not already. I think the first way is better because it gives the user more specific information about what they need to fix. Thinking about how each case might happen, since the default for recovery_connections is 'on', it seems that recovery_connections=off will likely only be an issue if the user has explicitly turned it off. The other case, where archive_mode=off and max_wal_senders=0, will likely only occur if someone takes a snapshot of the master without first setting up archiving or SR. Both of these will probably happen relatively rarely, but since we're burning a whole byte for XLogStandbyInfoMode (plus 3 more bytes of padding?), it seems like we might as well snag one more bit for clarity. Thoughts? I like the second choice since it's simpler and enough for me. But I have no objection to the first. When we encounter the error, we would need to not only change those parameter values but also take a fresh base backup and restart the standby using it. The description of this required procedure needs to be in the document or error message, I think. I quite liked Robert's proposal to add an explicit GUC to control what extra information is logged (http://archives.postgresql.org/pgsql-hackers/2010-04/msg00509.php). It is quite difficult to explain the current behavior, a simple explicit wal_mode GUC would be a lot simpler. It wouldn't add any extra steps to setting the system up, you currently need to set archive_mode='on' anyway to enable archiving. You would just set wal_mode='archive' or wal_mode='standby' instead, depending on what you want to do with the WAL. I liked it, too, but I sort of decided it didn't buy much. There are three separate sets of things that need to be controlled: 1. What WAL to emit - (a) just enough for crash recovery, (b) enough for log shipping, (c) enough for log shipping with recovery connections. 2. Whether to run the archiver. 3. Whether to allow streaming replication connections (and if so, how many). If the answer to (1) is just enough for crash recovery, then (2) and (3) must be no. But if (1) is either of the other two options, then any combination of answers for (2) and (3) is seemingly sensible, though having both (2) and (3) as no is probably of limited utility. But at a mimium, you could certainly have: crash recovery/no archiver/no SR log shipping/archiver/no SR log shipping/no archiver/SR log shipping/archiver/SR recovery connections/archiver/no SR recovery connections/no archiver/SR recovery connections/archiver/SR I don't see any reasonable way to package all of that up in a single GUC. Thoughts? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas wrote: On Fri, Apr 23, 2010 at 5:24 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I quite liked Robert's proposal to add an explicit GUC to control what extra information is logged (http://archives.postgresql.org/pgsql-hackers/2010-04/msg00509.php). It is quite difficult to explain the current behavior, a simple explicit wal_mode GUC would be a lot simpler. It wouldn't add any extra steps to setting the system up, you currently need to set archive_mode='on' anyway to enable archiving. You would just set wal_mode='archive' or wal_mode='standby' instead, depending on what you want to do with the WAL. I liked it, too, but I sort of decided it didn't buy much. There are three separate sets of things that need to be controlled: 1. What WAL to emit - (a) just enough for crash recovery, (b) enough for log shipping, (c) enough for log shipping with recovery connections. 2. Whether to run the archiver. 3. Whether to allow streaming replication connections (and if so, how many). Streaming replication needs the same information in the WAL as archiving does, there's no difference between 2 and 3. (the how many aspect of 3 is controlled by max_wal_senders). Let's have these three settings: wal_mode = crash/archive/standby (replaces archive_mode) archive_command max_wal_senders If wal_mode is set to 'crash', you can't set archive_command or max_wal_senders0. If it's set to 'archive', you can set archive_command and/or max_wal_senders for archiving and streaming replication, but the standby server won't allow queries. If you set it to 'standby', it will (assuming you've set recovery_connections=on in the standby). Note that wal_mode=standby replaces recovery_connections=on in the primary. I think this would be much easier to understand than the current situation. I'm not wedded to the GUC name or values, though, maybe it should be archive_mode=off/on/standby, or wal_mode=minimal/archive/full. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 7:12 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: On Fri, Apr 23, 2010 at 5:24 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I quite liked Robert's proposal to add an explicit GUC to control what extra information is logged (http://archives.postgresql.org/pgsql-hackers/2010-04/msg00509.php). It is quite difficult to explain the current behavior, a simple explicit wal_mode GUC would be a lot simpler. It wouldn't add any extra steps to setting the system up, you currently need to set archive_mode='on' anyway to enable archiving. You would just set wal_mode='archive' or wal_mode='standby' instead, depending on what you want to do with the WAL. I liked it, too, but I sort of decided it didn't buy much. There are three separate sets of things that need to be controlled: 1. What WAL to emit - (a) just enough for crash recovery, (b) enough for log shipping, (c) enough for log shipping with recovery connections. 2. Whether to run the archiver. 3. Whether to allow streaming replication connections (and if so, how many). Streaming replication needs the same information in the WAL as archiving does, True. there's no difference between 2 and 3. (the how many aspect of 3 is controlled by max_wal_senders). False. I thought what you think too, but discovered otherwise when I read the code. Some uses of archive_mode are used to control what WAL is generated, but others control a *process* called the archiver. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas wrote: On Fri, Apr 23, 2010 at 7:12 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: On Fri, Apr 23, 2010 at 5:24 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I quite liked Robert's proposal to add an explicit GUC to control what extra information is logged (http://archives.postgresql.org/pgsql-hackers/2010-04/msg00509.php). It is quite difficult to explain the current behavior, a simple explicit wal_mode GUC would be a lot simpler. It wouldn't add any extra steps to setting the system up, you currently need to set archive_mode='on' anyway to enable archiving. You would just set wal_mode='archive' or wal_mode='standby' instead, depending on what you want to do with the WAL. I liked it, too, but I sort of decided it didn't buy much. There are three separate sets of things that need to be controlled: 1. What WAL to emit - (a) just enough for crash recovery, (b) enough for log shipping, (c) enough for log shipping with recovery connections. 2. Whether to run the archiver. 3. Whether to allow streaming replication connections (and if so, how many). Streaming replication needs the same information in the WAL as archiving does, True. there's no difference between 2 and 3. (the how many aspect of 3 is controlled by max_wal_senders). False. I thought what you think too, but discovered otherwise when I read the code. Some uses of archive_mode are used to control what WAL is generated, but others control a *process* called the archiver. Hmm, never mind the archiver process, we could just launch it always and it would just sit idle if archive_command was not set. But a more serious concern is that if you set archive_mode=on, and archive_command='', we retain all WAL indefinitely, because it's not being archived, until you set archive_command to something that succeeds again. You're right, with the wal_mode='crash/archive/standby there would be no way to distinguish archiving is temporarily disabled, keep all accumulated WAL around and we're not archiving, but wal_mode='archive' to enable streaming replication. Ok, that brings us back to square one. We could still add the wal_mode GUC to explicitly control how much WAL is written (replacing recovery_connections in the primary), I think it would still make the system easier to explain. But it would add an extra hurdle to enabling archiving, you'd have to set wal_mode='archive', archive_mode='on', and archive_command. I'm not sure if that would be better or worse than the current situation. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Apr 23, 2010, at 13:12 , Heikki Linnakangas wrote: Let's have these three settings: wal_mode = crash/archive/standby (replaces archive_mode) archive_command max_wal_senders If wal_mode is set to 'crash', you can't set archive_command or max_wal_senders0. If it's set to 'archive', you can set archive_command and/or max_wal_senders for archiving and streaming replication, but the standby server won't allow queries. If you set it to 'standby', it will (assuming you've set recovery_connections=on in the standby). Note that wal_mode=standby replaces recovery_connections=on in the primary. I think this would be much easier to understand than the current situation. I'm not wedded to the GUC name or values, though, maybe it should be archive_mode=off/on/standby, or wal_mode=minimal/archive/full. Hm, but but that would preclude the possibility of running master and (log-shipping) slave off the same configuration, since one would need wal_mode=standby and the other recovery_connections=on. Whereas with the current GUCs, iarchive_mode=on, recovery_connections=on, archive_command=... should be a valid configuration for both master and slave, no? best regards, Florian Pflug smime.p7s Description: S/MIME cryptographic signature
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 7:40 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Ok, that brings us back to square one. We could still add the wal_mode GUC to explicitly control how much WAL is written (replacing recovery_connections in the primary), I think it would still make the system easier to explain. But it would add an extra hurdle to enabling archiving, you'd have to set wal_mode='archive', archive_mode='on', and archive_command. I'm not sure if that would be better or worse than the current situation. I wasn't either, that's why I gave up. It didn't seem worth doing a major GUC reorganization on the eve of beta unless there was a clear win. I think there may be a way to improve this but I don't think it's we should take the time now to figure out what it is. Let's revisit it for 9.1, and just improve the error reporting for now. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 8:54 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Apr 23, 2010 at 7:40 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Ok, that brings us back to square one. We could still add the wal_mode GUC to explicitly control how much WAL is written (replacing recovery_connections in the primary), I think it would still make the system easier to explain. But it would add an extra hurdle to enabling archiving, you'd have to set wal_mode='archive', archive_mode='on', and archive_command. I'm not sure if that would be better or worse than the current situation. I wasn't either, that's why I gave up. It didn't seem worth doing a major GUC reorganization on the eve of beta unless there was a clear win. I think there may be a way to improve this but I don't think it's we should take the time now to figure out what it is. Let's revisit it for 9.1, and just improve the error reporting for now. +1 Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION 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
Re: [HACKERS] testing HS/SR - 1 vs 2 performance
On Thu, 2010-04-22 at 23:45 +0100, Simon Riggs wrote: On Thu, 2010-04-22 at 20:39 +0200, Erik Rijkers wrote: On Sun, April 18, 2010 13:01, Simon Riggs wrote: any comment is welcome... Please can you re-run with -l and post me the file of times Erik has sent me details of a test run. My analysis of that is: I'm seeing the response time profile on the standby as 99% 110us 99.9% 639us 99.99% 615ms 0.052% (52 samples) are 5ms elapsed and account for 24 s, which is about 45% of elapsed time. Of the 52 samples 5ms, 50 of them are 100ms and 2 1s. 99% of transactions happen in similar times between primary and standby, everything dragged down by rare but severe spikes. We're looking for something that would delay something that normally takes 0.1ms into something that takes 100ms, yet does eventually return. That looks like a severe resource contention issue. This effect happens when running just a single read-only session on standby from pgbench. No confirmation as yet as to whether recovery is active or dormant, and what other activitity if any occurs on standby server at same time. So no other clues as yet as to what the contention might be, except that we note the standby is writing data and the database is large. Please also rebuild using --enable-profile so we can see what's happening. Can you also try the enclosed patch which implements prefetching during replay of btree delete records. (Need to set effective_io_concurrency) As yet, no confirmation that the attached patch is even relevant. It was just a wild guess at some tuning, while we wait for further info. Thanks for your further help. Some kind of contention is best we can say at present. -- Simon Riggs www.2ndQuadrant.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] testing HS/SR - 1 vs 2 performance
On Fri, Apr 23, 2010 at 11:14 AM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-04-22 at 23:45 +0100, Simon Riggs wrote: On Thu, 2010-04-22 at 20:39 +0200, Erik Rijkers wrote: On Sun, April 18, 2010 13:01, Simon Riggs wrote: any comment is welcome... Please can you re-run with -l and post me the file of times Erik has sent me details of a test run. My analysis of that is: I'm seeing the response time profile on the standby as 99% 110us 99.9% 639us 99.99% 615ms 0.052% (52 samples) are 5ms elapsed and account for 24 s, which is about 45% of elapsed time. Of the 52 samples 5ms, 50 of them are 100ms and 2 1s. 99% of transactions happen in similar times between primary and standby, everything dragged down by rare but severe spikes. We're looking for something that would delay something that normally takes 0.1ms into something that takes 100ms, yet does eventually return. That looks like a severe resource contention issue. Wow. Good detective work. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PGCon 2010 - registered yet?
Registration for PGCon 2010 is open. http://www.pgcon.org/2010/registration.php The full list of talks and a preliminary schedule is available here: http://www.pgcon.org/2010/schedule/ There are still some rooms available on campus but I recommend booking soon as they always fill up. -- Dan Langille - http://langille.org/ -- 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] BETA
Robert Haas robertmh...@gmail.com writes: I think it might be time to think about shipping a beta release. I guess this is a -core decision, but I can't argue for it there, so I'll argue for it here. It seems like we're about ready, so maybe we could plan for a beta, say, a week from now? A bit of discussion among -core and -packagers has not turned up any objections, so we'll plan to wrap beta1 on Thursday 29th for public announcement Monday 5/3. 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] Issue with ReRaise in PG
Piyush Newe piyush.n...@enterprisedb.com writes: Please consider the following test case CREATE OR REPLACE FUNCTION raisetest() returns void AS $$ BEGIN BEGIN RAISE syntax_error; EXCEPTION WHEN syntax_error THEN BEGIN raise notice 'exception thrown in inner block, reraising'; RAISE; EXCEPTION WHEN OTHERS THEN raise notice 'RIGHT - exception caught in innermost block'; END; END; EXCEPTION WHEN OTHERS THEN raise notice 'WRONG - exception caught in outer block'; END; $$ LANGUAGE plpgsql; select raisetest(); NOTICE: exception thrown in inner block, reraising NOTICE: WRONG - exception caught in outer block RAISE without parameters is only allowed inside an exception handler, and what it throws is that handler's exception. In this example, it is within an exception handler of the outer block. So it's allowed, but it re-throws from that handler. We could possibly make the above case throw an improperly placed RAISE error instead of doing what it does now, but I don't think there is a good argument for having it do what you propose. 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] psql: Add setting to make '+' on \d implicit
I asked on IRC if there was any way to make \d behave like \d+ by default, and davidfetter said no but suggest it here. endpoint_david pointed out you could use \d- to get the old behavior if you wanted to temporarily negate the setting. So the proposal would be: \d+ does as it has always done, no change \d- (new) always behaves like 'old' \d \d acts as 'old' \d or as \d+, depending on the setting of 'verbose_describe', set via \pset. Default setting of verbose_describe would presumably yield 'old' behavior. Motivation is that I like to see comments when they exist. Probably useful for other reasons too. Cheers -Terry -- 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] Issue with ReRaise in PG
Tom Lane wrote: Piyush Newe piyush.n...@enterprisedb.com writes: Please consider the following test case CREATE OR REPLACE FUNCTION raisetest() returns void AS $$ BEGIN BEGIN RAISE syntax_error; EXCEPTION WHEN syntax_error THEN BEGIN raise notice 'exception thrown in inner block, reraising'; RAISE; EXCEPTION WHEN OTHERS THEN raise notice 'RIGHT - exception caught in innermost block'; END; END; EXCEPTION WHEN OTHERS THEN raise notice 'WRONG - exception caught in outer block'; END; $$ LANGUAGE plpgsql; select raisetest(); NOTICE: exception thrown in inner block, reraising NOTICE: WRONG - exception caught in outer block RAISE without parameters is only allowed inside an exception handler, and what it throws is that handler's exception. In this example, it is within an exception handler of the outer block. So it's allowed, but it re-throws from that handler. We could possibly make the above case throw an improperly placed RAISE error instead of doing what it does now, but I don't think there is a good argument for having it do what you propose. It's worth noting that RAISE without parameters was added to mimic the corresponding RAISE command on Oracle, and on Oracle Piyush's test case works as he says. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 7:12 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Streaming replication needs the same information in the WAL as archiving does, True. FWIW, I still don't believe that claim, and I think it's complete folly to set the assumption in stone by choosing a user-visible GUC API that depends on it being true. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, 2010-04-23 at 07:54 -0400, Robert Haas wrote: Let's revisit it for 9.1, and just improve the error reporting for now. +1 -- Simon Riggs www.2ndQuadrant.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] testing HS/SR - 1 vs 2 performance
On 4/18/10, Simon Riggs si...@2ndquadrant.com wrote: On Sat, 2010-04-17 at 16:48 -0400, Tom Lane wrote: There are some places where we suppose that a *single* write into shared memory can safely be done without a lock, if we're not too concerned about how soon other transactions will see the effects. But what you are proposing here requires more than one related write. I've been burnt by this myself: http://archives.postgresql.org/pgsql-committers/2008-06/msg00228.php W O W - thank you for sharing. What I'm not clear on is why you've used a spinlock everywhere when only weak-memory thang CPUs are a problem. Why not have a weak-memory-protect macro that does does nada when the hardware already protects us? (i.e. a spinlock only for the hardware that needs it). Um, you have been burned by exactly this on x86 also: http://archives.postgresql.org/pgsql-hackers/2009-03/msg01265.php -- marko -- 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] testing HS/SR - 1 vs 2 performance
Marko Kreen mark...@gmail.com writes: Um, you have been burned by exactly this on x86 also: http://archives.postgresql.org/pgsql-hackers/2009-03/msg01265.php Yeah, we never did figure out exactly how come you were observing that failure on Intel-ish hardware. I was under the impression that Intel machines didn't have weak-memory-ordering behavior. I wonder whether your compiler had rearranged the code in ProcArrayAdd so that the increment happened before the array element store at the machine-code level. I think it would be entitled to do that under standard C semantics, since that ProcArrayStruct pointer isn't marked volatile. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 12:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 7:12 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Streaming replication needs the same information in the WAL as archiving does, True. FWIW, I still don't believe that claim, and I think it's complete folly to set the assumption in stone by choosing a user-visible GUC API that depends on it being true. Huh? We're clearly talking about two different things here, because that doesn't make any sense. Archiving and streaming replication are just two means of transporting WAL records from point A to point B. By definition, any two manners of moving a byte stream around are isomorphic and can't possibly affect what that byte stream does or does not need to contain. What affects the WAL that must be emitted is the purpose for which it is to be used. As to that, I believe everyone (including the code) is in agreement that a minimum amount of WAL is always needed for crash recovery, plus if we want to do archive recovery on another server there are some additional bits that must be emitted (XLogIsNeeded) and plus if further want to process queries on the standby then there are a few more bits beyond that (XLogStandbyInfoActive). ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 12:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: FWIW, I still don't believe that claim, and I think it's complete folly to set the assumption in stone by choosing a user-visible GUC API that depends on it being true. Huh? We're clearly talking about two different things here, because that doesn't make any sense. Archiving and streaming replication are just two means of transporting WAL records from point A to point B. Sorry, not enough caffeine. What I should have said was that Hot Standby could put stronger requirements on what gets put into WAL than archiving for recovery does. Heikki's proposal upthread was wal_mode='standby' versus wal_mode='archive' (versus 'off'), which seemed sensible to me. We realized some time ago that it was a good idea to separate archive_mode (what to put in WAL) from archive_command (whether we are actually archiving right now). If we fail to apply that same principle to Hot Standby, I think we'll come to regret it. 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] psql: Add setting to make '+' on \d implicit
On Fri, Apr 23, 2010 at 10:58:40AM -0500, Terry Brown wrote: I asked on IRC if there was any way to make \d behave like \d+ by default, and davidfetter said no but suggest it here. endpoint_david pointed out you could use \d- to get the old behavior if you wanted to temporarily negate the setting. So the proposal would be: \d+ does as it has always done, no change \d- (new) always behaves like 'old' \d \d acts as 'old' \d or as \d+, depending on the setting of 'verbose_describe', set via \pset. Default setting of verbose_describe would presumably yield 'old' behavior. Motivation is that I like to see comments when they exist. Probably useful for other reasons too. Hmm, what about all the other + variants? Would this setting affect them? I'd suggest perhaps it should. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Tom Lane wrote: We realized some time ago that it was a good idea to separate archive_mode (what to put in WAL) from archive_command (whether we are actually archiving right now). If we fail to apply that same principle to Hot Standby, I think we'll come to regret it. The recovery_connections GUC does that. If you enable it, the extra information required for hot standby is written to the WAL, otherwise it's not. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] testing HS/SR - 1 vs 2 performance
On 4/23/10, Tom Lane t...@sss.pgh.pa.us wrote: Marko Kreen mark...@gmail.com writes: Um, you have been burned by exactly this on x86 also: http://archives.postgresql.org/pgsql-hackers/2009-03/msg01265.php Yeah, we never did figure out exactly how come you were observing that failure on Intel-ish hardware. I was under the impression that Intel machines didn't have weak-memory-ordering behavior. I wonder whether your compiler had rearranged the code in ProcArrayAdd so that the increment happened before the array element store at the machine-code level. I think it would be entitled to do that under standard C semantics, since that ProcArrayStruct pointer isn't marked volatile. Sounds likely. Which seems to hint its better to handle all processors as weak ordered and then work with explicit locks/memory barriers, than to sprinkle code with 'volatile' to supress optimizations on intel and then still fail on non-intel. -- marko -- 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] psql: Add setting to make '+' on \d implicit
Ross J. Reedstrom reeds...@rice.edu writes: On Fri, Apr 23, 2010 at 10:58:40AM -0500, Terry Brown wrote: So the proposal would be: \d+ does as it has always done, no change \d- (new) always behaves like 'old' \d \d acts as 'old' \d or as \d+, depending on the setting of 'verbose_describe', set via \pset. Hmm, what about all the other + variants? Would this setting affect them? I'd suggest perhaps it should. If we were to do something like that, it would certainly have to affect every \d variant that has a + option. Which is probably not a very good idea --- in many cases that's a very expensive/verbose option. I can't get excited about this proposal, personally. What the OP actually seemed to care about was database object comments. I could see somebody who relied heavily on comments wanting his comments to be included in all display commands, even without the + option. Maybe a configuration variable along the lines of 'always_show_comments' would be a better design. BTW, \pset seems like the wrong place for this. That's for formatting table output, not for controlling what specific \d commands show. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: We realized some time ago that it was a good idea to separate archive_mode (what to put in WAL) from archive_command (whether we are actually archiving right now). If we fail to apply that same principle to Hot Standby, I think we'll come to regret it. The recovery_connections GUC does that. If you enable it, the extra information required for hot standby is written to the WAL, otherwise it's not. No, driving it off recovery_connections is exactly NOT that. It's confusing the transport mechanism with the desired WAL contents. I maintain that this design is exactly isomorphic to our original PITR GUC design wherein what got written to WAL was determined by the current state of archive_command. We eventually realized that was a bad idea. So is this. As a concrete example, there is nothing logically wrong with driving a hot standby slave from WAL records shipped via old-style pg_standby. Or how about wanting to turn off recovery_connections temporarily, but not wanting the archived WAL to be unable to support HS? 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 2:36 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: We realized some time ago that it was a good idea to separate archive_mode (what to put in WAL) from archive_command (whether we are actually archiving right now). If we fail to apply that same principle to Hot Standby, I think we'll come to regret it. The recovery_connections GUC does that. If you enable it, the extra information required for hot standby is written to the WAL, otherwise it's not. No, driving it off recovery_connections is exactly NOT that. It's confusing the transport mechanism with the desired WAL contents. I maintain that this design is exactly isomorphic to our original PITR GUC design wherein what got written to WAL was determined by the current state of archive_command. We eventually realized that was a bad idea. So is this. As a concrete example, there is nothing logically wrong with driving a hot standby slave from WAL records shipped via old-style pg_standby. Or how about wanting to turn off recovery_connections temporarily, but not wanting the archived WAL to be unable to support HS? You're all confused about what the different GUCs actually do. Which is probably not a good sign for their usability. But yeah, that's one of the things that concerned me, too. If you turn off max_wal_senders, it doesn't just make it so that no WAL senders can connect: it actually changes what gets WAL-logged. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Tom Lane t...@sss.pgh.pa.us wrote: As a concrete example, there is nothing logically wrong with driving a hot standby slave from WAL records shipped via old-style pg_standby. Or how about wanting to turn off recovery_connections temporarily, but not wanting the archived WAL to be unable to support HS? As one more concrete example, we are likely to find SR beneficial if it can feed into a warm standby, but only if we can also do traditional WAL file archiving from the same source at the same time. The extra logging for HS would be useless for us in any event. +1 for *not* tying WAL contents to the transport mechanism. -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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, 2010-04-23 at 13:45 -0400, Robert Haas wrote: Archiving and streaming replication are just two means of transporting WAL records from point A to point B. By definition, any two manners of moving a byte stream around are isomorphic and can't possibly affect what that byte stream does or does not need to contain. It is currently true, but there is no benefit in us constraining future implementation routes without good reason. -- Simon Riggs www.2ndQuadrant.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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 2:43 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane t...@sss.pgh.pa.us wrote: As a concrete example, there is nothing logically wrong with driving a hot standby slave from WAL records shipped via old-style pg_standby. Or how about wanting to turn off recovery_connections temporarily, but not wanting the archived WAL to be unable to support HS? As one more concrete example, we are likely to find SR beneficial if it can feed into a warm standby, but only if we can also do traditional WAL file archiving from the same source at the same time. The extra logging for HS would be useless for us in any event. +1 for *not* tying WAL contents to the transport mechanism. OK. Well, it's a shame we didn't get this settled last week when I first brought it up, but it's not too late to try to straighten it out if we have a consensus behind changing it, which it's starting to sound like we do. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, 2010-04-23 at 15:05 -0400, Robert Haas wrote: we have a consensus behind changing it, which it's starting to sound like we do. I think you misread the +1s from Masao and myself. Those confusing things are options and I want them to remain optional, not compressed into a potentially too simple model based upon how the world looks right now. -- Simon Riggs www.2ndQuadrant.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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 3:11 PM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, 2010-04-23 at 15:05 -0400, Robert Haas wrote: we have a consensus behind changing it, which it's starting to sound like we do. I think you misread the +1s from Masao and myself. Those confusing things are options and I want them to remain optional, not compressed into a potentially too simple model based upon how the world looks right now. I didn't, but Heikki, Kevin and Tom seem to be on the other side, so we at least have to consider where to go with it. We're going to need a bunch of GUCs any way we slice it. The issue is whether there's a way to slice it that involves fewer AND and OR operators that have to be understood by users. I'm still unconvinced of our ability to come up with a solid design in the time we have, but I think it would make sense to listen to proposals people want to make. I poked some holes in Heikki's design from this morning (which was, more or less, my design from last week) but that doesn't mean they can't be plugged. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Simon Riggs si...@2ndquadrant.com writes: Those confusing things are options and I want them to remain optional, not compressed into a potentially too simple model based upon how the world looks right now. What are you arguing is too simple? What *I* think is too simple is what we have got now, namely a GUC that controls both the availability of replication connections and the contents of WAL. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas robertmh...@gmail.com writes: ... I'm still unconvinced of our ability to come up with a solid design in the time we have, but I think it would make sense to listen to proposals people want to make. I poked some holes in Heikki's design from this morning (which was, more or less, my design from last week) but that doesn't mean they can't be plugged. The only hole I saw poked was the one about how archive_mode is used to decide whether to start the archiver process. I think we could reasonably deal with that by starting the archiver iff wal_mode 'crash'. There's no point in archiving otherwise, and the overhead of an idle archiver is small enough that we can live with the corner cases where you're starting an archiver you don't really need. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, 2010-04-23 at 15:18 -0400, Robert Haas wrote: We're going to need a bunch of GUCs any way we slice it. The issue is whether there's a way to slice it that involves fewer AND and OR operators that have to be understood by users. So we're proposing adding parameters to simplify things for users? I don't think fiddling is going to improve things significantly from a usability perspective, especially at the last minute. I'm guessing this conversation has more to do with the situation that some very clever people have a little time on their hands after a long period of hard work. I see no problem that needs to be solved, not alongside this water cooler at least. Smells like beta time. -- Simon Riggs www.2ndQuadrant.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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Simon Riggs si...@2ndquadrant.com wrote: So we're proposing adding parameters to simplify things for users? I think it's a matter of having parameters which do simple, clear things; rather than magically interacting to guess what the user wants. What do you want to log? How many connections to you want to allow for streaming it? What's your script for sending it in archive file format? Is archiving turned on at the moment? Let's have GUC for each question, rather than having to work backwards from what you want to which combination of GUC settings gets you to that, or at least as close as the magic interpretation allows. I don't think fiddling is going to improve things significantly from a usability perspective, especially at the last minute. If it involves changing the internal variables in a dangerous way, perhaps we should settle for whatever we have at the moment. If it's a matter of how they get set from the GUCs, that doesn't sound very risky to me. Perhaps there are combinations which were previously disallowed which would need to be tested, but are there any other risks? [ad hominem digression] Please, can we keep it to the merits? It sounds like there are several reasonable use-cases which could be handled by HS/SR except for how our GUCs are set up for it. Why limit the uses to a subset of where it can be useful? I'm extraordinarily busy right now, which is why my skimming of these threads didn't alert me to the problem sooner. For that I apologize. -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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Simon Riggs si...@2ndquadrant.com writes: So we're proposing adding parameters to simplify things for users? Not so much simplify as make understandable; although flexibility is a concern too. I'm guessing this conversation has more to do with the situation that some very clever people have a little time on their hands after a long period of hard work. I see no problem that needs to be solved, not alongside this water cooler at least. Smells like beta time. [ shrug... ] I'm just trying to learn from history and not repeat a previous mistake. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 3:34 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
... I'm still unconvinced of our ability to come
up with a solid design in the time we have, but I think it would make
sense to listen to proposals people want to make. I poked some holes
in Heikki's design from this morning (which was, more or less, my
design from last week) but that doesn't mean they can't be plugged.
The only hole I saw poked was the one about how archive_mode is used to
decide whether to start the archiver process. I think we could
reasonably deal with that by starting the archiver iff wal_mode 'crash'.
There's no point in archiving otherwise, and the overhead of an idle
archiver is small enough that we can live with the corner cases where
you're starting an archiver you don't really need.
Well, I think the real hole is that turning archive_mode=on results in
WAL never being deleted unless it's successfully archived.
But we might be able to handle that like this:
wal_mode={standby|archive|crash} # or whatever
wal_segments_always=integer # keep this many segments always, for
SR - like current wal_keep_segments
wal_segments_unarchived=integer # keep this many unarchived
segments, -1 for infinite
max_wal_senders=integer # same as now
archive_command=string# same as now
So we always retain wal_segments_always segments, but if we have
trouble with archiving we'll retain up to wal_segments_archived.
...Robert
--
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] [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
sri...@postgresql.org (Simon Riggs) writes: Log Message: --- Make CheckRequiredParameterValues() depend upon correct combination of parameters. Fix bug report by Robert Haas that error message and hint was incorrect if wrong mode parameters specified on master. Internal changes only. Proposals for parameter simplification on master/primary still under way. Modified Files: -- pgsql/src/backend/access/transam: xlog.c (r1.401 - r1.402) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/transam/xlog.c?r1=1.401r2=1.402) pgsql/src/include/catalog: pg_control.h (r1.51 - r1.52) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/pg_control.h?r1=1.51r2=1.52) This is a change in pg_control layout and requires a bump to the pg_control version number (and hence forced initdb's all round). I think it was quite premature to commit this when the design is still under active discussion --- you may be forcing two rounds of initdb on testers, when maybe only one or none would be enough. Especially when you appear to be in the minority about what the design should be. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, 2010-04-23 at 14:56 -0500, Kevin Grittner wrote: Simon Riggs si...@2ndquadrant.com wrote: So we're proposing adding parameters to simplify things for users? I think it's a matter of having parameters which do simple, clear things; rather than magically interacting to guess what the user wants. What do you want to log? How many connections to you want to allow for streaming it? What's your script for sending it in archive file format? Is archiving turned on at the moment? Let's have GUC for each question, rather than having to work backwards from what you want to which combination of GUC settings gets you to that, or at least as close as the magic interpretation allows. I've just committed a change to make Hot Standby depend only upon the setting recovery_connections = on on the master. That makes it clear that there is one lever, not lots of confusing ones. That might forestall further changes, because the correct way of doing this was already as simple as people wanted it to be. The previous requirement was actually a bug: the method of WAL delivery has nothing at all to do with Hot Standby (currently). Not intended to stop further debate, if people wish. -- Simon Riggs www.2ndQuadrant.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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: ... I'm still unconvinced of our ability to come up with a solid design in the time we have, but I think it would make sense to listen to proposals people want to make. I poked some holes in Heikki's design from this morning (which was, more or less, my design from last week) but that doesn't mean they can't be plugged. The only hole I saw poked was the one about how archive_mode is used to decide whether to start the archiver process. I think we could reasonably deal with that by starting the archiver iff wal_mode 'crash'. There's no point in archiving otherwise, and the overhead of an idle archiver is small enough that we can live with the corner cases where you're starting an archiver you don't really need. Agreed, but a more serious hole is what I pointed out at http://archives.postgresql.org/message-id/4bd18722.3090...@enterprisedb.com. That is, if you do: wal_mode=standby archive_command='' max_wal_senders=5 That would be a valid configuration for enabling streaming replication without archiving (which is possible and reasonable if you set the new wal_keep_segments setting high enough). But as things stand, WAL segments would be readied for archiving (.ready files would be created), but they'e never archived and will accumulate indefinitely in the master. You could work around that with archive_command='/usr/bin/true', but that's not user-frienfly. So my proposal would be: wal_mode=crash/archive/standby archive_mode=on/off # if on, wal_mode must be = 'archive' archive_command='command' max_wal_senders=integer # if 0, wal_mode must be = 'archive' replication_connections is not needed on the master anymore; on the standby it enables/disables hot standby. It is ignored on the master, to allow the same configuration file to be used on master and standby. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Fri, 2010-04-23 at 16:04 -0400, Tom Lane wrote: sri...@postgresql.org (Simon Riggs) writes: Log Message: --- Make CheckRequiredParameterValues() depend upon correct combination of parameters. Fix bug report by Robert Haas that error message and hint was incorrect if wrong mode parameters specified on master. Internal changes only. Proposals for parameter simplification on master/primary still under way. Modified Files: -- pgsql/src/backend/access/transam: xlog.c (r1.401 - r1.402) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/transam/xlog.c?r1=1.401r2=1.402) pgsql/src/include/catalog: pg_control.h (r1.51 - r1.52) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/pg_control.h?r1=1.51r2=1.52) This is a change in pg_control layout and requires a bump to the pg_control version number (and hence forced initdb's all round). OK I think it was quite premature to commit this when the design is still under active discussion --- you may be forcing two rounds of initdb on testers, when maybe only one or none would be enough. Especially when you appear to be in the minority about what the design should be. No intention of doing that. This change allows people to see what the dependency actually is once the bug has been fixed. Change needs to start from here, not from where we were before. -- Simon Riggs www.2ndQuadrant.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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas robertmh...@gmail.com writes:
Well, I think the real hole is that turning archive_mode=on results in
WAL never being deleted unless it's successfully archived.
Hm, good point. And at least in principle you could have SR setups
that don't care about having a backing WAL archive.
But we might be able to handle that like this:
wal_mode={standby|archive|crash} # or whatever
wal_segments_always=integer # keep this many segments always, for
SR - like current wal_keep_segments
wal_segments_unarchived=integer # keep this many unarchived
segments, -1 for infinite
max_wal_senders=integer # same as now
archive_command=string# same as now
So we always retain wal_segments_always segments, but if we have
trouble with archiving we'll retain up to wal_segments_archived.
And when that limit is reached, what happens? Panic shutdown?
Silently drop unarchived data? Neither one sounds very good.
I think either you want your WAL archived or you don't. Archive
if it's convenient doesn't sound like a useful operating mode.
So maybe we do indeed need to keep archive_mode as a separate toggle.
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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, 2010-04-23 at 23:10 +0300, Heikki Linnakangas wrote: So my proposal would be: wal_mode=crash/archive/standby OK, I agree to change in this area. I definitely don't like the word crash, which may scare and confuse people. I don't think I would ever set any parameter to a word like crash since it isn't clear whether it allows that event or protects against it. Also, I don't like the word standby on its own, since that has already been used for Warm Standby for some time, which corresponds to the archive setting and is therefore confusing. How about something like wal_additional_info = none | archive | connect Then its easy to understand that things slow down when you request additional information in the WAL, and also clear that Hot Standby requires slightly more info on top of that. It's also clear that this has nothing at all to do with the delivery mechanism. -- Simon Riggs www.2ndQuadrant.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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Simon Riggs si...@2ndquadrant.com writes: No intention of doing that. This change allows people to see what the dependency actually is once the bug has been fixed. Change needs to start from here, not from where we were before. Well, actually, now that I've looked at the patch I think it's starting from a fundamentally wrong position anyway. Checkpoint records are a completely wrong mechanism for transmitting this data to slaves, because a checkpoint is emitted *after* we do something, not *before* we do it. In particular it's ludicrous to be looking at shutdown checkpoints to try to determine whether the subsequent WAL will meet the slave's requirements. There's no connection at all between what the GUC state was at shutdown and what it might be after starting again. A design that might work is (1) store the active value of wal_mode in pg_control (but NOT as part of the last-checkpoint-record image). (2) invent a new WAL record type that is transmitted when we change wal_mode. Then, slaves could check whether the master's wal_mode is high enough by looking at pg_control when they start plus any wal_mode_change records they come across. If we did this then we could get rid of those WAL record types that were added to signify that information had been omitted from WAL at specific times. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Simon Riggs si...@2ndquadrant.com wrote: On Fri, 2010-04-23 at 23:10 +0300, Heikki Linnakangas wrote: So my proposal would be: wal_mode=crash/archive/standby I definitely don't like the word crash, which may scare and confuse people. I don't think I would ever set any parameter to a word like crash since it isn't clear whether it allows that event or protects against it. Also, I don't like the word standby on its own, since that has already been used for Warm Standby for some time, which corresponds to the archive setting and is therefore confusing. Good points, although recovery instead of crash would seem to cover that. How about something like wal_additional_info = none | archive | connect Then its easy to understand that things slow down when you request additional information in the WAL, and also clear that Hot Standby requires slightly more info on top of that. It's also clear that this has nothing at all to do with the delivery mechanism. Are we going to support running warm standby through SR? If so, connect seems confusing for the level to support hot standby. Perhaps live?: wal_mode=recovery/archive/live -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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Simon Riggs si...@2ndquadrant.com writes: How about something like wal_additional_info = none | archive | connect connect seems like a completely inappropriate word here. It is not obviously related to HS slaves and it could be taken to refer to ordinary database connections (sessions). Personally I agree with your objection to crash but not with the objection to standby. Maybe this would be appropriate: wal_mode = minimal | archive | hot_standby 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Fri, Apr 23, 2010 at 4:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: No intention of doing that. This change allows people to see what the dependency actually is once the bug has been fixed. Change needs to start from here, not from where we were before. Well, actually, now that I've looked at the patch I think it's starting from a fundamentally wrong position anyway. Checkpoint records are a completely wrong mechanism for transmitting this data to slaves, because a checkpoint is emitted *after* we do something, not *before* we do it. In particular it's ludicrous to be looking at shutdown checkpoints to try to determine whether the subsequent WAL will meet the slave's requirements. There's no connection at all between what the GUC state was at shutdown and what it might be after starting again. A design that might work is (1) store the active value of wal_mode in pg_control (but NOT as part of the last-checkpoint-record image). (2) invent a new WAL record type that is transmitted when we change wal_mode. Well, right now wal_mode would only be able to be changed at server restart. Eventually we might relax that, but I think there are some restrictions on how we can do it - like maybe needing to wait until all the transactions running at the time the change was decided on have committed, or, well, I'm not sure. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 4:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: How about something like wal_additional_info = none | archive | connect connect seems like a completely inappropriate word here. It is not obviously related to HS slaves and it could be taken to refer to ordinary database connections (sessions). Personally I agree with your objection to crash but not with the objection to standby. Maybe this would be appropriate: wal_mode = minimal | archive | hot_standby I was thinking maybe log_shipping instead of archive, since we're conflating the technology (log shipping) with the technology used to implement it (archiving or streaming). Possible crash_recovery rather than just crash where you have mimimal. I don't love hot_standby either but it might be the least of evils. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, 2010-04-23 at 16:50 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: How about something like wal_additional_info = none | archive | connect connect seems like a completely inappropriate word here. It is not obviously related to HS slaves and it could be taken to refer to ordinary database connections (sessions). Personally I agree with your objection to crash but not with the objection to standby. Maybe this would be appropriate: wal_mode = minimal | archive | hot_standby Sounds good, I'll go for that. In my understanding this means that archive_mode does completely and the max_wal_senders does not affect WAL contents? Does that mean that wal_mode can be SIGHUP now? It would be good. I think this is how to do that: At the start of every WAL-avoiding operation we could take a copy of wal_mode for the server and store in MyProc-wal_mode. At transaction start we would set that to not set. We could then make pg_start_backup() wait for all transactions with wal_mode set to complete before we continue. -- Simon Riggs www.2ndQuadrant.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] psql: Add setting to make '+' on \d implicit
On Apr 23, 2010, at 11:28 AM, Tom Lane wrote: Ross J. Reedstrom reeds...@rice.edu writes: On Fri, Apr 23, 2010 at 10:58:40AM -0500, Terry Brown wrote: So the proposal would be: \d+ does as it has always done, no change \d- (new) always behaves like 'old' \d \d acts as 'old' \d or as \d+, depending on the setting of 'verbose_describe', set via \pset. Hmm, what about all the other + variants? Would this setting affect them? I'd suggest perhaps it should. If we were to do something like that, it would certainly have to affect every \d variant that has a + option. Which is probably not a very good idea --- in many cases that's a very expensive/verbose option. I can't get excited about this proposal, personally. What the OP actually seemed to care about was database object comments. I could see somebody who relied heavily on comments wanting his comments to be included in all display commands, even without the + option. Maybe a configuration variable along the lines of 'always_show_comments' would be a better design. Or more generally an ability to set aliases via .psqlrc similar to \set, maybe? \alias \d- = \d \alias \d = \d+ Cheers, Steve -- 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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Fri, 2010-04-23 at 16:44 -0400, Tom Lane wrote: There's no connection at all between what the GUC state was at shutdown and what it might be after starting again. A design that might work is (1) store the active value of wal_mode in pg_control (but NOT as part of the last-checkpoint-record image). (2) invent a new WAL record type that is transmitted when we change wal_mode. Then, slaves could check whether the master's wal_mode is high enough by looking at pg_control when they start plus any wal_mode_change records they come across. Seems OK on standby side. On the primary there are some other points, mentioned on other thread as to when we can change wal_mode. If we did this then we could get rid of those WAL record types that were added to signify that information had been omitted from WAL at specific times. Please. -- Simon Riggs www.2ndQuadrant.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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, 2010-04-23 at 17:29 -0400, Robert Haas wrote: Possible crash_recovery rather than just crash where you have mimimal. Minimal is good because it is a performance option also, which is an aspect crash_recovery does not convey. (Plus we use the word crash again, which is too scary to use) -- Simon Riggs www.2ndQuadrant.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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 4:10 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: So my proposal would be: wal_mode=crash/archive/standby archive_mode=on/off # if on, wal_mode must be = 'archive' archive_command='command' max_wal_senders=integer # if 0, wal_mode must be = 'archive' As a general design comment, I think we should avoid still having an archive_mode GUC but having it do something different. If we're going to change the semantics, we should also change the name, maybe to archiving. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, 2010-04-23 at 17:43 -0400, Robert Haas wrote: On Fri, Apr 23, 2010 at 4:10 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: So my proposal would be: wal_mode=crash/archive/standby archive_mode=on/off # if on, wal_mode must be = 'archive' archive_command='command' max_wal_senders=integer # if 0, wal_mode must be = 'archive' As a general design comment, I think we should avoid still having an archive_mode GUC but having it do something different. If we're going to change the semantics, we should also change the name, maybe to archiving. We don't need *both* wal_mode and archive_mode, since archive_mode exists only to ensure that full WAL is written even when archive_command = '' momentarily. Should do this wal_mode=crash/archive/standby archive_command='command' max_wal_senders=integer # if 0, wal_mode must be = 'archive' and make wal_mode SIGHUP -- Simon Riggs www.2ndQuadrant.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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 4:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: A design that might work is (1) store the active value of wal_mode in pg_control (but NOT as part of the last-checkpoint-record image). (2) invent a new WAL record type that is transmitted when we change wal_mode. Well, right now wal_mode would only be able to be changed at server restart. Right, but slave servers won't find out about the change until the first checkpoint after the start. Which is Too Late. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 4:10 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: So my proposal would be: wal_mode=crash/archive/standby archive_mode=on/off # if on, wal_mode must be = 'archive' archive_command='command' max_wal_senders=integer # if 0, wal_mode must be = 'archive' As a general design comment, I think we should avoid still having an archive_mode GUC but having it do something different. If we're going to change the semantics, we should also change the name, maybe to archiving. Agreed on the general point, but AFAICS that proposal keeps the meaning of archive_mode the same as it was. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Simon Riggs si...@2ndquadrant.com writes: In my understanding this means that archive_mode does completely and the max_wal_senders does not affect WAL contents? I think we'd concluded that we have to keep archive_mode as a separate boolean. (Or we could use Heikki's idea of a max number of unarchived segments to hold onto, but I maintain that there are only two useful values and so we might as well leave it as the existing boolean.) Does that mean that wal_mode can be SIGHUP now? It would be good. I think this is how to do that: At the start of every WAL-avoiding operation we could take a copy of wal_mode for the server and store in MyProc-wal_mode. At transaction start we would set that to not set. We could then make pg_start_backup() wait for all transactions with wal_mode set to complete before we continue. I think that there are probably more synchronization issues than that, and in any case now is not the time to be trying to implement that feature. Maybe we can make it work in 9.1. 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] testing HS/SR - 1 vs 2 performance
On Fri, 2010-04-23 at 11:32 -0400, Robert Haas wrote: 99% of transactions happen in similar times between primary and standby, everything dragged down by rare but severe spikes. We're looking for something that would delay something that normally takes 0.1ms into something that takes 100ms, yet does eventually return. That looks like a severe resource contention issue. Wow. Good detective work. While we haven't fully established the source of those problems, I am now happy that these test results don't present any reason to avoid commiting the main patch tested by Erik (not the smaller additional one I sent). I expect to commit that on Sunday. -- Simon Riggs www.2ndQuadrant.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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Simon Riggs si...@2ndquadrant.com writes: We don't need *both* wal_mode and archive_mode, since archive_mode exists only to ensure that full WAL is written even when archive_command = '' momentarily. No, you missed the point of the upthread discussion: archive_mode controls whether to start the archiver *and whether to hold onto not-yet-archived segments*. We could maybe finesse the first point but it's much harder to deal with the latter. The only workable alternative I can see to keeping archive_mode is to tell people to set archive_command to something like /usr/bin/true ... which is not simpler, especially not on Windows. 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] [COMMITTERS] pgsql: Add missing optimizer hooks for function cost and number of rows.
sri...@postgresql.org (Simon Riggs) writes: Log Message: --- Add missing optimizer hooks for function cost and number of rows. Closely follow design of other optimizer hooks: if hook exists retrieve value from plugin; if still not set then get from cache. What exactly are we doing adding new features without discussion (or documentation, or known use cases) at this stage of the release cycle? 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] [COMMITTERS] pgsql: Add missing optimizer hooks for function cost and number of rows.
On Fri, Apr 23, 2010 at 6:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: sri...@postgresql.org (Simon Riggs) writes: Log Message: --- Add missing optimizer hooks for function cost and number of rows. Closely follow design of other optimizer hooks: if hook exists retrieve value from plugin; if still not set then get from cache. What exactly are we doing adding new features without discussion (or documentation, or known use cases) at this stage of the release cycle? I'm confused, too. It seems like there have been a LOT of patches this week that were not posted to or discussed on -hackers. I thought that was not within the ground rules. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 6:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 4:10 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: So my proposal would be: wal_mode=crash/archive/standby archive_mode=on/off # if on, wal_mode must be = 'archive' archive_command='command' max_wal_senders=integer # if 0, wal_mode must be = 'archive' As a general design comment, I think we should avoid still having an archive_mode GUC but having it do something different. If we're going to change the semantics, we should also change the name, maybe to archiving. Agreed on the general point, but AFAICS that proposal keeps the meaning of archive_mode the same as it was. Well, clearly it doesn't. Someone who thinks they can simply turn archive_mode=on and set archive_command is going to be sadly disappointed. Before, archive_mode arguably switched the server between two modes, with a whole set of behaviors associated with it: type of WAL logging, whether the archive runs, number of WAL segments maintained. Under any of the proposals on the table (other than, just adjust the error message, which still seems tempting) it's new purview will be more limited. ...Robert ...Robert -- 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] testing HS/SR - 1 vs 2 performance
On Fri, Apr 23, 2010 at 6:39 PM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, 2010-04-23 at 11:32 -0400, Robert Haas wrote: 99% of transactions happen in similar times between primary and standby, everything dragged down by rare but severe spikes. We're looking for something that would delay something that normally takes 0.1ms into something that takes 100ms, yet does eventually return. That looks like a severe resource contention issue. Wow. Good detective work. While we haven't fully established the source of those problems, I am now happy that these test results don't present any reason to avoid commiting the main patch tested by Erik (not the smaller additional one I sent). I expect to commit that on Sunday. Both Heikki and I objected to that patch. And apparently it doesn't fix the problem, either. So, -1 from me. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 6:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Agreed on the general point, but AFAICS that proposal keeps the meaning of archive_mode the same as it was. Well, clearly it doesn't. Someone who thinks they can simply turn archive_mode=on and set archive_command is going to be sadly disappointed. Well, there is another variable that they'll have to adjust as well, but ISTM that archive_mode still does what it did before, ie, determine whether we attempt to archive WAL segments. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 7:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 6:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Agreed on the general point, but AFAICS that proposal keeps the meaning of archive_mode the same as it was. Well, clearly it doesn't. Someone who thinks they can simply turn archive_mode=on and set archive_command is going to be sadly disappointed. Well, there is another variable that they'll have to adjust as well, but ISTM that archive_mode still does what it did before, ie, determine whether we attempt to archive WAL segments. But it doesn't do EVERYTHING that it did before. Changing the name would make that a lot more clear. Of course I just work here. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 7:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, there is another variable that they'll have to adjust as well, but ISTM that archive_mode still does what it did before, ie, determine whether we attempt to archive WAL segments. But it doesn't do EVERYTHING that it did before. Changing the name would make that a lot more clear. Of course I just work here. I think from the user's point of view it does what it did before. The fact that the actual content of WAL changed was an implementation detail that users weren't aware of. Now that we have two interacting features that affect WAL contents, it's getting too hard to hide that from users --- but I see no need to rename archive_mode. 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] testing HS/SR - 1 vs 2 performance
On Sat, April 24, 2010 00:39, Simon Riggs wrote: On Fri, 2010-04-23 at 11:32 -0400, Robert Haas wrote: 99% of transactions happen in similar times between primary and standby, everything dragged down by rare but severe spikes. We're looking for something that would delay something that normally takes 0.1ms into something that takes 100ms, yet does eventually return. That looks like a severe resource contention issue. Wow. Good detective work. While we haven't fully established the source of those problems, I am now happy that these test results don't present any reason to avoid commiting the main patch tested by Erik (not the smaller additional one I sent). I expect to commit that on Sunday. yes, that (main) patch seems to have largely closed the gap between primary and standby; here are some results from a lower scale (10): scale: 10 clients: 10, 20, 40, 60, 90 for each: 4x primary, 4x standby: (6565=primary, 6566=standby) - scale: 10 clients: 10 tps = 27624.339871 pgbench -p 6565 -n -S -c 10 -T 900 -j 1 scale: 10 clients: 10 tps = 27604.261750 pgbench -p 6565 -n -S -c 10 -T 900 -j 1 scale: 10 clients: 10 tps = 28015.093466 pgbench -p 6565 -n -S -c 10 -T 900 -j 1 scale: 10 clients: 10 tps = 28422.561280 pgbench -p 6565 -n -S -c 10 -T 900 -j 1 scale: 10 clients: 10 tps = 27254.806526 pgbench -p 6566 -n -S -c 10 -T 900 -j 1 scale: 10 clients: 10 tps = 27686.470866 pgbench -p 6566 -n -S -c 10 -T 900 -j 1 scale: 10 clients: 10 tps = 28078.904035 pgbench -p 6566 -n -S -c 10 -T 900 -j 1 scale: 10 clients: 10 tps = 27101.622337 pgbench -p 6566 -n -S -c 10 -T 900 -j 1 - scale: 10 clients: 20 tps = 23106.795587 pgbench -p 6565 -n -S -c 20 -T 900 -j 1 scale: 10 clients: 20 tps = 23101.681155 pgbench -p 6565 -n -S -c 20 -T 900 -j 1 scale: 10 clients: 20 tps = 22893.364004 pgbench -p 6565 -n -S -c 20 -T 900 -j 1 scale: 10 clients: 20 tps = 23038.577109 pgbench -p 6565 -n -S -c 20 -T 900 -j 1 scale: 10 clients: 20 tps = 22903.578552 pgbench -p 6566 -n -S -c 20 -T 900 -j 1 scale: 10 clients: 20 tps = 22970.691946 pgbench -p 6566 -n -S -c 20 -T 900 -j 1 scale: 10 clients: 20 tps = 22999.473318 pgbench -p 6566 -n -S -c 20 -T 900 -j 1 scale: 10 clients: 20 tps = 22884.854749 pgbench -p 6566 -n -S -c 20 -T 900 -j 1 - scale: 10 clients: 40 tps = 23522.499429 pgbench -p 6565 -n -S -c 40 -T 900 -j 1 scale: 10 clients: 40 tps = 23611.319191 pgbench -p 6565 -n -S -c 40 -T 900 -j 1 scale: 10 clients: 40 tps = 23616.905302 pgbench -p 6565 -n -S -c 40 -T 900 -j 1 scale: 10 clients: 40 tps = 23572.213990 pgbench -p 6565 -n -S -c 40 -T 900 -j 1 scale: 10 clients: 40 tps = 23714.721220 pgbench -p 6566 -n -S -c 40 -T 900 -j 1 scale: 10 clients: 40 tps = 23711.781175 pgbench -p 6566 -n -S -c 40 -T 900 -j 1 scale: 10 clients: 40 tps = 23691.867023 pgbench -p 6566 -n -S -c 40 -T 900 -j 1 scale: 10 clients: 40 tps = 23691.699231 pgbench -p 6566 -n -S -c 40 -T 900 -j 1 - scale: 10 clients: 60 tps = 21987.497095 pgbench -p 6565 -n -S -c 60 -T 900 -j 1 scale: 10 clients: 60 tps = 21950.344204 pgbench -p 6565 -n -S -c 60 -T 900 -j 1 scale: 10 clients: 60 tps = 22006.461447 pgbench -p 6565 -n -S -c 60 -T 900 -j 1 scale: 10 clients: 60 tps = 21824.071303 pgbench -p 6565 -n -S -c 60 -T 900 -j 1 scale: 10 clients: 60 tps = 22149.415231 pgbench -p 6566 -n -S -c 60 -T 900 -j 1 scale: 10 clients: 60 tps = 22211.064402 pgbench -p 6566 -n -S -c 60 -T 900 -j 1 scale: 10 clients: 60 tps = 22164.238081 pgbench -p 6566 -n -S -c 60 -T 900 -j 1 scale: 10 clients: 60 tps = 22174.585736 pgbench -p 6566 -n -S -c 60 -T 900 -j 1 - scale: 10 clients: 90 tps = 18751.213002 pgbench -p 6565 -n -S -c 90 -T 900 -j 1 scale: 10 clients: 90 tps = 18757.115811 pgbench -p 6565 -n -S -c 90 -T 900 -j 1 scale: 10 clients: 90 tps = 18692.942329 pgbench -p 6565 -n -S -c 90 -T 900 -j 1 scale: 10 clients: 90 tps = 18765.390154 pgbench -p 6565 -n -S -c 90 -T 900 -j 1 scale: 10 clients: 90 tps = 18929.462104 pgbench -p 6566 -n -S -c 90 -T 900 -j 1 scale: 10 clients: 90 tps = 18999.851184 pgbench -p 6566 -n -S -c 90 -T 900 -j 1 scale: 10 clients: 90 tps = 18972.321607 pgbench -p 6566 -n -S -c 90 -T 900 -j 1 scale: 10 clients: 90 tps = 18924.058827 pgbench -p 6566 -n -S -c 90 -T 900 -j 1 The higher scales still have that other standby-slowness. It may be caching effects (as Mark Kirkwood suggested): the idea being that the primary data is pre-cached because of the initial create; standby data needs to be first-time-read from disk. Does that make sense? I will try to confirm this. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 7:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 7:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, there is another variable that they'll have to adjust as well, but ISTM that archive_mode still does what it did before, ie, determine whether we attempt to archive WAL segments. But it doesn't do EVERYTHING that it did before. Changing the name would make that a lot more clear. Of course I just work here. I think from the user's point of view it does what it did before. The fact that the actual content of WAL changed was an implementation detail that users weren't aware of. Now that we have two interacting features that affect WAL contents, it's getting too hard to hide that from users --- but I see no need to rename archive_mode. Well, when people use their same settings that they used for 8.4 and it doesn't work, you can field those reports... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 7:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think from the user's point of view it does what it did before. The fact that the actual content of WAL changed was an implementation detail that users weren't aware of. Now that we have two interacting features that affect WAL contents, it's getting too hard to hide that from users --- but I see no need to rename archive_mode. Well, when people use their same settings that they used for 8.4 and it doesn't work, you can field those reports... I would expect that they'll get an error message that makes it clear enough what to do ;-). In any case, changing the name is hardly going to fix things so that 8.4 settings will still work, so why are you giving that case as an argument for it? 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 7:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 7:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think from the user's point of view it does what it did before. The fact that the actual content of WAL changed was an implementation detail that users weren't aware of. Now that we have two interacting features that affect WAL contents, it's getting too hard to hide that from users --- but I see no need to rename archive_mode. Well, when people use their same settings that they used for 8.4 and it doesn't work, you can field those reports... I would expect that they'll get an error message that makes it clear enough what to do ;-). In any case, changing the name is hardly going to fix things so that 8.4 settings will still work, so why are you giving that case as an argument for it? Principle of obvious breakage. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 7:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: I would expect that they'll get an error message that makes it clear enough what to do ;-). In any case, changing the name is hardly going to fix things so that 8.4 settings will still work, so why are you giving that case as an argument for it? Principle of obvious breakage. And? If we do it by adding the new variable while not renaming archive_mode, then I'd expect an 8.4 configuration to yield an error along the lines of ERROR: invalid combination of configuration parameters HINT: To turn on archive_mode, you must set wal_mode to archive or hot_standby. (precise wording open to debate, but clearly we can do at least this well) whereas if we rename archive_mode, it's unlikely we can do better than ERROR: unrecognized parameter archive_mode Do you really think the second one is going to make any user happier than the first? 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] vcregress.bat check triggered Heap error in the Debug version of win32 build
Xiong He wrote: When I build the debug version of PostgreSQL (latest code), I found that I always failed to run the vcregress.bat check. It always pops up the following error. The release version can pass the test without any error. Anyone met such error? [graphic deleted] Please don't send pictures to the list. Send text of error messages. You will need to dig down much more to find out exactly what triggers the error. You haven't even told us which regression set you are running, let alone which test it fails on. 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 8:00 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 7:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: I would expect that they'll get an error message that makes it clear enough what to do ;-). In any case, changing the name is hardly going to fix things so that 8.4 settings will still work, so why are you giving that case as an argument for it? Principle of obvious breakage. And? If we do it by adding the new variable while not renaming archive_mode, then I'd expect an 8.4 configuration to yield an error along the lines of ERROR: invalid combination of configuration parameters HINT: To turn on archive_mode, you must set wal_mode to archive or hot_standby. (precise wording open to debate, but clearly we can do at least this well) whereas if we rename archive_mode, it's unlikely we can do better than ERROR: unrecognized parameter archive_mode Do you really think the second one is going to make any user happier than the first? OK, good point. I overlooked the fact that we could cross-check the parameter settings on the master - I was imagining the error showing up on the standby. Guess I'm a little slow today... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Re: [HACKERS] vcregress.bat check triggered Heap error in the Debugversion of win32 build
Thanks. In my test, it fails during the vcregress.bat check startup. It's a Debug Assertion Error. File: dbgheap.c Line: 1252. E:\learn\db_research\postgreSQL\cvsroot\pgsql.latest\src\tools\msvcvcregress.ba t check No test can run. I used VS2005 for the build. Xiong He 2010-04-24 08:15:04 发件人： Andrew Dunstan 发送时间： 2010-04-24 08:09:32 收件人： Xiong He 抄送： pgsql-hackers 主题： Re: [HACKERS] vcregress.bat check triggered Heap error in the Debugversion of win32 build Xiong He wrote: When I build the debug version of PostgreSQL (latest code), I found that I always failed to run the vcregress.bat check. It always pops up the following error. The release version can pass the test without any error. Anyone met such error? [graphic deleted] Please don't send pictures to the list. Send text of error messages. You will need to dig down much more to find out exactly what triggers the error. You haven't even told us which regression set you are running, let alone which test it fails on. cheers andrew
Re: [HACKERS] vcregress.bat check triggered Heap error in the Debugversion of win32 build
Xiong He wrote: Thanks. In my test, it fails during the vcregress.bat check startup. It's a Debug Assertion Error. File: dbgheap.c Line: 1252. E:\learn\db_research\postgreSQL\cvsroot\pgsql.latest\src\tools\msvcvcregress.ba t check No test can run. I used VS2005 for the build. Please do not top-answer. You still need to tell us more about exactly what it is doing when it fails. What program is it that is failing (and don't answer vcregress.bat)? 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
[HACKERS] global temporary tables
A couple of recent threads made got me thinking again about the idea of global temporary tables. There seem to be two principal issues: 1. What is a global temporary table? 2. How could we implement that? Despite rereading the idea: global temp tables thread from April 2009 in some detail, I was not able to get a clear understanding of (1). What I *think* it is supposed to mean is that the table is a permanent object which is globally visible - that is, it's part of some non-temp schema like public or $user and it's column definitions etc. are visible to all backends - and it's not automatically removed on commit, backend exit, etc. - but the *contents* of the table are temporary and backend-local, so that each new backend initially sees it as empty and can then insert, update, and delete data independently of what any other backend does. As to (2), my thought is that perhaps we could implement this by instantiating a separate relfilenode for the relation for each backend which accesses it. relfilenode would be 0 in pg_class, as it is for mapped relations, but every time a backend touched the rel, we'd allocate a relfilenode and associated the oid of the temp table to it using some kind of backend-local storage - actually similar to what the relmapper code does, except without the complexity of ever actually having to persist the value; and perhaps using a hash table rather than an array, since the number of mapped rels that a backend can need to deal with is rather more limited than the number of temp tables it might want to use. Thoughts? ...Robert -- 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] global temporary tables
Robert Haas robertmh...@gmail.com writes: A couple of recent threads made got me thinking again about the idea of global temporary tables. There seem to be two principal issues: 1. What is a global temporary table? 2. How could we implement that? Despite rereading the idea: global temp tables thread from April 2009 in some detail, I was not able to get a clear understanding of (1). I believe that the spec's distinction between global and local temp tables has to do with whether they are visible across module boundaries. Since we haven't implemented modules, that distinction is meaningless to us. In the spec, *both* types of temp tables have the property that the definition (schema) of the table is global across all sessions, and only the content of the table is session-local. This arrangement clearly is useful for some applications, but so is our current definition wherein different sessions can have different schemas for the same temp table name. So eventually it'd be good to support both. But the GLOBAL/LOCAL TEMP TABLE distinction is something entirely different. PG's behavior does not correspond to either of those. Your idea of using the relmapper layer to instantiate copies of temp tables is an interesting one. It's only a small piece of the puzzle though. In particular, what you described would result in the table having the same OID in all sessions, even though the relfilenodes are different --- amd since locking is done on the basis of OID, that's probably *not* what we want. It would be much better for performance if the different sessions' versions of the table were independently lockable. I also kind of wonder what is supposed to happen if someone DROPs or ALTERs the temp table definition ... 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] global temporary tables
On Fri, Apr 23, 2010 at 11:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: A couple of recent threads made got me thinking again about the idea of global temporary tables. There seem to be two principal issues: 1. What is a global temporary table? 2. How could we implement that? Despite rereading the idea: global temp tables thread from April 2009 in some detail, I was not able to get a clear understanding of (1). I believe that the spec's distinction between global and local temp tables has to do with whether they are visible across module boundaries. Since we haven't implemented modules, that distinction is meaningless to us. In the spec, *both* types of temp tables have the property that the definition (schema) of the table is global across all sessions, and only the content of the table is session-local. This arrangement clearly is useful for some applications, but so is our current definition wherein different sessions can have different schemas for the same temp table name. So eventually it'd be good to support both. But the GLOBAL/LOCAL TEMP TABLE distinction is something entirely different. PG's behavior does not correspond to either of those. I don't really care what we call it, although I find the GLOBAL name convenient and descriptive. Your idea of using the relmapper layer to instantiate copies of temp tables is an interesting one. It's only a small piece of the puzzle though. In particular, what you described would result in the table having the same OID in all sessions, even though the relfilenodes are different --- amd since locking is done on the basis of OID, that's probably *not* what we want. It would be much better for performance if the different sessions' versions of the table were independently lockable. Well, it depends on what operation we're talking about. For operations that involve only the table contents, yeah, we'd like to lock the versions independently. But for this sort of thing: I also kind of wonder what is supposed to happen if someone DROPs or ALTERs the temp table definition ... ...not so much. Here you REALLY want a DROP attempt to acquire an AccessExclusiveLock that will conflict with any outstanding AccessShareLocks. Similarly, you're only going to be able to modify the schema for the relation if it's not otherwise in use. ...Robert -- 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] global temporary tables
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 11:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: I also kind of wonder what is supposed to happen if someone DROPs or ALTERs the temp table definition ... ...not so much. Here you REALLY want a DROP attempt to acquire an AccessExclusiveLock that will conflict with any outstanding AccessShareLocks. Similarly, you're only going to be able to modify the schema for the relation if it's not otherwise in use. I think you're presuming the answer to the question. We could also view the desired behavior as being that each session clones the temp table definition at some instant (eg, first use). The approach that you're assuming seems fraught with large downsides: in particular, implementing ALTER TABLE would be a mess. The would-be alterer would need access to the physical copies of all sessions, which throws out not only the assumption that the relmapper entries can be private data, but all of the access optimizations we currently have in the local buffer manager. Not to mention the coding mess of having to repeat the ALTER operation for each of N copies, some of which might disappear while we're trying to do it (or if they don't, we're blocking backends from exiting). I don't even know how you'd do the ALTER over again N times if you only have one set of catalog entries describing the N copies. 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] CIText and pattern_ops
Is there any particular reason why the citext module doesn't have citext_pattern_ops operator family? Specifically, I wish to index for this type of query: ... WHERE citext_column LIKE 'Foo%'; This, of course, is equivalent to ILIKE 'Foo%' which does not appear to be indexable without using a functional index ( lower(citext_column) ). -- 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] global temporary tables
On Fri, Apr 23, 2010 at 11:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Apr 23, 2010 at 11:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: I also kind of wonder what is supposed to happen if someone DROPs or ALTERs the temp table definition ... ...not so much. Here you REALLY want a DROP attempt to acquire an AccessExclusiveLock that will conflict with any outstanding AccessShareLocks. Similarly, you're only going to be able to modify the schema for the relation if it's not otherwise in use. I think you're presuming the answer to the question. We could also view the desired behavior as being that each session clones the temp table definition at some instant (eg, first use). The approach that you're assuming seems fraught with large downsides: in particular, implementing ALTER TABLE would be a mess. The would-be alterer would need access to the physical copies of all sessions, which throws out not only the assumption that the relmapper entries can be private data, but all of the access optimizations we currently have in the local buffer manager. I agree, that would be pretty unfortunate, althogh maybe it's the only way to make it work. It's not what I had in mind. I was thinking that the would-be ALTERER could just take an AccessExclusiveLock, but now that I think about it that doesn't work, since a backend could have the table unlocked between transactions but still have private contents in it. :-( Not to mention the coding mess of having to repeat the ALTER operation for each of N copies, some of which might disappear while we're trying to do it (or if they don't, we're blocking backends from exiting). I don't even know how you'd do the ALTER over again N times if you only have one set of catalog entries describing the N copies. Well, if you clone the table, that just pushes the problem around. When I run ALTER TABLE on one of these thingamabobs, does it modify my clone? The original? Both? If it modifies my clone, how do we modify the original? If it modifies the original, won't I be rather surprised to find my clone unaffected? If it modifies both, how do we avoid complete havoc if the original has since been modified (perhaps incompatibly, perhaps not) by some other backend doing its own ALTER TABLE? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
