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