Thread: Problems with triggers and table lock

Problems with triggers and table lock

From
Job
Date:
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

Re: Problems with triggers and table lock

From
Alban Hertroys
Date:
> 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.



Re: Problems with triggers and table lock

From
Melvin Davidson
Date:


On Fri, Dec 1, 2017 at 4:39 PM, 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, 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.

Attachment

R: Problems with triggers and table lock

From
Job
Date:
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]


Re: Problems with triggers and table lock

From
Melvin Davidson
Date:
On Mon, Dec 4, 2017 at 6:57 AM, Job wrote: > 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>> 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] > >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? This is telling you the process id's (pid) 5646 & 25659 are the ones in conflict. So you have to investigate which user(s) are attached to pid's 5646 & 25659. It is always advisable to state your O/S, but since you have not, it is up to you to determine the appropriate procedure to find which user is associated with those pid's. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.