From shridharpolas@gmail.com Fri Nov 11 03:04:58 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 25428951979 for ; Thu, 10 Nov 2011 23:04:58 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 34714-10 for ; Fri, 11 Nov 2011 03:04:51 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-pz0-f44.google.com (mail-pz0-f44.google.com [209.85.210.44]) by mail.postgresql.org (Postfix) with ESMTP id 3DB1C951978 for ; Thu, 10 Nov 2011 23:04:50 -0400 (AST) Received: by pzk33 with SMTP id 33so7151856pzk.3 for ; Thu, 10 Nov 2011 19:04:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=from:to:subject:date:message-id:mime-version:content-type:x-mailer :thread-index:content-language:x-antivirus:x-antivirus-status; bh=SvqzuJfFXvlG4F09RjpfzWvPUldErmW+MjvThoQN1q0=; b=IhleC6zhgA9a3NOT4+nvWSNnXj27D8KKpjxkHf5RWkkwXqGNYO9tmicaQaHwwcu7Q8 IoQNXba1lOU/yfRUWNQ+ZTbf2pZJE4sY7ky8fpvwlxC4yofmmujRClki9KcktC0npSNe O7Y9Wq9nGHqlOx3w5PfzG77FmNzovhyvmzKYc= Received: by 10.68.14.105 with SMTP id o9mr19698739pbc.95.1320980690273; Thu, 10 Nov 2011 19:04:50 -0800 (PST) Received: from shreeVAIO ([117.195.97.85]) by mx.google.com with ESMTPS id wn14sm26436684pbb.5.2011.11.10.19.04.46 (version=SSLv3 cipher=OTHER); Thu, 10 Nov 2011 19:04:48 -0800 (PST) From: "Shridhar Polas" To: Subject: Deadlock like scenario Date: Fri, 11 Nov 2011 08:34:47 +0530 Message-ID: <004b01cca01e$ae297f80$0a7c7e80$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_004C_01CCA04C.C7E67670" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AcygHRAfRm4zhn85Q1GRUwhaypHgiw== Content-Language: en-us X-Antivirus: avast! (VPS 111110-1, 11/11/2011), Outbound message X-Antivirus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201111/52 X-Sequence-Number: 38646 This is a multipart message in MIME format. ------=_NextPart_000_004C_01CCA04C.C7E67670 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Hi, I am experience the problem where locks acquired by postgres are NOT getting released. The use case is something like:- . There is a table let's say "A" and the trigger is created on this table let say "A_TRIGGER". . The trigger captures the data change happens in table A into table B. . There would be a huge insert, update, delete on table A, the side effect of it table B also get updated very frequently. . There is periodic job that runs every midnight to do full vacuum and re-indexing on table B (but NOT on table A). . It has been found that the queries are hanging and never completes when there are some operation on table A. Can someone please help me out to resolve this problem. If someone need more information on this then please let me know. Thanks in advance, Shridhar ------=_NextPart_000_004C_01CCA04C.C7E67670 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Hi,

 

I am = experience the problem where locks acquired by postgres are NOT getting = released.

 

The use case is something like:-

 

·         = There is a table let’s say = “A” and the trigger is created on this table let say = “A_TRIGGER”.

·         = The trigger captures the data change = happens in table A into table B.

·         = There would be a huge insert, update, = delete on table A, the side effect of it table B also get updated very = frequently.

·         = There is periodic job that runs every = midnight to do full vacuum and re-indexing on table B (but NOT on table = A).

·         = It has been found that the queries are = hanging and never completes when there are some operation on table = A.

 

 

Can someone = please help me out to resolve this problem. If someone need more = information on this then please let me know.

 

Thanks in = advance,

Shridhar

------=_NextPart_000_004C_01CCA04C.C7E67670-- From robins.tharakan@comodo.com Fri Nov 11 04:30:27 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 85D4E147B0C6 for ; Fri, 11 Nov 2011 00:30:27 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 90941-06 for ; Fri, 11 Nov 2011 04:30:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mmmail1.mcr.colo.comodoca.net (mdfw.comodoca.net [91.209.196.68]) by mail.postgresql.org (Postfix) with ESMTP id B3B87147B0C7 for ; Fri, 11 Nov 2011 00:30:20 -0400 (AST) Received: (qmail 19487 invoked from network); 11 Nov 2011 04:30:18 -0000 Received: from ian.bd.office.comodo.net (HELO ian.brad.office.comodo.net) (192.168.0.201) by mail.colo.comodoca.net with ESMTPS (DHE-RSA-AES256-SHA encrypted); 11 Nov 2011 04:30:18 -0000 Received: (qmail 7783 invoked by uid 1000); 11 Nov 2011 04:30:18 -0000 Received: from Unknown (HELO [192.168.199.58]) (192.168.199.58) (smtp-auth username robinst, mechanism plain) by ian.brad.office.comodo.net (qpsmtpd/0.40) with (CAMELLIA256-SHA encrypted) ESMTPSA; Fri, 11 Nov 2011 04:30:18 +0000 Message-ID: <4EBCA4D7.1030305@comodo.com> Date: Fri, 11 Nov 2011 10:00:15 +0530 From: Robins Tharakan Organization: Comodo User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.23) Gecko/20110922 Thunderbird/3.1.15 MIME-Version: 1.0 To: pgsql-admin@postgresql.org CC: Shridhar Polas Subject: Re: Deadlock like scenario References: <004b01cca01e$ae297f80$0a7c7e80$@gmail.com> In-Reply-To: <004b01cca01e$ae297f80$0a7c7e80$@gmail.com> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201111/54 X-Sequence-Number: 38648 Hi Shridhar, Probably you should read a bit about how PostgreSQL locks and what operation requires which locks. More importantly what happens when these locks are not available. ISTM, your trigger operation tries a bulk operation (INSERT/DELETE/UPDATE) on table B but the nightly reindex acquires a lock because of which your bulk operations get stuck. Have you tried reindexing CONCURRENTLY ? It doesn't solve all problems as Greg rightly points out but could help in this situation. http://www.postgresql.org/docs/8.4/static/explicit-locking.html#TABLE-LOCK-COMPATIBILITY http://www.postgresql.org/docs/8.4/static/sql-reindex.html -- Robins Tharakan On 11/11/2011 08:34 AM, Shridhar Polas wrote: > Hi, > > I am experience the problem where locks acquired by postgres are NOT > getting released. > > The use case is something like:- > > ·There is a table let’s say “A” and the trigger is created on this table > let say “A_TRIGGER”. > > ·The trigger captures the data change happens in table A into table B. > > ·There would be a huge insert, update, delete on table A, the side > effect of it table B also get updated very frequently. > > ·There is periodic job that runs every midnight to do full vacuum and > re-indexing on table B (but NOT on table A). > > ·It has been found that the queries are hanging and never completes when > there are some operation on table A. > > Can someone please help me out to resolve this problem. If someone need > more information on this then please let me know. > > Thanks in advance, > > Shridhar From raghavendra.rao@enterprisedb.com Fri Nov 11 08:38:43 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 69C251EDD2F6 for ; Fri, 11 Nov 2011 04:38:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 37312-05 for ; Fri, 11 Nov 2011 08:38:37 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from exprod7og116.obsmtp.com (exprod7og116.obsmtp.com [64.18.2.219]) by mail.postgresql.org (Postfix) with ESMTP id 9936A1EDD2F5 for ; Fri, 11 Nov 2011 04:38:36 -0400 (AST) Received: from mail-gy0-f177.google.com ([209.85.160.177]) (using TLSv1) by exprod7ob116.postini.com ([64.18.6.12]) with SMTP ID DSNKTrzfDJT/ATAV8rjZ6QqpHl+mr/i7aMN7@postini.com; Fri, 11 Nov 2011 00:38:37 PST Received: by gyf1 with SMTP id 1so3573960gyf.22 for ; Fri, 11 Nov 2011 00:38:35 -0800 (PST) Received: by 10.146.171.16 with SMTP id t16mr3183870yae.29.1321000313804; Fri, 11 Nov 2011 00:31:53 -0800 (PST) MIME-Version: 1.0 Received: by 10.146.158.8 with HTTP; Fri, 11 Nov 2011 00:31:27 -0800 (PST) In-Reply-To: <4EBCA4D7.1030305@comodo.com> References: <004b01cca01e$ae297f80$0a7c7e80$@gmail.com> <4EBCA4D7.1030305@comodo.com> From: Raghavendra Date: Fri, 11 Nov 2011 14:01:27 +0530 Message-ID: Subject: Re: Deadlock like scenario To: Robins Tharakan Cc: pgsql-admin@postgresql.org, Shridhar Polas Content-Type: multipart/alternative; boundary=000e0cd4c45273d80804b1715a21 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201111/56 X-Sequence-Number: 38650 --000e0cd4c45273d80804b1715a21 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable > The use case is something like:- >> >> =B7There is a table let=92s say =93A=94 and the trigger is created on th= is table >> let say =93A_TRIGGER=94. >> >> =B7The trigger captures the data change happens in table A into table B. >> >> =B7There would be a huge insert, update, delete on table A, the side >> effect of it table B also get updated very frequently. >> >> In my recent observation I seen deadlocks with ShareLock, when any trigger doing with INSERT/UPDATE/DELETE. As Robins Tharakan said its also with any blocking operation like REINDEX. If your logs looking like Eg:- 2011-07-09 04:10:44 ETC/GMT ERROR: deadlock detected 2011-07-09 04:10:44 ETC/GMT DETAIL: Process 22986 waits for ShareLock on transaction 939; blocked by process 22959. Then I say, you need to give a try by testing Lock on Parent Table (i.e. in your case A) by *SHARE UPDATE EXCLUSIVE MODE *in trigger calling function. * * *Eg:-* CREATE OR REPLACE FUNCTION CHILD_TEST() RETURNS TRIGGER AS $$ BEGIN * LOCK TABLE PARENT IN SHARE UPDATE EXCLUSIVE MODE;* UPDATE PARENT SET A=3DNEW.A; RETURN NEW; END; $$ LANGUAGE PLPGSQL; Here *SHARE UPDATE EXCLUSIVE MODE *Works as a Select *For update* clause. * * http://www.postgresql.org/docs/9.0/static/sql-lock.html I believe you should give a try test on this. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ --000e0cd4c45273d80804b1715a21 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable
=A0
The use case is something like:-

=B7There is a table let=92s say =93A=94 and the trigger is created on this = table
let say =93A_TRIGGER=94.

=B7The trigger captures the data change happens in table A into table B.
=B7There would be a huge insert, update, delete on table A, the side
effect of it table B also get updated very frequently.

=

In my recent observation I see= n deadlocks with ShareLock, when any trigger doing with INSERT/UPDATE/DELET= E.=A0
As Robins Tharakan said its also with any blocking operation like REIN= DEX.

If your logs looking like
Eg:-=A02011-07-09 04:10:44 ETC/GMT ERROR:=A0 deadlock detected
2011-07-09 04:10:44 ETC/GMT DETAIL:=A0 Process 22986 waits for ShareL= ock on transaction 939; blocked by process 22959.=A0
=A0
Then I say, you need to give a try by testing Lock on Pa= rent Table (i.e. in your case A) =A0by=A0SHARE UPDATE EXCLUSIVE MODE in trigger calling function.

Eg:-
CREATE OR = REPLACE FUNCTION CHILD_TEST() RETURNS TRIGGER
AS
$$
=A0 BEGIN=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
=A0 LOCK TABLE PARENT I= N SHARE UPDATE EXCLUSIVE MODE;

=A0 UPDATE PARENT SET A=3DNEW.A;
=A0 RETURN NEW;=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0
=A0 END;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
$$
LANGUAGE PLPGSQL;

Her= e=A0SHARE UPDATE EXCLU= SIVE MODE=A0Works as a Select=A0For update=A0clause.


I believe you should give a try test on this= .

---
Regards,
Raghavendra
EnterpriseDB Corporation

--000e0cd4c45273d80804b1715a21--