R: Problems with triggers and table lock - Mailing list pgsql-general

From Job
Subject R: Problems with triggers and table lock
Date
Msg-id 88EF58F000EC4B4684700C2AA3A73D7A08180ABD2135@W2008DC01.ColliniConsulting.lan
Whole thread Raw
In response to Re: Problems with triggers and table lock  (Melvin Davidson <melvin6925@gmail.com>)
Responses Re: Problems with triggers and table lock
List pgsql-general
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]


pgsql-general by date:

Previous
From: Bharanee Rathna
Date:
Subject: Re: ISO8601 vs POSIX offset clarification
Next
From: Martin Moore
Date:
Subject: Replication causing publisher node to use excessive cpu over time