Ron wrote:
> > > 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?
> >
> > 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.
If there is nothing with "granted" set to FALSE in "pg_locks", you are not blocked by
a database lock.
What is the "state" of the hanging database session in "pg_stat_activity"?
If it is "idle" or "idle in transaction", then the lock must be in your Java process.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com