Hi.
I'm continuing on with the problems I have in our reports/data warehouse system. Basically, the system brings in tables from our various production systems (sybase, postgresql, mssql, different servers) every night. Some tables are brought in whole, and some are brought in based on a date field, and only the relevant interval is imported.
For tables which are brought whole, I first truncate the local table, then copy in the up-to-date data. For the ones that are brought partially, I delete partially first, and then copy in the same way.
The trouble is that sometimes there is a stray select which has been initiated and then abandoned (without cancellation) by the crystal reports system. When these queries happen to last into the night, they lock some of the tables which are supposed to be truncated. Then the whole process hangs until the query quits or dies, which, we have seen in the past, can take several hours sometimes.
What I want to do is write a script that kills any queries or connections from the crystal system, and then prevents new queries from being ran, until I finish loading all the tables, at which point I want to allow queries again.
First, the easy part - regarding allowing/disallowing queries. Is it possible to GRANT or REVOKE access to tables based on the originating IP?
Second, and the more complicated one - what do I do about rogue queries that are running when my process starts? Today we had a query that ran since yesterday. I called pg_cancel_backend() on it several times and waited for almost two hours - to no avail. Eventually I had to ask our sysadmin to shutdown PostgreSQL, which took some five minutes, but eventually worked. Is there a way to do the same thing to a single process without shutting down the whole server, and without causing any harm to the database or memory corruption? Something I can call from within SQL? I run the nightly script from a linux user which is not "postgres", so I'd prefer a way that doesn't require using "kill".
Thank you,
Herouth Maoz
