On 11/05/2018 08:30 PM, Rob Sargent wrote:
On 11/5/18 7:05 PM, Ron wrote:
I've got a very puzzling problem on 9.6.6 systems we just migrated from 8.4. (The same problem happened on 9.6.9, but rolled it back so as to make prod have the same version as our Staging systems.)
We've got a giant script full of DROP TRIGGER IF EXISTS and CREATE TABLE and DROP TABLE and CREATE OR REPLACE FUNCTION statements.
It's purpose is to drop old parts of partitioned tables and add new tables.
It ALWAYS worked just fine on our big, ancient, production 8.4 databases (otherwise I'd have heard the screams of user rage), and on our 9.6.6 staging environment. However, one or more of our big (and schema-identical) prod databases (which are each on a different server) it is finicky and tends to just "sit" at a random one of the CREATE OR REPLACE FUNCTION statements.
The "list all blocking queries" I run doesn't show that anything is blocking it (though it blocks everything else), and neither top(1) nor iotop(1) show any activity.
If it matters, this script is fed to the databases via the JDBC driver, and it works fine when I run it via psql. (I'd gladly run the scripts manually, but these are child databases, and a parent db must be updated at the same time by a canned application.)
Where in Postgres can I look to see why it's just sitting there?
Thanks
--
Angular momentum makes the world go 'round.
select * from pg_stat_activity;
might shed some light?
That (plus pg_locks) is the heart of the "list all blocking queries" statement I copied from
https://wiki.postgresql.org/wiki/Lock_Monitoring.
--
Angular momentum makes the world go 'round.