I have something that I think is a fairly common code model, but with
an SQL query that feels like it's fighting the system.
The 'cron' table has a number of tasks (one row = one task), and the
primary loop of the program (massively simplified) fetches one row,
processes it, commits. One row/task is
performed in one transaction, atomically, including the removal of the
row (so if something crashes out, the row remains and will be
processed later). The top of the loop therefore
needs to fetch one row, and delete it. If I were deleting *all* the
appropriate rows, it would be:
DELETE FROM cron WHERE ts<now() RETURNING *;
and iterate over the result set. But to delete and fetch just one row,
I can't simply put a LIMIT 1 clause onto that. Other people have run
into this before, and suggested this:
http://stackoverflow.com/questions/5170546/how-do-i-delete-a-fixed-number-of-rows-with-sorting-in-postgresql
DELETE FROM cron WHERE ctid=(SELECT ctid FROM cron WHERE ts<now()
ORDER BY ts limit 1) RETURNING *;
(I'm simplifying this drastically; the actual query has other
conditions on it, and does some processing in the RETURNING instead of
just *. But the effect is the same.)
Is there any better way than using ctid (or, equivalently, a
primary/unique key value) to fetch in this way?
It appears that the exact proposal of LIMIT on a DELETE has been
proposed and rejected before (eg
http://www.postgresql.org/message-id/26819.1291133045@sss.pgh.pa.us
and
surrounding), which is a pity because it does make good sense to do
this in a single pass rather than fetching some kind of unique
identifier and then re-locating by that. But is
the ctid somehow magical in being actually fast/simple enough to not
care about the difference?
Chris Angelico
