CREATE OR REPLACE FUNCTION statement just sitting there

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?

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.

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?

|selectpid,usename,pg_blocking_pids(pid)asblocked_by,query asblocked_query
frompg_stat_activity wherecardinality(pg_blocking_pids(pid))>0;|

--
Angular momentum makes the world go 'round.

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