Skip site navigation (1) Skip section navigation (2)

Endgame for all those SELECT FOR UPDATE changes: fix plan node order

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Endgame for all those SELECT FOR UPDATE changes: fix plan node order
Date: 2009-10-26 02:34:02
Message-ID: 1611.1256524442@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Now that we've got a hopefully-non-broken implementation of SELECT FOR
UPDATE locking as a plan node, we can finally contemplate fixing two
misbehaviors that are called out on the SELECT reference page:

    It is possible for a SELECT command using both LIMIT and FOR
    UPDATE/SHARE clauses to return fewer rows than specified by LIMIT. This
    is because LIMIT is applied first. The command selects the specified
    number of rows, but might then block trying to obtain a lock on one or
    more of them. Once the SELECT unblocks, the row might have been deleted
    or updated so that it does not meet the query WHERE condition anymore,
    in which case it will not be returned.

    Similarly, it is possible for a SELECT command using ORDER BY and FOR
    UPDATE/SHARE to return rows out of order. This is because ORDER BY is
    applied first. The command orders the result, but might then block
    trying to obtain a lock on one or more of the rows. Once the SELECT
    unblocks, one of the ordered columns might have been modified and be
    returned out of order. A workaround is to perform SELECT ... FOR
    UPDATE/SHARE and then SELECT ... ORDER BY.

All that we have to do to fix the first one is to put the LockRows node
below the Limit node instead of above it.  The solution for the second
one is to also put LockRows underneath the Sort node, and to regard its
output as unsorted so that a Sort node will certainly be generated.
(This in turn implies that we should prefer the cheapest-total plan
for the rest of the query.)

Does anyone have any objections to this?  I can't see that it will break
any applications that work today, but maybe I'm missing something.

			regards, tom lane

Responses

pgsql-hackers by date

Next:From: Fujii MasaoDate: 2009-10-26 02:35:59
Subject: Re: pg_standby doesnt't work
Previous:From: Itagaki TakahiroDate: 2009-10-26 02:11:23
Subject: Re: Statement Level Deferred Triggers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group