8000
We read every piece of feedback, and take your input very seriously.
To see all available qualifiers, see our documentation.
There was an error while loading. Please reload this page.
1 parent 9a743ff commit 1f897aeCopy full SHA for 1f897ae
src/backend/commands/tablecmds.c
@@ -4735,13 +4735,6 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
4735
/* Make changes-so-far visible */
4736
CommandCounterIncrement();
4737
4738
- /*
4739
- * Build and execute a CREATE CONSTRAINT TRIGGER statement for the CHECK
4740
- * action for both INSERTs and UPDATEs on the referencing table.
4741
- */
4742
- CreateFKCheckTrigger(myRel, fkconstraint, &constrobj, &trigobj, true);
4743
- CreateFKCheckTrigger(myRel, fkconstraint, &constrobj, &trigobj, false);
4744
-
4745
/*
4746
* Build and execute a CREATE CONSTRAINT TRIGGER statement for the ON
4747
* DELETE action on the referenced table.
@@ -4879,6 +4872,27 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
4879
4872
4880
4873
/* Register dependency from trigger to constraint */
4881
4874
recordDependencyOn(&trigobj, &constrobj, DEPENDENCY_INTERNAL);
4875
+
4876
+ /* Make changes-so-far visible */
4877
+ CommandCounterIncrement();
4878
+ /*
+ * Build and execute CREATE CONSTRAINT TRIGGER statements for the CHECK
+ * action for both INSERTs and UPDATEs on the referencing table.
4882
+ *
4883
+ * Note: for a self-referential FK (referencing and referenced tables are
4884
+ * the same), it is important that the ON UPDATE action fires before the
4885
+ * CHECK action, since both triggers will fire on the same row during an
4886
+ * UPDATE event; otherwise the CHECK trigger will be checking a non-final
4887
+ * state of the row. Because triggers fire in name order, we are
4888
+ * effectively relying on the OIDs of the triggers to sort correctly as
4889
+ * text. This will work except when the OID counter wraps around or adds
4890
+ * a digit, eg "99999" sorts after "100000". That is infrequent enough,
4891
+ * and the use of self-referential FKs is rare enough, that we live with
4892
+ * it for now. There will be a real fix in PG 9.2.
4893
+ */
4894
+ CreateFKCheckTrigger(myRel, fkconstraint, &constrobj, &trigobj, true);
4895
+ CreateFKCheckTrigger(myRel, fkconstraint, &constrobj, &trigobj, false);
4896
}
4897
4898
src/test/regress/expected/foreign_key.out
@@ -1276,3 +1276,35 @@ SELECT * FROM tasks;
1276
(3 rows)
1277
1278
COMMIT;
1279
+--
1280
+-- Test self-referential FK with CASCADE (bug #6268)
1281
1282
+create temp table selfref (
1283
+ a int primary key,
1284
+ b int,
1285
+ foreign key (b) references selfref (a)
1286
+ on update cascade on delete cascade
1287
+);
1288
+NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "selfref_pkey" for table "selfref"
1289
+insert into selfref (a, b)
1290
+values
1291
+ (0, 0),
1292
+ (1, 1);
1293
+begin;
1294
+ update selfref set a = 123 where a = 0;
1295
+ select a, b from selfref;
1296
+ a | b
1297
+-----+-----
1298
+ 1 | 1
1299
+ 123 | 123
1300
+(2 rows)
1301
1302
+ update selfref set a = 456 where a = 123;
1303
1304
1305
1306
1307
+ 456 | 456
1308
1309
1310
+commit;
src/test/regress/sql/foreign_key.sql
@@ -899,3 +899,25 @@ SELECT * FROM tasks;
899
DELETE FROM users WHERE id = 2;
900
SELECT * FROM tasks;
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923