From tfo@sitening.com Fri Jun 5 13:37:11 2026 X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 18875528DE for ; Thu, 21 Jul 2005 16:47:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57208-01 for ; Thu, 21 Jul 2005 19:47:46 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id 9ED21528B3 for ; Thu, 21 Jul 2005 16:47:44 -0300 (ADT) Received: (qmail 19519 invoked from network); 21 Jul 2005 19:47:44 -0000 Received: from pcp0012204803pcs.blairblvd.tn.nash.comcast.net (HELO ?10.0.1.3?) (69.245.49.69) by 0 with SMTP; 21 Jul 2005 19:47:44 -0000 Mime-Version: 1.0 (Apple Message framework v733) To: pgsql-docs@postgresql.org Message-Id: <7B27AD9D-67FC-47BE-9956-B4066E6B9C53@sitening.com> Content-Type: multipart/alternative; boundary=Apple-Mail-33--213343395 From: "Thomas F. O'Connell" Subject: ROW SHARE/SELECT ... FOR UPDATE + foreign keys Date: Thu, 21 Jul 2005 14:47:40 -0500 X-Mailer: Apple Mail (2.733) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.035 tagged_above=0 required=5 tests=AWL, HTML_60_70, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200507/18 X-Sequence-Number: 3144 --Apple-Mail-33--213343395 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed What would people think of adding a note to the ROW SHARE section of =20 Table-Level Locks remarking on the fact that referential integrity =20 checks expressed through foreign keys do SELECT ... FOR UPDATES. http://www.postgresql.org/docs/8.0/static/explicit-=20 locking.html#LOCKING-TABLES It was somewhat challenging today to try to track down a pervasive =20 locking issue related to a table not directly referenced in a stored =20 procedure but that was referenced by a key in a table that was being =20 updated. I'm not sure how this changes with the new shared row locking =20 implementation in 8.1... -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i=99 http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 --Apple-Mail-33--213343395 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=WINDOWS-1252

What would people think of = adding a note to the ROW SHARE section of Table-Level Locks remarking on = the fact that referential integrity checks expressed through foreign = keys do SELECT ... FOR UPDATES.

http://www.postgresql.org/docs/8.0/static/explicit-locking.h= tml#LOCKING-TABLES

It was somewhat challenging = today to try to track down a pervasive locking issue related to a table = not directly referenced in a stored procedure but that was referenced by = a key in a table that was being updated.

I'm not sure how this = changes with the new shared row locking implementation in = 8.1...

Thomas F. O'Connell

Co-Founder, Information Architect

Sitening, LLC

Strategic Open Source: Open Your i=99

http://www.sitening.com/

110 30th Avenue North, Suite = 6

Nashville, TN 37203-6320

615-260-0005

= --Apple-Mail-33--213343395-- From pgman@candle.pha.pa.us Fri Jun 5 13:37:11 2026 X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DB89A52A27 for ; Fri, 22 Jul 2005 02:13:31 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68596-10 for ; Fri, 22 Jul 2005 05:13:21 +0000 (GMT) Received: from candle.pha.pa.us (candle.pha.pa.us [64.139.89.126]) by svr1.postgresql.org (Postfix) with ESMTP id DBCC6529F5 for ; Fri, 22 Jul 2005 02:13:20 -0300 (ADT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id j6M5DM209271; Fri, 22 Jul 2005 01:13:22 -0400 (EDT) From: Bruce Momjian Message-Id: <200507220513.j6M5DM209271@candle.pha.pa.us> Subject: Re: ROW SHARE/SELECT ... FOR UPDATE + foreign keys In-Reply-To: <7B27AD9D-67FC-47BE-9956-B4066E6B9C53@sitening.com> To: "Thomas F. O'Connell" Date: Fri, 22 Jul 2005 01:13:22 -0400 (EDT) Cc: pgsql-docs@postgresql.org X-Mailer: ELM [version 2.4ME+ PL121 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/22 X-Sequence-Number: 3148 Thomas F. O'Connell wrote: > What would people think of adding a note to the ROW SHARE section of > Table-Level Locks remarking on the fact that referential integrity > checks expressed through foreign keys do SELECT ... FOR UPDATES. > > http://www.postgresql.org/docs/8.0/static/explicit- > locking.html#LOCKING-TABLES > > It was somewhat challenging today to try to track down a pervasive > locking issue related to a table not directly referenced in a stored > procedure but that was referenced by a key in a table that was being > updated. > > I'm not sure how this changes with the new shared row locking > implementation in 8.1... > 8.1 will use _shared_ row locks for such cases. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073