Thread: does update of column with no relation imply a relation check of other column?

On Wed, Oct 19, 2011 at 12:42 PM, Greg Jaskiewicz <gryzman@gmail.com> 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
> “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”.
>
> In short - I believe it does. No reason for it not to.

I just tested this, and it seems not.

rhaas=# create table a (id serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "a_id_seq" for
serial column "a.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"a_pkey" for table "a"
CREATE TABLE
rhaas=# create table b (table_a_id integer primary key references a
(id), address text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"b_pkey" for table "b"
CREATE TABLE
rhaas=# insert into a DEFAULT VALUES ;
INSERT 0 1
rhaas=# insert into b values (1);
INSERT 0 1

Then, in another session:

rhaas=# begin;
BEGIN
rhaas=# lock a;
LOCK TABLE

Back to the first session:

rhaas=# update b set address = 'cow';
UPDATE 1
rhaas=# select * from b;
 table_a_id | address
------------+---------
          1 | cow
(1 row)

rhaas=# update b set table_a_id = table_a_id + 1;
<blocks>

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Oct 19, 2011 at 12:42 PM, Greg Jaskiewicz <gryzman@gmail.com> 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