Re: [HACKERS] max_standby_delay considered harmful
On Sat, 2010-05-08 at 14:48 -0400, Bruce Momjian wrote: I think the consensus is to change this setting to a boolean. If you don't want to do it, I am sure we can find someone who will. You expect others to act on consensus and follow rules, yet ignore them yourself when it suits your purpose. Your other points seem designed to distract people from seeing that. There is clear agreement that a problem exists. The action to take as a result of that problem is very clearly in doubt and yet you repeatedly ignore other people's comments and viable technical resolutions. If you can find a cat's paw to break consensus for you, more fool them. You might find someone with a good resolution, if you ask that instead. -- 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] 9.0b1: ERROR: btree index keys must be ordered by attribute
Hi I've just compiled the 9.0 beta1 source tarball and am testing my custom application against it (which has been running on PostgreSQL since 7.3 or so). The below statement results in the following error message: ERROR: btree index keys must be ordered by attribute evidently in relation to the subselect. The statement works fine on previous versions up to 8.4.3. I can provide more details later if required: SELECT o.object_id FROM object o INNER JOIN class c ON (o.class_id = c.class_id) INNER JOIN object_version ov ON (o.object_id = ov.object_id) INNER JOIN site ON (o.site_id=site.site_id) WHERE o.object_id = '3143' AND ov.version = '0' AND o.site_id = '2' AND ov.object_status_id = (SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE o.object_id=ov1.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en' SELECT version(): PostgreSQL 9.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu 4.3.2-1ubuntu12) 4.3.2, 32-bit Ubuntu 8.10 running on a VIA C7-M Processor (netbook). Regards Ian Barwick -- 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 wrote: I think everyone agrees the current code is unusable, per Heikki's comment about a WAL file arriving after a period of no WAL activity I don't. I am curious to hear how many complaints we've had from alpha and beta testers of HS regarding this issue. I know that if we used it with our software, the issue would probably go unnoticed because of our usage patterns and automatic query retry. A positive setting would work as intended for us. I can think of pessimal usage patterns, different software approaches, and/or goals for HS usage which would conflict badly with a positive setting. Hopefully we can document this area better than we've historically done with, for example, fsync -- which has similar trade-offs, only with more dire consequences for bad user choices. -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] max_standby_delay considered harmful
Tom Lane t...@sss.pgh.pa.us writes: I like the proposal of a boolean because it provides only the minimal feature set of two cases that are both clearly needed and easily implementable. Whatever we do later is certain to provide a superset of those two cases. If we do something else (and that includes my own proposal of a straight lock timeout), we'll be implementing something we might wish to take back later. Taking out features after they've been in a release is very hard, even if we realize they're badly designed. That's where I though my proposal fitted in. I fail to see us wanting to take back explicit pause/resume admin functions in any future release. Now, after having read Greg's arguments, my vote would be the following: - hot_standby_conflict_winner = queries|replay, defaults to replay - add pause/resume so that people can switch temporarily to queries - label max_standby_delay *experimental*, keep current code By clearly stating the feature is *experimental* it should be easy to both get feedback on it so that we know what to implement in 9.1, and should that be completely different, take back the feature. It should even be possible to continue tweaking its behavior during beta, or do something better. Of course it will piss off some users, but they knew they were depending on some *experimental* feature after all. 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
On May 9, 2010, at 13:59 , Dimitri Fontaine wrote: Tom Lane t...@sss.pgh.pa.us writes: I like the proposal of a boolean because it provides only the minimal feature set of two cases that are both clearly needed and easily implementable. Whatever we do later is certain to provide a superset of those two cases. If we do something else (and that includes my own proposal of a straight lock timeout), we'll be implementing something we might wish to take back later. Taking out features after they've been in a release is very hard, even if we realize they're badly designed. That's where I though my proposal fitted in. I fail to see us wanting to take back explicit pause/resume admin functions in any future release. Now, after having read Greg's arguments, my vote would be the following: - hot_standby_conflict_winner = queries|replay, defaults to replay - add pause/resume so that people can switch temporarily to queries - label max_standby_delay *experimental*, keep current code Adding pause/resume seems to introduce some non-trivial locking problems, though. How would you handle a pause request if the recovery process currently held a lock? Dropping the lock is not an option for correctness reasons. Otherwise you wouldn't have needed to take the lock in the first place, no? Pausing with the lock held leads to priority-inversion like problems. Queries now might block until recovery is resumed - quite the opposite of what pause() is supposed to archive The only remaining option is to continue applying WAL until you reach a point where no locks are held, then pause. But from a user's POV that is nearly indistinguishable from simply setting hot_standby_conflict_winner to in the first place I think. best regards, Florian Pflug smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
On Sun, May 09, 2010 at 05:48:27PM +0900, Ian Barwick wrote: Hi I've just compiled the 9.0 beta1 source tarball and am testing my custom application against it (which has been running on PostgreSQL since 7.3 or so). The below statement results in the following error message: ERROR: btree index keys must be ordered by attribute evidently in relation to the subselect. The statement works fine on previous versions up to 8.4.3. I can provide more details later if required: A self-contained way to reproduce this, ideally small, would be fantastic :) Cheers, David. SELECT o.object_id FROM object o INNER JOIN class c ON (o.class_id = c.class_id) INNER JOIN object_version ov ON (o.object_id = ov.object_id) INNER JOIN site ON (o.site_id=site.site_id) WHERE o.object_id = '3143' AND ov.version = '0' AND o.site_id = '2' AND ov.object_status_id = (SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE o.object_id=ov1.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en' SELECT version(): PostgreSQL 9.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu 4.3.2-1ubuntu12) 4.3.2, 32-bit Ubuntu 8.10 running on a VIA C7-M Processor (netbook). Regards Ian Barwick -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] 9.0b1: ERROR: btree index keys must be ordered by attribute
2010/5/9 David Fetter da...@fetter.org: On Sun, May 09, 2010 at 05:48:27PM +0900, Ian Barwick wrote: Hi I've just compiled the 9.0 beta1 source tarball and am testing my custom application against it (which has been running on PostgreSQL since 7.3 or so). The below statement results in the following error message: ERROR: btree index keys must be ordered by attribute evidently in relation to the subselect. The statement works fine on previous versions up to 8.4.3. I can provide more details later if required: A self-contained way to reproduce this, ideally small, would be fantastic :) Unfortunately I'm a bit pressed for time right now :(, however in the meantime I have confirmed the same error crops up on OS X 10.5 and also with the query pruned to reference just one table: SELECT ov.object_id FROM object_version ov WHERE ov.object_id = '3143' AND ov.version = '0' AND ov.object_status_id = ( SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; PostgreSQL 9.0beta1 on i386-apple-darwin9.8.0, compiled by GCC i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465), 32-bit I will see if I can knock together a reproducible test case, might take a day or so. At the moment all I can report is that creating a reduced version of the object_version table in a fresh DB with no data does not reproduce the error when running the above query. Ian Barwick -- 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] 9.0b1: ERROR: btree index keys must be ordered by attribute
Ian Barwick barw...@gmail.com writes: 2010/5/9 David Fetter da...@fetter.org: A self-contained way to reproduce this, ideally small, would be fantastic :) s/fantastic/absolutely required to do anything with this report/ I will see if I can knock together a reproducible test case, might take a day or so. At the moment all I can report is that creating a reduced version of the object_version table in a fresh DB with no data does not reproduce the error when running the above query. It probably depends on a specific plan being chosen for the query, and with no data loaded you'd most likely not get the same plan. 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 Sun, May 9, 2010 at 4:00 AM, Greg Smith g...@2ndquadrant.com wrote: The use cases are covered as best they can be without better support from expected future SR features like heartbeats and XID loopback. For what it's worth I think deferring these extra complications is a very useful exercise. I would like to see a system that doesn't depend on them for basic functionality. In particular I would like to see a system that can be useful using purely WAL log shipping without streaming replication at all. I'm a bit unclear how the boolean proposal would solve things though. Surely if you set the boolean to recovery-wins then when using streaming replication with any non-idle master virtually every query would be cancelled immediately as every HOT cleanup would cause a snapshot conflict with even short-lived queires in the slave. -- 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] max_standby_delay considered harmful
On Sun, May 9, 2010 at 12:47 PM, Greg Stark gsst...@mit.edu wrote: On Sun, May 9, 2010 at 4:00 AM, Greg Smith g...@2ndquadrant.com wrote: The use cases are covered as best they can be without better support from expected future SR features like heartbeats and XID loopback. For what it's worth I think deferring these extra complications is a very useful exercise. I would like to see a system that doesn't depend on them for basic functionality. In particular I would like to see a system that can be useful using purely WAL log shipping without streaming replication at all. I'm a bit unclear how the boolean proposal would solve things though. Surely if you set the boolean to recovery-wins then when using streaming replication with any non-idle master virtually every query would be cancelled immediately as every HOT cleanup would cause a snapshot conflict with even short-lived queires in the slave. It sounds to me like what we need here is some testing. -- 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: pg_start_backup and pg_stop_backup Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Sat, 2010-05-08 at 23:55 -0400, Robert Haas wrote: On Sat, May 8, 2010 at 10:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: Uh, did we decide that 'wal_keep_segments' was the best name for this GUC setting? I know we shipped beta1 using that name. I thought min_wal_segments was a reasonable proposal, but it wasn't clear if there was consensus or not. I think most people thought it was another reasonable choice, but I think the consensus position is probably something like it's about the same rather than it's definitely better. We had one or two people with stronger opinions than that on either side, I believe. It's only a name and not worth a long discussion on. -- 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 Sat, 2010-05-08 at 20:57 -0400, Tom Lane wrote: Andres Freund and...@anarazel.de writes: On Sunday 09 May 2010 01:34:18 Bruce Momjian wrote: I think everyone agrees the current code is unusable, per Heikki's comment about a WAL file arriving after a period of no WAL activity, and look how long it took our group to even understand why that fails so badly. To be honest its not *that* hard to simply make sure generating wal regularly to combat that. While it surely aint a nice workaround its not much of a problem either. Well, that's dumping a kluge onto users; but really that isn't the point. What we have here is a badly designed and badly implemented feature, and we need to not ship it like this so as to not institutionalize a bad design. No, you have it backwards. HS was designed to work with SR. SR unfortunately did not deliver any form of monitoring, and in doing so the keepalive that it was known HS needed was left out, although it had been on the todo list for some time. Luckily Greg and I argued to have some monitoring added and my code was used to provide barest minimum monitoring for SR, yet not enough to help HS. Of course, if one team doesn't deliver for whatever reason then others must take up the slack, if they can: no complaints. Since I personally didn't know this was going to be the case until after freeze, it is very late to resolve this situation sensibly and time has been against us. It's much harder for me to reach into the depths of another person's work and see how to add necessary mechanisms, especially when I'm working elsewhere. Even if I had done, it's likely that I would have been blocked with the great idea, next release response as already used on this thread. Without doubt the current mechanism suffers from the issues you mention, though the current state is not the result of bad design, merely inaction and lack of integration. We could resolve the current state in many ways, if we chose. Bruce has used the word crippleware for the current state. Raising a problem and then blocking solutions is the best way I know to cripple a release. It should be clear that I've done my best to avoid this situation and have been active on both SR and HS. Had I not acted as I have done to date, SR would at this point slurp CPU like a bandit and be unmonitorable, both fatal flaws in production. I point this out not to argue, but to set the record straight. IMHO your assignment of blame is misplaced and your comments about poor design do not reflect how we arrived at the current state. -- 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 Sun, 2010-05-09 at 16:10 +0200, Florian Pflug wrote: Adding pause/resume seems to introduce some non-trivial locking problems, though. How would you handle a pause request if the recovery process currently held a lock? (We are only talking about AccessExclusiveLocks here. No LWlocks are held across WAL records during replay) Just pause. There are no technical problem there. Perhaps a danger of unforeseen consequences, though doing that might also be desirable, who can say? -- 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
Florian Pflug f...@phlo.org writes: The only remaining option is to continue applying WAL until you reach a point where no locks are held, then pause. But from a user's POV that is nearly indistinguishable from simply setting hot_standby_conflict_winner to in the first place I think. Not really, the use case would be using the slave as a reporting server, you know you have say 4 hours of reporting queries during which you will pause the recovery. So it's ok for the pause command to take time. What I understand the boolean option would do is to force the user into choosing either high-availability or using the slave for other purposes too. The problem is in wanting both, and that's what HS was meant to solve. Having pause/resume allows for a mixed case usage which is simple to drive and understand, yet fails to provide adaptive behavior where queries are allowed to pause recovery implicitly for a while. In my mind, that would be a compromise we could reach for 9.0, but it seems introducing those admin functions now is to far a stretch. I've been failing to understand exactly why, only getting a generic answer I find unsatisfying here, because all the alternative paths being proposed, apart from improve documentation, are more involved code wise. 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
On May 9, 2010, at 21:04 , Simon Riggs wrote: On Sun, 2010-05-09 at 16:10 +0200, Florian Pflug wrote: Adding pause/resume seems to introduce some non-trivial locking problems, though. How would you handle a pause request if the recovery process currently held a lock? (We are only talking about AccessExclusiveLocks here. No LWlocks are held across WAL records during replay) Just pause. There are no technical problem there. Perhaps a danger of unforeseen consequences, though doing that might also be desirable, who can say? No technical problems perhaps, but some usability ones, no? I assume people would pause recovery to prevent it from interfering with long-running reporting queries. Now, if those queries might block indefinitely if the pause request by chance was issued while the recovery process held an AccessExclusiveLock, then the pause *caused* exactly what it was supposed to prevent. Setting hot_standby_conflict_winner to queries would at least have allowed the reporting queries to finish eventually. If AccessExclusiveLocks are taken out of the picture (they're supposed to be pretty rare on a production system anyway), setting hot_standby_conflict_winner to queries seems to act like a conditional pause request - recovery is paused as soon as it gets in the way. In this setting, the real advantage of pause would be to prevent recovery from using up all available IO bandwidth. This seems like a valid concern, but calls more for something like recovery_delay (similar to vacuum_delay) instead of pause(). best regards, Florian Pflug -- 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 Sun, May 9, 2010 at 3:09 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Florian Pflug f...@phlo.org writes: The only remaining option is to continue applying WAL until you reach a point where no locks are held, then pause. But from a user's POV that is nearly indistinguishable from simply setting hot_standby_conflict_winner to in the first place I think. Not really, the use case would be using the slave as a reporting server, you know you have say 4 hours of reporting queries during which you will pause the recovery. So it's ok for the pause command to take time. Seems like it could take FOREVER on a busy system. Surely that's not OK. The fact that Hot Standby has to take exclusive locks that can't be released until WAL replay has progressed to a certain point seems like a fairly serious wart. We had a discussion on another thread of how this can make the database fail to shut down properly, a problem we're not addressing because we're too busy arguing about max_standby_delay. In fact, if we knew how to pause replay without leaving random locks lying around, we could rearrange the whole smart shutdown sequence so that we paused replay FIRST and then waited for all backends to exit, but the consensus on the thread where we discussed this was that we did not know how to do that. -- 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
On Sun, 2010-05-09 at 16:01 -0400, Robert Haas wrote: The fact that Hot Standby has to take exclusive locks that can't be released until WAL replay has progressed to a certain point seems like a fairly serious wart. LOL And people lecture me about design. -- 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] List traffic
Traffic on the PostgreSQL lists is very high now and I freely admit that reading every email is simply not possible for me, even the ones that mention topics that keyword searches tell me are of potential interest. If anybody knows of a bug or suspected bug in my code, I have no problem in being copied in on mails so that I can see the issues exist. I do not promise to respond to every mail I'm copied on, though, but it at least helps me manage the fire hydrant. Thanks! -- 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 May 9, 2010, at 22:01 , Robert Haas wrote: On Sun, May 9, 2010 at 3:09 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Florian Pflug f...@phlo.org writes: The only remaining option is to continue applying WAL until you reach a point where no locks are held, then pause. But from a user's POV that is nearly indistinguishable from simply setting hot_standby_conflict_winner to in the first place I think. Not really, the use case would be using the slave as a reporting server, you know you have say 4 hours of reporting queries during which you will pause the recovery. So it's ok for the pause command to take time. Seems like it could take FOREVER on a busy system. Surely that's not OK. The fact that Hot Standby has to take exclusive locks that can't be released until WAL replay has progressed to a certain point seems like a fairly serious wart. If this is a serious wart then it's not one of hot standby, but one of postgres proper. AccessExclusiveLocks (SELECT-blocking locks that is, as opposed to UPDATE/DELETE-blocking locks) are never necessary from a correctness POV, they're only there for implementation reasons. Getting rid of them doesn't seem completely insurmountable either - just as multiple row versions remove the need to block SELECTs dues to concurrent UPDATEs, multiple datafile versions could remove the need to block SELECTs due to concurrent ALTERs. But people seem to live with them quite well, judged from the amount of work put into getting rid of them (zero). I therefore fail to see why they should pose a significant problem in HS setups. We had a discussion on another thread of how this can make the database fail to shut down properly, a problem we're not addressing because we're too busy arguing about max_standby_delay. In fact, if we knew how to pause replay without leaving random locks lying around, we could rearrange the whole smart shutdown sequence so that we paused replay FIRST and then waited for all backends to exit, but the consensus on the thread where we discussed this was that we did not know how to do that. Yeah, this was exactly my line of thought too. best regards, Florian Pflug -- 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 Monday 10 May 2010 00:25:44 Florian Pflug wrote: On May 9, 2010, at 22:01 , Robert Haas wrote: On Sun, May 9, 2010 at 3:09 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Florian Pflug f...@phlo.org writes: The only remaining option is to continue applying WAL until you reach a point where no locks are held, then pause. But from a user's POV that is nearly indistinguishable from simply setting hot_standby_conflict_winner to in the first place I think. Not really, the use case would be using the slave as a reporting server, you know you have say 4 hours of reporting queries during which you will pause the recovery. So it's ok for the pause command to take time. Seems like it could take FOREVER on a busy system. Surely that's not OK. The fact that Hot Standby has to take exclusive locks that can't be released until WAL replay has progressed to a certain point seems like a fairly serious wart. If this is a serious wart then it's not one of hot standby, but one of postgres proper. AccessExclusiveLocks (SELECT-blocking locks that is, as opposed to UPDATE/DELETE-blocking locks) are never necessary from a correctness POV, they're only there for implementation reasons. Getting rid of them doesn't seem completely insurmountable either - just as multiple row versions remove the need to block SELECTs dues to concurrent UPDATEs, multiple datafile versions could remove the need to block SELECTs due to concurrent ALTERs. But people seem to live with them quite well, judged from the amount of work put into getting rid of them (zero). I therefore fail to see why they should pose a significant problem in HS setups. The difference is that in HS you have to wait for a moment where *no exclusive lock at all* exist, possibly without contending for any of them, while on the master you might not even blocked by the existence of any of those locks. If you have two sessions which in overlapping transactions lock different tables exlusively you have no problem shutting the master down, but you will never reach a point where no exclusive lock is taken on the slave. 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] 9.0b1: ERROR: btree index keys must be ordered by attribute
Hi 2010/5/10 Tom Lane t...@sss.pgh.pa.us: Ian Barwick barw...@gmail.com writes: 2010/5/9 David Fetter da...@fetter.org: A self-contained way to reproduce this, ideally small, would be fantastic :) s/fantastic/absolutely required to do anything with this report/ Yes, I appreciate that :) I am a bit pressed for time and as googling the error message didn't produce any kind of result I thought it better to at least give a heads-up on the offchance someone might be able to do something with it as is, and / or events overtake me and I never end up doing anything about it at all. Luckily this is easy to reproduce with a stripped-down version of the original table and minimal data set: CREATE TABLE object_version ( object_version_id SERIAL, object_id INT NOT NULL, version INT NOT NULL DEFAULT 0, object_status_idINT NOT NULL, parent_id INT DEFAULT NULL, owner_idINT NOT NULL, created TIMESTAMP(0) NOT NULL DEFAULT NOW(), langCHAR(2) NOT NULL, PRIMARY KEY (object_version_id), UNIQUE (object_id, version, object_status_id, lang) ); INSERT INTO object_version VALUES (DEFAULT, 1, 0, 0, NULL, 1, DEFAULT,'en'), (DEFAULT, 1, 0, -1, NULL, 1, DEFAULT,'en'), (DEFAULT, 1, 1, -1, NULL, 1, DEFAULT,'en'); SELECT ov.object_id FROM object_version ov WHERE ov.object_id = 1 AND ov.version ='0 AND ov.object_status_id = ( SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; SELECT version(); PostgreSQL 9.0beta1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Ubuntu 4.4.1-4ubuntu9) 4.4.1, 64-bit HTH Ian Barwick -- 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] 9.0b1: ERROR: btree index keys must be ordered by attribute
2010/5/10 Ian Barwick barw...@gmail.com: SELECT ov.object_id FROM object_version ov WHERE ov.object_id = 1 AND ov.version ='0 AND ov.object_status_id = ( SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; Apologies, slight cp error; correct version of query: SELECT ov.object_id FROM object_version ov WHERE ov.object_id = 1 AND ov.version =0 AND ov.object_status_id = ( SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; FWIW the test case works fine in 8.4.3 Ian Barwick -- 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 Sun, May 9, 2010 at 6:58 PM, Andres Freund and...@anarazel.de wrote: On Monday 10 May 2010 00:25:44 Florian Pflug wrote: On May 9, 2010, at 22:01 , Robert Haas wrote: On Sun, May 9, 2010 at 3:09 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Florian Pflug f...@phlo.org writes: The only remaining option is to continue applying WAL until you reach a point where no locks are held, then pause. But from a user's POV that is nearly indistinguishable from simply setting hot_standby_conflict_winner to in the first place I think. Not really, the use case would be using the slave as a reporting server, you know you have say 4 hours of reporting queries during which you will pause the recovery. So it's ok for the pause command to take time. Seems like it could take FOREVER on a busy system. Surely that's not OK. The fact that Hot Standby has to take exclusive locks that can't be released until WAL replay has progressed to a certain point seems like a fairly serious wart. If this is a serious wart then it's not one of hot standby, but one of postgres proper. AccessExclusiveLocks (SELECT-blocking locks that is, as opposed to UPDATE/DELETE-blocking locks) are never necessary from a correctness POV, they're only there for implementation reasons. Getting rid of them doesn't seem completely insurmountable either - just as multiple row versions remove the need to block SELECTs dues to concurrent UPDATEs, multiple datafile versions could remove the need to block SELECTs due to concurrent ALTERs. But people seem to live with them quite well, judged from the amount of work put into getting rid of them (zero). I therefore fail to see why they should pose a significant problem in HS setups. The difference is that in HS you have to wait for a moment where *no exclusive lock at all* exist, possibly without contending for any of them, while on the master you might not even blocked by the existence of any of those locks. If you have two sessions which in overlapping transactions lock different tables exlusively you have no problem shutting the master down, but you will never reach a point where no exclusive lock is taken on the slave. A possible solution to this in the shutdown case is to kill anyone waiting on a lock held by the startup process at the same time we kill the startup process, and to kill anyone who subsequently waits for such a lock as soon as they attempt to take it. I'm not sure if this would also make sense in the pause case. Another possible solution would be to try to figure out if there's a way to delay application of WAL that requires the taking of AELs to the point where we could apply it all at once. That might not be feasible, though, or only in some cases, and it's certainly 9.1 material (at least) in any case. Anyway, this is all a little off-topic. We need to get back to arguing about how best to cut the legs out from under a feature that's been in the tree for six months but Tom didn't get around to looking at until last week. I'll restate my position: now that I understand what the issues are (I think), the feature as currently implemented seems pretty wonky, but cutting it down to a boolean seems like an exercise in excessive pessimism about our ability to predict future development directions, as well as possibly quite inconvenient for people attempting to use Hot Standby. Therefore I think we should adopt Tom's original proposal (with +1 also from Stephen Frost), but that doesn't seem likely to fly because, on the one hand, we have Tom himself arguing (along with Bruce and possibly Heikki) that we should whack it down all the way to a boolean; and on the other hand Simon and Greg Smith and I think also Andres Freund and Kevin Grittner arguing that the original feature is OK as-is. Other people who weighed in include Stefan Kaltenbrunner (who opined that Tom had a legitimate complaint about the current design but didn't vote for a specific resolution), Greg Sabino Mullane (who pointed out that SOME of the issues that Tom raised could be solved with proper time synchronization), Josh Drake (who thought requiring NTP to be working was a bad idea, and therefore presumably favors changing something), Josh Berkus (who changed his vote at least once and whose priority seems to have to do with releasing before the turn of the century than with the actual technical option we select, apologies if I'm misreading his emails), Greg Stark (who seems to think that a boolean will be bad news but didn't specifically vote for another option), Dimitri Fontaine (who wants a boolean plus pause/resume functions, or maybe a plugin facility of some kind), Rob Wultsch (who doesn't ever want to kill queries and therefore would be happy with a boolean), Yeb Havinga (who never wants to stall recovery and therefore would also be happy with a boolean), and Florian Pflug (who points out that pause/resume is actually a nontrivial feature). Apologies if I've
Re: [HACKERS] 9.0b1: ERROR: btree index keys must be ordered by attribute
Ian Barwick barw...@gmail.com writes: Apologies, slight cp error; correct version of query: SELECT ov.object_id FROM object_version ov WHERE ov.object_id = 1 AND ov.version =0 AND ov.object_status_id = ( SELECT MAX(ov1.object_status_id) FROM object_version ov1 WHERE ov1.object_id=ov.object_id AND ov1.version = ov.version AND ov1.lang = ov.lang ) AND ov.lang = 'en'; Ah, I see it: - Index Scan Backward using object_version_object_id_version_object_status_id_lang_key on object_version ov1 (cost=0.00..8.27 rows=1 width=4) Index Cond: ((object_id = $0) AND (version = $1) AND (lang = $2) AND (object_status_id IS NOT NULL)) where regression=# \d object_version_object_id_version_object_status_id_lang_key Index public.object_version_object_id_version_object_status_id_lang_key Column | Type |Definition --+--+-- object_id| integer | object_id version | integer | version object_status_id | integer | object_status_id lang | character(2) | lang unique, btree, for table public.object_version The index-based-max code is throwing in the IS NOT NULL condition without thought for where it has to go in the index condition order. Will look into fixing this tomorrow. 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
