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

Re: DELETE with LIMIT (or my first hack)

From: Valentine Gogichashvili <valgog(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Bruce Momjian <bruce(at)momjian(dot)us>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Csaba Nagy <ncslists(at)googlemail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Jaime Casanova <jaime(at)2ndquadrant(dot)com>, Daniel Loureiro <loureirorg(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: DELETE with LIMIT (or my first hack)
Date: 2010-12-01 11:46:49
Message-ID: AANLkTikfYdhC8-N8hO5dNbgNK7SDPoZO4WYr6JapevDR@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

actually introducing LIMIT and OFFSET with ORDER BY to DELETE/UPDATE would
make it much easier to push data from one (say "queue") table to another.
And to fetch chunks of queue entries updating their status in one statement.
Now I have to do SELECT...ORDER BY...LIMIT and then do some magic with
arrays of IDs and updates/deletes or UPDATE ... WHERE id in (SELECT .. ORDER
BY... LIMIT) RETURNING ... to make that work, but this is still possible to
do with the WHERE clause, though I am not quite sure if that is most
efficient in comparison to the direct approach. And speaking about pushing
data from one table to another, what I really would like to be able to do
would be also something like:

INSERT INTO ...
DELETE FROM ... WHERE... ORDER BY.. [LIMIT...]
RETURNING...;

this would be also quite efficient when re-arranging data in table
partitions (though LIMIT/OFFSET there will be just nice to have possibility
for reducing chunk sized of data being moved).

Additionally we need quite often to clean up some log tables depending not
on the timestamps but on the number of rows in that tables, so leaving only
last N newest records in a table... OFFSET would be really cool to have for
that usecase as well...

With best regards,

-- Valentine Gogichashvili

In response to

Responses

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2010-12-01 11:47:13
Subject: Re: [HACKERS] Improved JDBC driver part 2
Previous:From: Marti RaudseppDate: 2010-12-01 11:46:45
Subject: Re: We really ought to do something about O_DIRECT and data=journalled on ext4

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