On Fri, Dec 1, 2017 at 4:39 PM, Job <j...@colliniconsulting.it> 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;
