Обсуждение: CREATE OR REPLACE FUNCTION statement just sitting there

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

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?

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.

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

On 2018-Nov-05, Ron wrote:

> That (plus pg_locks)  is the heart of the "list all blocking queries"
> statement I copied from https://wiki.postgresql.org/wiki/Lock_Monitoring.

On that page there's a note about 9.6.  Did you see the referenced
commit
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=52f5d578d6c29bf254e93c69043b817d4047ca67
?  Maybe see about using the "pg_blocking_pids(int) returns int[]"
function instead.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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.

On 11/06/2018 05:34 AM, Alvaro Herrera wrote:

On 2018-Nov-05, Ron wrote:

That (plus pg_locks)  is the heart of the "list all blocking queries"
statement I copied from https://wiki.postgresql.org/wiki/Lock_Monitoring.

On that page there's a note about 9.6.  Did you see the referenced
commit
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=52f5d578d6c29bf254e93c69043b817d4047ca67
?  Maybe see about using the "pg_blocking_pids(int) returns int[]"
function instead.

I did see it, but the https://wiki.postgresql.org/wiki/Lock_Monitoring query seems to work (seeing that it regularly shows locks).

Is this query from https://stackoverflow.com/a/43363536/1543618  adequate to the task?

select pid,       usename,       pg_blocking_pids(pid) as blocked_by,       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

On 2018-Nov-06, Ron wrote:

> On 11/06/2018 05:34 AM, Alvaro Herrera wrote:

> I did see it, but the https://wiki.postgresql.org/wiki/Lock_Monitoring query
> seems to work (seeing that it regularly shows locks).
> 
> Is this query from https://stackoverflow.com/a/43363536/1543618 adequate to
> the task?
> 
> |selectpid,usename,pg_blocking_pids(pid)asblocked_by,query asblocked_query
> frompg_stat_activity wherecardinality(pg_blocking_pids(pid))>0;|

Seems a bit short on details ... I would add the queries being run by
those other PIDs, just to understand what might be going on.  Now, if
that query returns empty when the CREATE is blocked, then this may be
a red herring.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services