pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedselect for update
6+ messages / 3 participants
[nested] [flat]
* select for update
@ 2011-04-22 17:49 Craig James <craig_james@emolecules.com>
2011-04-22 20:25 ` Re: select for update Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-22 20:58 ` Re: select for update Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Craig James @ 2011-04-22 17:49 UTC (permalink / raw)
To: pgsql-admin
I thought I understood "select ... for update," but maybe not.
We have a number of separate databases and a unique integer identifier that's supposed to be global across all databases. A single "archive" database is used to issue the next available ID when a process wants to create a new object. The sequence of operations goes like this (pseudo-code):
/* start a transaction *
begin;
/* see if an objectid has been returned for re-use */
select objectid from archive where db_id is null limit 1 for update
/* no ID available? issue a new one */
if (objectid is null)
new_id = select nextval('object_id_sequence')
insert into archive(objectid, db_id) values('new_id', 'new_id')
/* ID available? claim it */
else
update archive set db_id = this_db_id where objectid
commit
The problem is that very occasionally the same ID will be issued twice. I don't see how this can be. Doesn't the "for update" guarantee that no other process can claim that same row?
Thanks,
Craig
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select for update
2011-04-22 17:49 select for update Craig James <craig_james@emolecules.com>
@ 2011-04-22 20:25 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2011-04-22 20:25 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; pgsql-admin
Craig James <craig_james@emolecules.com> wrote:
> The sequence of operations goes like this (pseudo-code):
> The problem is that very occasionally the same ID will be issued
> twice. I don't see how this can be. Doesn't the "for update"
> guarantee that no other process can claim that same row?
I don't see a flaw in the pseudo-code (other than an apparent typo
on the insert). Can you show the actual code?
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select for update
2011-04-22 17:49 select for update Craig James <craig_james@emolecules.com>
@ 2011-04-22 20:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-22 23:10 ` Re: select for update Craig James <craig_james@emolecules.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2011-04-22 20:58 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-admin
Craig James <craig_james@emolecules.com> writes:
> select objectid from archive where db_id is null limit 1 for update
The interaction between LIMIT and FOR UPDATE changed in 9.0 ... what
PG version are you using?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select for update
2011-04-22 17:49 select for update Craig James <craig_james@emolecules.com>
2011-04-22 20:58 ` Re: select for update Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-04-22 23:10 ` Craig James <craig_james@emolecules.com>
2011-04-23 03:17 ` Re: select for update Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Craig James @ 2011-04-22 23:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On 4/22/11 1:58 PM, Tom Lane wrote:
> Craig James<craig_james@emolecules.com> writes:
>> select objectid from archive where db_id is null limit 1 for update
> The interaction between LIMIT and FOR UPDATE changed in 9.0 ... what
> PG version are you using?
8.4.4
thanks,
Craig
> regards, tom lane
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select for update
2011-04-22 17:49 select for update Craig James <craig_james@emolecules.com>
2011-04-22 20:58 ` Re: select for update Tom Lane <tgl@sss.pgh.pa.us>
2011-04-22 23:10 ` Re: select for update Craig James <craig_james@emolecules.com>
@ 2011-04-23 03:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-24 00:49 ` Re: select for update Craig James <craig_james@emolecules.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2011-04-23 03:17 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-admin
Craig James <craig_james@emolecules.com> writes:
> On 4/22/11 1:58 PM, Tom Lane wrote:
>> Craig James<craig_james@emolecules.com> writes:
>>> select objectid from archive where db_id is null limit 1 for update
>> The interaction between LIMIT and FOR UPDATE changed in 9.0 ... what
>> PG version are you using?
> 8.4.4
Well, note what it says in the 8.4 SELECT reference page:
Caution
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.
I think what's probably happening to you is you're getting a NULL not
because there isn't a matching row, but because somebody is updating the
first matching row underneath you and then the LIMIT prevents finding
any other matches. However, that pseudo-code is too pseudo to tell
whether this theory is correct.
(9.0 handles these situations in a less unintuitive fashion, btw.)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select for update
2011-04-22 17:49 select for update Craig James <craig_james@emolecules.com>
2011-04-22 20:58 ` Re: select for update Tom Lane <tgl@sss.pgh.pa.us>
2011-04-22 23:10 ` Re: select for update Craig James <craig_james@emolecules.com>
2011-04-23 03:17 ` Re: select for update Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-04-24 00:49 ` Craig James <craig_james@emolecules.com>
0 siblings, 0 replies; 6+ messages in thread
From: Craig James @ 2011-04-24 00:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On 4/22/11 8:17 PM, Tom Lane wrote:
> Craig James<craig_james@emolecules.com> writes:
>> On 4/22/11 1:58 PM, Tom Lane wrote:
>>> Craig James<craig_james@emolecules.com> writes:
>>>> select objectid from archive where db_id is null limit 1 for update
>>> The interaction between LIMIT and FOR UPDATE changed in 9.0 ... what
>>> PG version are you using?
>> 8.4.4
> Well, note what it says in the 8.4 SELECT reference page:
>
> Caution
>
> 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.
>
> I think what's probably happening to you is you're getting a NULL not
> because there isn't a matching row, but because somebody is updating the
> first matching row underneath you and then the LIMIT prevents finding
> any other matches. However, that pseudo-code is too pseudo to tell
> whether this theory is correct.
Thanks, it sounds like this is exactly what's happening. It happens very rarely (a few times per month), so this makes sense.
I think I just need a two-step approach:
$object_id = $dbh->selectrow_array("select min(objectid) from archive where db_id is null");
if ($object_id) {
$db_id = $dbh->selectrow_array("select db_id from archive where objectid = $object_id for update");
... double check that db_id is still NULL, repeat if someone else grabbed it...
}
> (9.0 handles these situations in a less unintuitive fashion, btw.)
We'll be migrating soon, thanks.
Craig
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-04-24 00:49 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-22 17:49 select for update Craig James <craig_james@emolecules.com>
2011-04-22 20:25 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-22 20:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-22 23:10 ` Craig James <craig_james@emolecules.com>
2011-04-23 03:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-24 00:49 ` Craig James <craig_james@emolecules.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox