Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 01A10632A4C for ; Thu, 4 Feb 2010 22:11:31 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 31551-04 for ; Fri, 5 Feb 2010 02:11:12 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f220.google.com (mail-fx0-f220.google.com [209.85.220.220]) by mail.postgresql.org (Postfix) with ESMTP id 194FE632651 for ; Thu, 4 Feb 2010 22:11:19 -0400 (AST) Received: by fxm20 with SMTP id 20so3416380fxm.21 for ; Thu, 04 Feb 2010 18:11:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=fBKHnra6gsXJ/lw7lIJt8fKHbdp1+xWj/rOvHZ47AQ4=; b=tVu5sbw3O4OaPT/aPUu77LA2TtkapeQj8As54b5GxYlaO+tgtZJdC6t2EDVyORYSLf C58mD9t4a40HO1zoXCOGeZ6DCNX6mXzWWddoexDhhoXQtSnii+TR0x8ZixD1WmsAEPX6 loGLYHv3jxYQMzImLppF4oktMBkRYqpf+Fwdg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=Gw1UbHRyTGit8N0eJypQ34QbaJjBZP1FF6ZJ8RslPxT39weJKLBm8mBQluwPJS/d/z WX07Kcjg40DnbQqFU6tpeeC+aOMlIiNYzLf3HnzmHFIn8wcdZTz76yAy0DKVeU5UXwGb hPxDH3FxayijtkFMyP3doWIkU5cuTe5YEAPOE= MIME-Version: 1.0 Received: by 10.239.188.148 with SMTP id p20mr219004hbh.7.1265335877684; Thu, 04 Feb 2010 18:11:17 -0800 (PST) In-Reply-To: References: <4B6A478D.5060805@gmail.com> Date: Thu, 4 Feb 2010 21:11:17 -0500 Message-ID: <603c8f071002041811o642c04e9j570cfdc879862622@mail.gmail.com> Subject: Re: foreign key constraint lock behavour in postgresql From: Robert Haas To: david@lang.hm Cc: wangyuxiang , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.475 tagged_above=-10 required=5 tests=AWL=0.124, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/96 X-Sequence-Number: 37517 On Thu, Feb 4, 2010 at 12:40 AM, wrote: > I could be wrong in this (if so I know I'll be corrected :-) > > but Postgres doesn't need to lock anything for what you are describing. > > instead there will be multiple versions of the 'b1' row, one version will be > deleted, one version that will be kept around until the first transaction > ends, after which a vaccum pass will remove the data. Just for kicks I tried this out and the behavior is as the OP describes: after a little poking around, it sees that the INSERT grabs a share-lock on the referenced row so that a concurrent update can't modify the referenced column. It's not really clear how to get around this. If it were possible to lock individual columns within a tuple, then the particular update above could be allowed since only the name is being changed. Does anyone know what happens in Oracle if the update targets the id column rather than the name column? Another possibility is that instead of locking the row, you could recheck that the foreign key constraint still holds at commit time. But that seems like it could potentially be quite expensive. ...Robert