Re: [HACKERS] LogStandbySnapshot (was another thread)
Simon Riggs wrote: On Tue, 2010-05-04 at 13:23 -0400, Tom Lane wrote: * LogStandbySnapshot is merest fantasy: no guarantee that either the XIDs list or the locks list will be consistent with the point in WAL where it will get inserted. What's worse, locking things down enough to guarantee consistency would be horrid for performance, or maybe even deadlock-inducing. Could lose both ways: list might contain an XID whose commit/abort went to WAL before the snapshot did, or list might be missing an XID started just after snap was taken, The latter case could possibly be dealt with via nextXid filtering, but that doesn't fix the former case, and anyway we have both ends of the same problem for locks. This was the only serious complaint on your list, so lets address it. Clearly we don't want to lock everything down, for all the reasons you say. That creates a gap between when data is derived and when data logged to WAL. Right. This was discussed first in August: http://archives.postgresql.org/message-id/4a8ce561.4000...@enterprisedb.com. I concur that the idea is that we deal at replay with the fact that the snapshot lags behind. At replay, any locks/XIDs in the snapshot that have already been committed/aborted are ignored. For any locks/XIDs taken just after the snapshot was taken, the replay will see the other WAL records with that information. We need to add comments explaining all that. -- 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] max_standby_delay considered harmful
On Tue, 2010-05-04 at 23:06 -0400, Bruce Momjian wrote: Should I be concerned that we are redesigning HS features at this stage in the release? We knew we had to have one final discussion on HS snapshots. This is it. Tom has raised valid issues, all of which already known. If we can address them, we should. A straightforward patch [walrcv_timestamp.patch] to address all of those points. (Posted 13 hours prior to your post. That it was ignored by all while debate continued is one point of concern, for me, though there seems to have been confusion as to what that patch actually was.) Tom has also raised a separate proposal, though that hasn't yet been properly explained and there has been much debate about what he actually meant. It is possible there is something worthwhile there, if that involves adding a new capability. Myself, Stephen, Josh and Greg say that changing max_standby_delay so there is no bounded startup time would be a bad thing, if that is its only behaviour in 9.0. I will tidy up walrcv_timestamp.patch and apply on Thu evening unless there are concise, rational objections to that patch, which I consider to be a bug fix and not blocked by beta. Tom raised 7 other main points, that following detailed investigation have resulted in 2 minor bugs, 2 unresolved questions on the patch and 1 further request for code comments. The 2 bugs affect corner cases only and so are minor. They will be fixed over next few days since not instant fixes. Open items list updated with items mentioned here, plus performance query discussed on other thread. Nothing much here likely to cause a problem if we need to go beta immediately, IMO. I am mostly unavailable for next few days. (Repairing bikeshed.) Expect at least 3 commits from me over next few days. -- 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] Reg: SQL Query for Postgres 8.4.3
Hi Mark, I took the output of the Postgresql. Please find the output: Package: postgresql-8.3 State: installed Automatically installed: no Version: 8.3.9-0ubuntu8.10 Priority: optional Section: misc Maintainer: Martin Pitt martin.p...@ubuntu.com Uncompressed Size: 14.2M Depends: libc6 (= 2.4), libcomerr2 (= 1.01), libkrb53 (= 1.6.dfsg.2), libldap-2.4-2 (= 2.4.7), libpam0g (= 0.99.7.1), libpq5 (= 8.3~beta1), libssl0.9.8 (= 0.9.8f-5), libxml2 (= 2.6.27), postgresql-client-8.3, postgresql-common (= 79), tzdata, ssl-cert, locales Suggests: oidentd | ident-server Conflicts: postgresql ( 7.5) Description: object-relational SQL database, version 8.3 server PostgreSQL is a fully featured object-relational database management system. It supports a large part of the SQL standard and is designed to be extensible by users in many aspects. Some of the features are: ACID transactions, foreign keys, views, sequences, subqueries, triggers, user-defined types and functions, outer joins, multiversion concurrency control. Graphical user interfaces and bindings for many programming languages are available as well. This package provides the database server for PostgreSQL 8.3. Servers for other major release versions can be installed simultaneously and are coordinated by the postgresql-common package. A package providing ident-server is needed if you want to authenticate remote connections with identd. Regards, Srinivas Naik On Wed, May 5, 2010 at 1:21 AM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: On 05/05/10 13:15, Mark Kirkwood wrote: Please log into postgres do: SELECT version(); (and Robert suggested) Should read *as* Robert suggested - sorry. Also you could do this from the os: $ aptitude show postgresql-8.3* *which will display more detail for the version. Cheers Mark * *
Re: [HACKERS] max_standby_delay considered harmful
On Wed, May 5, 2010 at 3:16 AM, Simon Riggs si...@2ndquadrant.com wrote: Expect at least 3 commits from me over next few days. I think you need to rethink the way that you decide when it's time to commit things. There is certainly no consensus on any of the things you are proposing to commit, nor have they been adequately (or, uh, at all) reviewed. Saying that your proposal addresses all of Tom's objections doesn't make it so. I am planning to read that patch and offer an opinion on it, but I haven't done so yet and I imagine Tom will weigh in at some point as well. Racing to commit a pile of code that nobody else has tested is not going to improve anything. ...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] buildfarm building all live branches from git
Alex Hunsaker wrote: On Mon, May 3, 2010 at 14:04, Andrew Dunstan and...@dunslane.net wrote: [ Awesome work getting buildfarm support for git ] Note, this is running from my test git repo, not the community's repo. BTW +1 for gitting (heh, git puns are fun) a good git repo published. Ive given up trying to trust it for back branches and always either go to release tarballs or cvs. The repo I have created is currently available publicly at http://github.com/oicu/pg-cvs-mirror and you can clone git://github.com/oicu/pg-cvs-mirror.git It is kept fairly up to date (mostly within an hour of the community CVS repo) and checked daily for validity against all live branches. Sadly, that means its change links will be broken - I'm not exactly sure what gets hashed to provide a commit ID in git, but the IDs don't match between these two repos. Yeah, git basically hashes *everything* including the previous commits. So if one commit is different in the repo all the commits after that will have a different hash :-( Right. However, I have in fact solved this issue by allowing buildfarm members to specify a url to show changesets. In tha case of quoll this is set thus scm_url = 'http://github.com/oicu/pg-cvs-mirror/commit/', and its change links now do the right thing. The new client code should be released in about a week. 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] max_standby_delay considered harmful
Simon Riggs wrote: The attached patch redefines standby delay to be the amount of time elapsed from point of receipt to point of application. The point of receipt is reset every chunk of data when streaming, or every file when reading file by file. In all cases this new time is later than the latest log time we would have used previously. This seems completely wrong to me. If the WAL receiver keeps receiving stuff, (last receive timestamp) - (current timestamp) would never be more than a few seconds. Regardless of how much applying the WAL has fallen behind. To accomplish what you're trying to accomplish, you would need to label each received WAL record with the timestamp when it was received, and compare the reception timestamp of the record you're applying against current timestamp. -- 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] max_standby_delay considered harmful
Tom Lane wrote: Comments? There's currently three ways to set max_standby_delay: max_standby_delay = -1 # Query wins max_standby_delay = 0 # Recovery wins max_standby_delay X # Query wins until lag X. As Tom points out, the 3rd option has all sorts of problems. I very much like the behavior that max_standby_delay tries to accomplish, but I have to agree that it's not very reliable as it is. I don't like Tom's proposal either; the standby can fall behind indefinitely, and queries get a varying grace period. Let's rip out the concept of a delay altogether, and make it a boolean. If you really want your query to finish, set it to -1 (using the current max_standby_delay nomenclature). If recovery is important to you, set it to 0. If you have the monitoring in place to sensibly monitor the delay between primary and standby, and you want a limit on that, you can put together a script to flip the switch in postgresql.conf if the standby falls too much behind. It would be nice to make that settable per-session, BTW. Though as soon as you have one session using -1, the standby could fall behind. Still, it might be useful if you run both kinds of queries on the same standby. Ok, now that we've gotten over that, here's another proposal for what a delay setting could look like. Let's have a setting similar to statement_timeout, that specifies how long a statement is allowed to run until it becomes subject to killing if it conflicts with recovery (actually, it would have to be a per-transaction setting, at least in serializable mode). This would be similar to Tom's proposal, and it would have the same drawback that it would give no guarantee on how much the standby can fall behind. However, it would be easier to understand: a query gets to run for X seconds, and after that it will be killed if it gets in the way. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] possible memory leak with SRFs
Hi, I saw this behavior with latest GIT head: create table xlarge(val numeric(19,0)); insert into xlarge values(generate_series(1,5)); The above generate series will return an int8 which will then be casted to numeric (via int8_to_numericvar) before being inserted into the table. I observed that the ExprContext memory associated with econtext-ecxt_per_tuple_memory is slowly bloating up till the end of the insert operation. This becomes significant the moment we try to insert a significant number of entries using this SRF. I can see the memory being consumed by the PG backend slowly grow to a large percentage. I see that the executor (take ExecResult as an example) does not reset the expression context early if an SRF is churning out tuples. What could be a good way to fix this? Regards, Nikhils -- 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] max_standby_delay considered harmful
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: Comments? There's currently three ways to set max_standby_delay: max_standby_delay = -1# Query wins max_standby_delay = 0 # Recovery wins max_standby_delay X # Query wins until lag X. As Tom points out, the 3rd option has all sorts of problems. I very much like the behavior that max_standby_delay tries to accomplish, but I have to agree that it's not very reliable as it is. I don't like Tom's proposal either; the standby can fall behind indefinitely, and queries get a varying grace period. Let's rip out the concept of a delay altogether, and make it a boolean. If you really want your query to finish, set it to -1 (using the current max_standby_delay nomenclature). If recovery is important to you, set it to 0. I can't help but insisting on it, sorry. But. The obvious solution to this problem for me is that to either make the boolean reload friendly or to have pause/resume recovery. Ideally, both. Then the default setting would be recovery wins, you pause the standby replaying to ensure your query runs to completion. Very crude setting, but 9.0 would offer easy to setup slave for *either* HA *or* off-load, and a way to mitigate somehow. The automated educated conflict solving based on some sort of timeout running for one or all the current queries seems much harder to agree upon when compared to applying existing code we tough we wouldn't yet need. Let's revisit that decision: it seems to me we need it for 9.0. 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] max_standby_delay considered harmful
Simon Riggs wrote: I am mostly unavailable for next few days. (Repairing bikeshed.) Hey, you're supposed to do the bikeshedding on-list! ;-) -- 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] max_standby_delay considered harmful
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: To accomplish what you're trying to accomplish, you would need to label each received WAL record with the timestamp when it was received, and compare the reception timestamp of the record you're applying against current timestamp. Yeah, this is why I thought that closed-loop lag control was a research project. In practice, we don't have to track it at the individual record level. The real behavior of walsender is that we get a gob of WAL each activity cycle, and so tracking the WAL start location and receipt time for each gob ought to be sufficient. (In fact trying to ascribe any finer-grain receipt time than that to individual WAL records is probably bogus anyway.) It might be enough to remember the start location and time for the latest gob, depending on exactly what control algorithm you want to use. But the whole thing requires significant thought and testing, which we really haven't got time for now. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
On Wed, May 5, 2010 at 9:58 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Tom Lane wrote: Comments? There's currently three ways to set max_standby_delay: max_standby_delay = -1 # Query wins max_standby_delay = 0 # Recovery wins max_standby_delay X # Query wins until lag X. As Tom points out, the 3rd option has all sorts of problems. I very much like the behavior that max_standby_delay tries to accomplish, but I have to agree that it's not very reliable as it is. I don't like Tom's proposal either; the standby can fall behind indefinitely, and queries get a varying grace period. Let's rip out the concept of a delay altogether, and make it a boolean. If you really want your query to finish, set it to -1 (using the current max_standby_delay nomenclature). If recovery is important to you, set it to 0. Does my proposal (upthread) to limit this by quantity of WAL rather than time have any legs, or is that impractical and/or otherwise poor? ...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] max_standby_delay considered harmful
Robert Haas wrote: Does my proposal (upthread) to limit this by quantity of WAL rather than time have any legs, or is that impractical and/or otherwise poor? That would certainly be easier to implement sanely than a time-based quantity. One problem is that we don't know how much unapplied WAL there is, when you're not using streaming replication. And I'm not sure how useful that is to users - it's very hard to estimate what to set it to. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Upcoming back-branch updates
The core team has agreed that the data-corruption bug fixed here http://archives.postgresql.org/pgsql-committers/2010-05/msg00016.php is serious enough to justify a prompt update release. Although that bug only affects 8.4 and HEAD, we have some other significant bug fixes pending in the older back branches, so we may as well do them all. The current plan is to wrap next Thursday, 5/13, for public release Monday 5/17. It will probably be appropriate to wrap a 9.0beta2 at the same time. 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] max_standby_delay considered harmful
On Wed, May 5, 2010 at 12:30 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: Does my proposal (upthread) to limit this by quantity of WAL rather than time have any legs, or is that impractical and/or otherwise poor? That would certainly be easier to implement sanely than a time-based quantity. One problem is that we don't know how much unapplied WAL there is, when you're not using streaming replication. Hmm, that's a problem, likely fatally so. And I'm not sure how useful that is to users - it's very hard to estimate what to set it to. I'm not sure whether that's really an issue or not. I mean, if we say that the standby is allowed to be, say, 16MB behind the master, we know that recovery time is bounded by how long it takes to replay 16MB. Which is in some ways more defined than saying we're behind the primary by 30 min, which could take a long time to replay or not much at all. But, I guess it's moot. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator to /contrib in a later 9.0 beta
So what was the conclusion here? Is pg_migrator going to be in contrib for beta2 or 3, after cleaning it up? -- Alvaro Herrerahttp://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
[HACKERS] patch: to_string, to_array functions
Hello
attached patch contains to_string and to_array functions. These
functions are equivalent of array_to_string and string_to_array
function with maybe more correct NULL handling.
postgres=# select to_array('1,2,3,4,,6',',');
to_array
--
{1,2,3,4,NULL,6}
(1 row)
postgres=# select to_array('1,2,3,4,,6',',','***');
to_array
{1,2,3,4,,6}
(1 row)
postgres=# select to_string(array[1,2,3,4,NULL,6],',');
to_string
1,2,3,4,,6
(1 row)
postgres=# select to_string(array[1,2,3,4,NULL,6],',','***');
to_string
---
1,2,3,4,***,6
(1 row)
Regards
Pavel Stehule
to_array.diff
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator to /contrib in a later 9.0 beta
On 2010-05-03 23:09, Bruce Momjian wrote: Robert Haas wrote: On Sun, May 2, 2010 at 3:45 PM, Dimitri Fontainedfonta...@hi-media.com wrote: Now you tell me how awful this idea really is :) I'm not sure I can count that high. :-) While I can't improve on Robert's reply, I can supply a PDF about how pg_migrator works: http://momjian.us/main/presentations/technical.html#pg_migrator There is a huge amount of users to whom pg_migrator is at least a big a feature as HS+SR is. Last dump/restore was a 24 hours process in one of our installations. I think it was due to in-efficiency in handling BYTEA types in the process (but not sure). But I'm one of the few guys who seem to have an infinite amount of time for reading on mailing lists, but without my knowledge from reading this list I would never have run pg_migrator on my production data if I had to pick it from pg_foundry. Just my 0.25€ Jesper -- Jesper -- 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] max_standby_delay considered harmful
On Wed, 2010-05-05 at 16:58 +0300, Heikki Linnakangas wrote: Let's have a setting similar to statement_timeout, that specifies how long a statement is allowed to run until it becomes subject to killing if it conflicts with recovery (actually, it would have to be a per-transaction setting, at least in serializable mode). This would be similar to Tom's proposal, and it would have the same drawback that it would give no guarantee on how much the standby can fall behind. However, it would be easier to understand: a query gets to run for X seconds, and after that it will be killed if it gets in the way. If you want this, I have no problem with you getting this (though new feature alert sirens going off, presumably). I only have a problem with the suggestion that this replaces the current max_standby_delay. There is no good case for only a single option. -- 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] max_standby_delay considered harmful
On Wed, 2010-05-05 at 16:46 +0300, Heikki Linnakangas wrote: Simon Riggs wrote: The attached patch redefines standby delay to be the amount of time elapsed from point of receipt to point of application. The point of receipt is reset every chunk of data when streaming, or every file when reading file by file. In all cases this new time is later than the latest log time we would have used previously. This seems completely wrong to me. If the WAL receiver keeps receiving stuff, (last receive timestamp) - (current timestamp) would never be more than a few seconds. Regardless of how much applying the WAL has fallen behind. I see your point. To accomplish what you're trying to accomplish, you would need to label each received WAL record with the timestamp when it was received, and compare the reception timestamp of the record you're applying against current timestamp. Yes, OK. Obviously doing it for every record would be silly, so sampling WAL records is the only way. If we save the timestamp every 16MB of WAL that would work for both file and streaming. Of course, if WAL was written regularly none of this would be a problem. Why not have WALSender write a new WAL record with a timestamp every X seconds? -- 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] max_standby_delay considered harmful
On Wed, 2010-05-05 at 06:23 -0400, Robert Haas wrote: On Wed, May 5, 2010 at 3:16 AM, Simon Riggs si...@2ndquadrant.com wrote: Expect at least 3 commits from me over next few days. I think you need to rethink the way that you decide when it's time to commit things. There is certainly no consensus on any of the things you are proposing to commit, nor have they been adequately (or, uh, at all) reviewed. Saying that your proposal addresses all of Tom's objections doesn't make it so. I am planning to read that patch and offer an opinion on it, but I haven't done so yet and I imagine Tom will weigh in at some point as well. Racing to commit a pile of code that nobody else has tested is not going to improve anything. Only you have spoken of a race to commit and I have not said I would refuse to listen to you or others. Reading your words, it would be easy to forget we are a team of people whose aim is software development. It's not the OK Corral. Yesterday you berated me for unstable software. Today you oppose my promise to fix that. Why is it, we all wonder, is it that you oppose everything I say and do? No doubt you will oppose other committers in the way you oppose me... -- 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] On a somewhat disappointing correspondence (was: max_standby_delay considered harmful)
Simon Riggs si...@2ndquadrant.com wrote: I've refrained from comment on max_standby_delay because I have neither read the patch nor am likely to be an early adopter of HS; however, as a potential eventual user I have to say that the semantics for this GUC proposed by Simon seem sane and useful to me. Certainly the documentation would need to be clear on the pitfalls of using something other than 0 or -1, and there were technical issues raised on the thread outside the scope of the semantics of the GUC, but the issues around clock sync and transfer time ring of FUD. We sync our central router to a bank of atomic clocks around the world, and sync every server to the router -- if a server drifts we would have much bigger problems than this GUC would pose, so we monitor that and make loud noises should something drift. Are there other controls that would be useful? Undoubtedly. Should they be added to 9.0? I'm not in a position to say. I don't see the point of ripping out one potentially useful control, which *might* be sufficient for 9.0 because someone might choose to use it inappropriately. Just make sure it's documented well enough. Yesterday you berated me for unstable software. Today you oppose my promise to fix that. Why is it, we all wonder, is it that you oppose everything I say and do? Robert strikes me as a top-notch project manager, and his comments struck me as totally in line with someone wearing that hat. -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] Reg: SQL Query for Postgres 8.4.3
On 05/05/10 22:13, Srinivas Naik wrote: Hi Mark, I took the output of the Postgresql. Please find the output: Package: postgresql-8.3 State: installed Automatically installed: no Version: 8.3.9-0ubuntu8.10 Ok - your bug is fixed in 8.3.10. This should make its way to your Ubuntu apt repository soon (provided 8.10 is still getting updates that is...). regards Mark
Re: [HACKERS] max_standby_delay considered harmful
Heikki, all, There's currently three ways to set max_standby_delay: max_standby_delay = -1# Query wins max_standby_delay = 0 # Recovery wins max_standby_delay X # Query wins until lag X. As Tom points out, the 3rd option has all sorts of problems. I very much like the behavior that max_standby_delay tries to accomplish, but I have to agree that it's not very reliable as it is. Wow, thanks for the summary. Based on that, I take back what I said to Greg. Because I think getting 9.0 out *on time* is more important than any of these issues, I'm revising my opinion to be more in line with Greg Smith. So, proposed path forwards. (1) We work on getting the specific bugs Tom reported fixed. (2) max_standby_delay default is 0 (3) documentation covers setting it to an integer, but warns extensively about the required sysadminning and query cancel. As in for advanced users only. (4) discussion of other synch methods gets shifted to 9.0 Ultimately, I think we'll be going to something lock-based like what Tom suggested. However, I don't think that's doable without delaying 9.0 for 6 months, and I think that would be much worse than any current bug with 9.0. No matter how much we tinker with HS/SR, it's not going to be bulletproof until 9.1. Or, more likely, 9.2. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
Heikki Linnakangas wrote: Tom Lane wrote: Comments? There's currently three ways to set max_standby_delay: max_standby_delay = -1# Query wins max_standby_delay = 0 # Recovery wins max_standby_delay X # Query wins until lag X. As Tom points out, the 3rd option has all sorts of problems. I very much like the behavior that max_standby_delay tries to accomplish, but I have to agree that it's not very reliable as it is. I don't like Tom's proposal either; the standby can fall behind indefinitely, and queries get a varying grace period. Let's rip out the concept of a delay altogether, and make it a boolean. If you really want your query to finish, set it to -1 (using the current max_standby_delay nomenclature). If recovery is important to you, set it to 0. If you have the monitoring in place to sensibly monitor the delay between primary and standby, and you want a limit on that, you can put together a script to flip the switch in postgresql.conf if the standby falls too much behind. It would be nice to make that settable per-session, BTW. Though as soon as you have one session using -1, the standby could fall behind. Still, it might be useful if you run both kinds of queries on the same standby. +1 for a boolean We are not supposed to be designing the behavior during beta, which is exactly what we are doing, and I don't think we even know what behavior we want, let alone have we implemented it. I think a boolean is very clear and it gives you the chance to optimize _one_ case, which is enough for 9.0. Let's revisit this for 9.1 when we will know a lot more than we do now. Once 9.1 reports slave snapshots back to the master, we might not need anything more than a boolean here anyway. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] LD_LIBRARY_PATH versus rpath
Over at http://archives.postgresql.org/pgsql-general/2010-05/msg00091.php we have a complaint about make check failing when the install is intended to overwrite existing libraries (in particular, replacing 8.4 with 9.0 libpq). I've done some off-list investigation and found that this appears to be a generic issue on Linux. pg_regress invokes psql, which depends on libpq.so, and if psql fails due to picking up the wrong libpq.so then you get behavior as described. Now, pg_regress tries to ensure that the temporary installation will work as desired by setting LD_LIBRARY_PATH to point at the temp installation's lib/ directory. However, the psql executable will by default get built with a DT_RPATH entry pointing at the intended final installation lib/. And DT_RPATH overrides LD_LIBRARY_PATH, in the Linux dynamic loader. man ld.so says: The shared libraries needed by the program are searched for in the fol- lowing order: o (ELF only) Using the directories specified in the DT_RPATH dynamic section attribute of the binary if present and DT_RUNPATH attribute does not exist. Use of DT_RPATH is deprecated. o Using the environment variable LD_LIBRARY_PATH. Except if the exe- cutable is a set-user-ID/set-group-ID binary, in which case it is ignored. o (ELF only) Using the directories specified in the DT_RUNPATH dynamic section attribute of the binary if present. o (etc etc) Given that deprecation note, and the fact that what we're doing entirely fails to work as desired, it seems like what we need to do is set DT_RUNPATH instead of DT_RPATH. Further reading discloses that the way to do that is to add --enable-new-dtags to the linker switches. So the question is, should we modify Makefile.linux along the lines of -rpath = -Wl,-rpath,'$(rpathdir)' +rpath = -Wl,-rpath,'$(rpathdir)',--enable-new-dtags I asked around at Red Hat and was told that this would be unlikely to have any negative side-effects, but I'm not sure how thoroughly those guys thought about the consequences for non-mainstream Linux machines. (In particular, I'm worried about really old distros possibly not having this switch.) My inclination is to try this in HEAD only and see if any problems emerge during the beta cycle. Comments? 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] Reg: SQL Query for Postgres 8.4.3
On 06/05/10 09:48, Mark Kirkwood wrote: Ok - your bug is fixed in 8.3.10. This should make its way to your Ubuntu apt repository soon (provided 8.10 is still getting updates that is...). Unfortunately it looks like you may not get this version - see: http://ubuntuguide.org/wiki/Ubuntu:Intrepid i.e no longer supported. I would recommend planning an upgrade to a supported version (10.04 is an LTS release - i.e much longer period of support). Cheers Mark
Re: [HACKERS] pg_migrator to /contrib in a later 9.0 beta
Alvaro Herrera wrote: So what was the conclusion here? Is pg_migrator going to be in contrib for beta2 or 3, after cleaning it up? Thanks for asking. :-) I can add pg_migrator to contrib by the end of next week, so it will be in beta2. I will remove 8.4 as a migration target, which will allow the removal of some C code and documentation warnings. Unless I hear otherwise, I will start on it in the next few days. Total work will be 8 hours, including testing. One outstanding question is whether we want to rename pg_migrator to something clearer, like pg_upgrade or pg_binary_upgrade. (pg_upgrade was the original name for this migration method in the 1998.) I am slightly concerned that the migration word is too associated with cross-database-product migration. (There are no mentions of pg_migrator in our CVS now, except for an 8.4 release note item mention when pg_dump --binary-upgrade was added.) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://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] pg_migrator to /contrib in a later 9.0 beta
Jesper Krogh wrote: On 2010-05-03 23:09, Bruce Momjian wrote: Robert Haas wrote: On Sun, May 2, 2010 at 3:45 PM, Dimitri Fontainedfonta...@hi-media.com wrote: Now you tell me how awful this idea really is :) I'm not sure I can count that high. :-) While I can't improve on Robert's reply, I can supply a PDF about how pg_migrator works: http://momjian.us/main/presentations/technical.html#pg_migrator There is a huge amount of users to whom pg_migrator is at least a big a feature as HS+SR is. Last dump/restore was a 24 hours process in one of our installations. I think it was due to in-efficiency in handling BYTEA types in the process (but not sure). But I'm one of the few guys who seem to have an infinite amount of time for reading on mailing lists, but without my knowledge from reading this list I would never have run pg_migrator on my production data if I had to pick it from pg_foundry. So, did you use copy or link mode, and how fast was the pg_migrator upgrade? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: On a somewhat disappointing correspondence (was: max_standby_delay considered harmful)
Kevin Grittner wrote: Simon Riggs si...@2ndquadrant.com wrote: I've refrained from comment on max_standby_delay because I have neither read the patch nor am likely to be an early adopter of HS; however, as a potential eventual user I have to say that the semantics for this GUC proposed by Simon seem sane and useful to me. Certainly the documentation would need to be clear on the pitfalls of using something other than 0 or -1, and there were technical issues raised on the thread outside the scope of the semantics of the GUC, but the issues around clock sync and transfer time ring of FUD. We sync our central router to a bank of atomic clocks around the world, and sync every server to the router -- if a server drifts we would have much bigger problems than this GUC would pose, so we monitor that and make loud noises should something drift. Are there other controls that would be useful? Undoubtedly. Should they be added to 9.0? I'm not in a position to say. I don't see the point of ripping out one potentially useful control, which *might* be sufficient for 9.0 because someone might choose to use it inappropriately. Just make sure it's documented well enough. We are not very good at _removing_ functionality/GUCs, and based on the discussion so far, I think there is a very slim chance we would get it right for 9.0, which is why I suggested converting it to a boolean and revisiting this for 9.1. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://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] max_standby_delay considered harmful
On Wed, May 5, 2010 at 7:18 PM, Bruce Momjian br...@momjian.us wrote: Heikki Linnakangas wrote: Tom Lane wrote: Comments? There's currently three ways to set max_standby_delay: max_standby_delay = -1 # Query wins max_standby_delay = 0 # Recovery wins max_standby_delay X # Query wins until lag X. As Tom points out, the 3rd option has all sorts of problems. I very much like the behavior that max_standby_delay tries to accomplish, but I have to agree that it's not very reliable as it is. I don't like Tom's proposal either; the standby can fall behind indefinitely, and queries get a varying grace period. Let's rip out the concept of a delay altogether, and make it a boolean. If you really want your query to finish, set it to -1 (using the current max_standby_delay nomenclature). If recovery is important to you, set it to 0. If you have the monitoring in place to sensibly monitor the delay between primary and standby, and you want a limit on that, you can put together a script to flip the switch in postgresql.conf if the standby falls too much behind. It would be nice to make that settable per-session, BTW. Though as soon as you have one session using -1, the standby could fall behind. Still, it might be useful if you run both kinds of queries on the same standby. +1 for a boolean We are not supposed to be designing the behavior during beta, which is exactly what we are doing, and I don't think we even know what behavior we want, let alone have we implemented it. I think a boolean is very clear and it gives you the chance to optimize _one_ case, which is enough for 9.0. Let's revisit this for 9.1 when we will know a lot more than we do now. The existing behavior is probably not optimal, but I'm not seeing what benefit we get out of neutering it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator to /contrib in a later 9.0 beta
On Wed, May 5, 2010 at 7:44 PM, Bruce Momjian br...@momjian.us wrote: Alvaro Herrera wrote: So what was the conclusion here? Is pg_migrator going to be in contrib for beta2 or 3, after cleaning it up? Thanks for asking. :-) I can add pg_migrator to contrib by the end of next week, so it will be in beta2. I will remove 8.4 as a migration target, which will allow the removal of some C code and documentation warnings. Unless I hear otherwise, I will start on it in the next few days. Total work will be 8 hours, including testing. One outstanding question is whether we want to rename pg_migrator to something clearer, like pg_upgrade or pg_binary_upgrade. (pg_upgrade was the original name for this migration method in the 1998.) I am slightly concerned that the migration word is too associated with cross-database-product migration. (There are no mentions of pg_migrator in our CVS now, except for an 8.4 release note item mention when pg_dump --binary-upgrade was added.) I think it will be confusing if we change the name, so I vote to not change the name. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
Robert Haas wrote: If you have the monitoring in place to sensibly monitor the delay between primary and standby, and you want a limit on that, you can put together a script to flip the switch in postgresql.conf if the standby falls too much behind. It would be nice to make that settable per-session, BTW. Though as soon as you have one session using -1, the standby could fall behind. Still, it might be useful if you run both kinds of queries on the same standby. +1 for a boolean We are not supposed to be designing the behavior during beta, which is exactly what we are doing, and I don't think we even know what behavior we want, let alone have we implemented it. ?I think a boolean is very clear and it gives you the chance to optimize _one_ case, which is enough for 9.0. ?Let's revisit this for 9.1 when we will know a lot more than we do now. The existing behavior is probably not optimal, but I'm not seeing what benefit we get out of neutering it. We get to design it right, or maybe not need it at all in 9.1. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://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] max_standby_delay considered harmful
Bruce Momjian br...@momjian.us writes: Robert Haas wrote: The existing behavior is probably not optimal, but I'm not seeing what benefit we get out of neutering it. We get to design it right, or maybe not need it at all in 9.1. Yeah. The good thing about a boolean is that it covers the two noncontroversial cases (no-wait and wait forever), and doesn't lock us into supporting cases that we don't really know how to do well yet. 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] construct_array() use with PQexec with binary data
Dear PostgreSQL development community, I am working on adapting a regular PQexec() call to use binary transmission of the parameters. One of the parameters is an array of BIGINT. Looking in include/utils/array.h, it appears that construct_array() will do exactly what I need to get an array to pass in with the PQexec() call. Is there a library that includes that functionality? Or do I need to cobble it together from the various pieces of code? Thank you for any help. Regards, Ken -- 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] construct_array() use with PQexec with binary data
Kenneth Marshall k...@rice.edu writes: I am working on adapting a regular PQexec() call to use binary transmission of the parameters. One of the parameters is an array of BIGINT. Looking in include/utils/array.h, it appears that construct_array() will do exactly what I need to get an array to pass in with the PQexec() call. Is there a library that includes that functionality? Or do I need to cobble it together from the various pieces of code? Thank you for any help. libpq does not provide any functionality for manipulating binary data --- it just sends and receives it. You might care to look at http://libpqtypes.esilo.com/ which offers a lot of higher-level functionality in this area. 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] max_standby_delay considered harmful
Heikki Linnakangas wrote: Let's rip out the concept of a delay altogether, and make it a boolean. If you really want your query to finish, set it to -1 (using the current max_standby_delay nomenclature). If recovery is important to you, set it to 0. So the only user options would be allow long-running queries to block WAL application forever and always cancel queries on conflict? That would be taking away the behavior I was going to suggest as the default to many customers I work with. I expect a non-trivial subset of people using this feature will set max_standby_delay to is some small number of minutes, similarly to how archive_timeout is sized now. Enough time to get reasonably sized queries executed, not so long as to allow something that might try to run for hours on the standby to increase failover catchup time very much. The way the behavior works is admittedly limited, and certainly some people are going to want to set it to either 0 or -1. But taking it away altogether is going to cripple one category of potential Hot Standby use in the field. Consider this for a second: do you really think that Simon would have waded into this coding mess, or that I would have spent as much energy as I have highlighting issues with its use, if there wasn't demand for it? If it wouldn't hurt the usefulness of PostgreSQL 9.0 significantly to cut it, I'd have suggested that myself two months ago and saved everyone (especially myself) a lot of trouble. If you have the monitoring in place to sensibly monitor the delay between primary and standby, and you want a limit on that, you can put together a script to flip the switch in postgresql.conf if the standby falls too much behind. There's a couple of things you should do in order for max_standby_delay to working as well as it can. Watching clock sync and forcing periodic activity are two of them that always come up. Those are both trivial to script for, and something I wouldn't expect any admin to object to. If you need a script that involves changing a server setting to do something, that translates into you can't do that for a typical DBA. The idea of a program regularly changing a server configuration setting on a production system is one you just can't sell. That makes this idea incredibly more difficult to use in the field than any of the workarounds that cope with the known max_standby_delay issues. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] max_standby_delay considered harmful
Greg Smith g...@2ndquadrant.com writes: Heikki Linnakangas wrote: Let's rip out the concept of a delay altogether, and make it a boolean. So the only user options would be allow long-running queries to block WAL application forever and always cancel queries on conflict? Got it in one. Obviously, this is something that would be high priority to improve in some fashion in 9.1. That doesn't mean that it's reasonable to drop in a half-baked redesign now, nor to put in the amount of work that would be required to have a really well-designed implementation, and most certainly not to uncritically ship what we've got. We have a ton of other work that has to be done to get 9.0 out the door, and this feature is something that IMO we can live without for this release. One reason I believe this isn't so critical as all that is that it only matters for cases where the operation on the master took an exclusive lock. In high-performance production scenarios that's something you try hard to avoid anyway. When you succeed, the standby behavior is moot. Even if you can't avoid exclusive locks entirely, you may be able to confine them to maintenance windows where performance doesn't matter so much ... and then that goes for the standby performance as well. 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] max_standby_delay considered harmful
On Thu, May 6, 2010 at 2:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: One reason I believe this isn't so critical as all that is that it only matters for cases where the operation on the master took an exclusive lock. Uhm, or a vacuum ran. Or a HOT page cleanup occurred, or a btree page split deleted old tuples. -- 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] On a somewhat disappointing correspondence
Bruce Momjian wrote: We are not very good at _removing_ functionality/GUCs, and based on the discussion so far, I think there is a very slim chance we would get it right for 9.0, which is why I suggested converting it to a boolean and revisiting this for 9.1. There's some feedback you can only get by exposing a complicated feature to the users and seeing what they make of it. This one hasn't even had a full week to gather beta user reports. Given that it's easy to disable (just limiting the range on what is effectively a 3-way switch to two positions), I don't understand why you're pushing at this point for its removal. You could be encouraging testing instead, which I believe is needed to know exactly what the right thing to do in 9.1 is. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] CP949 for EUC-KR?
Ioseph Kim pgsql...@postgresql.kr wrote:
CP51949 is EUC-KR correct.
{PG_EUC_KR, CP51949}, /* or 20949 ? */
Thank you for the information. I removed or 20949 ? from the line.
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator to /contrib in a later 9.0 beta
On Wed, 2010-05-05 at 20:24 -0400, Robert Haas wrote: On Wed, May 5, 2010 at 7:44 PM, Bruce Momjian br...@momjian.us wrote: Alvaro Herrera wrote: So what was the conclusion here? Is pg_migrator going to be in contrib for beta2 or 3, after cleaning it up? Thanks for asking. :-) I can add pg_migrator to contrib by the end of next week, so it will be in beta2. I will remove 8.4 as a migration target, which will allow the removal of some C code and documentation warnings. Unless I hear otherwise, I will start on it in the next few days. Total work will be 8 hours, including testing. One outstanding question is whether we want to rename pg_migrator to something clearer, like pg_upgrade or pg_binary_upgrade. (pg_upgrade was the original name for this migration method in the 1998.) I am slightly concerned that the migration word is too associated with cross-database-product migration. (There are no mentions of pg_migrator in our CVS now, except for an 8.4 release note item mention when pg_dump --binary-upgrade was added.) I think it will be confusing if we change the name, so I vote to not change the name. Actually, I would vote yes to change the name. Once its in contrib, we likely never will and this isn't really a migration tool. It is an upgrade tool. Joshua D. Drake -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator to /contrib in a later 9.0 beta
Joshua D. Drake j...@commandprompt.com writes: On Wed, 2010-05-05 at 20:24 -0400, Robert Haas wrote: I think it will be confusing if we change the name, so I vote to not change the name. Actually, I would vote yes to change the name. I lean that way too. If there were no history involved, we'd certainly prefer pg_upgrade to pg_migrator. 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] max_standby_delay considered harmful
Greg Stark wrote: On Thu, May 6, 2010 at 2:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: One reason I believe this isn't so critical as all that is that it only matters for cases where the operation on the master took an exclusive lock. Uhm, or a vacuum ran. Or a HOT page cleanup occurred, or a btree page split deleted old tuples. Right; because there are so many regularly expected causes for query cancellation, the proposed boolean setup really hurts the ability of a server whose primary goal is high-availability to run queries of any useful duration. For years I've been hearing my HA standby is idle, how can I put it to use?; that's the back story of the users I thought everyone knew were the known audience waiting for this feature. If the UI for vacuum_defer_cleanup_age that prevented these things was good, I would agree that the cases where max_standby_delay does something useful are marginal. That's why I tried to get someone working on SR to provide a hook for that purpose months ago. But since the vacuum adjustment we have in completely obtuse xid units, that leaves max_standby_delay as the only tunable here that you can even think about in terms of human time. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator to /contrib in a later 9.0 beta
Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: On Wed, 2010-05-05 at 20:24 -0400, Robert Haas wrote: I think it will be confusing if we change the name, so I vote to not change the name. Actually, I would vote yes to change the name. I lean that way too. If there were no history involved, we'd certainly prefer pg_upgrade to pg_migrator. Yeah, that was my feeling too. People like pg_upgrade, or something else? I will add some text like pg_upgrade (formerly pg_migrator) in the docs. I will also add something about the fact that there is no guarantee that pg_upgrade will work with all future major Postgres releases, per Tom's concern. FYI, I specifically labeled backend changes as binary upgrade because I wanted to make sure those changes were useful for other binary upgrade tools, in case someone wanted to create another one. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://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] max_standby_delay considered harmful
On Wed, May 5, 2010 at 9:36 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Smith g...@2ndquadrant.com writes: Heikki Linnakangas wrote: Let's rip out the concept of a delay altogether, and make it a boolean. So the only user options would be allow long-running queries to block WAL application forever and always cancel queries on conflict? Got it in one. Obviously, this is something that would be high priority to improve in some fashion in 9.1. That doesn't mean that it's reasonable to drop in a half-baked redesign now, nor to put in the amount of work that would be required to have a really well-designed implementation, and most certainly not to uncritically ship what we've got. If you had a genuinely better idea for how this should work, I would be the first to endorse it, but it's becoming clear that you don't, which makes me also skeptical of your contention that we will be better off with no knob at all. I find that position not very plausible. Nor do I really see how this is backing us into any kind of a corner. If we're really concerned that we're going to suddenly come up with a much better method of controlling this behavior (and so far nobody seems close to having such a brilliant insight), then let's just put a note in the documentation saying that the setting has problems X, Y, and Z and that if we develop a better method for controlling this behavior, the GUC may be modified or removed in a future release. Ripping it out seems like a drastic overreaction, particularly considering that we're already in beta. This feature has been in the tree since December 19th when the initial Hot Standby patch was committed, and the last significant code change was on February 13th. It is now May 5th. The fact that you didn't read the patch sooner is not a reason why we should rip it out now. Yes, the current implementation is a little crufty and has some limitations. See also work_mem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] max_standby_delay considered harmful
Greg Smith wrote: Heikki Linnakangas wrote: Let's rip out the concept of a delay altogether, and make it a boolean. If you really want your query to finish, set it to -1 (using the current max_standby_delay nomenclature). If recovery is important to you, set it to 0. So the only user options would be allow long-running queries to block WAL application forever and always cancel queries on conflict? That would be taking away the behavior I was going to suggest as the default to many customers I work with. I expect a non-trivial subset of people using this feature will set max_standby_delay to is some small number of minutes, similarly to how archive_timeout is sized now. Enough time to get reasonably sized queries executed, not so long as to allow something that might try to run for hours on the standby to increase failover catchup time very much. The way the behavior works is admittedly limited, and certainly some people are going to want to set it to either 0 or -1. But taking it away altogether is going to cripple one category of potential Hot Standby use in the field. Consider this for a second: do you really think that Simon would have waded into this coding mess, or that I would have spent as much energy as I have highlighting issues with its use, if there wasn't demand for it? If it wouldn't hurt the usefulness of PostgreSQL 9.0 significantly to cut it, I'd have suggested that myself two months ago and saved everyone (especially myself) a lot of trouble. We are not designing in a green field here. We have released beta1 and we are trying to get to 9.0 final in a few months. If this feature could have been designed easily months ago, it would have been done, but it doesn't seem to have any easy solution, and we have run out of time to fix it. As painful as it is, we need to cut our loses and move on. We have already cut features like sync replication and communicating the slave snapshot to the master; I don't see how removing this ability is any worse. We don't have time to develop this for every use case, even if those use cases are significant. If someone wants to suggest that HS is useless if max_standby_delay supports only boolean values, I am ready to suggest we remove HS as well and head to 9.0 because that would suggest that HS itself is going to be useless. The code will not be thrown away; we will bring it back for 9.1. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://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] max_standby_delay considered harmful
Robert Haas wrote: If you had a genuinely better idea for how this should work, I would be the first to endorse it, but it's becoming clear that you don't, which makes me also skeptical of your contention that we will be better off with no knob at all. I find that position not very plausible. Nor do I really see how this is backing us into any kind of a corner. If we're really concerned that we're going to suddenly come up with a much better method of controlling this behavior (and so far nobody seems close to having such a brilliant insight), then let's just put a note in the documentation saying that the setting has problems X, Y, and Z and that if we develop a better method for controlling this behavior, the GUC may be modified or removed in a future release. Ripping it out seems like a drastic overreaction, particularly considering that we're already in beta. This feature has been in the tree since December 19th when the initial Hot Standby patch was committed, and the last significant code change was on February 13th. It is now May 5th. The fact that you didn't read the patch sooner is not a reason why we should rip it out now. Yes, the current implementation is a little crufty and has some limitations. See also work_mem. I am afraid the current setting is tempting for users to enable, but will be so unpredictable that it will tarnish the repuation of HS and Postgres. We don't want to be thinking in 9 months, Wow, we shouldn't have shipped that features. It is causing all kinds of problems. We have done that before (rarely), and it isn't a good feeling. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://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] On a somewhat disappointing correspondence
Greg Smith wrote: Bruce Momjian wrote: We are not very good at _removing_ functionality/GUCs, and based on the discussion so far, I think there is a very slim chance we would get it right for 9.0, which is why I suggested converting it to a boolean and revisiting this for 9.1. There's some feedback you can only get by exposing a complicated feature to the users and seeing what they make of it. This one hasn't even had a full week to gather beta user reports. Given that it's easy to disable (just limiting the range on what is effectively a 3-way switch to two positions), I don't understand why you're pushing at this point for its removal. You could be encouraging testing instead, which I believe is needed to know exactly what the right thing to do in 9.1 is. Our developers can't even figure out how it behaves; it is hard to see how beta users will ever figure it out. Now is the time to remove stuff, not late in beta. Pushing decisions into the future is how betas drag. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://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] pg_migrator to /contrib in a later 9.0 beta
On 2010-05-06 01:45, Bruce Momjian wrote: Jesper Krogh wrote: On 2010-05-03 23:09, Bruce Momjian wrote: Robert Haas wrote: On Sun, May 2, 2010 at 3:45 PM, Dimitri Fontainedfonta...@hi-media.com wrote: Now you tell me how awful this idea really is :) I'm not sure I can count that high. :-) While I can't improve on Robert's reply, I can supply a PDF about how pg_migrator works: http://momjian.us/main/presentations/technical.html#pg_migrator There is a huge amount of users to whom pg_migrator is at least a big a feature as HS+SR is. Last dump/restore was a 24 hours process in one of our installations. I think it was due to in-efficiency in handling BYTEA types in the process (but not sure). But I'm one of the few guys who seem to have an infinite amount of time for reading on mailing lists, but without my knowledge from reading this list I would never have run pg_migrator on my production data if I had to pick it from pg_foundry. So, did you use copy or link mode, and how fast was the pg_migrator upgrade? I did go the painful way (dump+restore) at that point in time. It was an 8.1 - 8.3 migration. Since then data has grown and the dump restore is even less favorable on the 8.3 - 9.0 migration. So in general the pg_migrator way seems to be the only way to aviod the slony way which is orders of magnitude more complicated. Q: I read you pdf, why isn't statistics copied over? It seems to be the last part missing from doing an upgrade in a few minutes. Jesper -- Jesper -- 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] max_standby_delay considered harmful
On Wed, May 5, 2010 at 11:50 PM, Bruce Momjian br...@momjian.us wrote: If someone wants to suggest that HS is useless if max_standby_delay supports only boolean values, I am ready to suggest we remove HS as well and head to 9.0 because that would suggest that HS itself is going to be useless. I think HS is going to be a lot less useful than many people think, at least in 9.0. But I think ripping out max_standby_delay will make it worse. The code will not be thrown away; we will bring it back for 9.1. If that's the case, then taking it out makes no sense. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator to /contrib in a later 9.0 beta
Excerpts from Jesper Krogh's message of jue may 06 00:32:09 -0400 2010: Q: I read you pdf, why isn't statistics copied over? It seems to be the last part missing from doing an upgrade in a few minutes. Seems fraught with peril, and a bit pointless. What's so bad about having to run ANALYZE afterwards? -- -- 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] max_standby_delay considered harmful
On Wed, May 5, 2010 at 11:52 PM, Bruce Momjian br...@momjian.us wrote: I am afraid the current setting is tempting for users to enable, but will be so unpredictable that it will tarnish the repuation of HS and Postgres. We don't want to be thinking in 9 months, Wow, we shouldn't have shipped that features. It is causing all kinds of problems. We have done that before (rarely), and it isn't a good feeling. I am not convinced it will be unpredictable. The only caveats that I've seen so far are: - You need to run ntpd. - Queries will get cancelled like crazy if you're not using steaming replication. That just doesn't sound that bad to me, especially since the proposed alternative is: - Queries will get cancelled like crazy, period. Or else: - Replication can fall infinitely far behind and you can write a tedious and error-prone script to try to prevent it if you like. I think THAT is going to tarnish our reputation. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator to /contrib in a later 9.0 beta
On 2010-05-06 06:41, Alvaro Herrera wrote: Excerpts from Jesper Krogh's message of jue may 06 00:32:09 -0400 2010: Q: I read you pdf, why isn't statistics copied over? It seems to be the last part missing from doing an upgrade in a few minutes. Seems fraught with peril, and a bit pointless. What's so bad about having to run ANALYZE afterwards? There is nothing directly bad about it.. but: It's just an extra step, that might be overseen and is absolutely required. I should have written: Why isn't statistics copied over or why doesnt pg_migrator run analyze by itself? The database (of a reasonable size) is useless until statistics is available. I guess it is because pg_dump/restore doesn't do it either. Jesper -- Jesper -- 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] max_standby_delay considered harmful
Robert Haas wrote: On Wed, May 5, 2010 at 11:52 PM, Bruce Momjian br...@momjian.us wrote: I am afraid the current setting is tempting for users to enable, but will be so unpredictable that it will tarnish the repuation of HS and Postgres. We don't want to be thinking in 9 months, Wow, we shouldn't have shipped that features. It is causing all kinds of problems. We have done that before (rarely), and it isn't a good feeling. I am not convinced it will be unpredictable. The only caveats that I've seen so far are: - You need to run ntpd. - Queries will get cancelled like crazy if you're not using steaming replication. And also in situations where the master is idle for a while and then starts doing stuff. That's the most significant source of confusion, IMHO, I wouldn't mind the requirement of ntpd so much. That just doesn't sound that bad to me, especially since the proposed alternative is: - Queries will get cancelled like crazy, period. Or else: - Replication can fall infinitely far behind and you can write a tedious and error-prone script to try to prevent it if you like. I think THAT is going to tarnish our reputation. The difference is that that's easy to document and understand, so the behavior won't be a surprise to anyone. -- 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] max_standby_delay considered harmful
Robert Haas wrote: On Wed, May 5, 2010 at 11:50 PM, Bruce Momjian br...@momjian.us wrote: The code will not be thrown away; we will bring it back for 9.1. If that's the case, then taking it out makes no sense. I doubt we're going to bring back the same code, because it still has the same issues. But we will do something better thought-out. Or people are happy with the boolean and no-one cares anymore, that's pretty likely too. -- 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] max_standby_delay considered harmful
On Wed, 2010-05-05 at 17:56 +0300, Heikki Linnakangas wrote: Simon Riggs wrote: I am mostly unavailable for next few days. (Repairing bikeshed.) Hey, you're supposed to do the bikeshedding on-list! ;-) That was just a joke, I'm mostly unavailable for other reasons. -- 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
