Remove all data from table but last N entries

Question

I'm using psycopg2 with Python.

I'd like to periodically flush data from my db. I've set up a task with Timer for this. I had asked this question before, but using the answer listed there freezes up my machine (keyboard stops responding and entire system grinds to halt). Instead, I would like to delete all entries in my table albeit the last N (Not sure that this is the right approach either).

Basically, there is another python process that is running (separate executable), which is populating the db that I wish to interrogate. It seems that if I delete all entries, and that other process is running, that it can lead to the freeze. I don't know of a safe way in which I can remove entries; it's almost as if the other process is relying on an incrementing ID as it writes to the db.

If anyone could help me work this out it'd be greatly appreciated. Thoughts?

Mariano · Answer 1 · 2013-04-01T05:29:43.187

2

A possible solution is to run a DELETE on all ids except those returned by select ... order by pk desc limit N given an autoincremental pk. If no such pk exists, having a created_date and ordering by it should do the same.

Non tested example:

import psycopg2
connection = psycopg2.connect('dbname=test user=postgres')
cursor = conn.cursor()

query = 'delete from my_table where id not in (
            select id from my_table order by id desc limit 30)'
cursor.execute(query)
cursor.commit() #Don't know if necessary
cursor.close()
connection.close()

edited Apr 01 '13 at 05:29

answered Apr 01 '13 at 03:56

Mariano

1,357
3
17
30

Thanks. Could you give me an example? – jml Apr 01 '13 at 04:29
how often would you call cursor.close()? – jml Apr 01 '13 at 06:22
You only need to execute a single query, so my guess would be only once. – Mariano Apr 01 '13 at 09:07
Actually, the code is run based on a Task, which occurs anywhere from 1-3 seconds apart on a Timer. I can get the system to run the Task around 3 times and then my machine freezes up. I think that the process which is propagating the data (also python) is looking to insert into an ID which now doesn't exist, as we've deleted it. Possible? – jml Apr 01 '13 at 23:49
I just had a thought - is there a way to get Postgres to reset the ID on that table? I think it's being auto-generated. – jml Apr 02 '13 at 00:18
Resetting the ID (i.e., "starting over from 0") is almost **always** a sign of bad design. You should leave those those details to the engine, and find the error in your code. Making the DDBB work around faulty code is asking for trouble. – Mariano Apr 02 '13 at 00:20
1

I figured out that I wasn't committing, which was causing a disconnect between the two programs. – jml Apr 02 '13 at 00:34

score 1 · Accepted Answer · edited May 23 '17 at 12:28

This is probably much faster:

CRETE TEMP TABLE tbl_tmp AS
SELECT * FROM tbl ORDER BY <undisclosed> LIMIT <N>;

TRUNCATE TABLE tbl;
INSERT INTO tbl SELECT * FROM tbl_tmp;

Do it all in one session. Specifics depend on additional circumstances you did not disclose.

Compare to this related, comprehensive answer (your case is simpler):
Remove duplicates from table based on multiple criteria and persist to other table

Remove all data from table but last N entries

2 Answers2