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: (view raw or whole thread)
Lists: pgsql-novice
	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


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-2015 The PostgreSQL Global Development Group