Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
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 contemplating the Monastery: (12)
As of 2014-08-22 15:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (159 votes), past polls