Re: CREATE OR REPLACE FUNCTION statement just sitting there

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.