Re: [HACKERS] PostgreSQL Developer meeting minutes up
2009/6/7 Markus Wanner mar...@bluegap.ch: However, there's no special whitespace treatment. Nor anything remotely as clever as nearby variable renaming. There's no such magic, the developer still needs to tell the tool what he wants. If I understand correctly, nearby variable renaming refers to changes to the few lines surrounding the changes-to-be-merged. There is certainly supposed to be an advantage relative to diff/patch here: as all changes leading to both versions are known (up to some common ancestor), git doesn't need context lines to recognize the position in the file that is supposed to receive the updates. Example: Original file: a b c Random other changes later (a and c are updated to incorporate nearby variable renaming or somesuch): extra line a' b c' (Note that the extra line is important, because if the line numbers stay the same and the lines-to-update are exactly the same, patch could just ignore the context lines.) An update to line b yields: extra line a' b' c' This change would not be diff/patch-mergeable to the original file, because the context lines a' and c' wouldn't be found. Git is smarter than this and doesn't need the context lines; rather it uses the full history to determine that the change to line 3 becomes a change to line 2 in the original file. It therefore merges this change to yield: a b' c Disclaimer: I don't use git, but I assume that this is how all systems that are smarter than diff/patch work. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
On 7 Jun 2009, at 03:27, Bruce Momjian wrote: Grzegorz Jaskiewicz wrote: On 6 Jun 2009, at 19:50, Tom Lane wrote: We have five days. I don't think we need testing, per se. The first step should be to diff the 8.3 and 8.4 versions of the various contrib .sql.in files and determine what changed. Any module whose .sql.in file hasn't changed is definitely safe. I can tell you already that dblink has changed, and I had to drop it before migration, otherwise everything went fine. Migration of 57GB data in place took about 1 minute. The good news is that the INSTALL instructions where clear enough for the tester to understand that uninstalling dblink from the old cluster and reinstalling it in the new cluster would work. Yes, but I forgot about one database, and had to do it all over again, luckily I first tested it without --link... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
On Sun, Jun 7, 2009 at 12:11 AM, Tom Lanet...@sss.pgh.pa.us wrote: * intarray has removed its internal @ and @ operators. As I was mentioning the other day, it might be the best thing to just revert that change anyway, until we can get a better handle on the behavior of GIN for empty arrays. +1. So we've definitely got some work here. If we do nothing, several of these issues are going to end up with CVE numbers. I think it's becoming increasingly clear that pg_migrator is not for the faint of heart; in fact, I think we should hedge references to it with words like experimental. If we set an expectation that this tool has the same level of maturity and reliability that people associate with PostgreSQL in general, we are going to have a lot of disappointed users. Just to recall the history, the first pgfoundry commit messages for this tool were on February 9th, three months after the start of the final CommitFest and feature freeze for 8.4. Since then, development has proceeded at an amazingly rapid pace, but there's only so much you can do in four months, especially when it's too late to rearchitect previously-committed 8.4 features to play more nicely with the new feature being added. It seems to me that if we keep plugging at this problem, 8.4-8.5 migration has the potential to be considerably smoother than 8.3 to 8.4 migration (and, yes, a good module facility will help), but it wouldn't be surprising to me if we're well into 9.x territory before we really get all of the issues hammered out. I don't think even a major feature like Hot Standby has the far-reaching implications on how the system needs to be designed that upgrade-in-place does. So while I agree with Tom that we should fix as many of these issues as we reasonably well can for 8.4, I also agree with Bruce that a lot of this comes down to setting appropriate expectations for our users: this is a potentially useful tool, especially for users with huge databases, but it's new, and it's not perfect, and use with caution. ...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] pg_migrator issue with contrib
Robert Haas robertmh...@gmail.com writes: I think it's becoming increasingly clear that pg_migrator is not for the faint of heart; in fact, I think we should hedge references to it with words like experimental. Probably ... Just to recall the history, the first pgfoundry commit messages for this tool were on February 9th, three months after the start of the final CommitFest and feature freeze for 8.4. Since then, development has proceeded at an amazingly rapid pace, but there's only so much you can do in four months, ... but the above is a *complete* misrepresentation of the thing's history (apparently you failed to look in the Attic?). EDB have been using previous versions of this tool for some years, and the basic premise is the same as contrib/pg_upgrade that existed as far back as 7.1/7.2 timeframe. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
On Jun 7, 2009, at 10:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I think it's becoming increasingly clear that pg_migrator is not for the faint of heart; in fact, I think we should hedge references to it with words like experimental. Probably ... Just to recall the history, the first pgfoundry commit messages for this tool were on February 9th, three months after the start of the final CommitFest and feature freeze for 8.4. Since then, development has proceeded at an amazingly rapid pace, but there's only so much you can do in four months, ... but the above is a *complete* misrepresentation of the thing's history (apparently you failed to look in the Attic?). EDB have been using previous versions of this tool for some years, and the basic premise is the same as contrib/pg_upgrade that existed as far back as 7.1/7.2 timeframe. I did know that EDB had been using the tool for a while, but I admit I'm not familiar with the whole history. I had the impression that we'd gotten a lot more serious about really making this rock solid since Bruce took it in February. But maybe that's not the case? ...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] Revisiting default_statistics_target
On Sat, 2009-06-06 at 12:06 -0700, Josh Berkus wrote: On the DL380 GB system, where I'm using a lot more drives the Jignesh, I see a performance change of under 5%. 15651.14 notpm vs 16333.32 notpm. And this is after a bit of tuning, not sure how much the out of the box experience changes on this system. Well, Jignesh and I identified two things which we think are special about DBT2: (1) it uses C stored procedures, and (2) we don't think it uses prepared plans. If there is a performance regression it is almost certain to effect planning; obviously if there is no planning there is no effect. But not everybody can or wants to use prepared plans for a variety of reasons. I've been unable to reproduce any performance drop using pgbench. I think we aren't likely to measure the effects accurately, since we are unable to measure planning times with any sensible level of accuracy. Increased planning times may not directly translate into performance drops on many tests, though can still represent a problem for many people. If we can specify an accurate test mechanism, we may get some reasonable information for decision making. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: I think it's becoming increasingly clear that pg_migrator is not for the faint of heart; in fact, I think we should hedge references to it with words like experimental. Probably ... I'm with Robert on that one - while pg_migrator is extremely inmportant for us to go forward. I really think we need to tag it experimental for this release at least. pg_migrator is complex and we are still discovering new issues every day I don't think rushing it as _THE_ solution will do any good for our reputation. A lot of our code(as software in general) took years to mature and pg_migrator is likely not an exception. Just to recall the history, the first pgfoundry commit messages for this tool were on February 9th, three months after the start of the final CommitFest and feature freeze for 8.4. Since then, development has proceeded at an amazingly rapid pace, but there's only so much you can do in four months, ... but the above is a *complete* misrepresentation of the thing's history (apparently you failed to look in the Attic?). EDB have been using previous versions of this tool for some years, and the basic premise is the same as contrib/pg_upgrade that existed as far back as 7.1/7.2 timeframe. well - how much field exposure has pg_migrator/edb_migrator seen actually? given the large number of breaks your database bugs and issues that got found during the last few weeks I have a hard time imaging that edb really used the current code for their customers. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Revisiting default_statistics_target
Simon Riggs si...@2ndquadrant.com writes: On Sat, 2009-06-06 at 12:06 -0700, Josh Berkus wrote: Well, Jignesh and I identified two things which we think are special about DBT2: (1) it uses C stored procedures, and (2) we don't think it uses prepared plans. If there is a performance regression it is almost certain to effect planning; obviously if there is no planning there is no effect. Yeah; on a benchmark that relies mainly on prepared plans, it'd be unlikely you'd notice any effect at all, even from a very significant increase in planning time. My guess about the C stored procedure bit, if it really has any relevance, is that it reduces the other overhead of the test case enough that planning time becomes more significant than it would be in other benchmark scenarios. In any case, what we seem to have here is evidence that there are some cases where the new default value of default_statistics_target is too high and you can get a benefit by lowering it. I'm not sure we should panic about that. Default values ought to be compromises. If people only ever change the default in one direction then it's probably not a very good compromise. We know that there are applications for which 100 is still too low, so maybe now we have got the pain spread out roughly evenly... 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] Revisiting default_statistics_target
On Sun, Jun 7, 2009 at 5:13 PM, Tom Lanet...@sss.pgh.pa.us wrote: In any case, what we seem to have here is evidence that there are some cases where the new default value of default_statistics_target is too high and you can get a benefit by lowering it. I'm not sure we should panic about that. Default values ought to be compromises. If people only ever change the default in one direction then it's probably not a very good compromise. We know that there are applications for which 100 is still too low, so maybe now we have got the pain spread out roughly evenly... I would be nice to get oprofile working and see results for various target sizes. I've had trouble with oprofile myself. I think some machines have bogus rtc hardware or something related that prevents it from working properly. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
I wrote: * pageinspect has changed the ABI of get_raw_page() in a way that will likely make it dump core if the function definition is migrated from an old DB. This needs to be fixed. [ and similarly for some other contrib modules ] After thinking about this some more, I think that there is a fairly simple coding rule we can adopt to prevent post-migration crashes of the sort I'm worrying about above. That is: * If you change the ABI of a C-language function, change its C name. This will ensure that if someone tries to migrate the old function definition from an old database, they will get a pg_migrator failure, or at worst a clean runtime failure when they attempt to use the old definition. They won't get a core dump or some worse form of security problem. As an example, the problem in pageinspect is this diff: *** *** 6,16 -- -- get_raw_page() -- ! CREATE OR REPLACE FUNCTION get_raw_page(text, int4) RETURNS bytea AS 'MODULE_PATHNAME', 'get_raw_page' LANGUAGE C STRICT; -- -- page_header() -- --- 6,21 -- -- get_raw_page() -- ! CREATE OR REPLACE FUNCTION get_raw_page(text, text, int4) RETURNS bytea AS 'MODULE_PATHNAME', 'get_raw_page' LANGUAGE C STRICT; + CREATE OR REPLACE FUNCTION get_raw_page(text, int4) + RETURNS bytea + AS $$ SELECT get_raw_page($1, 'main', $2); $$ + LANGUAGE SQL STRICT; + -- -- page_header() -- *** The underlying C-level get_raw_page function is still there, but it now expects three arguments not two, and will crash if it's passed an int4 where it's expecting a text argument. But the old function definition will migrate without error --- there's no way for pg_migrator to realize it's installing a security hazard. The way we should have done this, which I intend to go change it to, is something like CREATE OR REPLACE FUNCTION get_raw_page(text, int4) RETURNS bytea AS 'MODULE_PATHNAME', 'get_raw_page' LANGUAGE C STRICT; CREATE OR REPLACE FUNCTION get_raw_page(text, text, int4) RETURNS bytea AS 'MODULE_PATHNAME', 'get_raw_page_3' LANGUAGE C STRICT; so that the old function's ABI is preserved. Migration of the old contrib module will then lead to the 3-argument function not being immediately available, but the 2-argument one still works. Had we not wanted to keep the 2-argument form for some reason, we would have provided only get_raw_page_3 in the .so file, and attempts to use the old function definition would fail safely. (We have actually seen similar problems before with people trying to dump and reload database containing contrib modules. pg_migrator is not creating a problem that wasn't there before, it's just making it worse.) Comments, better ideas? 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] Revisiting default_statistics_target
On Sun, 2009-06-07 at 12:13 -0400, Tom Lane wrote: In any case, what we seem to have here is evidence that there are some cases where the new default value of default_statistics_target is too high and you can get a benefit by lowering it. I'm not sure we should panic about that. Default values ought to be compromises. If people only ever change the default in one direction then it's probably not a very good compromise. We know that there are applications for which 100 is still too low, so maybe now we have got the pain spread out roughly evenly... I'm certainly happy with 100 as the default. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partial vacuum versus pg_class.reltuples
Simon Riggs si...@2ndquadrant.com writes: On Sat, 2009-06-06 at 15:44 -0400, Tom Lane wrote: In the longer term, we need to do something else. -1 for such radical change at this stage of release. Uh, by longer term I meant this is something to think about for 8.5. 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: [Fwd: Re: [HACKERS] dblink patches for comment]
Tom Lane wrote: Joe Conway m...@joeconway.com writes: how to trigger the failure (and therefore how to test the solution). A naive test with two databases, one LATIN2, the other UTF8 does not produce the error with simple text literals. Any guidance on specific literals that would trigger the problem? Hmm, sending some non-ASCII characters from the LATIN2 end to the UTF8 end should do it, I would think. The other direction would probably not show any immediate error. I tried some random high-bit characters on the LATIN2 side, but was not able to stumble upon the right combination of characters to trigger a complaint. I've contacted Ruzsinszky Attila off-list and he said he will get me a self contained test case. Joe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
Grzegorz Jaskiewicz wrote: On 7 Jun 2009, at 03:27, Bruce Momjian wrote: Grzegorz Jaskiewicz wrote: On 6 Jun 2009, at 19:50, Tom Lane wrote: We have five days. I don't think we need testing, per se. The first step should be to diff the 8.3 and 8.4 versions of the various contrib .sql.in files and determine what changed. Any module whose .sql.in file hasn't changed is definitely safe. I can tell you already that dblink has changed, and I had to drop it before migration, otherwise everything went fine. Migration of 57GB data in place took about 1 minute. The good news is that the INSTALL instructions where clear enough for the tester to understand that uninstalling dblink from the old cluster and reinstalling it in the new cluster would work. Yes, but I forgot about one database, and had to do it all over again, luckily I first tested it without --link... Any failure would have allowed you to revert to the old server. It is only when you _start_ the new server that the old server cannot be used; I have clarified the INSTALL file on that point. If you use linking, the migration will be much faster (no data copying), but you will no longer be able to access your old cluster once you start the new cluster after the upgrade. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
On Jun 7, 2009, at 12:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I did know that EDB had been using the tool for a while, but I admit I'm not familiar with the whole history. I had the impression that we'd gotten a lot more serious about really making this rock solid since Bruce took it in February. But maybe that's not the case? I don't actually know the EDB end of the history either; maybe someone can educate us about that. But it's true that the core developers, at least, weren't taking it seriously until this year. That's because it really can only handle catalog changes, not changes to the contents of user tables; and it's been quite a long time since we've had a release where we didn't change tuple header layout or packing rules or something that made it a nonstarter. It wasn't clear till early this year that 8.3-8.4 would be a cycle where pg_migrator had a chance of being useful in production ... so we got serious about it. OK, that's more or less what I thought, and what I intended to convey upthread. As far as core Postgres is concerned this is a new feature, and we haven't worked out all the kinks yet. As long as we set expectations accordingly, I think that's OK. You mention CVEs for these contrib issues, but will CVEs still be issued if we make clear that this is experimental only? I would hope not, since that would amount to a policy that any half-baked code anywhere on pgfoundry is just as much our responsibility as core Postgres. Surely we're allowed to say good progress has been made here, but we harbor no illusions that it's bullet-proof. ...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] Partial vacuum versus pg_class.reltuples
On Sat, Jun 6, 2009 at 3:44 PM, Tom Lanet...@sss.pgh.pa.us wrote: I complained a couple days ago that in HEAD, vacuum is putting very bogus values into pg_class.reltuples for indexes: http://archives.postgresql.org/pgsql-bugs/2009-06/msg00037.php After looking through the code a bit, I've confirmed my prior guess that this is caused by the partial-vacuum patch. The number that's being used is the number of live tuples found on the pages that were actually scanned. There's a kluge in there to keep from storing this number as the relation's own reltuples, but the implications for indexes were not considered. The index AMs generally assume that what they're told in IndexVacuumInfo.num_heap_tuples can be trusted, and that's not true anymore. I think probably the only workable solution for 8.4 is to use the prior value of the relation's reltuples field as num_heap_tuples when we have not scanned the whole heap. This will effectively mean that index reltuples values don't change either in a partial vacuum. Am I wrong to be frightened by the implications of updating this value only once in a blue moon? Doesn't this have the potential to result in really bad plans? Do we have any reasonable manual way of forcing VACUUM to scan the entire heap? ...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] Partial vacuum versus pg_class.reltuples
On Sun, Jun 7, 2009 at 7:11 PM, Robert Haasrobertmh...@gmail.com wrote: Am I wrong to be frightened by the implications of updating this value only once in a blue moon? Doesn't this have the potential to result in really bad plans? Do we have any reasonable manual way of forcing VACUUM to scan the entire heap? It shouldn't be necessary to scan the entire heap to get a reasonable estimate for the number of tuples. Analyze doesn't have to, for example. Perhaps we should just make autovacuum periodically run analyze even if it has run a vacuum recently -- i.e. not make vacuum count as a recent analyze. Actually it should be possible to get a more accurate estimate out of ANALYZE than we used to as well. It could very quickly scan the entire FSM and use that and the average tuple size to get a much more accurate estimate for the number of tuples. For VACUUM ANALYZE we could have it count the actual number of tuples in the vacuumable pages and separately take a sample of non-vacuumable pages and calculate an estimate based on the FSM and the average tuple size in those non-vacuumable pages and add those two values together. Since it just looked at every vacuumable page those FSM values are precisely accurate and the estimate for average tuple size ought to be pretty reliable. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Managing multiple branches in git
On Wednesday 03 June 2009 01:55:48 Andrew Dunstan wrote: Running recursive grep on a subversion working copy is quite nasty. I suggest export GREP_OPTIONS='-d skip -I --exclude=*.svn-base --exclude=tags --exclude=*~ --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.svn --exclude=TAGS' -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
On 6/7/09 10:56 AM, Robert Haas wrote: OK, that's more or less what I thought, and what I intended to convey upthread. As far as core Postgres is concerned this is a new feature, and we haven't worked out all the kinks yet. Yes, I'm calling it pg_migrator beta in any advocacy/PR about it. AFAIC, until we have these sorts of issues worked out, it's still a beta. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partial vacuum versus pg_class.reltuples
Robert Haas robertmh...@gmail.com writes: Am I wrong to be frightened by the implications of updating this value only once in a blue moon? It's not great, but I think it's probably not catastrophic either. Keep in mind that all we need from reltuples is that the ratio reltuples/relpages be a reasonable estimate of the density of live tuples, because what the planner actually uses is GetRelationNumberOfBlocks() * reltuples / relpages. So for example an append-only table isn't a big problem, even if it's been quite a while since we updated reltuples and relpages. There was some mention of having a partial vacuum extrapolate a value of reltuples and update pg_class with that. I'm afraid that that could be a seriously bad idea; because there is no very good reason to suppose that the subset of recently-modified pages forms a good sample of the whole table as far as live-tuple density goes. [ thinks a bit and reads the code some more ... ] There is a considerably safer alternative, which is to let ANALYZE update the reltuples estimate based on the pages it sampled; which should be a considerably less biased sample than the pages a partial vacuum would have looked at. And we have already got the code doing that, either in a standalone ANALYZE or an ANALYZE attached to a vacuum that turned out to be partial. So actually I think we are in better shape than I realized, so far as the heap reltuples numbers go. The case that's a bit nasty is where we are propagating the heap reltuples number to the index reltuples number for a GIN index. (Remember this only matters for a partial index.) As the code stands today, what we'll be propagating is the reltuples estimate from the most recent ANALYZE, not the ANALYZE that we might be about to conduct. This is not great; but considering that we are completely ignoring the first-order problem of the partial index predicate's selectivity, quibbling about a second-order effect like the estimate being out of date is pretty pointless. Do we have any reasonable manual way of forcing VACUUM to scan the entire heap? You could use VACUUM FREEZE, for instance. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
Josh Berkus wrote: On 6/7/09 10:56 AM, Robert Haas wrote: OK, that's more or less what I thought, and what I intended to convey upthread. As far as core Postgres is concerned this is a new feature, and we haven't worked out all the kinks yet. Yes, I'm calling it pg_migrator beta in any advocacy/PR about it. AFAIC, until we have these sorts of issues worked out, it's still a beta. afaiks bruce stated he is going to remove the BETA tag from pg_migrator soon so I guess calling it beta in the main project docs will confuse the hell out of people(or causing them to think that it is not beta any more). So maybe calling it experimental(from the POV of the main project) or something similiar might still be the better solution. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partial vacuum versus pg_class.reltuples
On Sun, Jun 7, 2009 at 3:24 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Am I wrong to be frightened by the implications of updating this value only once in a blue moon? It's not great, but I think it's probably not catastrophic either. Keep in mind that all we need from reltuples is that the ratio reltuples/relpages be a reasonable estimate of the density of live tuples, because what the planner actually uses is GetRelationNumberOfBlocks() * reltuples / relpages. So for example an append-only table isn't a big problem, even if it's been quite a while since we updated reltuples and relpages. My first reaction was to be relieved by this explanation, but on further thought I'm not sure I was right to be relieved. The frequency of anti-wraparound vacuums is so low that it doesn't seem inconceivable that this ratio could change considerably on a far shorter time scale. (For me, at least in some cases, it's less than the frequency with which I dump+reload for maintenance reasons like OS upgrade, PG upgrade, HW upgrade...) There was some mention of having a partial vacuum extrapolate a value of reltuples and update pg_class with that. I'm afraid that that could be a seriously bad idea; because there is no very good reason to suppose that the subset of recently-modified pages forms a good sample of the whole table as far as live-tuple density goes. I think you're right about that. [ thinks a bit and reads the code some more ... ] There is a considerably safer alternative, which is to let ANALYZE update the reltuples estimate based on the pages it sampled; which should be a considerably less biased sample than the pages a partial vacuum would have looked at. And we have already got the code doing that, either in a standalone ANALYZE or an ANALYZE attached to a vacuum that turned out to be partial. I'm confused with how this squares with the previous discussion - I thought you observed a case where this wasn't happening. So actually I think we are in better shape than I realized, so far as the heap reltuples numbers go. The case that's a bit nasty is where we are propagating the heap reltuples number to the index reltuples number for a GIN index. (Remember this only matters for a partial index.) As the code stands today, what we'll be propagating is the reltuples estimate from the most recent ANALYZE, not the ANALYZE that we might be about to conduct. This is not great; but considering that we are completely ignoring the first-order problem of the partial index predicate's selectivity, quibbling about a second-order effect like the estimate being out of date is pretty pointless. Being one ANALYZE behind shouldn't be too bad. Do we have any reasonable manual way of forcing VACUUM to scan the entire heap? You could use VACUUM FREEZE, for instance. That'll generate a fair amount of I/O. ...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] Partial vacuum versus pg_class.reltuples
Robert Haas robertmh...@gmail.com writes: On Sun, Jun 7, 2009 at 3:24 PM, Tom Lanet...@sss.pgh.pa.us wrote: [ thinks a bit and reads the code some more ... ] There is a considerably safer alternative, which is to let ANALYZE update the reltuples estimate based on the pages it sampled; which should be a considerably less biased sample than the pages a partial vacuum would have looked at. And we have already got the code doing that, either in a standalone ANALYZE or an ANALYZE attached to a vacuum that turned out to be partial. I'm confused with how this squares with the previous discussion - I thought you observed a case where this wasn't happening. No, the problem was that the wrong things were getting done to reltuples entries for indexes. The heap reltuples values were correct, but there is code that takes the heap value into account when estimating the value for an index, and that was all wet because it was using the number of tuples on the scanned pages rather than any total-table estimate. Do we have any reasonable manual way of forcing VACUUM to scan the entire heap? You could use VACUUM FREEZE, for instance. That'll generate a fair amount of I/O. Er, isn't that exactly what you asked for? 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] information_schema.columns changes needed for OLEDB
On Sunday 31 May 2009 18:41:55 Tom Lane wrote: AFAICS, the SQL standard demands that precision and scale fields be non-null all the time for those data types where they make sense (this is encoded in the CHECK CONSTRAINTs that are declared for the various information-schema tables, see particularly 21.15 DATA_TYPE_DESCRIPTOR base table in SQL99). DATE is clearly wrong per spec, but it's not the only problem. The DATE change is the only thing I'd be prepared to make right now. Our interpretation has been to set these values to null if the typmod is defaulted, which is reasonable in the abstract but it's still a violation of spec. I wonder whether we should be inserting some large limit value instead. That is something to think about, but it needs more time. We also have some inconsistency there; for example we produce a large limit value for octet length. Needs more thought. And if we go down that route, it should also require less hardcoding of numbers into information_schema.sql. -- 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] Partial vacuum versus pg_class.reltuples
On Sun, Jun 7, 2009 at 4:19 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Jun 7, 2009 at 3:24 PM, Tom Lanet...@sss.pgh.pa.us wrote: [ thinks a bit and reads the code some more ... ] There is a considerably safer alternative, which is to let ANALYZE update the reltuples estimate based on the pages it sampled; which should be a considerably less biased sample than the pages a partial vacuum would have looked at. And we have already got the code doing that, either in a standalone ANALYZE or an ANALYZE attached to a vacuum that turned out to be partial. I'm confused with how this squares with the previous discussion - I thought you observed a case where this wasn't happening. No, the problem was that the wrong things were getting done to reltuples entries for indexes. The heap reltuples values were correct, but there is code that takes the heap value into account when estimating the value for an index, and that was all wet because it was using the number of tuples on the scanned pages rather than any total-table estimate. Do we have any reasonable manual way of forcing VACUUM to scan the entire heap? You could use VACUUM FREEZE, for instance. That'll generate a fair amount of I/O. Er, isn't that exactly what you asked for? Basically, I'm trying to figure out what we're going to recommend to someone who gets bitten by whatever remaining corner case still exists after your recent patch, and I admit I'm not real clear on what that is. VACUUM FULL doesn't seem like a good solution because it's more than just vacuum but don't skip any pages even if the visibility map says you can. Maybe we should just have a GUC to enable/disable partial vacuums. ...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] B-Tree contention?
Hello, I have multiple files with that have very similar distributions and I'm seeing contention when concurrent COPY's are happening against a table with a b-tree index on the timestamp column. Each file look something like the following: ~4M rows with timestamp1 ~4M rows with timestamp2 ... To simulate the problem, I have a single file that has: a int, t timestamp [1-400],2007-01-01 00:00:01 [1-400], 2007-01-01 00:00:02 The machine is an eight-core system w/ 16gb of memory. postgresql.conf is configured with: shared_buffers = 256MB #256MB w/ one COPY, 512MB w/ two COPY's wal_buffers = 16MB checkpoint_segments = 100 When I COPY a single file into the table without an index on the time column, it takes: real0m19.628s user0m0.001s sys 0m0.002s When I have two COPY's with the same file concurrently into the same table without an index on the time column, it takes: real0m39.933s user0m0.001s sys 0m0.000s real0m41.294s user0m0.000s sys 0m0.003s So it takes roughly twice the time and we're not getting any increase in throughput for the parallel COPY's. The top LWLock contentions shows that it's the WALInsertLock. postgres=# select * from lwlocks order by blk desc limit 20; pid | lockid | share | write | blk ---++---+-+ 14065 | 7 | 0 | 801 | 311884 14062 | 7 | 0 | 801 | 289561 14062 | 45 | 0 | 89783 | 1287 14065 | 45 | 0 | 90104 | 1207 14062 | 8 | 0 | 652 | 10 14062 | 17 | 0 | 86469 | 7 14065 |370 | 0 | 7 | 6 14062 |370 | 0 | 251466 | 6 14065 | 8 | 0 | 660 | 4 14065 | 29 | 46010 |5337 | 3 14065 | 17 | 0 | 86467 | 3 14062 | 35 | 8830 |5279 | 1 14062 | 40 | 6735 |5139 | 1 14062 | 37 | 12860 |5304 | 1 14062 | 29 | 46037 |5387 | 1 14065 |521 | 0 | 1 | 1 14065 | 28 | 6775 |5236 | 1 14065 | 37 | 12831 |5057 | 1 14062 |506 | 0 |2043 | 1 14065 | 40 | 6763 |5251 | 1 (20 rows) When I COPY a single file into the table with an index on the time column, it takes: real0m51.486s user0m0.000s sys 0m0.002s When I have two COPY's with the same file concurrently into the same table with an index on the time column, it takes: real2m4.414s user0m0.001s sys 0m0.001s real2m4.428s user0m0.000s sys 0m0.002s So it's taking more than twice the time when the timestamp index was added. The top LWLock contentions shows that there's a bit more contention on the WALInsertLock, but there's additional contention on two buffer pages. postgres=# select * from lwlocks order by blk desc limit 20; pid | lockid | share | write | blk ---++--+--+ 13888 | 7 |0 | 16027716 | 342522 13889 | 7 |0 | 16027881 | 339595 13889 |400 | 400 | 402 | 72875 13889 |388 | 401 | 400 | 72545 13888 |388 | 401 | 401 | 70554 13888 |400 | 400 | 402 | 68748 13889 | 45 |0 |89402 | 1340 13888 | 45 |0 |89259 | 1290 13889 | 33 | 12117131 | 6772 | 1102 13888 | 33 | 12118267 | 7178 | 1054 13888 | 29 | 4232749 | 7159 |415 13889 | 29 | 4233831 | 7057 |353 13888 | 34 | 4190329 | 6907 |318 13889 | 34 | 4191618 | 6870 |292 13888 | 38 | 4185303 | 7004 |283 13889 | 38 | 4186310 | 7084 |259 13889 |922 |0 | 278 | 86 13889 |940 |0 | 207 | 80 13889 | 102316 |0 | 346 | 72 13889 |854 |0 | 194 | 70 (20 rows) The two hotspots seem to represent the first BTree pages that contain the '2007-01-01 00:00:01' and '2007-01-01 00:00:02' keys. I suspect it's due to concurrent _bt_doinsert(), which tries to acquire both a read lock (_bt_search) and a write lock (_bt_findinsertloc) on those first BTree pages. Does this sound like a reasonable explanation for the contention? Btw, this is against a 8.4beta2 build as of yesterday, and I'm pretty sure that this happens in REL8_3_STABLE as well. Thanks, Alan
Re: [HACKERS] pg_migrator issue with contrib
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I think the cleanest solution is to document that these issues might happen and suggest solutions. No, the cleanest solution is to fix it before people ever see a problem. This is trivial to do in the case of dblink and I don't see why you think it would be better to make users work around it. Also, dblink is one of the easiest cases because (a) it doesn't have anything but functions, and thus it's possible to drop it from the old DB without data loss, and (b) the inconsistency that it has is something that will result in a clean, readily understandable failure during migration. As opposed to some other cases that will migrate just fine and then dump core during use. I've just finished running through a diff of 8.3 vs 8.4 contrib SQL files. It looks like we have these cases: [ list removed] Certainly if you can fix /contrib problems at the source, please do so. I was commenting on the idea of having pg_migrator somehow skip specific items to try to make it more failure-proof. While I can do that for a few cases, such as suppress the creation of specific functions by filtering the schema dump file, I will never be able to get them all, and doing it extensively could destabilize pg_migrator. You are suggesting improving /contrib itself, which is better. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
Tom Lane wrote: The underlying C-level get_raw_page function is still there, but it now expects three arguments not two, and will crash if it's passed an int4 where it's expecting a text argument. But the old function definition will migrate without error --- there's no way for pg_migrator to realize it's installing a security hazard. FYI, there is nothing pg_migrator specific here. Someone doing a dump/reload from 8.3 to 8.4 would have the same security issue. pg_migrator is using the same pg_dump output as a dump restore, except it uses --schema. pg_migrator would actually be more secure because it will exit on the restore error rather than having the error possibly ignored by the user. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
Robert Haas wrote: OK, that's more or less what I thought, and what I intended to convey upthread. As far as core Postgres is concerned this is a new feature, and we haven't worked out all the kinks yet. As long as we set expectations accordingly, I think that's OK. You mention CVEs for these contrib issues, but will CVEs still be issued if we make clear that this is experimental only? I would hope not, since that would amount to a policy that any half-baked code anywhere on pgfoundry is just as much our responsibility as core Postgres. Surely we're allowed to say good progress has been made here, but we harbor no illusions that it's bullet-proof. Again, there is nothing pg_migrator-specific about these security issues. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
Stefan Kaltenbrunner wrote: Josh Berkus wrote: On 6/7/09 10:56 AM, Robert Haas wrote: OK, that's more or less what I thought, and what I intended to convey upthread. As far as core Postgres is concerned this is a new feature, and we haven't worked out all the kinks yet. Yes, I'm calling it pg_migrator beta in any advocacy/PR about it. AFAIC, until we have these sorts of issues worked out, it's still a beta. afaiks bruce stated he is going to remove the BETA tag from pg_migrator soon so I guess calling it beta in the main project docs will confuse the hell out of people(or causing them to think that it is not beta any more). So maybe calling it experimental(from the POV of the main project) or something similar might still be the better solution. This all sounds very discouraging. It is like, Oh, my, there is a migration tool and it might have bugs. How do we prevent people from using it? Right now nothing in the project is referring to pg_migrator except in the press release, and it is marked as beta there. How do you want to deemphasize it more than that? Why did I bother working on this if the community reaction is to try to figure out how to make people avoid using it? I am now thinking I need to my own PR for pg_migrator because obviously the community is only worried it might have a bug. Instead of testing it, looking at the code, submitting bug reports, or anything constructive, you sit around figuring out how to put a disparaging label on it! -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: I did know that EDB had been using the tool for a while, but I admit I'm not familiar with the whole history. I had the impression that we'd gotten a lot more serious about really making this rock solid since Bruce took it in February. But maybe that's not the case? I don't actually know the EDB end of the history either; maybe someone can educate us about that. But it's true that the core developers, at least, weren't taking it seriously until this year. That's because it really can only handle catalog changes, not changes to the contents of user tables; and it's been quite a long time since we've had a release where we didn't change tuple header layout or packing rules or something that made it a nonstarter. It wasn't clear till early this year that 8.3-8.4 would be a cycle where pg_migrator had a chance of being useful in production ... so we got serious about it. (I do not know whether EDB ever really used it in production. If they did, it must have been for private updates that changed catalogs and not user data.) pg_migrator verion 0.5 is still on the pg_migrator web site, and that is the version I started from. It had this line in the intro: PG_migrator is a tool (not a complete solution) that performs an in-place upgrade of existing data. Of course no one wants a toolkit, they want a full solution, so I modified the code to be easier to use and more robust. I am not sure how much EDB used it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] information_schema.columns changes needed for OLEDB
Peter Eisentraut pete...@gmx.net writes: On Sunday 31 May 2009 18:41:55 Tom Lane wrote: AFAICS, the SQL standard demands that precision and scale fields be non-null all the time for those data types where they make sense (this is encoded in the CHECK CONSTRAINTs that are declared for the various information-schema tables, see particularly 21.15 DATA_TYPE_DESCRIPTOR base table in SQL99). DATE is clearly wrong per spec, but it's not the only problem. The DATE change is the only thing I'd be prepared to make right now. At this point I think the clear decision is we're not changing anything for 8.4. I've put the issue on the TODO list for future development cycles. 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] Partial vacuum versus pg_class.reltuples
Robert Haas robertmh...@gmail.com writes: Basically, I'm trying to figure out what we're going to recommend to someone who gets bitten by whatever remaining corner case still exists after your recent patch, and I admit I'm not real clear on what that is. If anyone actually shows up with a clear problem, we can recommend VACUUM FREEZE. I'm not going to panic about it right now. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issue with contrib
On Sun, Jun 7, 2009 at 11:50 PM, Bruce Momjianbr...@momjian.us wrote: Stefan Kaltenbrunner wrote: Josh Berkus wrote: On 6/7/09 10:56 AM, Robert Haas wrote: OK, that's more or less what I thought, and what I intended to convey upthread. As far as core Postgres is concerned this is a new feature, and we haven't worked out all the kinks yet. Yes, I'm calling it pg_migrator beta in any advocacy/PR about it. AFAIC, until we have these sorts of issues worked out, it's still a beta. afaiks bruce stated he is going to remove the BETA tag from pg_migrator soon so I guess calling it beta in the main project docs will confuse the hell out of people(or causing them to think that it is not beta any more). So maybe calling it experimental(from the POV of the main project) or something similar might still be the better solution. This all sounds very discouraging. It is like, Oh, my, there is a migration tool and it might have bugs. How do we prevent people from using it? Right now nothing in the project is referring to pg_migrator except in the press release, and it is marked as beta there. How do you want to deemphasize it more than that? Why did I bother working on this if the community reaction is to try to figure out how to make people avoid using it? Because Rome wasn't built in a day. It seems to me that you yourself placed a far more disparaging label on it than anything that anyone has proposed today; this was a week ago: http://archives.postgresql.org/pgsql-hackers/2009-05/msg01470.php I don't think it's anyone's intention to disparage your work on this tool. It certainly isn't mine. But it seems obvious to me that it has some pretty severe limitations and warts. The fact that those limitations and warts are well-documented doesn't negate their existence. I also don't think calling the software beta or experimental is a way of deemphasizing it. I think it's a way of being clear that this software is not the bullet-proof, rock-solid, handles-all-cases-and-keeps-on-trucking level of robustness that people have come to expect from PostgreSQL. FWIW, I have no problem at all with mentioning pg_migrator in the release notes or the documentation; my failure to respond to your last emails on this topic was due to being busy and having already spent too much time responding to other emails, not due to thinking it was a bad idea. I actually think it's a good idea. But I also think those references should describe it as experimental, because I think it is. I really hope it won't remain experimental forever, but I think that's an accurate characterization of where it is now. You, or others, may disagree, of course. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
