On 06/26/2014 10:47 AM, Marti Raudsepp wrote:
> This deserves a caveat, in the default "read committed" isolation
> level, this example can delete more rows that it inserts;
This is only true because I accidentally inverted the date resolutions.
It should have been:
BEGIN;
INSERT INTO my_table_stable
SELECT * FROM ONLY my_table
WHERE date_col <= now() - INTERVAL '15 minutes';
DELETE FROM ONLY my_table
WHERE date_col <= now() - INTERVAL '15 minutes';
COMMIT;
Since now() is tied to transaction start time, it's safe in this
context. But you're right, I actually debated including the CTE syntax,
but you did it for me. :)
Though you fell into the same trap I did. The correct CTE should be:
WITH deleted AS (
DELETE FROM ONLY my_table
WHERE date_col <= now() - INTERVAL '15 minutes'
RETURNING *
)
INSERT INTO my_table_stable
SELECT * FROM deleted;
--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email