Re: Fast data, slow data

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