When enqueueing after-row triggers for updates of a table with a foreign

key, compare the new and old row versions. If the foreign key column has
not changed, we needn't enqueue the trigger, since the update cannot
violate the foreign key. This optimization was previously applied in the
RI trigger function, but it is more efficient to avoid firing the trigger
altogether. Per recent discussion on pgsql-hackers.

Also add a regression test for some unintuitive foreign key behavior, and
refactor some code that deals with the OIDs of the various RI trigger
functions.

2 files changed, 78 insertions, 0 deletions

diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index ae80b33c6f7..e3ea729e6fa 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1061,6 +1061,8 @@ INSERT INTO fktable VALUES (100, 200);
 COMMIT;
 ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
 DETAIL:  Key (fk)=(200) is not present in table "pktable".
+DROP TABLE pktable, fktable CASCADE;
+NOTICE:  drop cascades to constraint fktable_fk_fkey on table fktable
 -- test notice about expensive referential integrity checks,
 -- where the index cannot be used because of type incompatibilities.
 CREATE TEMP TABLE pktable (
@@ -1128,3 +1130,45 @@ WARNING:  foreign key constraint "fk_241_132" will require costly sequential sca
 DETAIL:  Key columns "x2" and "id1" are of different types: character varying and integer.
 WARNING:  foreign key constraint "fk_241_132" will require costly sequential scans
 DETAIL:  Key columns "x4" and "id3" are of different types: text and real.
+DROP TABLE pktable, fktable CASCADE;
+NOTICE:  drop cascades to constraint fk_241_132 on table fktable
+NOTICE:  drop cascades to constraint fk_123_231 on table fktable
+NOTICE:  drop cascades to constraint fk_253_213 on table fktable
+NOTICE:  drop cascades to constraint fk_213_213 on table fktable
+NOTICE:  drop cascades to constraint fk_123_123 on table fktable
+NOTICE:  drop cascades to constraint fk_5_1 on table fktable
+NOTICE:  drop cascades to constraint fk_3_1 on table fktable
+NOTICE:  drop cascades to constraint fk_2_1 on table fktable
+NOTICE:  drop cascades to constraint fktable_x1_fkey on table fktable
+NOTICE:  drop cascades to constraint fk_4_2 on table fktable
+NOTICE:  drop cascades to constraint fk_1_2 on table fktable
+NOTICE:  drop cascades to constraint fktable_x2_fkey on table fktable
+NOTICE:  drop cascades to constraint fk_1_3 on table fktable
+NOTICE:  drop cascades to constraint fk_2_3 on table fktable
+NOTICE:  drop cascades to constraint fktable_x3_fkey on table fktable
+-- test a tricky case: we can elide firing the FK check trigger during
+-- an UPDATE if the UPDATE did not change the foreign key
+-- field. However, we can't do this if our transaction was the one that
+-- created the updated row and the trigger is deferred, since our UPDATE
+-- will have invalidated the original newly-inserted tuple, and therefore
+-- cause the on-INSERT RI trigger not to be fired.
+CREATE TEMP TABLE pktable (
+    id int primary key,
+    other int
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable"
+CREATE TEMP TABLE fktable (
+    id int primary key,
+    fk int references pktable deferrable initially deferred
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fktable_pkey" for table "fktable"
+INSERT INTO pktable VALUES (5, 10);
+BEGIN;
+-- doesn't match PK, but no error yet
+INSERT INTO fktable VALUES (0, 20);
+-- don't change FK
+UPDATE fktable SET id = id + 1;
+-- should catch error from initial INSERT
+COMMIT;
+ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
+DETAIL:  Key (fk)=(20) is not present in table "pktable".
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index ad5865683fb..5ab15c1b39b 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -705,6 +705,8 @@ INSERT INTO fktable VALUES (100, 200);
 -- error here on commit
 COMMIT;
 
+DROP TABLE pktable, fktable CASCADE;
+
 -- test notice about expensive referential integrity checks,
 -- where the index cannot be used because of type incompatibilities.
 
@@ -774,3 +776,35 @@ FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1);
 
 ALTER TABLE fktable ADD CONSTRAINT fk_241_132
 FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2);
+
+DROP TABLE pktable, fktable CASCADE;
+
+-- test a tricky case: we can elide firing the FK check trigger during
+-- an UPDATE if the UPDATE did not change the foreign key
+-- field. However, we can't do this if our transaction was the one that
+-- created the updated row and the trigger is deferred, since our UPDATE
+-- will have invalidated the original newly-inserted tuple, and therefore
+-- cause the on-INSERT RI trigger not to be fired.
+
+CREATE TEMP TABLE pktable (
+    id int primary key,
+    other int
+);
+
+CREATE TEMP TABLE fktable (
+    id int primary key,
+    fk int references pktable deferrable initially deferred
+);
+
+INSERT INTO pktable VALUES (5, 10);
+
+BEGIN;
+
+-- doesn't match PK, but no error yet
+INSERT INTO fktable VALUES (0, 20);
+
+-- don't change FK
+UPDATE fktable SET id = id + 1;
+
+-- should catch error from initial INSERT
+COMMIT;