[YSQL] Deferred FK trigger performs read at wrong time

[d-uspenskiy]

Jira Link: DB-14665

Description

The following unit test fails in case the FLAGS_TEST_ysql_ignore_add_fk_reference flag is set to true

TEST_F_EX(PgFKeyTest, DeferredConstraintReadAtTxnEnd, PgFKeyTestNoFKCache) {
  auto conn = ASSERT_RESULT(Connect());
  ASSERT_OK(conn.Execute("CREATE TABLE pk_t(k INT, PRIMARY KEY (k ASC))"));
  ASSERT_OK(conn.Execute(
      "CREATE TABLE fk_t(k INT, pk_1 INT REFERENCES pk_t(k), "
      "pk_2 INT REFERENCES pk_t(k) DEFERRABLE INITIALLY DEFERRED, PRIMARY KEY (k ASC))"));
  ASSERT_OK(conn.Execute("INSERT INTO pk_t VALUES (1)"));
  ASSERT_OK(conn.StartTransaction(IsolationLevel::SNAPSHOT_ISOLATION));
  ASSERT_OK(conn.Execute("INSERT INTO fk_t VALUES(1, 1, 2)"));
  ASSERT_OK(conn.Execute("INSERT INTO pk_t VALUES(2)"));
  ASSERT_OK(conn.CommitTransaction());
}

This behavior may lead to undesired transaction conflicts.

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.