Re: Problems with triggers and table lock

> On 1 Dec 2017, at 22:39, Job <Job(at)colliniconsulting(dot)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?

In its most basic form:

select * from pg_locks;

There are a few more elaborate versions of that query around that look up identifiers and such. Those are probably provided in the docs, but otherwise the mailing list archives or your favourite internet search engine should do the trick.

Possibly you're waiting on an uncommitted transaction from a process in state "Idle in transaction" from pg_stat_activity.

If that doesn't get you anywhere, an explain plan of your query would make it easier to help you.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.