chenzhenguo2012
diff --git a/‎src/backend/utils/adt/ri_triggers.c
Lines changed: 9 additions & 11 deletions b/‎src/backend/utils/adt/ri_triggers.c
Lines changed: 9 additions & 11 deletions
diff --git a/‎src/test/regress/expected/foreign_key.out
Lines changed: 22 additions & 0 deletions b/‎src/test/regress/expected/foreign_key.out
Lines changed: 22 additions & 0 deletions
diff --git a/‎src/test/regress/sql/foreign_key.sql
Lines changed: 23 additions & 0 deletions b/‎src/test/regress/sql/foreign_key.sql
Lines changed: 23 additions & 0 deletions
@@ -42,6 +42,7 @@
 #include "parser/parse_coerce.h"
 #include "parser/parse_relation.h"
 #include "miscadmin.h"
+#include "storage/bufmgr.h"
 #include "utils/acl.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
@@ -289,20 +290,17 @@ RI_FKey_check(PG_FUNCTION_ARGS)
 	 * We should not even consider checking the row if it is no longer valid,
 	 * since it was either deleted (so the deferred check should be skipped)
 	 * or updated (in which case only the latest version of the row should be
-	 * checked).  Test its liveness according to SnapshotSelf.
-	 *
-	 * NOTE: The normal coding rule is that one must acquire the buffer
-	 * content lock to call HeapTupleSatisfiesVisibility.  We can skip that
-	 * here because we know that AfterTriggerExecute just fetched the tuple
-	 * successfully, so there cannot be a VACUUM compaction in progress on the
-	 * page (either heap_fetch would have waited for the VACUUM, or the
-	 * VACUUM's LockBufferForCleanup would be waiting for us to drop pin). And
-	 * since this is a row inserted by our open transaction, no one else can
-	 * be entitled to change its xmin/xmax.
+	 * checked).  Test its liveness according to SnapshotSelf.  We need pin
+	 * and lock on the buffer to call HeapTupleSatisfiesVisibility.  Caller
+	 * should be holding pin, but not lock.
 	 */
-	Assert(new_row_buf != InvalidBuffer);
+	LockBuffer(new_row_buf, BUFFER_LOCK_SHARE);
 	if (!HeapTupleSatisfiesVisibility(new_row, SnapshotSelf, new_row_buf))
+	{
+		LockBuffer(new_row_buf, BUFFER_LOCK_UNLOCK);
 		return PointerGetDatum(NULL);
+	}
+	LockBuffer(new_row_buf, BUFFER_LOCK_UNLOCK);
 
 	/*
 	 * Get the relation descriptors of the FK and PK tables.
 
@@ -1319,3 +1319,25 @@ begin;
 (2 rows)
 
 commit;
+--
+-- Test deferred FK check on a tuple deleted by a rolled-back subtransaction
+--
+create table pktable2(f1 int primary key);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pktable2_pkey" for table "pktable2"
+create table fktable2(f1 int references pktable2 deferrable initially deferred);
+insert into pktable2 values(1);
+begin;
+insert into fktable2 values(1);
+savepoint x;
+delete from fktable2;
+rollback to x;
+commit;
+begin;
+insert into fktable2 values(2);
+savepoint x;
+delete from fktable2;
+rollback to x;
+commit; -- fail
+ERROR:  insert or update on table "fktable2" violates foreign key constraint "fktable2_f1_fkey"
+DETAIL:  Key (f1)=(2) is not present in table "pktable2".
+drop table pktable2, fktable2;
@@ -943,3 +943,26 @@ begin;
     update selfref set a = 456 where a = 123;
     select a, b from selfref;
 commit;
+
+--
+-- Test deferred FK check on a tuple deleted by a rolled-back subtransaction
+--
+create table pktable2(f1 int primary key);
+create table fktable2(f1 int references pktable2 deferrable initially deferred);
+insert into pktable2 values(1);
+
+begin;
+insert into fktable2 values(1);
+savepoint x;
+delete from fktable2;
+rollback to x;
+commit;
+
+begin;
+insert into fktable2 values(2);
+savepoint x;
+delete from fktable2;
+rollback to x;
+commit; -- fail
+
+drop table pktable2, fktable2;