On 11/06/2018 05:05 AM, Laurenz Albe wrote:
> 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.
Good question. I'll look at that the next time we try it.
--
Angular momentum makes the world go 'round.