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

Re: Trx issues: SELECT FOR UPDATE LIMIT

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: James Mancz <james(at)mancz(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Trx issues: SELECT FOR UPDATE LIMIT
Date: 2003-04-30 15:07:44
Message-ID: 22264.1051715264@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
James Mancz <james(at)mancz(dot)com> writes:
> client 2 can carry on processing now, 
> and its SELECT * FROM work WHERE alloc IS NULL LIMIT 1 FOR UPDATE;
> **returns no rows** despite the fact the second row matches.

This is annoying but I do not think it is readily fixable, because the
LIMIT filter acts before the FOR UPDATE processing.  When FOR UPDATE
rejects the first row because it's been changed by the previous updater,
that's all she wrote --- the LIMIT's not gonna produce more rows.

I have some old notes suggesting that maybe FOR UPDATE could be turned
into a plan node that could be stuck underneath the LIMIT node, but
I'm not sure that's workable.  It'd be a nontrivial change certainly,
and arguably unintuitive (the effects of SQL clauses normally act
left-to-right, but you'd be forcing LIMIT to occur after FOR UPDATE).

Those other databases presumably implement LIMIT in a different place
... I bet they don't handle LIMIT in sub-selects though, which is the
advantage to treating it as a plan step.

			regards, tom lane


In response to

pgsql-hackers by date

Next:From: John LiuDate: 2003-04-30 15:13:28
Subject: pattern match, index use and performance
Previous:From: James ManczDate: 2003-04-30 14:49:49
Subject: Trx issues: SELECT FOR UPDATE LIMIT

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