Re: 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(at)gmail(dot)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