0

We use postgres as a real-time data cache for observations. We need to drop our tables on a daily basis. There are frequently clients that still have the db open for reading, actually they have it open for read/write and don't realize it. We have specifically noted that Python opens it rw and keeps a permanent transaction lock on the DB. This prevents us from dropping the tables.

The data table can have different number of columns on a daily basis, so 'delete from table' does not appear to be an option.

We have tried creating a read-only user, but that did not help, it was still getting "IDLE in transaction".

Is there any kind of 'kill -9' for dropping tables?

We are currently on PostgreSQL 8.4 on RHEL 6, but will be migrating to RHEL 7 soon.

Chris Webster
  • 1

1 Answers1

0

If you have administrative access then you can kill all the current sessions. I think your question is similar to this.

Community
  • 1
  • 1
Jigar
  • 468
  • 1
  • 5
  • 15
  • Thanks, yes it is similar. However some of our client connections have gotten smarter and reconnect immediately. Mostly since connections can dropped for more routine reasons (server reboot, etc). Currently we have to change connections to localhost only in the postgresql.conf file, restart the server, drop the tables, then re-install the original postgresql.conf file. – Chris Webster Dec 08 '15 at 17:57
  • I accept marked as duplicate. Didn't see the revoke connect bit. – Chris Webster Dec 08 '15 at 18:22