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@xxxxxxxxxxxxx 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
