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.
|
