Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re^4: Help on selecting and deleting many records in a DB.

by neilwatson (Curate)
on Apr 07, 2014 at 23:58 UTC ( #1081450=note: print w/ replies, xml ) Need Help??


in reply to Re^3: Help on selecting and deleting many records in a DB.
in thread Help on selecting and deleting many records in a DB.

SELECT * FROM agent_log t1 WHERE timestamp < now() - interval '7 days' AND timestamp < ( SELECT max(timestamp) FROM agent_log t2 WHERE t2.class=t1.class AND t2.promiser=t1.promiser AND t2.hostname=t1.hostname AND t2.ip_address=t1.ip_address ) ORDER by class,timestamp;
You almost had me there, but I don't think this will keep the maximum timestamp per day.

Neil Watson
watson-wilson.ca


Comment on Re^4: Help on selecting and deleting many records in a DB.
Download Code
Re^5: Help on selecting and deleting many records in a DB.
by vincent_veyron (Acolyte) on Apr 08, 2014 at 16:28 UTC

    Well, it does if you run it every day and clean up afterwards, with a cron job for instance.

    If possible, it seems you could solve a big part of your problem using partitioning of your table, probably by day; see:
    http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html



    libremen.com : legal case, contract and insurance claim management software

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1081450]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (8)
As of 2014-09-18 23:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (128 votes), past polls