Re: UPDATE/DELETE with ORDER BY and LIMIT

On Sat, 25 Sep 2010 12:32:55 +0200
Alban Hertroys  wrote:

> On 24 Sep 2010, at 21:20, Bartłomiej Korupczyński wrote:
> 
> > Hi guys,
> > 
> > I've found some posts from 2007 about UPDATE/DELETE ... LIMIT N syntax
> > and I'd like to raise it again. Current PostgreSQL of UPDATE/DELETE
> > implement RETURNING statement, so extending by ORDER and LIMIT would be
> > really useful.
> 
> > All that with just one query. In this specific example, the ORDER BY
> > statement could be even omitted if we don't care how slots are
> > distributed between users.
> 
> This probably came up in the discussion from back then as well, but what stops you from using a sub-select?
> 
> UPDATE slots
>   FROM (SELECT id FROM slots WHERE user IS NULL
> 	ORDER BY id LIMIT 1) AS available
>    SET user='joe'
>  WHERE id = available.id
>  RETURNING *;
> 
> Admittedly that's longer and would be slightly less efficient, but it is available now (and has been for a while) and it's still in one query.

Well, it's not that anything can stop me ;)

It's just a suggestion. I think that:
1. UPDATE ... LIMIT approach is more obvious (= more clear for people to read)
2. as you said -- it's shorter and more efficient, even if it's just a little bit (one index scan less, if id was indexed).


> Also:
> > CREATE TABLE slots (
> > 	id INTEGER UNIQUE NOT NULL,
> > 	user VARCHAR(32),
> > 	expires TIMESTAMP WITH TIMEZONE,
> > 	-- some other columns
> > );
> 
> 
> I'd declare a primary key as what it is, not as some generic UNIQUE NOT NULL column ;) It won't make much difference in practice, but for example, that way it's intended use is immediately clear from the table definition if people look it up.

It was just a quick and dirty example, but of course you're right :)


Regards,
BK