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

Atomic/locked "allocation" of table row?

From: Sten Daniel Soersdal <netslists(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Atomic/locked "allocation" of table row?
Date: 2007-08-06 02:02:30
Message-ID: 46B68136.1090206@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
CREATE TABLE tunnel (
	username	integer UNIQUE, -- ranges from 1 to ~250.000
	password	varchar
);

I need to write a function to "allocate" an empty "tunnel" on a 
prepopulated table where all fields, except username, IS NULL by default.

In essence this function would do;

UPDATE SET password = 'mysecret' WHERE username = ( SELECT username FROM 
tunnel WHERE password IS NULL LIMIT 1 );

But i need to know that no other concurrently running session would 
allocate that specific row in between the SELECT ... and the UPDATE.

Could this be done atomically somehow? Is it necessary to add some kind 
of locking? Help?

-- 
Sten Daniel Soersdal

Responses

pgsql-novice by date

Next:From: Rodrigo De LeónDate: 2007-08-06 03:44:29
Subject: Re: Atomic/locked "allocation" of table row?
Previous:From: Chirag PatelDate: 2007-08-04 23:32:50
Subject: clustering and denormalizing with PostgreSQL?

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