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