Re: [HACKERS] knngist patch support
On Thu, 11 Feb 2010, Tom Lane wrote: Oleg Bartunov o...@sai.msu.su writes: This is very disgraceful from my point of view and reflects real problem in scheduling of CF. The patch was submitted Nov 23 2009, discussed and reworked Nov 25. Long holidays in December-January, probably are reason why there were no any movement on reviewing the patch. There was a scheduling problem all right, which was that this patch *did not make* the deadline for the November CF. The fact that it got any review at all in November was more than expected under the CF process. And I remind you that we stated more than once that we didn't want major feature patches to show up only at the last CF. If it had come from anyone other than you and Teodor, there would have not been even a moment's consideration of letting it into 9.0. there were several long threads, which I have no possibility to follow, so we relied on the wisdom of people, who can discuss. So, it's true we didn't track all nuances of our development schedule. We just developed. Looked on commitfest page we didn't find any summary and it's hard to understand what is the final word. In the old-good time we also discussed a lot, we release faster and we always had tolerance of time, since many things were not formalized, we were developers and reviewed each other. Now, the whole process of development redesigned to be more enterprize, but we still have problem with resources - developers, reviewers. And I don't see, how all changes try to solve this problem. We have problem with long release cycle, it's getting worse and worse, in spite of CF. The main problem is not in scheduling - we have little delta here, the problem is in human resources and unclear regulations make it worse. My own feeling about it is that I much preferred the original proposal of a contrib module with little or no change to core code. I don't want to be changing core code for this at this late hour. If it were only touching GIST I'd be willing to rely on your and Teodor's expertise in that module, but it's not. It whacks around the planner, it makes questionable changes in the operator class structure, and the last aha, we originally submit contrib module, which didn't touch anything you mentioned, we improve stuff to follow discussion and now we are out of luck %( version I saw hadn't any documentation whatever. It's not committable on documentation grounds alone, even if everybody was satisfied about the code. well, there is enough documentation to review patch. In my understanding this was always enough to submit code. User's documentation is depend on discussion and review and can be added later before releasing beta. How do you feel about going back to the original contrib module for now and resubmitting the builtin version for 9.1? Hmm, one good thing is that rbtree seems ok for submisson. We need to discuss this, if it's good for PostGIS community. I'd not complain about this decision if it touch my interests only, I could live with closed-source patch. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] knngist patch support
On Thu, 11 Feb 2010, Robert Haas wrote: 2010/2/11 Oleg Bartunov o...@sai.msu.su: This is very disgraceful from my point of view and reflects real problem in scheduling of CF. The patch was submitted Nov 23 2009, discussed and reworked Nov 25. Long holidays in December-January, probably are reason why there were no any movement on reviewing the patch. People with So... I think the reason why there was no movement between November 25th and January 15th is because no CommitFest started between November 25th and January 15th. Had you submitted the patch on November 14th, you would have gotten a lot more feedback in November; I agree that we don't have a lot of formal documentation about the CommitFest process, but I would think that much would be pretty clear, but maybe not. The reason there was no movement after January 15th is because (1) I couldn't get anyone to volunteer to review it, except Mark Cave-Ayland who didn't actually do so (or anyway didn't post anything publicly), and (2) we were still working on rbtree. Personally, I am a little irritated about the whole way this situation has unfolded. I devoted a substantial amount of time over my Robert, please accept my public apology, if you feel I offense you. There are nothing against you. Your contribution is very important and I really don't understand why on the Earth you're not paid ! I remember discussion to paid you from our foundation. That's shame. Does nybody ever got support for development from our foundation ? Christmas vacation to patch review, and many of those patches went on to be committed. Some of the patches I reviewed were yours. I did not get paid one dime for any of that work. I expressed candidly, from the very beginning, that getting such a large patch done by the end of this CommitFest would likely be difficult, especially given that it had two precursor patches. In exchange for giving you my honest opinions about your patches two weeks before the scheduled start of the CommitFest, over my Christmas vacation, and for free, I got a long stream of complaints from you and others about how the process is unfair, and as nearly zero help making the prerequisite patches committable as it is possible for anyone to achieve. It regularly took 4-6 days for a new version of the patch to appear, and as often as not questions in my reviews were ignored for days, if not weeks. It took a LOT of iterations before my performance concerns were addressed; and I believe that process could have been done MUCH more quickly. Robert, it's very hard to marshal all developers, who are not-paid people with their regular duties and problems and their own interests in postgres. You just discovered we have long-long holidays in Russia, when people try to spend somewhere. I always beaten with Christmas in December, when I tried to communicate with business people un US. Earlier, we lived with this and our releases were faster. I'd not say, CF is a step back, but our system should have tolerance in time if we're open-source community, or go enterprize way - we are all paid, we follow business plan, ... etc. Something is really wrong, that's what I can say. Now, it is possible that as you are sitting there reading this email, you are thinking to yourself well, your feedback didn't actually make that patch any better, so this whole thing is just pure obstructionism. I don't believe that's the case, but obviously I'm biased and everyone is entitled to their own opinion. What I can tell you for sure is that all of my reviewing was done with the best of motivations and in a sincere attempt to do the right thing. You may be right that January 15th was a bad time to start a CommitFest, although it's very unclear to me why that might be. At least in the US, the holidays are over long before January 15th, but we had a very small crop of reviewers this time around, and a number of them failed to review the patches they picked up, or did only a very cursory review. It might be mentioned that if you have concerns about getting your own patches reviewed, you might want to think about reviewing some patches by other people. Of the 60 patches currently in the 2010-01 CommitFest, I'm listed as a reviewer on 12 of them. Needless to say, if someone else had volunteered to do some or all of the review work on some of those patches, I would have had more time to work on other patches. Robert, human resources are the main problem and, first of all, our system should work for developers ! If we will not understand each other and follow only some unclear rules, we'll lost current developers and will not attract new. We, probably, in our particulary case, will follow our original suggestion -just contrib module, but I concern about future. Now I have to think not just about algorithms and implementation, but about reviewer and current regulation. Regards, Oleg
Re: [HACKERS] TCP keepalive support for libpq
2010/2/10 daveg da...@sonic.net: On Tue, Feb 09, 2010 at 09:34:10AM -0500, Andrew Chernow wrote: Tollef Fog Heen wrote: (please Cc me on replies, I am not subscribed) Hi, libpq currently does not use TCP keepalives. This is a problem in our case where we have some clients waiting for notifies and then the connection is dropped on the server side. The client never gets the FIN and thinks the connection is up. The attached patch unconditionally adds keepalives. I chose unconditionally as this is what the server does. We didn't need the ability to tune the timeouts, but that could be added with reasonable ease. ISTM that the default behavior should be keep alives disabled, as it is now, and those wanting it can just set it in their apps: setsockopt(PQsocket(conn), SOL_SOCKET, SO_KEEPALIVE, ...) I disagree. I have clients who have problems with leftover client connections due to server host failures. They do not write apps in C. For a non-default change to be effective we would need to have all the client drivers, eg JDBC, psycopg, DBD-DBI, and the apps like psql make changes to turn it on. Adding this option as a non-default will not really help. Yes, that's definitely the use-case. PQsocket() will work fine for C apps only. But it should work fine as an option, no? As long as you can specify it on the connection string - I don't think there are any interfaces that won't take a connection string? -- 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
[HACKERS] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
On Mon, 2010-02-08 at 04:33 +, Tom Lane wrote: We still have to retain all code that copes with finding HEAP_MOVED_OFF and HEAP_MOVED_IN flag bits on existing tuples. This can't be removed as long as we want to support in-place update from pre-9.0 databases. This doesn't seem to be a great reason. Letting weird states exist is not a feature, its a risk. Let me explain. This would only happen if a VACUUM FULL had been run on the pre-9.0 database and it had failed part way through. Re-VACUUMing would remove those settings. ISTM that that the upgrade process should cover this, not force the server to cope with rare and legacy situations. If we do not do this, then we might argue it should *never* be removed because this same rare situation can persist into 9.1 etc.. There were data loss situations possible in early 8.4 and these persisted into later releases *because* the minor release upgrade process did not contain a scan to detect and remove the earlier problems. If we allow tuples to be in strange legacy states we greatly increase the difficulty of diagnosing and fixing problems. People will say moved in/off can be ignored now and mistakes will happen. We should remove the moved in/off flag bits and make it a part of the upgrade process to ensure the absence of those states. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parameter name standby_mode
On Wed, 2010-02-10 at 13:16 +0200, Heikki Linnakangas wrote: If they want to implement the warm standby using the (new) built-in logic to keep retrying restore_command, they would set standby_mode='on'. standby_mode='on' doesn't imply streaming replication. The docs say If this parameter is on, the streaming replication is enabled. So who is wrong? ISTM that Joachim's viewpoint is right and that most people will be confused about this. I think we need something named more intuitively. Something that better describes what action (i.e. a verb) will occur when this is set. Suggestions: streaming_replication = on We may need to split out various complexities into multiple parameters, or have valued parameters, e.g. standby_mode = REPLICA. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
On Wed, 2010-02-10 at 09:32 +0200, Heikki Linnakangas wrote: Fujii Masao wrote: As I pointed out previously, the standby might restore a partially-filled WAL file that is being archived by the primary, and cause a FATAL error. And this happened in my box when I was testing the SR. sby [20088] FATAL: archive file 00010087 has wrong size: 14139392 instead of 16777216 sby [20076] LOG: startup process (PID 20088) exited with exit code 1 sby [20076] LOG: terminating any other active server processes act [18164] LOG: received immediate shutdown request If the startup process is in standby mode, I think that it should retry starting replication instead of emitting an error when it finds a partially-filled file in the archive. Then if the replication has been terminated, it has only to restore the archived file again. Thought? Hmm, so after running restore_command, check the file size and if it's too short, treat it the same as if restore_command returned non-zero? And it will be retried on the next iteration. Works for me, though OTOH it will then fail to complain about a genuinely WAL file that's truncated for some reason. I guess there's no way around that, even if you have a script as restore_command that does the file size check, it will have the same problem. Are we trying to re-invent pg_standby here? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] log_error_verbosity function display
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Right now, log_error_verbosity displays the source code error location in this format: LOCATION: parserOpenTable, parse_relation.c:858 I think it would be clearer to add '()' next to the function name. We use '() to display function names in our docs and I think using '()' would clarify the output, e.g.: LOCATION: parserOpenTable(), parse_relation.c:858 Seems like a waste of log space to me. The convention about writing () to decorate function names is hardly universal, and anyway it's mainly useful to mark things that the reader might not realize are function names. This can't be anything else. I suggested it because it wasn't obvious to me it was a function name, so I figured others might not recognize it. Remember, we deal with the C code all the time so we have to consider how the general user would see it. FYI, here is the output that had me confused: ERROR: 42P01: relation lkjasdf does not exist at character 15 LOCATION: parserOpenTable, parse_relation.c:858 STATEMENT: select * from lkjasdf; Without the '()', I thought the LOCATION related to the query error location, not the source code error location. This is what the new format would look like, which I think is clearer: ERROR: 42P01: relation lkjasdf does not exist at character 15 LOCATION: parserOpenTable(), parse_relation.c:858 STATEMENT: select * from lkjasdf; Of course, maybe the word LOCATION is wrong and it should be FUNCTION: ERROR: 42P01: relation lkjasdf does not exist at character 15 FUNCTION: parserOpenTable(), parse_relation.c:858 STATEMENT: select * from lkjasdf; or SOURCE: ERROR: 42P01: relation lkjasdf does not exist at character 15 SOURCE: parserOpenTable(), parse_relation.c:858 STATEMENT: select * from lkjasdf; -- 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] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
On Thursday 11 February 2010 11:10:32 Simon Riggs wrote: On Mon, 2010-02-08 at 04:33 +, Tom Lane wrote: We still have to retain all code that copes with finding HEAP_MOVED_OFF and HEAP_MOVED_IN flag bits on existing tuples. This can't be removed as long as we want to support in-place update from pre-9.0 databases. This doesn't seem to be a great reason. Letting weird states exist is not a feature, its a risk. Let me explain. This would only happen if a VACUUM FULL had been run on the pre-9.0 database and it had failed part way through. Re-VACUUMing would remove those settings. ISTM that that the upgrade process should cover this, not force the server to cope with rare and legacy situations. If we do not do this, then we might argue it should *never* be removed because this same rare situation can persist into 9.1 etc.. There were data loss situations possible in early 8.4 and these persisted into later releases *because* the minor release upgrade process did not contain a scan to detect and remove the earlier problems. If we allow tuples to be in strange legacy states we greatly increase the difficulty of diagnosing and fixing problems. People will say moved in/off can be ignored now and mistakes will happen. We should remove the moved in/off flag bits and make it a part of the upgrade process to ensure the absence of those states. Essentially requiring a successfull VACUUM FULL or CLUSTER on all tables is imho in the same ballpark as requiring a dump+restore timewise on bigger databases. Andres -- 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] Bugs in b-tree dead page removal
On Sun, 2010-02-07 at 21:33 -0500, Tom Lane wrote: Another issue is that it's not clear what happens in a Hot Standby slave --- it doesn't look like Simon put any interlocking in this area to protect slave queries against having the page disappear from under them. The odds of an actual problem are probably a good bit higher in an HS slave. Seen. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
Hi Robert, On Tue, Feb 9, 2010 at 17:43, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen j...@xs4all.nl wrote: = Projected-cost threshold = If a prepared statement takes parameters, and the generic plan has a high projected cost, re-plan each EXECUTE individually with all its parameter values bound. It may or may not help, but unless the planner is vastly over-pessimistic, re-planning isn't going to dominate execution time for these cases anyway. How high is high? Perhaps this could be based on a (configurable?) ratio of observed planning time and projected execution time. I mean, if planning it the first time took 30 ms and projected execution time is 1 ms, then by all means NEVER re-plan. But if planning the first time took 1 ms and resulted in a projected execution time of 50 ms, then it's relatively cheap to re-plan every time (cost increase per execution is 1/50 = 2%), and the potential gains are much greater (taking a chunk out of 50 ms adds up quickly). Cheers, Bart
Re: [HACKERS] CommitFest status summary 2010-01-27
Hi, Boszormenyi Zoltan írta: Greg Smith írta: 4) Investigate and be explicit about the potential breakage here both for libpq clients and at least one additional driver too. If I saw a demonstration that this didn't break the JDBC driver, for example, I'd feel a lot better about the patch. ... (JDBC discussed to be non-vulnerable) The question is whether new versions of psqlODBC and the old ones shipped in unixODBC handle the change well. I looked at the unixODBC PG driver sources. Both the old and new versions return rowcount for STMT_TYPE_SELECT as the number of tuples returned, it doesn't look at the command status. But they both seems to be broken for INSERTs, as the source interprets the number found after the first ' ' (space) character, they would return 0 for WITHOUT OIDS case. I am talking about these files: unixODBC-x.y.z/Drivers/PostgreSQL/results.c unixODBC-x.y.z/Drivers/Postgre7.1/results.c Look at the SQLRowCount() function. The current psqlODBC driver versions do it in a similar way. They don't look at the actual command tag, if there is a space character in the command status string after trimming it, the string after the space gets interpreted with atoi(). This code also ignores that INSERT returns 2 values, the first value will be returned for rowcount. This means that the more recent ODBC drivers seem to start returning rowcount for utility SELECTs with this protocol change. I haven't tested it though. So, the latest JDBC won't change behaviour without code changes, ODBC may or may not, depending on the version. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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] Parameter name standby_mode
Simon Riggs wrote: The docs say If this parameter is on, the streaming replication is enabled. So who is wrong? The docs. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Simon Riggs wrote: On Wed, 2010-02-10 at 09:32 +0200, Heikki Linnakangas wrote: Hmm, so after running restore_command, check the file size and if it's too short, treat it the same as if restore_command returned non-zero? And it will be retried on the next iteration. Works for me, though OTOH it will then fail to complain about a genuinely WAL file that's truncated for some reason. I guess there's no way around that, even if you have a script as restore_command that does the file size check, it will have the same problem. Are we trying to re-invent pg_standby here? That's not the goal, but we seem to need some of the same functionality in the backend now. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
2010/2/11 Bart Samwel b...@samwel.tk: Hi Robert, On Tue, Feb 9, 2010 at 17:43, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen j...@xs4all.nl wrote: = Projected-cost threshold = If a prepared statement takes parameters, and the generic plan has a high projected cost, re-plan each EXECUTE individually with all its parameter values bound. It may or may not help, but unless the planner is vastly over-pessimistic, re-planning isn't going to dominate execution time for these cases anyway. How high is high? Perhaps this could be based on a (configurable?) ratio of observed planning time and projected execution time. I mean, if planning it the first time took 30 ms and projected execution time is 1 ms, then by all means NEVER re-plan. But if planning the first time took 1 ms and resulted in a projected execution time of 50 ms, then it's relatively cheap to re-plan every time (cost increase per execution is 1/50 = 2%), and the potential gains are much greater (taking a chunk out of 50 ms adds up quickly). It could be a good idea. I don't belive to sophisticate methods. There can be a very simply solution. The could be a limit for price. More expensive queries can be replaned every time when the price will be over limit. Regards Pavel Stehule Cheers, Bart -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
On Thu, 2010-02-11 at 14:22 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Wed, 2010-02-10 at 09:32 +0200, Heikki Linnakangas wrote: Hmm, so after running restore_command, check the file size and if it's too short, treat it the same as if restore_command returned non-zero? And it will be retried on the next iteration. Works for me, though OTOH it will then fail to complain about a genuinely WAL file that's truncated for some reason. I guess there's no way around that, even if you have a script as restore_command that does the file size check, it will have the same problem. Are we trying to re-invent pg_standby here? That's not the goal, but we seem to need some of the same functionality in the backend now. I think you need to say why... -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
On Thu, Feb 11, 2010 at 13:25, Pavel Stehule pavel.steh...@gmail.comwrote: 2010/2/11 Bart Samwel b...@samwel.tk: Perhaps this could be based on a (configurable?) ratio of observed planning time and projected execution time. I mean, if planning it the first time took 30 ms and projected execution time is 1 ms, then by all means NEVER re-plan. But if planning the first time took 1 ms and resulted in a projected execution time of 50 ms, then it's relatively cheap to re-plan every time (cost increase per execution is 1/50 = 2%), and the potential gains are much greater (taking a chunk out of 50 ms adds up quickly). It could be a good idea. I don't belive to sophisticate methods. There can be a very simply solution. The could be a limit for price. More expensive queries can be replaned every time when the price will be over limit. I guess the required complexity depends on how variable planning costs are. If planning is typically = 2 ms, then a hard limit on estimated price is useful and can be set as low as (the equivalent of) 15 ms. However, if planning costs can be 50 ms, then the lowest reasonable fixed limit is quite a bit larger than that -- and that does not solve the problem reported earlier in this thread, where a query takes 30 ms using a generic plan and 1 ms using a specialized plan. Anyhow, I have no clue how much time the planner takes. Can anybody provide any statistics in that regard? Cheers, Bart
Re: [HACKERS] Avoiding bad prepared-statement plans.
On Thu, Feb 11, 2010 at 7:39 AM, Bart Samwel b...@samwel.tk wrote: On Thu, Feb 11, 2010 at 13:25, Pavel Stehule pavel.steh...@gmail.com wrote: 2010/2/11 Bart Samwel b...@samwel.tk: Perhaps this could be based on a (configurable?) ratio of observed planning time and projected execution time. I mean, if planning it the first time took 30 ms and projected execution time is 1 ms, then by all means NEVER re-plan. But if planning the first time took 1 ms and resulted in a projected execution time of 50 ms, then it's relatively cheap to re-plan every time (cost increase per execution is 1/50 = 2%), and the potential gains are much greater (taking a chunk out of 50 ms adds up quickly). It could be a good idea. I don't belive to sophisticate methods. There can be a very simply solution. The could be a limit for price. More expensive queries can be replaned every time when the price will be over limit. I guess the required complexity depends on how variable planning costs are. If planning is typically = 2 ms, then a hard limit on estimated price is useful and can be set as low as (the equivalent of) 15 ms. However, if planning costs can be 50 ms, then the lowest reasonable fixed limit is quite a bit larger than that -- and that does not solve the problem reported earlier in this thread, where a query takes 30 ms using a generic plan and 1 ms using a specialized plan. Anyhow, I have no clue how much time the planner takes. Can anybody provide any statistics in that regard? It depends a great deal on the query, which is one of the things that makes implementing this rather challenging. ...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] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Simon Riggs wrote: On Thu, 2010-02-11 at 14:22 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Wed, 2010-02-10 at 09:32 +0200, Heikki Linnakangas wrote: Hmm, so after running restore_command, check the file size and if it's too short, treat it the same as if restore_command returned non-zero? And it will be retried on the next iteration. Works for me, though OTOH it will then fail to complain about a genuinely WAL file that's truncated for some reason. I guess there's no way around that, even if you have a script as restore_command that does the file size check, it will have the same problem. Are we trying to re-invent pg_standby here? That's not the goal, but we seem to need some of the same functionality in the backend now. I think you need to say why... See the quoted paragraph above. We should check the file size, so that we will not fail if the WAL file is just being copied into the archive directory. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
Andres Freund wrote: On Thursday 11 February 2010 11:10:32 Simon Riggs wrote: On Mon, 2010-02-08 at 04:33 +, Tom Lane wrote: We still have to retain all code that copes with finding HEAP_MOVED_OFF and HEAP_MOVED_IN flag bits on existing tuples. This can't be removed as long as we want to support in-place update from pre-9.0 databases. This doesn't seem to be a great reason. Letting weird states exist is not a feature, its a risk. Let me explain. This would only happen if a VACUUM FULL had been run on the pre-9.0 database and it had failed part way through. Re-VACUUMing would remove those settings. ISTM that that the upgrade process should cover this, not force the server to cope with rare and legacy situations. If we do not do this, then we might argue it should *never* be removed because this same rare situation can persist into 9.1 etc.. There were data loss situations possible in early 8.4 and these persisted into later releases *because* the minor release upgrade process did not contain a scan to detect and remove the earlier problems. If we allow tuples to be in strange legacy states we greatly increase the difficulty of diagnosing and fixing problems. People will say moved in/off can be ignored now and mistakes will happen. We should remove the moved in/off flag bits and make it a part of the upgrade process to ensure the absence of those states. Essentially requiring a successfull VACUUM FULL or CLUSTER on all tables is imho in the same ballpark as requiring a dump+restore timewise on bigger databases. A plain VACUUM would be enough. But FWIW, +1 from me for keeping the support for HEAP_IN/OUT in 9.0. It's not a lot of code, and that way we don't need to invent some safeguards in pg_migrator to check that there's no HEAP_IN/OUT flags just yet. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
On Thu, Feb 11, 2010 at 13:41, Robert Haas robertmh...@gmail.com wrote: On Thu, Feb 11, 2010 at 7:39 AM, Bart Samwel b...@samwel.tk wrote: Anyhow, I have no clue how much time the planner takes. Can anybody provide any statistics in that regard? It depends a great deal on the query, which is one of the things that makes implementing this rather challenging. But I guess you can probably expect it to be on the same order for the same query in generic form and with filled-in parameters? Because that's the underlying assumption of the ratio criterion -- that re-planning with filled-in parameters takes about as much time as the initial planning run took. Cheers, Bart
Re: [HACKERS] knngist patch support
On Thu, Feb 11, 2010 at 3:38 AM, Oleg Bartunov o...@sai.msu.su wrote: Robert, please accept my public apology, if you feel I offense you. There are nothing against you. Your contribution is very important and I really don't understand why on the Earth you're not paid ! I remember discussion to paid you from our foundation. That's shame. Does nybody ever got support for development from our foundation ? No, I don't feel like you offended me. It's more that, from my point of view, it seems like all the things you're complaining about are things that you more or less have control over, or at least could have foreseen. I have only been involved in this project for a year and a half, so the CommitFest process is the only process that I know or understand. On the whole, I've found it to be a pretty good process. I get my patches in; I help other people get their patches in (and hopefully improve them along the way). It's particularly appealing when you're a non-committer, as it gives you a formal structure to make sure your work gets looked at. It seems that you're sort of frustrated with the system and the need to go through a process before committing a patch; and that you feel that the rules are unclear. I don't think it's a bad thing to go through a process before committing a patch, especially a large patch like knngist, but of course that's just my opinion. I agree that the fact that the rules are unclear is a problem, though I'm not sure what to do about it. I am not sure they are so unclear as you are making them out to be, but again, I'm biased by being a relative newcomer, as well as someone who has been in the middle of many of the process discussions. Robert, human resources are the main problem and, first of all, our system should work for developers ! If we will not understand each other and follow only some unclear rules, we'll lost current developers and will not attract new. We, probably, in our particulary case, will follow our original suggestion -just contrib module, but I concern about future. Now I have to think not just about algorithms and implementation, but about reviewer and current regulation. IMHO, our system has to work for both developers and users, and it has to work for both committers and non-committers. ...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] Avoiding bad prepared-statement plans.
On Thu, Feb 11, 2010 at 7:48 AM, Bart Samwel b...@samwel.tk wrote: On Thu, Feb 11, 2010 at 13:41, Robert Haas robertmh...@gmail.com wrote: On Thu, Feb 11, 2010 at 7:39 AM, Bart Samwel b...@samwel.tk wrote: Anyhow, I have no clue how much time the planner takes. Can anybody provide any statistics in that regard? It depends a great deal on the query, which is one of the things that makes implementing this rather challenging. But I guess you can probably expect it to be on the same order for the same query in generic form and with filled-in parameters? I think so but I wouldn't bet the farm on it without testing. Because that's the underlying assumption of the ratio criterion -- that re-planning with filled-in parameters takes about as much time as the initial planning run took. We only want to replan when replanning is relatively cheap compared to execution, so the other assumption is that the planning-to-execution ratio is more or less constant. Whether that's sufficiently true to make the proposed system useful and reliable is not clear to me. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
On Thu, 2010-02-11 at 14:44 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Thu, 2010-02-11 at 14:22 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Wed, 2010-02-10 at 09:32 +0200, Heikki Linnakangas wrote: Hmm, so after running restore_command, check the file size and if it's too short, treat it the same as if restore_command returned non-zero? And it will be retried on the next iteration. Works for me, though OTOH it will then fail to complain about a genuinely WAL file that's truncated for some reason. I guess there's no way around that, even if you have a script as restore_command that does the file size check, it will have the same problem. Are we trying to re-invent pg_standby here? That's not the goal, but we seem to need some of the same functionality in the backend now. I think you need to say why... See the quoted paragraph above. We should check the file size, so that we will not fail if the WAL file is just being copied into the archive directory. We can read, but that's not an explanation. By giving terse answers in that way you are giving the impression that you don't want discussion on these points. If you were running pg_standby as the restore_command then this error wouldn't happen. So you need to explain why running pg_standby cannot solve your problem and why we must fix it by replicating code that has previously existed elsewhere. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] knngist patch support
On Thu, Feb 11, 2010 at 3:00 AM, Oleg Bartunov o...@sai.msu.su wrote: version I saw hadn't any documentation whatever. It's not committable on documentation grounds alone, even if everybody was satisfied about the code. well, there is enough documentation to review patch. Where is there any documentation at all? There are no changes to doc/ at all; no README; and not even a lengthy comment block anywhere that I saw. Nor did the email in which the patch was submitted clearly lay out the design of the feature. In my understanding this was always enough to submit code. User's documentation is depend on discussion and review and can be added later before releasing beta. Several people have said this lately, but it doesn't match what I've seen of our practice over the last year and a half; Tom regularly boots patches that lack documentation (or necessary regression test updates). Sure, people often submit small patches without documentation thinking to fill it in later, but anything major pretty much has to have it, AFAICS. From my own point of view, I would never commit anything that lacked documentation, for fear of being asked to write it myself if the patch author didn't. Of course it's a bit different for committers, who can presumably be counted on to clean up their own mess, but I still think it's fair to expect at least some effort to be put into the docs before commit. ...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] Hostnames in pg_hba.conf
Hi there, I've been working on a patch to add hostname support to pg_hba.conf. It's not ready for public display yet, but I would just like to run a couple of issues / discussion points past everybody. ISSUE #1: Performance / caching At present, I've simply not added caching. The reasoning for this is as follows: (a) getaddrinfo doesn't tell us about expiry, so when do you refresh? (b) If you put the cache in the postmaster, it will not work for exec-based backends as opposed to fork-based backends, since those read pg_hba.conf every time they are exec'ed. (c) If you put this in the postmaster, the postmaster will have to update the cache every once in a while, which may be slow and which may prevent new connections while the cache update takes place. (d) Outdated cache entries may inexplicably and without any logging choose the wrong rule for some clients. Big aargh: people will start using this to specify 'deny' rules based on host names. If you COULD get expiry info out of getaddrinfo you could potentially store this info in a table or something like that, and have it updated by the backends? But that's way over my head for now. ISTM that this stuff may better be handled by a locally-running caching DNS server, if people have performance issues with the lack of caching. These local caching DNS servers can also handle expiry correctly, etcetera. We should of course still take care to look up a given hostname only once for each connection request. ISSUE #2: Reverse lookup? There was a suggestion on the TODO list on the wiki, which basically said that maybe we could use reverse lookup to find the hostname and then check for that hostname in the list. I think that won't work, since IPs can go by many names and may not support reverse lookup for some hostnames (/etc/hosts anybody?). Furthermore, due to the top-to-bottom processing of pg_hba.conf, you CANNOT SKIP entries that might possibly match. For instance, if the third line is for host foo.example.com and the fifth line is for bar.example.com, both lines may apply to the same IP, and you still HAVE to check the first one, even if reverse lookup turns up the second host name. So it doesn't save you any lookups, it just costs an extra one. ISSUE #3: Multiple hostnames? Currently, a pg_hba entry lists an IP / netmask combination. I would suggest allowing lists of hostnames in the entries, so that you can at least mimic the match multiple hosts by a single rule. Any reason not to do this? Comments / bright ideas are welcome, especially regarding issue #1. Cheers, Bart
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
On Thu, 2010-02-11 at 14:53 +0200, Heikki Linnakangas wrote: Andres Freund wrote: On Thursday 11 February 2010 11:10:32 Simon Riggs wrote: We should remove the moved in/off flag bits and make it a part of the upgrade process to ensure the absence of those states. Essentially requiring a successfull VACUUM FULL or CLUSTER on all tables is imho in the same ballpark as requiring a dump+restore timewise on bigger databases. A plain VACUUM would be enough. Yes But FWIW, +1 from me for keeping the support for HEAP_IN/OUT in 9.0. It's not a lot of code, and that way we don't need to invent some safeguards in pg_migrator to check that there's no HEAP_IN/OUT flags just yet. The amount of code has nothing to do with keeping it or removing it. Requiring the backend to support something just because an external utility wants to optimise the performance of upgrades in a way that may introduce later bugs seems rather questionable to me. You still have to perform a backup of the database prior to upgrade and that also must scan the whole database, so the overall time to upgrade will still vary according to database size. So I don't see any overall benefit, just risk, and I cited a similar situation where that risk has already materialized into damage for a user in at least one case. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Simon Riggs wrote: If you were running pg_standby as the restore_command then this error wouldn't happen. So you need to explain why running pg_standby cannot solve your problem and why we must fix it by replicating code that has previously existed elsewhere. pg_standby cannot be used with streaming replication. I guess you're next question is: why not? The startup process alternates between streaming, and restoring files from archive using restore_command. It will progress using streaming as long as it can, but if the connection is lost, it will try to poll the archive until the connection is established again. The startup process expects the restore_command to try to restore the file and fail if it's not found. If the restore_command goes into sleep, waiting for the file to arrive, that will defeat the retry logic in the server because the startup process won't get control again to retry establishing the connection. That's the the essence of my proposal here: http://archives.postgresql.org/message-id/4b50afb4.4060...@enterprisedb.com which is what has now been implemented. To suppport a restore_command that does the sleeping itself, like pg_standby, would require a major rearchitecting of the retry logic. And I don't see why that'd desirable anyway. It's easier for the admin to set up using simple commands like 'cp' or 'scp', than require him/her to write scripts that handle the sleeping and retry logic. The real problem we have right now is missing documentation. It's starting to hurt us more and more every day, as more people start to test this. As shown by this thread and some other recent posts. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
Bart Samwel wrote: Perhaps this could be based on a (configurable?) ratio of observed planning time and projected execution time. I mean, if planning it the first time took 30 ms and projected execution time is 1 ms, then by all means NEVER re-plan. IMHO looking at ms is bad for this 'possible replan' decision. The only comparable numbers invariant to system load are the planners costs (not in ms but unitless) and maybe actual number of processed tuples, but never actual ms. Regards, Yeb Havinga -- 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] TCP keepalive support for libpq
ISTM that the default behavior should be keep alives disabled, as it is now, and those wanting it can just set it in their apps: setsockopt(PQsocket(conn), SOL_SOCKET, SO_KEEPALIVE, ...) I disagree. I have clients who have problems with leftover client connections due to server host failures. They do not write apps in C. For a non-default change to be effective we would need to have all the client drivers, eg JDBC, psycopg, DBD-DBI, and the apps like psql make changes to turn it on. Adding this option as a non-default will not really help. Yes, that's definitely the use-case. PQsocket() will work fine for C apps only. But it should work fine as an option, no? As long as you can specify it on the connection string - I don't think there are any interfaces that won't take a connection string? Perl and python appear to have the same abilities as C. I don't use either of these drivers but I *think* the below would work: DBD:DBI setsockopt($dbh-pg_socket(), ...); psycopg conn.cursor().socket().setsockopt(...); Although, I think Dave's comments have made me change my mind about this patch. Looks like it serves a good purpose. That said, there is no guarentee the driver will implement the new feature ... JDBC seems to lack the ability to get the backing Socket object but java can set socket options. Maybe a JDBC kong fu master knows how to do this. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Simon Riggs si...@2ndquadrant.com writes: If you were running pg_standby as the restore_command then this error wouldn't happen. So you need to explain why running pg_standby cannot solve your problem and why we must fix it by replicating code that has previously existed elsewhere. Let me try. pg_standby will not let the server get back to streaming replication mode once it's done with driving the replay of all the WAL files available in the archive, but will have the server sits there waiting for the next file. The way we want that is implemented now is to have the server switch back and forth between replaying from the archive and streaming from the master. So we want the server to restore from the archive the same way pg_standby used to, except that if the archive does not contain the next WAL files, we want to get back to streaming. And the archive reading will resume at next network glitch. I think it's the reasonning, I hope it explains what you see happening. -- dim -- 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] Writeable CTEs and empty relations
On 2010-02-11 03:44 +0200, I wrote: I'm going to have to disappoint a bunch of people and give up. :-( Btw. would it make sense to apply the WITH-on-top-of-DML part of this patch? At least to me, this seems useful because you can write a RECURSIVE SELECT and then use UPDATE .. FROM or DELETE .. USING on that CTE. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
On Thu, 2010-02-11 at 15:28 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: If you were running pg_standby as the restore_command then this error wouldn't happen. So you need to explain why running pg_standby cannot solve your problem and why we must fix it by replicating code that has previously existed elsewhere. pg_standby cannot be used with streaming replication. I guess you're next question is: why not? The startup process alternates between streaming, and restoring files from archive using restore_command. It will progress using streaming as long as it can, but if the connection is lost, it will try to poll the archive until the connection is established again. The startup process expects the restore_command to try to restore the file and fail if it's not found. If the restore_command goes into sleep, waiting for the file to arrive, that will defeat the retry logic in the server because the startup process won't get control again to retry establishing the connection. Why does the startup process need to regain control? Why not just let it sit and wait? Have you seen that if someone does use pg_standby or similar scripts in the restore_command that the server will never regain control in the way you hope. Would that cause a sporadic hang? The overall design was previously that the solution implementor was in charge of the archive and only they knew its characteristics. It seems strange that we will be forced to explicitly ban people from using a utility they were previously used to using and is still included with the distro. Then we implement in the server the very things the utility did. Only this time the solution implementor will not be in control. I would not be against implementing all aspects of pg_standby into the server. It would make life easier in some ways. I am against implementing only a *few* of the aspects because that leaves solution architects in a difficult position to know what to do. Please lay out some options here for discussion by the community. This seems like a difficult area and not one to be patched up quickly. That's the the essence of my proposal here: http://archives.postgresql.org/message-id/4b50afb4.4060...@enterprisedb.com which is what has now been implemented. To suppport a restore_command that does the sleeping itself, like pg_standby, would require a major rearchitecting of the retry logic. And I don't see why that'd desirable anyway. It's easier for the admin to set up using simple commands like 'cp' or 'scp', than require him/her to write scripts that handle the sleeping and retry logic. The real problem we have right now is missing documentation. It's starting to hurt us more and more every day, as more people start to test this. As shown by this thread and some other recent posts. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
On Thu, 2010-02-11 at 14:41 +0100, Dimitri Fontaine wrote: Simon Riggs si...@2ndquadrant.com writes: If you were running pg_standby as the restore_command then this error wouldn't happen. So you need to explain why running pg_standby cannot solve your problem and why we must fix it by replicating code that has previously existed elsewhere. Let me try. pg_standby will not let the server get back to streaming replication mode once it's done with driving the replay of all the WAL files available in the archive, but will have the server sits there waiting for the next file. The way we want that is implemented now is to have the server switch back and forth between replaying from the archive and streaming from the master. So we want the server to restore from the archive the same way pg_standby used to, except that if the archive does not contain the next WAL files, we want to get back to streaming. And the archive reading will resume at next network glitch. I think it's the reasonning, I hope it explains what you see happening. OK, thanks. One question then: how do we ensure that the archive does not grow too big? pg_standby cleans down the archive using %R. That function appears to not exist anymore. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Simon Riggs wrote: One question then: how do we ensure that the archive does not grow too big? pg_standby cleans down the archive using %R. That function appears to not exist anymore. You can still use %R. Of course, plain 'cp' won't know what to do with it, so a script will then be required. We should probably provide a sample of that in the docs, or even a ready-made tool similar to pg_standby but without the waiting. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
* Heikki Linnakangas heikki.linnakan...@enterprisedb.com [100211 08:29]: To suppport a restore_command that does the sleeping itself, like pg_standby, would require a major rearchitecting of the retry logic. And I don't see why that'd desirable anyway. It's easier for the admin to set up using simple commands like 'cp' or 'scp', than require him/her to write scripts that handle the sleeping and retry logic. But colour me confused, I'm still not understanding why this is any different that with normal PITR recovery. So even with a plain cp in your recovery command instead of a sleep+copy (a la pg_standby, or PITR tools, or all the home-grown solutions out thery), I'm not seeing how it's going to get half files. The only way I can see that is if you're out of disk space in your recovering pg_xlog. It's well know in PostgreSQL wal archivne - you don't just shove files into the archive, you make sure they appear there with the right name atomically. And if the master is only running the archive command on whole WAL files, I just don't understand this whole short wal problem. And don't try and tell me your just poaching files from a running cluster's pg_xlog directory, because I'm going to cry... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
On Thu, 2010-02-11 at 15:55 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: One question then: how do we ensure that the archive does not grow too big? pg_standby cleans down the archive using %R. That function appears to not exist anymore. You can still use %R. Of course, plain 'cp' won't know what to do with it, so a script will then be required. We should probably provide a sample of that in the docs, or even a ready-made tool similar to pg_standby but without the waiting. So we still need a script but it can't be pg_standby? Hmmm, OK... Might it not be simpler to add a parameter onto pg_standby? We send %s to tell pg_standby the standby_mode of the server which is calling it so it can decide how to act in each case. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Aidan Van Dyk wrote:
But colour me confused, I'm still not understanding why this is any
different that with normal PITR recovery.
So even with a plain cp in your recovery command instead of a
sleep+copy (a la pg_standby, or PITR tools, or all the home-grown
solutions out thery), I'm not seeing how it's going to get half files.
If the file is just being copied to the archive when restore_command
('cp', say) is launched, it will copy a half file. That's not a problem
for PITR, because PITR will end at the end of valid WAL anyway, but
returning a half WAL file in standby mode is a problem.
It's well know in PostgreSQL wal archivne - you don't just shove files
into the archive, you make sure they appear there with the right name
atomically. And if the master is only running the archive command on
whole WAL files, I just don't understand this whole short wal problem.
Yeah, if you're careful about that, then this change isn't required. But
pg_standby protects against that, so I think it'd be reasonable to have
the same level of protection built-in. It's not a lot of code.
We could well just document that you should do that, ie. make sure the
file appears in the archive atomically with the right size.
And don't try and tell me your just poaching files from a running
cluster's pg_xlog directory, because I'm going to cry...
No :-).
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Simon Riggs wrote: Might it not be simpler to add a parameter onto pg_standby? We send %s to tell pg_standby the standby_mode of the server which is calling it so it can decide how to act in each case. That would work too, but it doesn't seem any simpler to me. On the contrary. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] knngist patch support
Robert Haas robertmh...@gmail.com writes: It seems that you're sort of frustrated with the system and the need to go through a process before committing a patch; I've been handling arround here for years (since 2005 or before) and I think there always was a process. The only change is it's getting more and more formal. But still not any clearer. It's good to try to keep the major releases one year apart, but until now we had some flexibility towards developpers. They could have their own agenda then appear with a big patch and it was getting considered. We never asked contributors to arrange for being able to find a sponsor, do the closed source version, prepare for publishing, and then send a patch in a timely maneer so that to ease the integration and release. Before there was a Commit Fest process, we took some weeks then months at the end of the cycle to consider what had been accumulated. The new process is there for giving more feedback to developpers, and is being considered now as a way to get better control about the release agenda. I'm not sure it's a good tool for that. I'm not sure insisting that much on the release schedule is a good idea. Once more making compromises is easy. What's hard and challenging is making *good* compromises. IMHO, our system has to work for both developers and users, and it has to work for both committers and non-committers. That's an easy goal to share. The question is how you get there without losing existing developpers and possibly attracting new developpers on the way. -- dim -- 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] Bugs in b-tree dead page removal
On Sun, 2010-02-07 at 21:33 -0500, Tom Lane wrote: That last problem is easy to fix, but I'm not at all sure what to do about the scan interlock problem. Thoughts? AFAICS the problem doesn't exist in normal running. _bt_page_recyclable() tests against RecentXmin, which includes the xmins of read only transactions. So it doesn't matter if a read-only transaction still exists that is earlier than the value of opaque-btpo.xact when it is set. If it still there later then the page cannot be reused. A basic interlock approach can be put in place for Hot Standby. We just WAL log the reuse of a btree page in _bt_getbuf() just before we _bt_pageinit(), using transaction id that took that action. We can then conflict on that xid. - - For the TODO, I'm thinking whether there's a way to allow the page to be reused earlier and have it all just work. That would allow us to recycle index blocks faster and avoid index bloat from occurring in the presence of long lived transactions. Otherwise fixing this for the normal case will accentuate index bloat. It seems possible that a page can be reused and end up at exactly the same place in the index key space, so that the left link of the new page matches the right link of the page the scan just left. Most likely it would be in a different place entirely and so ignoring the issue will cause scans to potentially stop earlier than they should and we give an incomplete answer to a query. So we can't just re-check links to validate the page. The only thing we actually need to record about the old page is the right link, so perhaps we can store the right link value in a central place, together with visibility information. Make that info WAL-logged so it is available on standby also. That would allow us to find out whether we should read the page or use the right link info to move right. We then store a recycled-by transaction id on the new page we are recycling. When we scan onto a new page we check to see whether the page has been recycled by a transaction that we consider still in progress. If so, we consult the page-visibility info to see what the right link of the page was as far as our scan is concerned, then use that to continue our scan. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
On Thu, 2010-02-11 at 16:22 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: Might it not be simpler to add a parameter onto pg_standby? We send %s to tell pg_standby the standby_mode of the server which is calling it so it can decide how to act in each case. That would work too, but it doesn't seem any simpler to me. On the contrary. It would mean that pg_standby would act appropriately according to the setting of standby_mode. So you wouldn't need multiple examples of use, it would all just work whatever the setting of standby_mode. Nice simple entry in the docs. We've said we need a script and pg_standby is it. Having a second script, pg_standby2, rather than adding something to the existing tools just seems confusing and easier to get wrong. I'm happy to do the patch if you like. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bug on pg_lesslog
Dear Folks; A very serious bug was reported on pg_lesslog. So far, I found it's a bug in pg_compresslog. Please do not use pg_compresslog and pg_decompresslog until improved version is uploaded. I strongly advise to take base backup of your database. I apologize for inconvenience. I'll upload the new version ASAP. Warmest Regards; -- Koichi Suzuki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
* Heikki Linnakangas heikki.linnakan...@enterprisedb.com [100211 09:17]:
If the file is just being copied to the archive when restore_command
('cp', say) is launched, it will copy a half file. That's not a problem
for PITR, because PITR will end at the end of valid WAL anyway, but
returning a half WAL file in standby mode is a problem.
But it can be a problem - without the last WAL (or at least enough of
it) the master switched and archived, you have no guarantee of having
being consistent again (I'm thinking specifically of recovering from a
fresh backup)
Yeah, if you're careful about that, then this change isn't required. But
pg_standby protects against that, so I think it'd be reasonable to have
the same level of protection built-in. It's not a lot of code.
This 1 check isn't, but what about the rest of the things pg_standby
does. How much functionality should we bring it? Ideally, all of it.
We could well just document that you should do that, ie. make sure the
file appears in the archive atomically with the right size.
I have to admit, today was the first time I went and re-read the PITR
docs, and no, the docs don't seem to talk about that... Maybe it was
just plain obvious to me because it (the atomic apperance) is something
unix devloppers have always had to deal with, so it's ingrained in me.
But I'm *sure* that I've seen that bandied around as common knowledge on
the lists, and one of the reasons we alway see warnings about using
rsync instead of plain SCP, etc.
So ya, we should probably mention that somewhere in the docs. Section
24.3.6. Caveats?
a.
--
Aidan Van Dyk Create like a god,
ai...@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.
signature.asc
Description: Digital signature
Re: [HACKERS] knngist patch support
On Thu, Feb 11, 2010 at 1:18 PM, Robert Haas robertmh...@gmail.com wrote: In my understanding this was always enough to submit code. User's documentation is depend on discussion and review and can be added later before releasing beta. Several people have said this lately, but it doesn't match what I've seen of our practice over the last year and a half; Perhaps the confusion is that we often say not to worry about the quality of the English in the documentation. That's because it's easy for a reviewer to fix up the English but not so easy to figure out what you intend the behaviour to be. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Heikki Linnakangas wrote: Simon Riggs wrote: Might it not be simpler to add a parameter onto pg_standby? We send %s to tell pg_standby the standby_mode of the server which is calling it so it can decide how to act in each case. That would work too, but it doesn't seem any simpler to me. On the contrary. I don't know that the ideas are mutually exclusive. Should the server internals do a basic check that the files they're about to process seem sane before processing them? Yes. Should we provide a full-featured program that knows how far back it can delete archives rather than expecting people to write their own? Yes. And a modified pg_standby seems the easiest way to do that, since it already knows how to do the computations, among other benefits. I already have a work in progress patch to pg_standby I'm racing to finish here that cleans up some of the UI warts in the program, including the scary sounding and avoidable warnings Selena submitted a patch to improve. I think I'm going to add this feature to it, too, whether or not it's deemed necessary. I'm really tired of example setups provided that say just write a simple script using cp like trivial example and then supporting those non-production quality messes in the field. My scripts for this sort of thing have more error checking in them than functional code. And pg_standby already has a healthy sense of paranoia built into it. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [HACKERS] knngist patch support
On Thu, 11 Feb 2010, Robert Haas wrote: On Thu, Feb 11, 2010 at 3:00 AM, Oleg Bartunov o...@sai.msu.su wrote: version I saw hadn't any documentation whatever. It's not committable on documentation grounds alone, even if everybody was satisfied about the code. well, there is enough documentation to review patch. Where is there any documentation at all? There are no changes to doc/ at all; no README; and not even a lengthy comment block anywhere that I saw. Nor did the email in which the patch was submitted clearly lay out the design of the feature. Well, initial knngist announce http://archives.postgresql.org/pgsql-hackers/2009-11/msg01547.php isn't enough to review ? We made test data available to reproduce results, see http://www.sai.msu.su/~megera/wiki/2009-11-25 We are here and open to any reviewer's question. In my understanding this was always enough to submit code. User's documentation is depend on discussion and review and can be added later before releasing beta. Several people have said this lately, but it doesn't match what I've seen of our practice over the last year and a half; Tom regularly boots patches that lack documentation (or necessary regression test updates). Sure, people often submit small patches without documentation thinking to fill it in later, but anything major pretty much has to have it, AFAICS. From my own point of view, I would never commit anything that lacked documentation, for fear of being asked to write it myself if the patch author didn't. Of course it's a bit different for committers, who can presumably be counted on to clean up their own mess, but I still think it's fair to expect at least some effort to be put into the docs before commit. I think nobody will spend his time to write sgml code for user's documentation for fear his patch will be rejected/moved/getting rewritten, so his time will be just wasted. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch to implement ECPG side tracing / tracking ...
On Wed, Feb 10, 2010 at 01:12:31PM -0300, Alvaro Herrera wrote: What happened to this patch? Was it abandoned in favor of server-side tracing? I think it was abandoned but I don't remember seeing any patch/suggestion to improve server-side tracing. This might come from server-side tracing already being sufficient though. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Simon Riggs wrote: On Thu, 2010-02-11 at 16:22 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: Might it not be simpler to add a parameter onto pg_standby? We send %s to tell pg_standby the standby_mode of the server which is calling it so it can decide how to act in each case. That would work too, but it doesn't seem any simpler to me. On the contrary. It would mean that pg_standby would act appropriately according to the setting of standby_mode. So you wouldn't need multiple examples of use, it would all just work whatever the setting of standby_mode. Nice simple entry in the docs. We've said we need a script and pg_standby is it. Having a second script, pg_standby2, rather than adding something to the existing tools just seems confusing and easier to get wrong. I'm happy to do the patch if you like. Well, doesn't really seem that useful to me, but I won't object if you want to do it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] knngist patch support
On Thu, 11 Feb 2010, Greg Stark wrote: On Thu, Feb 11, 2010 at 1:18 PM, Robert Haas robertmh...@gmail.com wrote: In my understanding this was always enough to submit code. User's documentation is depend on discussion and review and can be added later before releasing beta. Several people have said this lately, but it doesn't match what I've seen of our practice over the last year and a half; Perhaps the confusion is that we often say not to worry about the quality of the English in the documentation. That's because it's easy for a reviewer to fix up the English but not so easy to figure out what you intend the behaviour to be. English + SGML stuff. We usually provide information in plain text, posted in -hackers and published in my wiki. I don't remember, that there were no information about patches. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] Hostnames in pg_hba.conf
Bart Samwel b...@samwel.tk wrote: I've been working on a patch to add hostname support to pg_hba.conf. At present, I've simply not added caching. Perhaps you could just recommend using nscd (or similar). There was a suggestion on the TODO list on the wiki, which basically said that maybe we could use reverse lookup to find the hostname and then check for that hostname in the list. I think that won't work, since IPs can go by many names and may not support reverse lookup for some hostnames (/etc/hosts anybody?). Right. Any reverse lookup should be, at best, for display in error messages or logs. There can be zero to many names for an IP address. Currently, a pg_hba entry lists an IP / netmask combination. I would suggest allowing lists of hostnames in the entries, so that you can at least mimic the match multiple hosts by a single rule. Any reason not to do this? I can't see any reason other than code complexity. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Simon Riggs escreveu: It would mean that pg_standby would act appropriately according to the setting of standby_mode. So you wouldn't need multiple examples of use, it would all just work whatever the setting of standby_mode. Nice simple entry in the docs. +1. I like the %s idea. IMHO fixing pg_standby for SR is a must-fix. I'm foreseeing a lot of users asking why pg_standby doesn't work on SR mode ... -- Euler Taveira de Oliveira http://www.timbira.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] Hostnames in pg_hba.conf
On 02/11/2010 08:13 AM, Bart Samwel wrote: ISSUE #1: Performance / caching At present, I've simply not added caching. The reasoning for this is as follows: (a) getaddrinfo doesn't tell us about expiry, so when do you refresh? (b) If you put the cache in the postmaster, it will not work for exec-based backends as opposed to fork-based backends, since those read pg_hba.conf every time they are exec'ed. (c) If you put this in the postmaster, the postmaster will have to update the cache every once in a while, which may be slow and which may prevent new connections while the cache update takes place. (d) Outdated cache entries may inexplicably and without any logging choose the wrong rule for some clients. Big aargh: people will start using this to specify 'deny' rules based on host names. If you COULD get expiry info out of getaddrinfo you could potentially store this info in a table or something like that, and have it updated by the backends? But that's way over my head for now. ISTM that this stuff may better be handled by a locally-running caching DNS server, if people have performance issues with the lack of caching. These local caching DNS servers can also handle expiry correctly, etcetera. We should of course still take care to look up a given hostname only once for each connection request. You should cache for some minimal amount of time or some minimal number of records - even if it's just one minute, and even if it's a fixed length LRU sorted list. This would deal with situations where a new connection is raised several times a second (some types of load). For connections raised once a minute or less, the benefit of caching is far less. But, this can be a feature tagged on later if necessary and doesn't need to gate the feature. Many UNIX/Linux boxes have some sort of built-in cache, sometimes persistent, sometimes shared. On my Linux box, I have nscd - name server caching daemon - which should be able to cache these sorts of lookups. I believe it is used for things as common as mapping uid to username in output of /bin/ls -l, so it does need to be pretty fast. The difference between in process cache and something like nscd is the inter-process communication required to use nscd. ISSUE #2: Reverse lookup? There was a suggestion on the TODO list on the wiki, which basically said that maybe we could use reverse lookup to find the hostname and then check for that hostname in the list. I think that won't work, since IPs can go by many names and may not support reverse lookup for some hostnames (/etc/hosts anybody?). Furthermore, due to the top-to-bottom processing of pg_hba.conf, you CANNOT SKIP entries that might possibly match. For instance, if the third line is for host foo.example.com http://foo.example.com and the fifth line is for bar.example.com http://bar.example.com, both lines may apply to the same IP, and you still HAVE to check the first one, even if reverse lookup turns up the second host name. So it doesn't save you any lookups, it just costs an extra one. I don't see a need to do a reverse lookup. Reverse lookups are sometimes done as a verification check, in the sense that it's cheap to get a map from NAME - IP, but sometimes it is much harder to get the reverse map from IP - NAME. However, it's not a reliable check as many legitimate users have trouble getting a reverse map from IP - NAME. It also doesn't same anything as IP - NAME lookups are a completely different set of name servers, and these name servers are not always optimized for speed as IP - NAME lookups are less common than NAME - IP. Finally, if one finds a map from IP - NAME, that doesn't prove that a map from NAME - IP exists, so using *any* results from IP - NAME is questionable. I think reverse lookups are unnecessary and undesirable. ISSUE #3: Multiple hostnames? Currently, a pg_hba entry lists an IP / netmask combination. I would suggest allowing lists of hostnames in the entries, so that you can at least mimic the match multiple hosts by a single rule. Any reason not to do this? I'm mixed. In some situations, I've wanted to put multiple IP/netmask. I would say that if multiple names are supported, then multiple IP/netmask should be supported. But, this does make the lines unwieldy beyond two or three. This direction leans towards the capability to define host classes, where the rules allows the host class, and the host class can have a list of hostnames. Two other aspects I don't see mentioned: 1) What will you do for hostnames that have multiple IP addresses? Will you accept all IP addresses as being valid? 2) What will you do if they specify a hostname and a netmask? This seems like a convenient way of saying everybody on the same subnet as NAME. Cheers, mark -- Mark Mielkem...@mielke.cc
Re: [HACKERS] Writeable CTEs and empty relations
On Thu, Feb 11, 2010 at 8:46 AM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: On 2010-02-11 03:44 +0200, I wrote: I'm going to have to disappoint a bunch of people and give up. :-( Btw. would it make sense to apply the WITH-on-top-of-DML part of this patch? At least to me, this seems useful because you can write a RECURSIVE SELECT and then use UPDATE .. FROM or DELETE .. USING on that CTE. Hmm, that's a thought. Can you split out just that part? ...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] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
Simon Riggs si...@2ndquadrant.com writes: This would only happen if a VACUUM FULL had been run on the pre-9.0 database and it had failed part way through. If that were true, you might have an argument, but it isn't. VACUUM FULL was never very careful about getting rid of all MOVED_xxx bits. See the comments for update_hint_bits(). We should remove the moved in/off flag bits and make it a part of the upgrade process to ensure the absence of those states. That's not happening. The whole point of upgrade in place is to not do anything as expensive as a full-database scan. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] a common place for pl/perlu modules
Hello, When developing pl/perlu functions common definitions and methods are often stored in external .pm modules. During deployment the modules should be installed somewhere in @INC to be reachable by the perl interpreter. However, installing the modules to a location outside of the PG installation makes it hard to have a consistent environment when running multiple PG versions on the same host. What do you think about defining a canonical place for pl/perlu .pm modules (i.e. PKGLIBDIR) and adding this location to @INC during the interpreter initialization ? Another idea is to allow a user to specify such location by adding a new custom GUC variable. -- Alexey Klyukin http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Output configuration status after ./configure run.
Ühel kenal päeval, K, 2010-02-10 kell 21:17, kirjutas Tom Lane: Robert Haas robertmh...@gmail.com writes: On Wed, Feb 10, 2010 at 3:30 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: If this doesn't fit in 24x80 maybe we need to find a more compact way to display things. +1. I wouldn't mind a one-line summary, but a two page summary seems like a lot. So it seems there's some consensus that: 1. This printout should display everything configurable from a configure option, and nothing else (ie, not any of the platform-dependent conclusions that configure draws). Do you mean also CC, CFLAGS, PREFIX, etc? 2. The printout has to be made to fit in 24x80 or so. I'm still quite dubious about the usefulness, but I could live with this if someone explains to me how the printout is going to stay within 24x80 given the inevitable growth in number of configure options ... I'm a bit reluctant to 24x80 requirement. Without this patch one has to scroll a lot more backlog from ./configure (hundreds of lines) to see what changes really applied and also the output is a bit more cryptic. The same goes to ./configure --help. And I don't really know any other ways... And besides, this feature only matters for people who play with the source. Everyone else is already using either binary packages or build systems (like portage). Also: Hmm. That implies that you didn't look at the command that you typed but you did look at its output. I'm not going to say no one does that (who am I to judge?) but it seems kind of strange to me. Yes, strange but I don't really make the connection of blindly typing the command and figuring out what options configure supports... :S -- 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] TCP keepalive support for libpq
]] daveg | I disagree. I have clients who have problems with leftover client connections | due to server host failures. They do not write apps in C. For a non-default | change to be effective we would need to have all the client drivers, eg JDBC, | psycopg, DBD-DBI, and the apps like psql make changes to turn it on. Adding | this option as a non-default will not really help. FWIW, this is my case. My application uses psycopg, which provides no way to get access to the underlying socket. Sure, I could hack my way around this, but from the application writer's point of view, I have a connection that I expect to stay around and be reliable. Whether that connection is over a UNIX socket, a TCP socket or something else is something I would rather not have to worry about; it feels very much like an abstraction violation. -- Tollef Fog Heen UNIX is user friendly, it's just picky about who its friends are -- 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] TCP keepalive support for libpq
On Thu, Feb 11, 2010 at 2:15 AM, Tollef Fog Heen tollef.fog.h...@collabora.co.uk wrote: ]] daveg | I disagree. I have clients who have problems with leftover client connections | due to server host failures. They do not write apps in C. For a non-default | change to be effective we would need to have all the client drivers, eg JDBC, | psycopg, DBD-DBI, and the apps like psql make changes to turn it on. Adding | this option as a non-default will not really help. FWIW, this is my case. My application uses psycopg, which provides no way to get access to the underlying socket. Sure, I could hack my way around this, but from the application writer's point of view, I have a connection that I expect to stay around and be reliable. Whether that connection is over a UNIX socket, a TCP socket or something else is something I would rather not have to worry about; it feels very much like an abstraction violation. I've sometimes wondered why keepalives aren't the default for all TCP connections. They seem like they're usually a Good Thing (TM), but I wonder if we can think of any situations where someone might not want them? ...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] Avoiding bad prepared-statement plans.
Robert Haas robertmh...@gmail.com writes: On Thu, Feb 11, 2010 at 7:48 AM, Bart Samwel b...@samwel.tk wrote: Because that's the underlying assumption of the ratio criterion -- that re-planning with filled-in parameters takes about as much time as the initial planning run took. We only want to replan when replanning is relatively cheap compared to execution, Well, no, consider the situation where planning takes 50 ms, the generic plan costs 100ms to execute, but a parameter-specific plan would take 1ms to execute. Planning is very expensive compared to execution but it's still a win to do it. The problem that we face is that we don't have any very good way to tell whether a fresh planning attempt is likely to yield a plan significantly better than the generic plan. I can think of some heuristics --- for example if the query contains LIKE with a parameterized pattern or a partitioned table --- but that doesn't seem like a particularly nice road to travel. A possible scheme is to try it and keep track of whether we ever actually do get a better plan. If, after N attempts, none of the custom plans were ever more than X% cheaper than the generic one, then give up and stop attempting to produce custom plans. Tuning the variables might be challenging though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Output configuration status after ./configure run.
On Thu, Feb 11, 2010 at 2:30 AM, Priit Laes pl...@plaes.org wrote: Also: Hmm. That implies that you didn't look at the command that you typed but you did look at its output. I'm not going to say no one does that (who am I to judge?) but it seems kind of strange to me. Yes, strange but I don't really make the connection of blindly typing the command and figuring out what options configure supports... :S Well if you want to know what options it supports just do ./configure --help ... ...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] [PATCH] Output configuration status after ./configure run.
Euler Taveira de Oliveira escribió: Tom Lane escreveu: I'm still quite dubious about the usefulness, but I could live with this if someone explains to me how the printout is going to stay within 24x80 given the inevitable growth in number of configure options ... AFAICS, we have 40 configure options. If we want this to fit in 24 rows (i) we should choose popular options or (ii) print only features/packages that have a non-default option/value. Or (iii) display more than one per line, for example Configured PL languages: Perl Python Tcl Configued block sizes: WAL: 8192data: 8192sth else: 16384 There you have six configure options in two lines. Both ideas aren't ideal for machine-readable format (as someone mentioned pgbuildfarm) because the summary is partial My idea regarding buildfarm was to have it for human consumption, not machine readable. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Hostnames in pg_hba.conf
Bart Samwel b...@samwel.tk writes: I've been working on a patch to add hostname support to pg_hba.conf. Have you read the previous discussions about that? 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] a common place for pl/perlu modules
Alexey Klyukin wrote: Hello, When developing pl/perlu functions common definitions and methods are often stored in external .pm modules. During deployment the modules should be installed somewhere in @INC to be reachable by the perl interpreter. However, installing the modules to a location outside of the PG installation makes it hard to have a consistent environment when running multiple PG versions on the same host. What do you think about defining a canonical place for pl/perlu .pm modules (i.e. PKGLIBDIR) and adding this location to @INC during the interpreter initialization ? Another idea is to allow a user to specify such location by adding a new custom GUC variable. Why won't setting this in the new on_perl_init setting work? It's even included in to documented examples using the standard lib module: http://developer.postgresql.org/pgdocs/postgres/plperl-under-the-hood.html#PLPERL-CONFIG cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
Simon Riggs si...@2ndquadrant.com writes: You still have to perform a backup of the database prior to upgrade and that also must scan the whole database, so the overall time to upgrade will still vary according to database size. So I don't see any overall benefit, just risk, and I cited a similar situation where that risk has already materialized into damage for a user in at least one case. You cited no such case; you merely hypothesized that it could happen. As for the alleged risks involved, keeping the tqual support for MOVED bits cannot create any data-loss risks that haven't existed right along in every previous release. But depending on MOVED bits to be reliably gone after a pg_upgrade would introduce a very obvious data loss risk that wasn't there before, namely that pg_upgrade misses one. 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] TCP keepalive support for libpq
Robert Haas robertmh...@gmail.com wrote: I've sometimes wondered why keepalives aren't the default for all TCP connections. They seem like they're usually a Good Thing (TM), but I wonder if we can think of any situations where someone might not want them? I think it's insane not to use them at all, but there are valid use cases for different timings. Personally, I'd be happy to see a default of sending them if a connection is idle for two minutes, but those people who create 2000 lightly used connections to the database might feel differently. -Kevin -- 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 on pg_lesslog
Dear Folks; A very serious bug was reported on pg_lesslog. So far, I found it's a bug in pg_compresslog. Please do not use pg_compresslog and pg_decompresslog until improved version is uploaded. I strongly advise to take base backup of your database. I apologize for inconvenience. I'll upload the new version ASAP. Warmest Regards; -- Koichi Suzuki -- 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] TCP keepalive support for libpq
From the Slony-I docs (http://www.slony.info/documentation/faq.html) : Supposing you experience some sort of network outage, the connection between slon and database may fail, and the slon may figure this out long before the PostgreSQL instance it was connected to does. The result is that there will be some number of idle connections left on the database server, which won't be closed out until TCP/IP timeouts complete, which seems to normally take about two hours. For that two hour period, the slon will try to connect, over and over, and will get the above fatal message, over and over. Speaking as someone who uses Slony quite a lot, this patch sounds very helpful. Why hasn't libpq had keepalives for years? Regards, Peter Geoghegan -- 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] TCP keepalive support for libpq
Robert Haas wrote: On Thu, Feb 11, 2010 at 2:15 AM, Tollef Fog Heen tollef.fog.h...@collabora.co.uk wrote: ]] daveg | I disagree. I have clients who have problems with leftover client connections | due to server host failures. They do not write apps in C. For a non-default | change to be effective we would need to have all the client drivers, eg JDBC, | psycopg, DBD-DBI, and the apps like psql make changes to turn it on. Adding | this option as a non-default will not really help. FWIW, this is my case. My application uses psycopg, which provides no way to get access to the underlying socket. Sure, I could hack my way around this, but from the application writer's point of view, I have a connection that I expect to stay around and be reliable. Whether that connection is over a UNIX socket, a TCP socket or something else is something I would rather not have to worry about; it feels very much like an abstraction violation. I've sometimes wondered why keepalives aren't the default for all TCP connections. They seem like they're usually a Good Thing (TM), but I wonder if we can think of any situations where someone might not want them? The only case I can think of are systems that send application layer keepalive-like packets; I've worked on systems like this. The goal wasn't to reinvent keepalives but to check-in every minute or two to meet a different set of requirements, thus TCP keepalives weren't needed. However, I don't think they would of caused any harm. The more I think about this the more I think it's a pretty non-invasive change to enable keepalives in libpq. I don't think this has any negative impact on clients written while the default was disabled. This is really a driver setting. There is no way to ensure libpq, DBI, psycopg, JDBC, etc... all enable or disable keepalives by default. I only bring this up because it appears there are complaints from non-libpq clients. This patch wouldn't fix those cases. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] Avoiding bad prepared-statement plans.
2010/2/11 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: On Thu, Feb 11, 2010 at 7:48 AM, Bart Samwel b...@samwel.tk wrote: Because that's the underlying assumption of the ratio criterion -- that re-planning with filled-in parameters takes about as much time as the initial planning run took. We only want to replan when replanning is relatively cheap compared to execution, Well, no, consider the situation where planning takes 50 ms, the generic plan costs 100ms to execute, but a parameter-specific plan would take 1ms to execute. Planning is very expensive compared to execution but it's still a win to do it. The problem that we face is that we don't have any very good way to tell whether a fresh planning attempt is likely to yield a plan significantly better than the generic plan. I can think of some heuristics --- for example if the query contains LIKE with a parameterized pattern or a partitioned table --- but that doesn't seem like a particularly nice road to travel. A possible scheme is to try it and keep track of whether we ever actually do get a better plan. If, after N attempts, none of the custom plans were ever more than X% cheaper than the generic one, then give up and stop attempting to produce custom plans. Tuning the variables might be challenging though. I afraid so every heuristic is bad. Problem is identification of bad generic plan. And nobody ensure, so non generic plan will be better than generic. Still I thing we need some way for lazy prepared statements - plan is generated everytime with known parameters. Other idea: some special debug/test mod, where pg store generic plan for every prepared statement, and still generate specific plan. When the prices are different, then pg produces a warning. This can be slower, but can identify problematic queries. It could be implemented as contrib module - some like autoexplain. regards Pavel 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] a common place for pl/perlu modules
On Feb 11, 2010, at 6:24 PM, Andrew Dunstan wrote: Alexey Klyukin wrote: Hello, When developing pl/perlu functions common definitions and methods are often stored in external .pm modules. During deployment the modules should be installed somewhere in @INC to be reachable by the perl interpreter. However, installing the modules to a location outside of the PG installation makes it hard to have a consistent environment when running multiple PG versions on the same host. What do you think about defining a canonical place for pl/perlu .pm modules (i.e. PKGLIBDIR) and adding this location to @INC during the interpreter initialization ? Another idea is to allow a user to specify such location by adding a new custom GUC variable. Why won't setting this in the new on_perl_init setting work? It's even included in to documented examples using the standard lib module: http://developer.postgresql.org/pgdocs/postgres/plperl-under-the-hood.html#PLPERL-CONFIG The lack of support for SPI functions makes this hardly an adequate solution. I do have both modules and SPI calls in several pl/perlu functions. -- Alexey Klyukin http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc -- 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] knngist patch support
On Thu, 11 Feb 2010, Oleg Bartunov wrote: On Thu, 11 Feb 2010, Tom Lane wrote: My own feeling about it is that I much preferred the original proposal of a contrib module with little or no change to core code. I don't want to be changing core code for this at this late hour. If it were only touching GIST I'd be willing to rely on your and Teodor's expertise in that module, but it's not. It whacks around the planner, it makes questionable changes in the operator class structure, and the last We splitted patch to make review easy, probably by several reviewers, since we touched several subsystems. http://archives.postgresql.org/message-id/4b4ccb9f.8080...@sigaev.ru Patch for planner is 5600 bytes long, not so big. aha, we originally submit contrib module, which didn't touch anything you mentioned, we improve stuff to follow discussion and now we are out of luck %( version I saw hadn't any documentation whatever. It's not committable on documentation grounds alone, even if everybody was satisfied about the code. well, there is enough documentation to review patch. In my understanding this was always enough to submit code. User's documentation is depend on discussion and review and can be added later before releasing beta. How do you feel about going back to the original contrib module for now and resubmitting the builtin version for 9.1? Hmm, one good thing is that rbtree seems ok for submisson. We need to discuss this, if it's good for PostGIS community. I'd not complain about this decision if it touch my interests only, I could live with closed-source patch. Contrib module is a big step backward and will produce compatibility problem, since we'll have to use awkward operation , which works different with/without index. Also, it'd be very difficult to add support to other contrib modules (btree_gist, pg_trgm). Links: Heikki complaint - http://archives.postgresql.org/message-id/4b0b8c30.2080...@enterprisedb.com Simon - http://archives.postgresql.org/message-id/1259115190.27757.11194.ca...@ebony Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] TCP keepalive support for libpq
Kevin Grittner kevin.gritt...@wicourts.gov writes: those people who create 2000 lightly used connections to the database might feel differently. Yeah I still run against installation using the infamous PHP pconnect() function. You certainly don't want to add some load there, but that could urge them into arranging for being able to use pgbouncer in transaction pooling mode (and stop using pconnect(), damn it). Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Aidan Van Dyk wrote:
* Heikki Linnakangas heikki.linnakan...@enterprisedb.com [100211 09:17]:
If the file is just being copied to the archive when restore_command
('cp', say) is launched, it will copy a half file. That's not a problem
for PITR, because PITR will end at the end of valid WAL anyway, but
returning a half WAL file in standby mode is a problem.
But it can be a problem - without the last WAL (or at least enough of
it) the master switched and archived, you have no guarantee of having
being consistent again (I'm thinking specifically of recovering from a
fresh backup)
You have to wait for the last WAL file required by the backup to be
archived before starting recovery. Otherwise there's no guarantee anyway.
We could well just document that you should do that, ie. make sure the
file appears in the archive atomically with the right size.
I have to admit, today was the first time I went and re-read the PITR
docs, and no, the docs don't seem to talk about that... Maybe it was
just plain obvious to me because it (the atomic apperance) is something
unix devloppers have always had to deal with, so it's ingrained in me.
But I'm *sure* that I've seen that bandied around as common knowledge on
the lists, and one of the reasons we alway see warnings about using
rsync instead of plain SCP, etc.
So ya, we should probably mention that somewhere in the docs. Section
24.3.6. Caveats?
-1. it isn't necessary for PITR. It's a new requirement for
standby_mode='on', unless we add the file size check into the backend. I
think we should add the file size check to the backend instead and save
admins the headache.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] a common place for pl/perlu modules
Alexey Klyukin wrote: On Feb 11, 2010, at 6:24 PM, Andrew Dunstan wrote: Alexey Klyukin wrote: Hello, When developing pl/perlu functions common definitions and methods are often stored in external .pm modules. During deployment the modules should be installed somewhere in @INC to be reachable by the perl interpreter. However, installing the modules to a location outside of the PG installation makes it hard to have a consistent environment when running multiple PG versions on the same host. What do you think about defining a canonical place for pl/perlu .pm modules (i.e. PKGLIBDIR) and adding this location to @INC during the interpreter initialization ? Another idea is to allow a user to specify such location by adding a new custom GUC variable. Why won't setting this in the new on_perl_init setting work? It's even included in to documented examples using the standard lib module: http://developer.postgresql.org/pgdocs/postgres/plperl-under-the-hood.html#PLPERL-CONFIG The lack of support for SPI functions makes this hardly an adequate solution. I do have both modules and SPI calls in several pl/perlu functions. That has nothing to do with what you asked about, namely setting the include path. You can set the include path in on_perl_init with use lib and then use your modules in your plperlu functions, at which point SPI will be available. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Confusion over Python drivers
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 The solution is to write the query in an unambiguous way: SELECT $1::date + 1; which is good practice, anyway. If it's not obvious to the type inference system, it's probably not obvious to you, and will probably surprise you ;) That address this specific case, but it's ugly and not general. The right thing is to set the correct type when you're marshalling the parameters... Well, ugly is in the eye of the beholder, and it certainly is a general solution. Any query with ambiguity in its parameters should explicitly declare the types, inside the query itself. Having the driver indicate the type should be the exception, not the rule. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201002091811 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN 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] TCP keepalive support for libpq
Also, more importantly (from http://www.slony.info/documentation/slonyadmin.html): A WAN outage (or flakiness of the WAN in general) can leave database connections zombied, and typical TCP/IP behaviour will allow those connections to persist, preventing a slon restart for around two hours. Regards, Peter Geoghegan -- 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] TCP keepalive support for libpq
On Thu, 11 Feb 2010, Andrew Chernow wrote: Although, I think Dave's comments have made me change my mind about this patch. Looks like it serves a good purpose. That said, there is no guarentee the driver will implement the new feature ... JDBC seems to lack the ability to get the backing Socket object but java can set socket options. Maybe a JDBC kong fu master knows how to do this. Use the tcpKeepAlive connection option as described here: http://jdbc.postgresql.org/documentation/84/connect.html#connection-parameters Java can only enable/disable keep alives, it can't set the desired timeout. http://java.sun.com/javase/6/docs/api/java/net/Socket.html#setKeepAlive%28boolean%29 Kris Jurka -- 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] Bug on pg_lesslog
On Thu, 2010-02-11 at 23:39 +0900, Koichi Suzuki wrote: Dear Folks; A very serious bug was reported on pg_lesslog. So far, I found it's a bug in pg_compresslog. Please do not use pg_compresslog and pg_decompresslog until improved version is uploaded. I strongly advise to take base backup of your database. I apologize for inconvenience. I'll upload the new version ASAP. Should this go out on announce? Warmest Regards; -- Koichi Suzuki -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- 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] Writeable CTEs and empty relations
On Thu, 11 Feb 2010 10:53:22 -0500, Robert Haas robertmh...@gmail.com
wrote:
On Thu, Feb 11, 2010 at 8:46 AM, Marko Tiikkaja
marko.tiikk...@cs.helsinki.fi wrote:
On 2010-02-11 03:44 +0200, I wrote:
I'm going to have to disappoint a bunch of people and give up. :-(
Btw. would it make sense to apply the WITH-on-top-of-DML part of this
patch? At least to me, this seems useful because you can write a
RECURSIVE SELECT and then use UPDATE .. FROM or DELETE .. USING on that
CTE.
Hmm, that's a thought. Can you split out just that part?
Here's the patch. It's the same as the stuff in writeable CTE patches, but
I added regression tests.
Regards,
Marko Tiikkaja*** a/doc/src/sgml/ref/delete.sgml
--- b/doc/src/sgml/ref/delete.sgml
***
*** 21,30 PostgreSQL documentation
--- 21,36
refsynopsisdiv
synopsis
+ [ WITH [ RECURSIVE ] replaceable class=parameterwith_query/replaceable
[, ...] ]
DELETE FROM [ ONLY ] replaceable class=PARAMETERtable/replaceable [ [
AS ] replaceable class=parameteralias/replaceable ]
[ USING replaceable class=PARAMETERusing_list/replaceable ]
[ WHERE replaceable class=PARAMETERcondition/replaceable | WHERE
CURRENT OF replaceable class=PARAMETERcursor_name/replaceable ]
[ RETURNING * | replaceable
class=parameteroutput_expression/replaceable [ [ AS ] replaceable
class=parameteroutput_name/replaceable ] [, ...] ]
+
+ phrasewhere replaceable class=parameterwith_query/replaceable
is:/phrase
+
+ replaceable class=parameterwith_query_name/replaceable [ (
replaceable class=parametercolumn_name/replaceable [, ...] ) ] AS (
replaceable class=parameterselect/replaceable | replaceable
class=parameterinsert/replaceable | replaceable
class=parameterupdate/replaceable | replaceable
class=parameterdelete/replaceable )
+
/synopsis
/refsynopsisdiv
***
*** 84,89 DELETE FROM [ ONLY ] replaceable
class=PARAMETERtable/replaceable [ [ AS ]
--- 90,104
variablelist
varlistentry
+ termreplaceable class=PARAMETERwith_query/replaceable/term
+ listitem
+ para
+ For information about with_query, see
+ xref linkend=sql-with endterm=sql-with-title.
+ /para
+ /listitem
+/varlistentry
+varlistentry
termliteralONLY//term
listitem
para
*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***
*** 21,29 PostgreSQL documentation
--- 21,36
refsynopsisdiv
synopsis
+ [ WITH [ RECURSIVE ] replaceable class=parameterwith_query/replaceable
[, ...] ]
INSERT INTO replaceable class=PARAMETERtable/replaceable [ (
replaceable class=PARAMETERcolumn/replaceable [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { replaceable
class=PARAMETERexpression/replaceable | DEFAULT } [, ...] ) [, ...] |
replaceable class=PARAMETERquery/replaceable }
[ RETURNING * | replaceable
class=parameteroutput_expression/replaceable [ [ AS ] replaceable
class=parameteroutput_name/replaceable ] [, ...] ]
+
+ phrasewhere replaceable class=parameterwith_query/replaceable
is:/phrase
+
+ replaceable class=parameterwith_query_name/replaceable [ (
replaceable class=parametercolumn_name/replaceable [, ...] ) ] AS (
replaceable class=parameterselect/replaceable | replaceable
class=parameterinsert/replaceable | replaceable
class=parameterupdate/replaceable | replaceable
class=parameterdelete/replaceable )
+
+
/synopsis
/refsynopsisdiv
***
*** 85,90 INSERT INTO replaceable class=PARAMETERtable/replaceable [
( replaceable
--- 92,106
variablelist
varlistentry
+ termreplaceable class=PARAMETERwith_query/replaceable/term
+ listitem
+ para
+ For information about with_query, see
+ xref linkend=sql-with endterm=sql-with-title.
+ /para
+ /listitem
+/varlistentry
+varlistentry
termreplaceable class=PARAMETERtable/replaceable/term
listitem
para
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***
*** 58,64 SELECT [ ALL | DISTINCT [ ON ( replaceable
class=parameterexpression/replac
phraseand replaceable class=parameterwith_query/replaceable
is:/phrase
! replaceable class=parameterwith_query_name/replaceable [ (
replaceable class=parametercolumn_name/replaceable [, ...] ) ] AS (
replaceable class=parameterselect/replaceable )
TABLE { [ ONLY ] replaceable class=parametertable_name/replaceable [ *
] | replaceable class=parameterwith_query_name/replaceable }
/synopsis
--- 58,64
phraseand replaceable class=parameterwith_query/replaceable
is:/phrase
! replaceable class=parameterwith_query_name/replaceable [ (
replaceable class=parametercolumn_name/replaceable [, ...] ) ] AS (
replaceable class=parameterselect/replaceable | replaceable
class=parameterinsert/replaceable | replaceable
class=parameterupdate/replaceable | replaceable
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Aidan Van Dyk wrote: * Heikki Linnakangas heikki.linnakan...@enterprisedb.com [100211 09:17]: Yeah, if you're careful about that, then this change isn't required. But pg_standby protects against that, so I think it'd be reasonable to have the same level of protection built-in. It's not a lot of code. This 1 check isn't, but what about the rest of the things pg_standby does. How much functionality should we bring it? Ideally, all of it. Well, how about we bite the bullet then and add enough bells and whistles to the backend that pg_standby really isn't needed anymore, and remove it from contrib? Looking at the options to pg_standby, we're not missing much: Options: -c copies file from archive (default) -l links into archive (leaves file in archive) Obsolete (link mode not supported anymore) -d generate lots of debugging output (testing only) We have DEBUG statements in the server... -k NUMFILESTOKEEP if RESTARTWALFILE not used, removes files prior to limit (0 keeps all) This is dangerous, and obsoleted by the RESTARTWALFILE option (%r). -r MAXRETRIES max number of times to retry, with progressive wait (default=3) Frankly this seems pretty useless, but it would be easy to implement -s SLEEPTIME seconds to wait between file checks (min=1, max=60, default=5) The sleep time in the backend is currently hard-coded at 5 s. Should we make it configurable? -t TRIGGERFILE defines a trigger file to initiate failover (no default) We have this in the backend already. -w MAXWAITTIME max seconds to wait for a file (0=no limit) (default=0) We don't have a timeout in the backend. Should we? (the timeout in pg_standby won't work, even if we add the option to use pg_standby with standby_mode='on' as Simon suggested) So the only major feature we're missing is the ability to clean up old files. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
* Heikki Linnakangas heikki.linnakan...@enterprisedb.com [100211 12:04]: But it can be a problem - without the last WAL (or at least enough of it) the master switched and archived, you have no guarantee of having being consistent again (I'm thinking specifically of recovering from a fresh backup) You have to wait for the last WAL file required by the backup to be archived before starting recovery. Otherwise there's no guarantee anyway. Right, but now define wait for. If you pull only half the last WAL (because you've accepted that you *can* have short WAL files in the archive), you have the problem with PITR. Is it wait for it to be in the archive, or wait for it to be in the archive, and be sure that the contents satisfy some criteria. I've always made my PITR such that in the archive (i.e. the first moment a recovery can see it) implies that it's bit-for-bit identical to the original (or at least as bit-for-bit I can assume by checking various hashes I can afford to). I just assumed that was kind of common practice. I'm amazed that partial WAL files are every available in anyones archive, for anyone's restore command to actually pull. I find that scarry, and sure, probably won't regularly be noticed... But man, I'ld hate the time I need that emergency PITR restore to be the one time when it needs that WAL, pulls it slightly before the copy has finished (i.e. the master is pushing the WAL over a WAN to a 2nd site), and have my restore complete consistently... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Writeable CTEs and empty relations
On Thu, 11 Feb 2010 19:28:28 +0200, I wrote:
On Thu, 11 Feb 2010 10:53:22 -0500, Robert Haas robertmh...@gmail.com
wrote:
On Thu, Feb 11, 2010 at 8:46 AM, Marko Tiikkaja
marko.tiikk...@cs.helsinki.fi wrote:
On 2010-02-11 03:44 +0200, I wrote:
I'm going to have to disappoint a bunch of people and give up. :-(
Btw. would it make sense to apply the WITH-on-top-of-DML part of this
patch? At least to me, this seems useful because you can write a
RECURSIVE SELECT and then use UPDATE .. FROM or DELETE .. USING on that
CTE.
Hmm, that's a thought. Can you split out just that part?
Here's the patch. It's the same as the stuff in writeable CTE patches,
but
I added regression tests.
Whoops. The reference section in docs still had some traces of writeable
CTEs. Updated patch attached.
Regards,
Marko Tiikkaja*** a/doc/src/sgml/ref/delete.sgml
--- b/doc/src/sgml/ref/delete.sgml
***
*** 21,30 PostgreSQL documentation
--- 21,36
refsynopsisdiv
synopsis
+ [ WITH [ RECURSIVE ] replaceable class=parameterwith_query/replaceable
[, ...] ]
DELETE FROM [ ONLY ] replaceable class=PARAMETERtable/replaceable [ [
AS ] replaceable class=parameteralias/replaceable ]
[ USING replaceable class=PARAMETERusing_list/replaceable ]
[ WHERE replaceable class=PARAMETERcondition/replaceable | WHERE
CURRENT OF replaceable class=PARAMETERcursor_name/replaceable ]
[ RETURNING * | replaceable
class=parameteroutput_expression/replaceable [ [ AS ] replaceable
class=parameteroutput_name/replaceable ] [, ...] ]
+
+ phrasewhere replaceable class=parameterwith_query/replaceable
is:/phrase
+
+ replaceable class=parameterwith_query_name/replaceable [ (
replaceable class=parametercolumn_name/replaceable [, ...] ) ] AS (
replaceable class=parameterselect/replaceable )
+
/synopsis
/refsynopsisdiv
***
*** 84,89 DELETE FROM [ ONLY ] replaceable
class=PARAMETERtable/replaceable [ [ AS ]
--- 90,104
variablelist
varlistentry
+ termreplaceable class=PARAMETERwith_query/replaceable/term
+ listitem
+ para
+ For information about with_query, see
+ xref linkend=sql-with endterm=sql-with-title.
+ /para
+ /listitem
+/varlistentry
+varlistentry
termliteralONLY//term
listitem
para
*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***
*** 21,29 PostgreSQL documentation
--- 21,36
refsynopsisdiv
synopsis
+ [ WITH [ RECURSIVE ] replaceable class=parameterwith_query/replaceable
[, ...] ]
INSERT INTO replaceable class=PARAMETERtable/replaceable [ (
replaceable class=PARAMETERcolumn/replaceable [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { replaceable
class=PARAMETERexpression/replaceable | DEFAULT } [, ...] ) [, ...] |
replaceable class=PARAMETERquery/replaceable }
[ RETURNING * | replaceable
class=parameteroutput_expression/replaceable [ [ AS ] replaceable
class=parameteroutput_name/replaceable ] [, ...] ]
+
+ phrasewhere replaceable class=parameterwith_query/replaceable
is:/phrase
+
+ replaceable class=parameterwith_query_name/replaceable [ (
replaceable class=parametercolumn_name/replaceable [, ...] ) ] AS (
replaceable class=parameterselect/replaceable )
+
+
/synopsis
/refsynopsisdiv
***
*** 85,90 INSERT INTO replaceable class=PARAMETERtable/replaceable [
( replaceable
--- 92,106
variablelist
varlistentry
+ termreplaceable class=PARAMETERwith_query/replaceable/term
+ listitem
+ para
+ For information about with_query, see
+ xref linkend=sql-with endterm=sql-with-title.
+ /para
+ /listitem
+/varlistentry
+varlistentry
termreplaceable class=PARAMETERtable/replaceable/term
listitem
para
*** a/doc/src/sgml/ref/update.sgml
--- b/doc/src/sgml/ref/update.sgml
***
*** 21,32 PostgreSQL documentation
--- 21,38
refsynopsisdiv
synopsis
+ [ WITH [ RECURSIVE ] replaceable class=parameterwith_query/replaceable
[, ...] ]
UPDATE [ ONLY ] replaceable class=PARAMETERtable/replaceable [ [ AS ]
replaceable class=parameteralias/replaceable ]
SET { replaceable class=PARAMETERcolumn/replaceable = {
replaceable class=PARAMETERexpression/replaceable | DEFAULT } |
( replaceable class=PARAMETERcolumn/replaceable [, ...] ) = (
{ replaceable class=PARAMETERexpression/replaceable | DEFAULT } [, ...] )
} [, ...]
[ FROM replaceable class=PARAMETERfrom_list/replaceable ]
[ WHERE replaceable class=PARAMETERcondition/replaceable | WHERE
CURRENT OF replaceable class=PARAMETERcursor_name/replaceable ]
[ RETURNING * | replaceable
class=parameteroutput_expression/replaceable [ [ AS ] replaceable
class=parameteroutput_name/replaceable ] [, ...] ]
+
+ phrasewhere replaceable class=parameterwith_query/replaceable
is:/phrase
+
+ replaceable
Re: [HACKERS] a common place for pl/perlu modules
On Feb 11, 2010, at 7:07 PM, Andrew Dunstan wrote: Alexey Klyukin wrote: On Feb 11, 2010, at 6:24 PM, Andrew Dunstan wrote: Alexey Klyukin wrote: Hello, When developing pl/perlu functions common definitions and methods are often stored in external .pm modules. During deployment the modules should be installed somewhere in @INC to be reachable by the perl interpreter. However, installing the modules to a location outside of the PG installation makes it hard to have a consistent environment when running multiple PG versions on the same host. What do you think about defining a canonical place for pl/perlu .pm modules (i.e. PKGLIBDIR) and adding this location to @INC during the interpreter initialization ? Another idea is to allow a user to specify such location by adding a new custom GUC variable. Why won't setting this in the new on_perl_init setting work? It's even included in to documented examples using the standard lib module: http://developer.postgresql.org/pgdocs/postgres/plperl-under-the-hood.html#PLPERL-CONFIG The lack of support for SPI functions makes this hardly an adequate solution. I do have both modules and SPI calls in several pl/perlu functions. That has nothing to do with what you asked about, namely setting the include path. You can set the include path in on_perl_init with use lib and then use your modules in your plperlu functions, at which point SPI will be available. Ah, it seems I misinterpreted the documentation. This is much better, but still it requires setting the path explicitly. What about having a default location for the modules that is automatically added to @INC and is recommended in the documentation as a place to put .pm files ? -- Alexey Klyukin http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] [HACKERS] Bug on pg_lesslog
Joshua D. Drake wrote: On Thu, 2010-02-11 at 23:39 +0900, Koichi Suzuki wrote: Dear Folks; A very serious bug was reported on pg_lesslog. So far, I found it's a bug in pg_compresslog. Please do not use pg_compresslog and pg_decompresslog until improved version is uploaded. I strongly advise to take base backup of your database. I apologize for inconvenience. I'll upload the new version ASAP. Should this go out on announce? I certainly think so. Anyone who gets caught by surprise on this could quite possibly lose all their data! I (fortunately) caught it during TESTING of my archives - before I needed them. -- Karl Denninger attachment: karl.vcf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: -1. it isn't necessary for PITR. It's a new requirement for standby_mode='on', unless we add the file size check into the backend. I think we should add the file size check to the backend instead and save admins the headache. I think the file size check needs to be in the backend purely on safety grounds. Whether we make pg_standby useful for this context is a different discussion. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Aidan Van Dyk wrote: * Heikki Linnakangas heikki.linnakan...@enterprisedb.com [100211 12:04]: But it can be a problem - without the last WAL (or at least enough of it) the master switched and archived, you have no guarantee of having being consistent again (I'm thinking specifically of recovering from a fresh backup) You have to wait for the last WAL file required by the backup to be archived before starting recovery. Otherwise there's no guarantee anyway. Right, but now define wait for. As in don't start postmaster until the last WAL file needed by backup has been fully copied to the archive. (because you've accepted that you *can* have short WAL files in the archive) Only momentarily, while the copy is in progress. I've always made my PITR such that in the archive (i.e. the first moment a recovery can see it) implies that it's bit-for-bit identical to the original (or at least as bit-for-bit I can assume by checking various hashes I can afford to). I just assumed that was kind of common practice. It's certainly good practice, agreed, but hasn't been absolutely required. I'm amazed that partial WAL files are every available in anyones archive, for anyone's restore command to actually pull. I find that scarry, and sure, probably won't regularly be noticed... But man, I'ld hate the time I need that emergency PITR restore to be the one time when it needs that WAL, pulls it slightly before the copy has finished (i.e. the master is pushing the WAL over a WAN to a 2nd site), and have my restore complete consistently... It's not as dramatic as you make it sound. We're only talking about the last WAL file, and only when it's just being copied to the archive. If you have a archive_command like 'cp', and you look at the archive at the same millisecond that 'cp' runs, then yes you will see that the latest WAL file in the archive is only partially copied. It's not a problem for robustness; if you had looked one millisecond earlier you would not have seen the file there at all. Windows 'copy' command preallocates the whole file, which poses a different problem: if you look at the file while it's being copied, the file has the right length, but isn't in fact fully copied yet. I think 'rsync' has the same problem. To avoid that issue, you have to use something like copy+rename to make it atomic. There isn't much we can do in the server (or in pg_standby) to work around that, because there's no way to distinguish a file that's being copied from a fully-copied corrupt file. We do advise to set up an archive_command that doesn't overwrite existing files. That together with a partial WAL segment can cause a problem: if archive_command crashes while it's writing the file, leaving a partial file in the archive, the subsequent run of archive_command won't overwrite it and will get stuck trying. However, there's a small window for that even if you put the file into the archive atomically: if you crash just after fully copying the file, but before the .done file is created, upon restart the server will also try to copy the file to archive, find that it already exists, and fail. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
On Thu, 2010-02-11 at 13:08 -0500, Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: -1. it isn't necessary for PITR. It's a new requirement for standby_mode='on', unless we add the file size check into the backend. I think we should add the file size check to the backend instead and save admins the headache. I think the file size check needs to be in the backend purely on safety grounds. Whether we make pg_standby useful for this context is a different discussion. Happy with that. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I think 'rsync' has the same problem. There is a switch you can use to create the problem under rsync, but by default rsync copies to a temporary file name and moves the completed file to the target name. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TCP keepalive support for libpq
]] Robert Haas | I've sometimes wondered why keepalives aren't the default for all TCP | connections. They seem like they're usually a Good Thing (TM), but I | wonder if we can think of any situations where someone might not want | them? As somebody mentioned somewhere else (I think): If you pay per byte transmitted, be it 3G/GPRS. Or if you're on a very, very high-latency link or have no bandwidth. Like, a rocket to Mars or maybe the moon. While I think they are valid use-cases, requiring people to change the defaults if that kind of thing sounds like a sensible solution to me. -- Tollef Fog Heen UNIX is user friendly, it's just picky about who its friends are -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
On Thu, 2010-02-11 at 11:27 -0500, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: You still have to perform a backup of the database prior to upgrade and that also must scan the whole database, so the overall time to upgrade will still vary according to database size. So I don't see any overall benefit, just risk, and I cited a similar situation where that risk has already materialized into damage for a user in at least one case. You cited no such case; you merely hypothesized that it could happen. Apologies for not providing more details. There was a serious problem in an 8.4.1 database just before Christmas. Mostly off-list but a few community members knew of it. The db had been upgraded from 8.4.0, where some data loss issues existed and the corruption persisted even in a release where it could never have been created. As for the alleged risks involved, keeping the tqual support for MOVED bits cannot create any data-loss risks that haven't existed right along in every previous release. But depending on MOVED bits to be reliably gone after a pg_upgrade would introduce a very obvious data loss risk that wasn't there before, namely that pg_upgrade misses one. Avoiding a scan before running pg_upgrade is just a performance optimisation. I don't think we should be optimising an upgrade in this way, especially since sane people do database backups before upgrade anyway. The optimisation is misplaced. The fact that we are actively planning to have code in the server that only gets executed if pg_upgrade screws up scares the hell out of me. If someone else suggested it you'd give them both barrels. We should be ensuring pg_upgrade works, not giving it leeway to miss a few things but work quickly. I think pg_upgrade should be investing time in a utility which pre-scans the database to check it is safely upgradeable, not have the server support an external utility that has unsafe usage procedures. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
On Thu, Feb 11, 2010 at 1:33 PM, Simon Riggs si...@2ndquadrant.com wrote: Avoiding a scan before running pg_upgrade is just a performance optimisation. But using pg_upgrade AT ALL is also a performance optimization; in fact AFAICS it's the only reason to use pg_upgrade. So if you take that away there's no reason to use it at all. ...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] [PATCH] Provide rowcount for utility SELECTs
On Mon, Feb 8, 2010 at 5:53 AM, Boszormenyi Zoltan z...@cybertec.at wrote: New patch is attached with the discussed changes. This looks OK to me now, but it lacks docs. I'll set it to Waiting on Author. ...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] [PATCH] Provide rowcount for utility SELECTs
Boszormenyi Zoltan escribió: Robert Haas írta: ... OK, please change it. New patch is attached with the discussed changes. This looks all wrong. PORTAL_ONE_SELECT is now being passed through FillPortalStore, which runs it to completion, whereas it was previously passed via PortalRunSelect first, which has different semantics regarding the count arg. Also, even if that weren't wrong, FillPortalStore states at its header comment that it is only used for the other two cases (ONE_RETURNING and UTIL_SELECT), but now is being used for ONE_SELECT as well. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
On Thu, 2010-02-11 at 13:42 -0500, Robert Haas wrote: On Thu, Feb 11, 2010 at 1:33 PM, Simon Riggs si...@2ndquadrant.com wrote: Avoiding a scan before running pg_upgrade is just a performance optimisation. But using pg_upgrade AT ALL is also a performance optimization; in fact AFAICS it's the only reason to use pg_upgrade. So if you take that away there's no reason to use it at all. I understand that the final process to switch from one release to another needs to be quick. Before that we can have any number of preparatory steps. One of those is backup, if you're sane. Another one should be a preparatory step that can be performed while the database is still on-line that checks that everything is in a good state for upgrade. No corruptions, no weird flags, everything good. If that last step is part of all upgrade procedures, including both minor and major we will all be happier and healthier. And the server can depend on that check and doesn't need to check itself for those weirdnesses from an earlier era. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] log_error_verbosity function display
Bruce Momjian wrote: FYI, here is the output that had me confused: ERROR: 42P01: relation lkjasdf does not exist at character 15 LOCATION: parserOpenTable, parse_relation.c:858 STATEMENT: select * from lkjasdf; How about something like ERROR: 42P01: relation lkjasdf does not exist at character 15 LOCATION: parserOpenTable function in file parse_relation.c:858 STATEMENT: select * from lkjasdf; -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
Simon Riggs si...@2ndquadrant.com writes: Avoiding a scan before running pg_upgrade is just a performance optimisation. I don't think we should be optimising an upgrade in this way, especially since sane people do database backups before upgrade anyway. The optimisation is misplaced. The fact that we are actively planning to have code in the server that only gets executed if pg_upgrade screws up scares the hell out of me. If someone else suggested it you'd give them both barrels. If we were putting in new, never tested, code of that description I'd be scared of it too. Code that's been there since the previous century, however, is not even remotely the same type of case. Arguably, there is bigger risk in removing it from tqual.c than not doing so --- it is not impossible to screw up the removal ... 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] log_error_verbosity function display
On Thu, Feb 11, 2010 at 6:03 AM, Bruce Momjian br...@momjian.us wrote: Of course, maybe the word LOCATION is wrong and it should be FUNCTION: ERROR: 42P01: relation lkjasdf does not exist at character 15 FUNCTION: parserOpenTable(), parse_relation.c:858 STATEMENT: select * from lkjasdf; i like this with or without the (), but maybe we are breaking client apps if change that -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Provide rowcount for utility SELECTs
On Thu, Feb 11, 2010 at 2:04 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Boszormenyi Zoltan escribió: Robert Haas írta: ... OK, please change it. New patch is attached with the discussed changes. This looks all wrong. PORTAL_ONE_SELECT is now being passed through FillPortalStore, which runs it to completion, whereas it was previously passed via PortalRunSelect first, which has different semantics regarding the count arg. Also, even if that weren't wrong, FillPortalStore states at its header comment that it is only used for the other two cases (ONE_RETURNING and UTIL_SELECT), but now is being used for ONE_SELECT as well. I was all prepared to admit that I hadn't actually looked at the patch carefully enough, but I just looked at (and CVS HEAD) again and what you've written here doesn't appear to describe what I'm seeing in the code: if ((portal-strategy != PORTAL_ONE_SELECT) (!portal-holdStore)) FillPortalStore(portal, isTopLevel); So one of us is confused... it may well be me. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] log_error_verbosity function display
Alvaro Herrera wrote: Bruce Momjian wrote: FYI, here is the output that had me confused: ERROR: 42P01: relation lkjasdf does not exist at character 15 LOCATION: parserOpenTable, parse_relation.c:858 STATEMENT: select * from lkjasdf; How about something like ERROR: 42P01: relation lkjasdf does not exist at character 15 LOCATION: parserOpenTable function in file parse_relation.c:858 STATEMENT: select * from lkjasdf; Well, that looks good, but perhaps we can trim it down a bit: ERROR: 42P01: relation lkjasdf does not exist at character 15 LOCATION: function parserOpenTable, file parse_relation.c:858 STATEMENT: select * from lkjasdf; or even shorter: ERROR: 42P01: relation lkjasdf does not exist at character 15 LOCATION: parserOpenTable() in file parse_relation.c:858 STATEMENT: select * from lkjasdf; -- 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] Re: [COMMITTERS] pgsql: Remove old-style VACUUM FULL (which was known for a little while
On Thu, Feb 11, 2010 at 2:03 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-02-11 at 13:42 -0500, Robert Haas wrote: On Thu, Feb 11, 2010 at 1:33 PM, Simon Riggs si...@2ndquadrant.com wrote: Avoiding a scan before running pg_upgrade is just a performance optimisation. But using pg_upgrade AT ALL is also a performance optimization; in fact AFAICS it's the only reason to use pg_upgrade. So if you take that away there's no reason to use it at all. I understand that the final process to switch from one release to another needs to be quick. Before that we can have any number of preparatory steps. One of those is backup, if you're sane. Another one should be a preparatory step that can be performed while the database is still on-line that checks that everything is in a good state for upgrade. No corruptions, no weird flags, everything good. If that last step is part of all upgrade procedures, including both minor and major we will all be happier and healthier. And the server can depend on that check and doesn't need to check itself for those weirdnesses from an earlier era. That's a good point. I think we're going to keep running across situations where we'd like to have a way of verifying that a particular invariant holds for every page of a given relation. With the infrastructure that we have now, we're going to be stuck with the MOVED_xxx bits essentially forever. When we got to release 9.5, we still won't be able to drop this code, because there could be someone who used pg_upgrade to go from 8.3 or 8.4 to 9.0 and then to 9.1 and then to 9.2 and then to 9.3 and then to 9.4 and now wants to go to 9.5. I'm not quite sure how to do this in practice. One idea would be to record the catversion that created the relation in pg_class, and make pg_upgrade preserve the catversion, but make CLUSTER or similar bump it on successful completion. But I'm not sure if that covers all the cases we care about, or if requiring CLUSTER is too intrusive. I think it's probably too late to work on this for 9.0, but it would be nice to get it done for 9.1 so that we can make a long-term plan to phase things like this out without relying on making statements like if before you pg_upgrade'd your database X times it was originally from version X or earlier, and if you ever vacuum full'd it and any of those tuples are still around, you might have a problem - but we can't tell you whether that's the case or not. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
