From aa@devshock.com Wed Oct 19 15:51:20 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 20D2BB5DBFC for ; Wed, 19 Oct 2011 12:51:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 76902-01 for ; Wed, 19 Oct 2011 15:51:13 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com [209.85.213.174]) by mail.postgresql.org (Postfix) with ESMTP id 6688FB5DBCE for ; Wed, 19 Oct 2011 12:51:13 -0300 (ADT) Received: by yxp4 with SMTP id 4so1821672yxp.19 for ; Wed, 19 Oct 2011 08:51:12 -0700 (PDT) Received: by 10.150.212.15 with SMTP id k15mr6856240ybg.56.1319039472600; Wed, 19 Oct 2011 08:51:12 -0700 (PDT) Received: from AnibalVAIO ([200.85.32.68]) by mx.google.com with ESMTPS id z6sm16874742anf.22.2011.10.19.08.51.09 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 19 Oct 2011 08:51:11 -0700 (PDT) From: "Anibal David Acosta" To: Subject: does update of column with no relation imply a relation check of other column? Date: Wed, 19 Oct 2011 12:51:08 -0300 Message-ID: <00ce01cc8e76$ed533480$c7f99d80$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00CF_01CC8E5D.C8070DF0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AcyOdo04kHTn0ZXDRJi34uTobZ2DLA== Content-Language: es-py 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: 201110/165 X-Sequence-Number: 45387 This is a multipart message in MIME format. ------=_NextPart_000_00CF_01CC8E5D.C8070DF0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit For example: Table A -id (PK) -name Table B -table_a_id (PK, FK) -address When I do an insert on table B, the database check if value for column "table_a_id" exists in table A But, if I do an update of column "address" of table B, does the database check again? My question is due to the nature of and update in postgres, that basically is a new version "insert". Thanks ------=_NextPart_000_00CF_01CC8E5D.C8070DF0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

For = example:

Table A

-id (PK)

-name

 

Table = B

-table_a_id (PK, = FK)

-address

 

When I do an = insert on table B, the database check if value for column = “table_a_id” exists in table A

But, if I do an update of column “address” = of table B, does the database check again?

 

My question = is due to the nature of and update in postgres, that basically is a new = version “insert”.

 

Thanks

------=_NextPart_000_00CF_01CC8E5D.C8070DF0-- From gryzman@gmail.com Wed Oct 19 16:43:10 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id CC607B5DC1E for ; Wed, 19 Oct 2011 13:43:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 85893-10 for ; Wed, 19 Oct 2011 16:43:04 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-ey0-f174.google.com (mail-ey0-f174.google.com [209.85.215.174]) by mail.postgresql.org (Postfix) with ESMTP id 0A38FB5DC17 for ; Wed, 19 Oct 2011 13:43:03 -0300 (ADT) Received: by eye27 with SMTP id 27so1808073eye.19 for ; Wed, 19 Oct 2011 09:43:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=subject:mime-version:content-type:from:in-reply-to:date:cc :message-id:references:to:x-mailer; bh=8/aTrj7Q5RHlRsHuISqIF2dvEVSsOVDbi9kbwcEyteU=; b=eoAIcNRH5oJE5shpDrFL0dBKPzjFL3hv9fRLCcYoV2k+vFFfiCtbZuhUAblJ4rnQHp 6gPFSQsYOFoupTlzvumMrfCXfdkrSGqusN4A27TtaJV7khSGTRIHCHmeh0vsMEKJT1O6 T2F+9mPNoX9I6fC6ET/P1tEdAZoOvxZuDR5Yc= Received: by 10.14.4.214 with SMTP id 62mr991531eej.216.1319042582458; Wed, 19 Oct 2011 09:43:02 -0700 (PDT) Received: from [192.168.103.116] (manaslu.utelisys.net. [217.117.239.6]) by mx.google.com with ESMTPS id f16sm17161161eec.8.2011.10.19.09.43.00 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 19 Oct 2011 09:43:01 -0700 (PDT) Subject: Re: does update of column with no relation imply a relation check of other column? Mime-Version: 1.0 (Apple Message framework v1251.1) Content-Type: multipart/alternative; boundary="Apple-Mail=_65CE61ED-E8C9-4481-8343-6C9CB6A4A3CA" From: Greg Jaskiewicz In-Reply-To: <00ce01cc8e76$ed533480$c7f99d80$@devshock.com> Date: Wed, 19 Oct 2011 18:42:59 +0200 Cc: Message-Id: References: <00ce01cc8e76$ed533480$c7f99d80$@devshock.com> To: Anibal David Acosta X-Mailer: Apple Mail (2.1251.1) 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: 201110/166 X-Sequence-Number: 45388 --Apple-Mail=_65CE61ED-E8C9-4481-8343-6C9CB6A4A3CA Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 On 19 Oct 2011, at 17:51, Anibal David Acosta wrote: > For example: > Table A > -id (PK) > -name > =20 > Table B > -table_a_id (PK, FK) > -address > =20 > When I do an insert on table B, the database check if value for column = =93table_a_id=94 exists in table A > But, if I do an update of column =93address=94 of table B, does the = database check again? > =20 > My question is due to the nature of and update in postgres, that = basically is a new version =93insert=94. In short - I believe it does. No reason for it not to.=20 --Apple-Mail=_65CE61ED-E8C9-4481-8343-6C9CB6A4A3CA Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252
On 19 Oct 2011, at 17:51, Anibal = David Acosta wrote:

For = example:
Table A
-id (PK)
-name
 
Table B
-table_a_id (PK, FK)
-address
 
When I do an insert on table B, the database check if = value for column =93table_a_id=94 exists in table A
But, if I do an update of column =93address=94 of table B, = does the database check again?
My question is due to the nature of = and update in postgres, that basically is a new version = =93insert=94.
So it seems that, when the fk field was unchanged, nothing was done that required accessing table a; otherwise, the access exclusive lock held by the other session would have blocked it. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company From tgl@sss.pgh.pa.us Mon Oct 31 19:55:45 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 44117B5DC4F for ; Mon, 31 Oct 2011 16:55:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 25727-02-5 for ; Mon, 31 Oct 2011 19:55:38 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id EC5A7B5DC52 for ; Mon, 31 Oct 2011 16:55:37 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p9VJtZnO007565; Mon, 31 Oct 2011 15:55:35 -0400 (EDT) To: Robert Haas cc: Greg Jaskiewicz , Anibal David Acosta , pgsql-performance@postgresql.org Subject: Re: does update of column with no relation imply a relation check of other column? In-reply-to: References: <00ce01cc8e76$ed533480$c7f99d80$@devshock.com> Comments: In-reply-to Robert Haas message dated "Mon, 31 Oct 2011 14:19:18 -0400" Date: Mon, 31 Oct 2011 15:55:35 -0400 Message-ID: <7564.1320090935@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-3.101 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-1.201 X-Spam-Level: X-Archive-Number: 201110/289 X-Sequence-Number: 45511 Robert Haas writes: > On Wed, Oct 19, 2011 at 12:42 PM, Greg Jaskiewicz wrote: >> When I do an insert on table B, the database check if value for column >> “table_a_id” exists in table A >> But, if I do an update of column “address” of table B, does the database >> check again? > I just tested this, and it seems not. It will not, unless you update the same row more than once in a single transaction. If you do that, it no longer has enough information to be sure the referencing value hasn't changed in that transaction, so it will do a check. regards, tom lane