Re: Deletion Challenge

[Benjamin Smith <lists@xxxxxxxxxxxxxxxxxx>]

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.* 




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general