Thread: Problems with triggers and table lock

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

> 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.

Hi guys,

thank you for everything.

Thanks for the queries, i ntocied the lock comes from here:
  16389 | flashstart |    17409 | public             | confs                                  |  5646 | postgres |
tuple   | ExclusiveLock    | t       |     2 
  16389 | flashstart |    17409 | public             | confs                                  | 25659 | postgres |
tuple   | ExclusiveLock    | f       |     2 

How can i see the origin of the problem?
The query is very simple, it is an update query.

Thank you again!
F

________________________________________
Da: Melvin Davidson [melvin6925@gmail.com]
Inviato: sabato 2 dicembre 2017 16.22
A: Job
Cc: pgsql-general@postgresql.org
Oggetto: Re: Problems with triggers and table lock

On Fri, Dec 1, 2017 at 4:39 PM, Job <Job@colliniconsulting.it<mailto: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, 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. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]