Re: Deletion Challenge

On Saturday, December 05, 2015 11:08:05 AM Berend Tober wrote:
> WITH max_click AS (
> SELECT
> cash_journal.fairian_id,
> max(cash_journal.click) AS click
> FROM cash_journal
> GROUP BY cash_journal.fairian_id
> )
> delete from cash_journal j
> using max_click b
> where j.fairian_id = b.fairian_id
> and j.click < b.click;
>
> WITH max_journal_id AS (
> SELECT
> cash_journal.fairian_id,
> cash_journal.click,
> max(cash_journal.cash_journal_id) AS cash_journal_id
> FROM cash_journal
> GROUP BY cash_journal.fairian_id, cash_journal.click
> )
> delete from cash_journal j
> using max_journal_id b
> where j.fairian_id = b.fairian_id
> and j.click = b.click
> and j.cash_journal_id < b.cash_journal_id;

Although I couldn't be sure if this would provide atomicity, I'd merge these
into one query like:

WITH max_click AS (
SELECT
cash_journal.fairian_id,
max(cash_journal.click) AS click
FROM cash_journal
GROUP BY cash_journal.fairian_id
),
max_journal_id AS (
SELECT
cash_journal.fairian_id,
cash_journal.click,
max(cash_journal.cash_journal_id) AS cash_journal_id
FROM cash_journal
GROUP BY cash_journal.fairian_id, cash_journal.click
),
delete_journal1 AS
(
delete from cash_journal j
using max_click b
where j.fairian_id = b.fairian_id
and j.click < b.click
returning *, 'journal1'::varchar AS source
),
delete_journal2 AS
(
delete from cash_journal j
using max_journal_id b
where j.fairian_id = b.fairian_id
and j.click = b.click
and j.cash_journal_id < b.cash_journal_id
returning *, 'journal2'::varchar AS source
)
-- AND THEN TO FIND OUT WHAT HAPPENED
SELECT delete_journal1.*
UNION ALL
select delete_journal2.*