> On 1 Dec 2017, at 22:39, Job <Job@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,
databaseservers.
> 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
providedin 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.