Endgame for all those SELECT FOR UPDATE changes: fix plan node order
in [PgSql]
|
From: Tom Lane on 27 Oct 2009 13:06 Robert Haas <robertmhaas(a)gmail.com> writes: > On Tue, Oct 27, 2009 at 11:22 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> What I am thinking we should do is define that FOR UPDATE happens before >> ORDER BY or LIMIT normally, but that if the FOR UPDATE is inherited from >> an outer query level, it happens after the sub-select's ORDER BY or >> LIMIT. �The first provision fixes the bugs noted in our documentation, >> and the second one allows people to get back the old behavior if they >> need it for performance. �This also seems reasonably non-astonishing >> from a semantic viewpoint. > When you refer to an "outer query level", is that the same thing as a > sub-select? If so, I think I agree that the behavior is > non-astonishing. Right, the case would be something like select * from (select * from foo order by x limit n) ss for update of ss; If you try this in any existing release it will just fail, because the planner knows that it hasn't got a way to execute FOR UPDATE in a subquery. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 27 Oct 2009 13:51 On Tue, Oct 27, 2009 at 1:06 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> On Tue, Oct 27, 2009 at 11:22 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >>> What I am thinking we should do is define that FOR UPDATE happens before >>> ORDER BY or LIMIT normally, but that if the FOR UPDATE is inherited from >>> an outer query level, it happens after the sub-select's ORDER BY or >>> LIMIT. The first provision fixes the bugs noted in our documentation, >>> and the second one allows people to get back the old behavior if they >>> need it for performance. This also seems reasonably non-astonishing >>> from a semantic viewpoint. > >> When you refer to an "outer query level", is that the same thing as a >> sub-select? If so, I think I agree that the behavior is >> non-astonishing. > > Right, the case would be something like > > select * from > (select * from foo order by x limit n) ss > for update of ss; > > If you try this in any existing release it will just fail, because the > planner knows that it hasn't got a way to execute FOR UPDATE in a > subquery. That's a pretty odd construction. In some sense I don't like the proposed behavior, because it's imaginable that someone would use this syntax without realizing that it could produce wrong answers. My own gut instinct would be to always push down the FOR UPDATE as being a clearer way to convey what was meant - but we've already established that not everyone's gut instincts agree with mine, and if someone does write this, they might easily fail to understand the risk that it poses. I'm not sure what to do about it, though. Not giving people ANY way to recover the old behavior is a little troubling. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 27 Oct 2009 16:42 Robert Haas <robertmhaas(a)gmail.com> writes: > On Tue, Oct 27, 2009 at 1:06 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> Right, the case would be something like >> >> select * from >> (select * from foo order by x limit n) ss >> for update of ss; > That's a pretty odd construction. Dunno why you think that. That's exactly what one would write if one wanted certain operations to execute in a different order than they're defined to execute in within a single query level. We have not previously been very clear about the order of operations for FOR UPDATE locking relative to other steps, but now we will be. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 27 Oct 2009 17:05 I wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> On Tue, Oct 27, 2009 at 1:06 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >>> Right, the case would be something like >>> select * from >>> (select * from foo order by x limit n) ss >>> for update of ss; >> That's a pretty odd construction. > Dunno why you think that. That's exactly what one would write if one > wanted certain operations to execute in a different order than they're > defined to execute in within a single query level. We have not > previously been very clear about the order of operations for FOR UPDATE > locking relative to other steps, but now we will be. Actually ... it strikes me that there is another way we could approach this. Namely, leave the semantics as-is (FOR UPDATE runs last) and document that you can do select * from (select * from foo for update) ss order by x limit n; if you need FOR UPDATE to run before sorting. Or perhaps better, redefine the ordering as ORDER BY then FOR UPDATE then LIMIT. Swapping FOR UPDATE and LIMIT has no performance cost and eliminates the worse of the two complaints in the documentation, without breaking any working queries AFAICS. If you have the case where you want to cope with concurrent updates to the sort key, then you can write the more complicated query, and it's gonna cost ya. But that's not a typical usage, as proven by the fact that it took years to realize there was a problem there. So we shouldn't optimize for that usage at the expense of cases where the sort key isn't expected to change. It could be argued that this approach doesn't satisfy the principle of least astonishment as well as doing FOR UPDATE first, but on reflection I'm not sure I buy that. The traditional definition has been that we only lock the rows that are actually returned, and putting FOR UPDATE underneath the sort will break that expectation. If it's only underneath LIMIT we can still meet that expectation. So I'm liking this more the more I think about it ... and it's also significantly less work than the other way would be. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
First
|
Prev
|
Pages: 1 2 3 Prev: [HACKERS] License clarification: BSD vs MIT Next: Extraneous newlines in logfile from vacuumdb |