Re: Problems with triggers and table lock

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Fri, Dec 1, 2017 at 4:39 PM, Job <Job@xxxxxxxxxxxxxxxxxxxx> wrote:

Dear guys,

we are using Postgresql 9.6.1 with Rubyrep in order to replicate some tables across two different, and remote, database servers.
Since few weeks sometimes when inserting/updating some rows, the statement remains waiting and table seems locked for insert/updates.

When i issue "select * from pg_stat_activity" no query is locked.
I only obtain this error messages when i kill (i also try directly from bash with pgsql) with "ctrl+c" the locked insert:

ERROR:  canceling statement due to user request
CONTEXT:  while updating tuple (0,7) in relation "TABLE"
SQL statement "UPDATE TABLE SET FIELD=NOW() WHERE FIELD IS NULL"
PL/pgSQL function TRIGGER_NAME() line 3 at SQL statement

How can i see what is locking my table/tables?

Thank you!
F

>How can i see what is locking my table/tables?

The attached query will give you all the necessary info.

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

SELECT l.database AS db_oid, 
             d.datname,
            l.relation, 
           n.nspname,
           c.relname,
           a.pid,
           a.usename,
           l.locktype,
           l. mode, 
           l. granted,
           l.tuple 
  FROM pg_locks l
  JOIN pg_class c ON (c.oid = l.relation)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_database d    ON (d.oid = l.database)
  JOIN pg_stat_activity a ON (a.pid = l.pid) 
ORDER BY database,
         relation, 
         pid;