Re: [HACKERS] Analyzing foreign tables memory problems
On Mon, May 14, 2012 at 09:21:20AM +0200, Albe Laurenz wrote: Noah Misch wrote: Just thinking out loud, we could provide an extern Datum AnalyzeWideValue; and direct FDW authors to use that particular datum. It could look like a toasted datum of external size WIDTH_THRESHOLD+1 but bear va_toastrelid == InvalidOid. Then, if future code movement leads us to actually examine one of these values, we'll get an early, hard error. That would be very convenient indeed. Even better would be a function extern Datum createAnalyzeWideValue(integer width) so that row width calculations could be more accurate. Yes; good call. -- 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] Why do we still have commit_delay and commit_siblings?
On Mon, May 14, 2012 at 10:24 AM, Peter Geoghegan pe...@2ndquadrant.com wrote: On 14 May 2012 15:09, Robert Haas robertmh...@gmail.com wrote: I don't have a strong opinion about that, and welcome discussion. But I'm always going to be opposed to adding or removing things on the basis of what we didn't test. The subject of the thread is Why do we still have commit_delay and commit_siblings?. I don't believe that anyone asserted that we should remove the settings without some amount of due-diligence testing. Simon said that thorough testing on many types of hardware was not practical, which, considering that commit_delay is probably hardly ever (never?) used in production, I'd have to agree with. With all due respect, for someone that doesn't have a strong opinion on the efficacy of commit_delay in 9.2, you seemed to have a strong opinion on the standard that would have to be met in order to deprecate it. I think we all could stand to give each other the benefit of the doubt more. I am a bit perplexed by this thread. It appeared to me that you were saying that these settings could not ever possibly be useful and therefore we ought to remove them right now, and I said we should gather some data first, because the current behavior, without using these settings, appears to be about 50% of the optimum. If you agree we need to gather some data first, then apparently we don't disagree about anything, but that wasn't mentioned in your original email or in Simon's reply to my post. There are certainly many instances where we've made changes quickly without gathering much data first, so I feel that it wasn't ridiculous on my part to think that might be the proposal on the table. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why do we still have commit_delay and commit_siblings?
On Mon, May 14, 2012 at 8:42 AM, Jeff Janes jeff.ja...@gmail.com wrote: On Sun, May 13, 2012 at 11:07 PM, Simon Riggs si...@2ndquadrant.com wrote: Keeping a parameter without any clue as to whether it has benefit is just wasting people's time. We don't ADD parameters based on supposition, why should we avoid removing parameters that have no measured benefit? Using pgbench -T30 -c 2 -j 2 on a 2 core laptop system, with a scale that fits in shared_buffers: --commit-delay=2000 --commit-siblings=0 tps = 162.924783 (excluding connections establishing) --commit-delay=0 --commit-siblings=0 tps = 89.237578 (excluding connections establishing) These results are astonishingly good, and I can't reproduce them. I spent some time this morning messing around with this on the IBM POWER7 machine and my MacBook Pro. Neither of these have exceptionally good fsync performance, and in particular the MacBook Pro has really, really bad fsync performance. On the IBM POWER7 machine, I'm not able to demonstrate any performance improvement at all from fiddling with commit delay. I tried tests at 2 clients, 32 clients, and 80 clients, and I'm getting... nothing. No improvement at all. Zip. I tried a few different settings for commit_delay, too. On the MacBook Pro, with wal_sync_method=obscenely_slow^Wfsync_writethrough, I can't demonstrate any improvement at 2 clients, but at 80 clients I observe a roughly 1.8x performance gain (~50 tps - ~90 tps). Whether this is anything to get excited about is another matter, since you'd hope to get more than 1.1 transactions per second no matter how slow fsync is. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why do we still have commit_delay and commit_siblings?
On 15 May 2012 15:17, Robert Haas robertmh...@gmail.com wrote: On Mon, May 14, 2012 at 10:24 AM, Peter Geoghegan pe...@2ndquadrant.com wrote: On 14 May 2012 15:09, Robert Haas robertmh...@gmail.com wrote: I don't have a strong opinion about that, and welcome discussion. But I'm always going to be opposed to adding or removing things on the basis of what we didn't test. The subject of the thread is Why do we still have commit_delay and commit_siblings?. I don't believe that anyone asserted that we should remove the settings without some amount of due-diligence testing. Simon said that thorough testing on many types of hardware was not practical, which, considering that commit_delay is probably hardly ever (never?) used in production, I'd have to agree with. With all due respect, for someone that doesn't have a strong opinion on the efficacy of commit_delay in 9.2, you seemed to have a strong opinion on the standard that would have to be met in order to deprecate it. I think we all could stand to give each other the benefit of the doubt more. I am a bit perplexed by this thread. It appeared to me that you were saying that these settings could not ever possibly be useful and therefore we ought to remove them right now, and I said we should gather some data first, because the current behavior, without using these settings, appears to be about 50% of the optimum. If you agree we need to gather some data first, then apparently we don't disagree about anything, but that wasn't mentioned in your original email or in Simon's reply to my post. There are certainly many instances where we've made changes quickly without gathering much data first, so I feel that it wasn't ridiculous on my part to think that might be the proposal on the table. We don't have enough evidence to show that there are any gains to be had here in a real world situation. Few if any benchmarks show anything of value, and if they do it is because they are too-regular and not very real. My comments were appropriate: if I tried to suggest we add commit_delay as a feature, it would be rejected and rightly so. Some caution in its removal is appropriate, but since we've been discussing it since before your first post to hackers, probably even before mine, I figure that is way past long enough. I beg you to prove me wrong and demonstrate the value of commit_delay, since we will all benefit from that. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Strange issues with 9.2 pg_basebackup replication
Jim, I didn't get as far as running any tests, actually. All I did was try to set up 3 servers in cascading replication. Then I tried shutting down master-master and promoting master-replica. That's it. - Original Message - On May 13, 2012, at 3:08 PM, Josh Berkus wrote: More issues: promoting intermediate standby breaks replication. To be a bit blunt here, has anyone tested cascading replication *at all* before this? Josh, do you have scripts that you're using to do this testing? If so can you post them somewhere? AFAIK we don't have any regression tests for all this replication stuff, but ISTM that we need some... -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] WalSndWakeup() and synchronous_commit=off
On Monday, May 14, 2012 07:55:32 PM Fujii Masao wrote: On Mon, May 14, 2012 at 6:32 PM, Andres Freund and...@2ndquadrant.com wrote: On Friday, May 11, 2012 08:45:23 PM Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: Its the only place though which knows whether its actually sensible to wakeup the walsender. We could make it return whether it wrote anything and do the wakeup at the callers. I count 4 different callsites which would be an annoying duplication but I don't really see anything better right now. Another point here is that XLogWrite is not only normally called with the lock held, but inside a critical section. I see no reason to take the risk of doing signal sending inside critical sections. BTW, a depressingly large fraction of the existing calls to WalSndWakeup are also inside critical sections, generally for no good reason that I can see. For example, in EndPrepare(), why was the call placed where it is and not down beside SyncRepWaitForLSN? Hm. While I see no real problem moving it out of the lock I don't really see a way to cleanly outside critical sections everywhere. The impact of doing so seems to be rather big to me. The only externally visible place where it actually is known whether we write out data and thus do the wakeup is XLogInsert, XLogFlush and XLogBackgroundFlush. The first two of those are routinely called inside a critical section. So what about moving the existing calls of WalSndWakeup() out of a critical section and adding new call of WalSndWakeup() into XLogBackgroundFlush()? Then all WalSndWakeup()s are called outside a critical section and after releasing WALWriteLock. I attached the patch. Imo its simply not sensible to call WalSndWakeup at *any* of the current locations. They simply don't have the necessary information. They will wakeup too often (because with concurrency commits often won't require additional wal writes) and too seldom (because a flush caused by XLogInsert wont cause a wakeup). Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why do we still have commit_delay and commit_siblings?
On Tue, May 15, 2012 at 7:47 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, May 14, 2012 at 8:42 AM, Jeff Janes jeff.ja...@gmail.com wrote: On Sun, May 13, 2012 at 11:07 PM, Simon Riggs si...@2ndquadrant.com wrote: Keeping a parameter without any clue as to whether it has benefit is just wasting people's time. We don't ADD parameters based on supposition, why should we avoid removing parameters that have no measured benefit? Using pgbench -T30 -c 2 -j 2 on a 2 core laptop system, with a scale that fits in shared_buffers: --commit-delay=2000 --commit-siblings=0 tps = 162.924783 (excluding connections establishing) --commit-delay=0 --commit-siblings=0 tps = 89.237578 (excluding connections establishing) These results are astonishingly good, and I can't reproduce them. I spent some time this morning messing around with this on the IBM POWER7 machine and my MacBook Pro. Neither of these have exceptionally good fsync performance, and in particular the MacBook Pro has really, really bad fsync performance. Did you also set --commit-siblings=0? Are you using -i -s 1, and therefor serializing on the sole entry in pgbench_branches? Could you instrument the call to pg_usleep and see if it is actually being called? (Or, simply strace-ing the process would probably tell you that). On the IBM POWER7 machine, I'm not able to demonstrate any performance improvement at all from fiddling with commit delay. I tried tests at 2 clients, 32 clients, and 80 clients, and I'm getting... nothing. No improvement at all. Zip. I tried a few different settings for commit_delay, too. On the MacBook Pro, with wal_sync_method=obscenely_slow^Wfsync_writethrough, If one of the methods gives sync times that matches the rotational speed of your disks, that is the one that I would use. If the sync is artificially slow because something in the kernel is gummed up, maybe whatever the problem is also interferes with other things. (Although I wouldn't expect it to, that is just a theory). I have a 5400 rpm drive, so 89 single client TPS is almost exactly to be expected. I can't demonstrate any improvement at 2 clients, but at 80 clients I observe a roughly 1.8x performance gain (~50 tps - ~90 tps). Whether this is anything to get excited about is another matter, since you'd hope to get more than 1.1 transactions per second no matter how slow fsync is. Yeah, you've got something much worse going on there than commit_delay can solve. With the improved group-commit code, or whatever we are calling it, if you get 50tps single-client then at 80 clients you should get almost 40x50 tps, assuming the scale is large enough to not block on row locks. Cheers, Jeff -- 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] Why do we still have commit_delay and commit_siblings?
On Tue, May 15, 2012 at 11:05 AM, Simon Riggs si...@2ndquadrant.com wrote: My comments were appropriate: if I tried to suggest we add commit_delay as a feature, it would be rejected and rightly so. Fair point. Some caution in its removal is appropriate, but since we've been discussing it since before your first post to hackers, probably even before mine, I figure that is way past long enough. I beg you to prove me wrong and demonstrate the value of commit_delay, since we will all benefit from that. Interestingly, we seem to have had this same argument 7 years ago, with different participants. http://archives.postgresql.org/pgsql-hackers/2005-06/msg01463.php What's really bothering me here is that a LOT has changed in 9.2. Besides the LWLockAcquireOrWait stuff, which improves fsync scalability quite a bit, we have also whacked around the WAL writer behavior somewhat. It's not necessarily the case that things which didn't work well before still won't work well now. On the other hand, I'll grant you that our current implementation of commit_delay is pretty boneheaded. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bug in to_tsquery(), and fix
A customer reported a mysterious crash, with the backtrace showing it to
come from several levels down deep in the infix() function, called by
tsqueryout(). I was eventually able to reproduce this and hunt down the
bug, using the same tsquery string as the customer.
The bug was actually in to_tsquery(), and resulted in a corrupt
operand string being stored in a tsquery Datum. In a nutshell, in
to_tsquery_byid(), we're using memcpy() to copy to a possibly
overlapping region of data. The obvious fix is to use memmove() instead,
attached.
This is pretty hairy code, it's hard to resist doing some more whacking
around. For example the infix() function would be a lot simpler if it
used a StringInfo instead of implementing a resizeable string of its
own. But I'll leave that alone for now, given that the bug was in fact
not in that function.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
diff --git a/src/backend/tsearch/to_tsany.c b/src/backend/tsearch/to_tsany.c
index 5284c9c..9c5b3a3 100644
--- a/src/backend/tsearch/to_tsany.c
+++ b/src/backend/tsearch/to_tsany.c
@@ -342,6 +342,7 @@ to_tsquery_byid(PG_FUNCTION_ARGS)
if (query-size == 0)
PG_RETURN_TSQUERY(query);
+ /* clean out any stopword placeholders from the tree */
res = clean_fakeval(GETQUERY(query), len);
if (!res)
{
@@ -351,6 +352,10 @@ to_tsquery_byid(PG_FUNCTION_ARGS)
}
memcpy((void *) GETQUERY(query), (void *) res, len * sizeof(QueryItem));
+ /*
+ * Removing the stopword placeholders might've resulted in fewer
+ * QueryItems. If so, move the operands up accordingly.
+ */
if (len != query-size)
{
char *oldoperand = GETOPERAND(query);
@@ -359,7 +364,7 @@ to_tsquery_byid(PG_FUNCTION_ARGS)
Assert(len query-size);
query-size = len;
- memcpy((void *) GETOPERAND(query), oldoperand, VARSIZE(query) - (oldoperand - (char *) query));
+ memmove((void *) GETOPERAND(query), oldoperand, VARSIZE(query) - (oldoperand - (char *) query));
SET_VARSIZE(query, COMPUTESIZE(len, lenoperand));
}
--
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] Strange issues with 9.2 pg_basebackup replication
On 13 May 2012 16:08, Josh Berkus j...@agliodbs.com wrote: More issues: promoting intermediate standby breaks replication. To be a bit blunt here, has anyone tested cascading replication *at all* before this? So, same setup as previous message. 1. Shut down master-master. 2. pg_ctl promote master-replica 3. replication breaks. error message on replica-replica: FATAL: timeline 2 of the primary does not match recovery target timeline 1 4. No amount of adjustment on replica-replica will get it replicating again. Note that replica-replica was configured with: recovery_target_timeline = 'latest' I can recreate this issue, although the docs say: Promoting a cascading standby terminates the immediate downstream replication connections which it serves. This is because the timeline becomes different between standbys, and they can no longer continue replication. The affected standby(s) may reconnect to reestablish streaming replication. (http://www.postgresql.org/docs/9.2/static/warm-standby.html#CASCADING-REPLICATION) However, this isn't true when I restart the standby. I've been informed that this should work fine if a WAL archive has been configured (which should be used anyway). But one new problem I appear to have is that once I set up archiving and restart, then try pg_basebackup, it gets stuck and never shows any progress. If I terminate pg_basebackup in this state and attempt to restart it more times than max_wal_senders, it can no longer run, as pg_basebackup didn't disconnect the stream, so ends up using all senders. And these show up in pg_stat_replication. I have a theory that if archiving is enabled, restart postgres then generate some WAL to the point there is a file or two in the archive, pg_basebackup can't stream anything. Once I restart the server, it's fine and continues as normal. This has the same symptoms of the pg_basebackup from running standby with streaming issue. Steps to recreate: 1) initdb new cluster 2) start new cluster 3) make archive dir (in my case, /tmp/arch) and set the following: wal_level = hot_standby max_wal_senders = 3 archive_mode = on archive_command = 'cp %p /tmp/arch/%f' 4) Set pg_hba.conf to allow streaming replication connections 5) Restart the cluster 6) Create a table and insert a few hundred thousand rows until /tmp/arch shows some WAL files 7) Run: pg_basebackup -x stream -D s1 -Pv This actually does finish eventually but it appears to need some encouragement by generating some WAL and issuing a checkpoint: thom@swift:~/Development$ time pg_basebackup -x stream -D s1 -Pv xlog start point: 0/420 pg_basebackup: starting background WAL receiver 53951/53951 kB (100%), 1/1 tablespace xlog end point: 0/5DE15E0 pg_basebackup: waiting for background process to finish streaming... pg_basebackup: base backup completed real2m37.456s user0m0.016s sys 0m0.724s If I terminate pg_basebackup and restart it without generating additional WAL, it doesn't appear to release the streaming connection ever (or not within my patience limit of a few minutes). And I can't free these connections without restarting the cluster. But once I get the standby up and running and acting as a hot standby, and ignore the current issue with it getting stuck creating a standby from a standby, I still get the mismatched timeline issue, so the addition of WAL archiving didn't appear to resolve this for me. -- Thom -- 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] Why do we still have commit_delay and commit_siblings?
On Tue, May 15, 2012 at 12:07 PM, Jeff Janes jeff.ja...@gmail.com wrote: These results are astonishingly good, and I can't reproduce them. I spent some time this morning messing around with this on the IBM POWER7 machine and my MacBook Pro. Neither of these have exceptionally good fsync performance, and in particular the MacBook Pro has really, really bad fsync performance. Did you also set --commit-siblings=0? No. Are you using -i -s 1, and therefor serializing on the sole entry in pgbench_branches? No. Scale factor is 10. Could you instrument the call to pg_usleep and see if it is actually being called? (Or, simply strace-ing the process would probably tell you that). I'm pretty sure it is. It was on the IBM POWER7 machine, anyway, because the pg_usleep calls showed up in the perf call graph I took. On the IBM POWER7 machine, I'm not able to demonstrate any performance improvement at all from fiddling with commit delay. I tried tests at 2 clients, 32 clients, and 80 clients, and I'm getting... nothing. No improvement at all. Zip. I tried a few different settings for commit_delay, too. On the MacBook Pro, with wal_sync_method=obscenely_slow^Wfsync_writethrough, If one of the methods gives sync times that matches the rotational speed of your disks, that is the one that I would use. If the sync is artificially slow because something in the kernel is gummed up, maybe whatever the problem is also interferes with other things. (Although I wouldn't expect it to, that is just a theory). I have a 5400 rpm drive, so 89 single client TPS is almost exactly to be expected. I can't demonstrate any improvement at 2 clients, but at 80 clients I observe a roughly 1.8x performance gain (~50 tps - ~90 tps). Whether this is anything to get excited about is another matter, since you'd hope to get more than 1.1 transactions per second no matter how slow fsync is. Yeah, you've got something much worse going on there than commit_delay can solve. With the improved group-commit code, or whatever we are calling it, if you get 50tps single-client then at 80 clients you should get almost 40x50 tps, assuming the scale is large enough to not block on row locks. I am definitely not getting that. Let's try this again. Increase scale factor to 40. Decrease commit_siblings to 0. With 10 clients, and commit_delay=5000, I get 109-132 tps. With commit_delay=0, I get 58-71 tps. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Draft release notes complete
On Mon, May 14, 2012 at 3:21 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: The mere ability to notice that an XLogFlush() call is unnecessary and fastpath out could be argued to be an aboriginal group commit, predating even commit_delay, as could skipping duplicate fsync() requests in XLogWrite(), which I think Jeff pointed out, but I don't think anyone actually takes this position. Well, Tom appears to have to have he'd implemented group commit in 2002. http://archives.postgresql.org/pgsql-hackers/2002-10/msg00331.php More accurately, he seems to have thought that group commit was already there, and he'd improved it. So saying that we're getting it for the first time ten years later seems pretty odd to me. I don't deny that the new feature is a significant improvement under the right circumstances. But I still maintain it's an improvement of something that was already there, rather than something new. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Draft release notes complete
On Fri, May 11, 2012 at 11:44 AM, Andrew Dunstan and...@dunslane.net wrote: On 05/11/2012 05:32 AM, Magnus Hagander wrote: But in the interest of actually being productive - what *is* the usecase for needing a 5 minute turnaround time? I don't buy the check what a patch looks like, because that should be done *before* the commit, not after - so it's best verified by a local docs build anyway (which will also be faster). I'm sure we can put something in with a pretty quick turnaround again without too much strain on the system, but it does, as I mentioned before, require decoupling it from the buildfarm which means it's not just tweaking a config file. If it's of any use to you I have made some adjustments to the buildfarm code which would let you do *just* the docs build (and dist make if you want). It would still pull from git, and only do anything if there's a (relevant) change. So using that to set up a machine that would run every few minutes might work. Of course, building the docs can itself be fairly compute intensive, so you still might not want to run every few minutes if that's a limiting factor. that would definitely be useful. Compute intensive is not really a problem, we can easily shape the box on that (and I think we already do). Do you have some details of what to do and how to do it to use that, so Stefan can set it up for us ? ;) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Draft release notes complete
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Bruce wrote: In summary, names on release note items potentially have the following beneficial effects: * Encouraging new developers/reviewers * Encouraging long-established developers * Showing appreciation to developers * Assisting future employment for developers * Helping developers get future funding * Assigning responsibility for features * Showing Postgres's increased developer base The only important ones are: * Assisting future employment for developers * Helping developers get future funding * Assigning responsibility for features * Assigning blame for feature problems That last one is not very important either. If there is a bug, you report it. The original author may or may not handle it. A better way to state some of the above is: * Quick cross-reference of a person to a feature. If I claim to have written ON_ERROR_ROLLBACK, nobody should have to scroll back through git logs to confirm or deny. (For that matter, we should do everything possible to prevent anyone from using git log, especially non-developers, for any meta-information.) +1 to keep things they way they are. If you were significantly invested in [re]writing the patch, you get a name. Reviewers, I love you dearly, but you don't belong next to the patch. Group them all at the bottom if we must have them there. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201205151259 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk+yi3cACgkQvJuQZxSWSsiAcACfYC1HCxbMor/c0EJF6kn+XKc9 kOcAoMn0vnOJLa8+HVz5oWKAZxjkOtQi =eiUT -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Draft release notes complete
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I'd vote for starting a separate thread to solicit people's opinions on whether we need names in the release notes. Is there anybody on -hackers who would be offended, or would have a harder time persuading $BOSS to let them spend time on Postgres if they weren't mentioned in the release notes? There'd still be a policy of crediting people in commit messages of course, but it's not clear to me whether the release note mentions are important to anybody. Looks like this is mostly answered, and we obviously don't need another thread, but the answer to the above is yes. Release notes are very public, plain text, easy to read, very archived and searchable. Commit messages might as well be a black hole as far as visibility to anyone not a developer in the project. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201205151301 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk+yjFEACgkQvJuQZxSWSsi3gACgmikPzvshZPftTuEdmcB8/Ply 4vMAn1DxvG6hntfxJzWRDdPyWlP5X7WM =pUbl -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Strange issues with 9.2 pg_basebackup replication
On Wed, May 16, 2012 at 1:36 AM, Thom Brown t...@linux.com wrote: However, this isn't true when I restart the standby. I've been informed that this should work fine if a WAL archive has been configured (which should be used anyway). The WAL archive should be shared by master-replica and replica-replica, and recovery_target_timeline should be set to latest in replica-replica. If you configure that way, replica-replica would successfully reconnect to master-replica with no need to restart it. But one new problem I appear to have is that once I set up archiving and restart, then try pg_basebackup, it gets stuck and never shows any progress. If I terminate pg_basebackup in this state and attempt to restart it more times than max_wal_senders, it can no longer run, as pg_basebackup didn't disconnect the stream, so ends up using all senders. And these show up in pg_stat_replication. I have a theory that if archiving is enabled, restart postgres then generate some WAL to the point there is a file or two in the archive, pg_basebackup can't stream anything. Once I restart the server, it's fine and continues as normal. This has the same symptoms of the pg_basebackup from running standby with streaming issue. This seems to be caused by spread checkpoint which is requested by pg_basebackup. IOW, this looks a normal behavior rather than a bug or an issue. What if you specify -c fast option in pg_basebackup? Regards, -- Fujii Masao -- 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] Strange issues with 9.2 pg_basebackup replication
On Mon, May 14, 2012 at 4:04 AM, Josh Berkus j...@agliodbs.com wrote: Doing some beta testing, managed to produce this issue using the daily snapshot from Tuesday: 1. Created master server, loaded it with a couple dummy databases. 2. Created standby server. 3. Did pg_basebackup -x stream on standby server 4. Started standby server. 5. Realized I'd forgotten to create a recovery.conf. Shut down the standby server, wrote a recovery.conf, and restarted it. Before restarting it, you need to do pg_basebackup and make a base backup onto the standby again. Since you started the standby without recovery.conf, a series of WAL in the standby has gotten inconsistent with that in the master. So you need a fresh backup to restart the standby. Regards, -- Fujii Masao -- 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] Strange issues with 9.2 pg_basebackup replication
On 15 May 2012 13:15, Fujii Masao masao.fu...@gmail.com wrote: On Wed, May 16, 2012 at 1:36 AM, Thom Brown t...@linux.com wrote: However, this isn't true when I restart the standby. I've been informed that this should work fine if a WAL archive has been configured (which should be used anyway). The WAL archive should be shared by master-replica and replica-replica, and recovery_target_timeline should be set to latest in replica-replica. If you configure that way, replica-replica would successfully reconnect to master-replica with no need to restart it. I had set the archive_command on the primary, then produced a base backup which would have copied the archive settings, but I also added a corresponding recovery_command setting, so everything was pointing at the same archive. But one new problem I appear to have is that once I set up archiving and restart, then try pg_basebackup, it gets stuck and never shows any progress. If I terminate pg_basebackup in this state and attempt to restart it more times than max_wal_senders, it can no longer run, as pg_basebackup didn't disconnect the stream, so ends up using all senders. And these show up in pg_stat_replication. I have a theory that if archiving is enabled, restart postgres then generate some WAL to the point there is a file or two in the archive, pg_basebackup can't stream anything. Once I restart the server, it's fine and continues as normal. This has the same symptoms of the pg_basebackup from running standby with streaming issue. This seems to be caused by spread checkpoint which is requested by pg_basebackup. IOW, this looks a normal behavior rather than a bug or an issue. What if you specify -c fast option in pg_basebackup? Yes, it works fine with that option. And it appears this isn't to do with there being an archive as I get the same symptoms without setting one up. But in any case, shouldn't the replication connection be terminated when pg_basebackup is terminated? -- Thom -- 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] Strange issues with 9.2 pg_basebackup replication
Fujii, Wait, are you telling me that we *still* can't remaster from streaming replication? Why wasn't that fixed in 9.2? And: if we still have to ship logs, what's the point in even having cascading replication? - Original Message - On Wed, May 16, 2012 at 1:36 AM, Thom Brown t...@linux.com wrote: However, this isn't true when I restart the standby. I've been informed that this should work fine if a WAL archive has been configured (which should be used anyway). The WAL archive should be shared by master-replica and replica-replica, and recovery_target_timeline should be set to latest in replica-replica. If you configure that way, replica-replica would successfully reconnect to master-replica with no need to restart it. But one new problem I appear to have is that once I set up archiving and restart, then try pg_basebackup, it gets stuck and never shows any progress. If I terminate pg_basebackup in this state and attempt to restart it more times than max_wal_senders, it can no longer run, as pg_basebackup didn't disconnect the stream, so ends up using all senders. And these show up in pg_stat_replication. I have a theory that if archiving is enabled, restart postgres then generate some WAL to the point there is a file or two in the archive, pg_basebackup can't stream anything. Once I restart the server, it's fine and continues as normal. This has the same symptoms of the pg_basebackup from running standby with streaming issue. This seems to be caused by spread checkpoint which is requested by pg_basebackup. IOW, this looks a normal behavior rather than a bug or an issue. What if you specify -c fast option in pg_basebackup? Regards, -- Fujii Masao -- 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] Strange issues with 9.2 pg_basebackup replication
Before restarting it, you need to do pg_basebackup and make a base backup onto the standby again. Since you started the standby without recovery.conf, a series of WAL in the standby has gotten inconsistent with that in the master. So you need a fresh backup to restart the standby. You're not understanding the bug. The problem is that the standby came up and reported that it was replicating OK, when clearly it wasn't. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] transformations between types and languages
Here is a draft design for the transforms feature, which I'd like to work on. The purpose of this is to allow adapting types to languages. The most popular case is to enable converting hstore to something useful like a dict or a hash in PL/Python or PL/Perl, respectively. In general, the type and the language don't know of each other, and neither need to be in core. Maybe you want to adapt PostGIS types to pygeometry objects in PL/Python (made up example, but you get the idea). What we basically need is a system catalog like this: type -- the type to which this applies, e.g. hstore lang -- e.g. plperl fromsql -- function to convert from SQL to language-specific tosql -- function to convert from language-specific to SQL fromsql takes one argument of the respective type and returns internal. tosql is the other way around. It's the responsibility of the language handler to look up this information and use it. The internal argument or return value will be something specific to the language implementation and will likely be under the memory management of the language handler. The reason I call this transforms is that there is an SQL feature called transforms. This was originally intended to allow adapting user-defined types to client side languages, so it's about the same concept. If there are concerns about overloading a standard feature like that, we can change the name, but I fear there aren't going to be that many handy synonyms available in the transform/translate/convert space. Syntax examples: CREATE LANGUAGE plpythonu ...; CREATE TYPE hstore ...; CREATE FUNCTION hstore_to_plpython(hstore) RETURNS internal ...; CREATE FUNCTION plpython_to_hstore(internal) RETURNS hstore ...; The actual implementation of these will look like the existing PLyObject_ToBytea() and all those, except that instead of a hard-coded switch statement, they will be selected through a system catalog. CREATE TRANSFORM FOR hstore LANGUAGE plpythonu ( FROM SQL WITH hstore_to_plpython, TO SQL WITH plpython_to_hstore); If you have a plfoo/plfoou pair, you need to issue two statements like that. But maybe we could offer the syntax LANGUAGE plperl, plperlu. In practice, you would wrap this up in an extension which would depend on hstore and plpython. -- 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] transformations between types and languages
Peter Eisentraut wrote: Here is a draft design for the transforms feature, which I'd like to work on. The purpose of this is to allow adapting types to languages. The most popular case is to enable converting hstore to something useful like a dict or a hash in PL/Python or PL/Perl, respectively. In general, the type and the language don't know of each other, and neither need to be in core. Maybe you want to adapt PostGIS types to pygeometry objects in PL/Python (made up example, but you get the idea). This is a good idea in principle. I expect we should be able to use the same syntax both for system-defined types and user-defined types. I would expect, though, that in some common cases one can't avoid say having to call hstore_to_plpython() directly, in order to disambiguate, and we may want to provide terser syntax for using the desired TRANSFORM. For example, if we have a Perl 5 hash, that could reasonably either map to an hstore or to a tuple. Or a Perl 5 string with false utf8 flag could map to either a character string or a byte string. Or a Perl 5 empty string (result of 1==0) could map to the false Boolean. Or a Perl 5 string that looks like a number could map to either a character string or some kind of numeric. Or a Perl 5 number 1 could map to either a numeric 1 (result of 1==1) or the true Boolean. Or we have to tighten the conversion rules so that things which are sometimes equivalent and sometimes not on one side have different interpretations in the transform. Ideally the feature would also work not only for interfacing with PLs but also with client languages, since conceptually its alike but just differing on who calls who. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] could not open relation with OID errors after promoting the standby to master
I've switched servers yesterday night and the previous slave is now the master. This is 9.0.6 (originally) / 9.0.7 (now) on Linux. Now I'm seeing a bunch of ERROR: could not open relation with OID 1990987633 STATEMENT: create temp table seen_files (fileid integer) Interestingly enough, 90% of these happen with create temp table statements, but I also see them with regular read-only select statements, but I'd say it's at most 10% of these. Some reports for this error message suggested running reindex, so I've run reindex table and also vacuum full on all system catalogs (just for good measure) but that didn't help much. Restarting the cluster didn't help either. If it matters, I have not promoted the master with a trigger file but restarted it after deleting recovery.conf. Everything else appears to be running fine but since it's a) annoying and b) not very comforting, I might dump and restore tomorrow night. I added a sleep() after this error message so that I could attach a debugger and grab a stack trace: (gdb) bt #0 0x003eb509a170 in __nanosleep_nocancel () from /lib64/libc.so.6 #1 0x003eb5099fc4 in sleep () from /lib64/libc.so.6 #2 0x0046375c in relation_open (relationId=1990987633, lockmode=value optimized out) at heapam.c:906 #3 0x004b26e6 in heap_drop_with_catalog (relid=1990987633) at heap.c:1567 #4 0x004ace01 in doDeletion (object=0x62dfbc8, depRel=0x2b9ea756f6a8) at dependency.c:1046 #5 deleteOneObject (object=0x62dfbc8, depRel=0x2b9ea756f6a8) at dependency.c:1004 #6 0x004aeb00 in deleteWhatDependsOn (object=value optimized out, showNotices=0 '\000') at dependency.c:401 #7 0x004b6e90 in RemoveTempRelations () at namespace.c:3234 #8 InitTempTableNamespace () at namespace.c:3066 #9 0x004b70b5 in RangeVarGetCreationNamespace (newRelation=value optimized out) at namespace.c:351 #10 0x00536e13 in DefineRelation (stmt=0x638da00, relkind=114 'r', ownerId=0) at tablecmds.c:409 #11 0x0063c15f in standard_ProcessUtility (parsetree=value optimized out, queryString=0x6298460 create temp table temp_defs_table_20068 (symhash char(32), symbolid integer), params=0x0, isTopLevel=value optimized out, dest=value optimized out, completionTag=value optimized out) at utility.c:512 #12 0x00637d81 in PortalRunUtility (portal=0x61ec860, utilityStmt=0x62992d0, isTopLevel=1 '\001', dest=0x6299670, completionTag=0x7a9c1c30 ) at pquery.c:1191 #13 0x006384de in PortalRunMulti (portal=0x61ec860, isTopLevel=1 '\001', dest=0x6299670, altdest=0x6299670, completionTag=0x7a9c1c30 ) at pquery.c:1296 #14 0x00639732 in PortalRun (portal=0x61ec860, count=9223372036854775807, isTopLevel=1 '\001', dest=0x6299670, altdest=0x6299670, completionTag=0x7a9c1c30 ) at pquery.c:822 #15 0x006359e5 in exec_simple_query (argc=value optimized out, argv=value optimized out, username=value optimized out) at postgres.c:1058 #16 PostgresMain (argc=value optimized out, argv=value optimized out, username=value optimized out) at postgres.c:3936 #17 0x005f95d6 in BackendRun () at postmaster.c:3560 #18 BackendStartup () at postmaster.c:3247 #19 ServerLoop () at postmaster.c:1431 #20 0x005fb934 in PostmasterMain (argc=value optimized out, argv=value optimized out) at postmaster.c:1092 #21 0x0058de36 in main (argc=3, argv=0x61dd1d0) at main.c:188 (gdb) Any idea? It looks suspicious that it calls into RemoveTempRelations() from InitTempNamespace() thereby removing the table it is about to create? -- 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] Draft release notes complete
On 15 May 2012 17:51, Robert Haas robertmh...@gmail.com wrote: More accurately, he seems to have thought that group commit was already there, and he'd improved it. So saying that we're getting it for the first time ten years later seems pretty odd to me. Maybe it's odd, and maybe it's inconsistent with earlier terminology that was privately used, and maybe I'm just plain wrong. Nevertheless, it is my position that: 1. Group commit isn't a rigorously defined term, which sure is apparent by our confusion. So even if you're right, that's only by virtue of a precedent being set regarding the terminology, for which there could just as easily have been another precedent without there having to be substantive differences to the code, had things happened to go that way. 2. Group commit is associated in people's minds with results that look much like the results we can now show. It is my understanding that we couldn't show improvements like this before. So while group commit isn't rigorously defined, people have a certain vague set of expectations about it that we previously basically failed to meet. For these reasons, it may be timely and appropriate, from a purely advocacy point-of-view, to call our new group commit group commit in release notes and documentation, and announce it as a new feature. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
