Обсуждение: UPDATE/DELETE with ORDER BY and LIMIT

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.

Let's say we need to reserve some kind of identifier for user.

CREATE TABLE slots (
    id INTEGER UNIQUE NOT NULL,
    user VARCHAR(32),
    expires TIMESTAMP WITH TIMEZONE,
    -- some other columns
);

-- We have 4 identifiers to use:
INSERT INTO slots VALUES (1);
INSERT INTO slots VALUES (2);
INSERT INTO slots VALUES (3);
INSERT INTO slots VALUES (4);

-- Now the clue: we want to reserve unused slot:
UPDATE slots SET user='joe' WHERE user IS NULL
    ORDER BY id LIMIT 1 RETURNING *;

-- Or another approach: reserve slot for 30 minutes:
UPDATE slots SET expires=now()+INTERVAL '30 minutes'
    WHERE expires<now() ORDER BY id LIMIT 1 RETURNING *;

Our query will return:
1. all data of the slot that have been reserved
2. no rows, if there is no free slot.

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.


Comments are welcome.


Best regards,

--
Bartłomiej Korupczyński <bartek-fora@klolik.org>

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'sstill in one query. 

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.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c9dcfe7678304776795795!

On Sat, 25 Sep 2010 12:32:55 +0200
Alban Hertroys <dalroi@solfertje.student.utwente.nl> 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'sstill 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
inpractice, but for example, that way it's intended use is immediately clear from the table definition if people look
itup. 

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


Regards,
BK