From: | Shaun Thomas <sthomas(at)optionshouse(dot)com> |
---|---|
To: | Marti Raudsepp <marti(at)juffo(dot)org> |
Cc: | Tim Uckun <timuckun(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Fast data, slow data |
Date: | 2014-06-26 16:05:47 |
Message-ID: | 53AC44DB.30100@optionshouse.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)optionshouse(dot)com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
From | Date | Subject | |
---|---|---|---|
Next Message | Shaun Thomas | 2014-06-26 16:07:32 | Re: DATA corruption after promoting slave to master |
Previous Message | Marti Raudsepp | 2014-06-26 15:47:47 | Re: Fast data, slow data |