Re: Problems with triggers and table lock

On Mon, Dec 4, 2017 at 6:57 AM, Job <Job(at)colliniconsulting(dot)it> 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(at)gmail(dot)com]
> Inviato: sabato 2 dicembre 2017 16.22
> A: Job
> Cc: pgsql-general(at)postgresql(dot)org
> Oggetto: Re: Problems with triggers and table lock
>
> On Fri, Dec 1, 2017 at 4:39 PM, Job <Job(at)colliniconsulting(dot)it<mailto:
> 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?
>
> 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.