Steve Crawford wrote:
If I understand correctly the value of "click" always advances and within a "click" the
"cash_journal_id" always advances - not necessarily by single steps so within a fairian_id, ordering
by "click" plus "cash_journal_id" would return the records in order from which you want the most
recent 5 for each farian_id.
Typing without testing and ignoring performance optimizations, something along the lines of the
following should work and covers the "last 5" issue as well.
with stuff_to_delete as (
select farian_id, click, cash_journal_id,
rank() over (partition by farian_id order by (click, cash_journal_id) desc) as howold)
from cash_journal)
delete from cash_journal
using stuff_to_delete
where
cash_journal.farian_id = stuff_to_delete.farian_id
and cash_journal.click = stuff_to_delete.click
and cash_journal.cash_journal_id = stuff_to_delete.cash_journal_id
and stuff_to_delete.howold > 5;
Assessing without testing, I like that. Thanks!
Although the above is not the exactly the form I was using, an earlier iteration of a related
problem employed window functions. But as the data set grew performance suffered, so if deletes were
not done on a regular, continuing basis in order to keep the historical data set approximately
"small", the process execution time using a windowing scheme eventually exceeded the extent of my
patience.
That "non-scalable" situation is actually what motivated the deliberate de-normalization (of
retaining the "running balance" in a separate column) and the desire to delete old data. The
original implementation calculated the running balance on-the-fly, employing windowing per
fairian_id, and those tallies of the net balance entailed increasingly lengthy execution times as
the number of rows increased, hence I was motivated to retain only a relatively constant-sized
per-farian history, and I dismissed the use of windowing for the delete problem since it was so
problematic for the running-balance-without-delete problem.
Thanks for knocking some sense into me!
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
