Failure of foreign key checks

27 Feb 2024
Product Affected Versions Related Issues Fixed In
YSQL All #20864 N/A

Description

Foreign key checks may erroneously fail when writes impacting the foreign key check are done in the same statement.

Mitigation

Upgrade to a release with the fix.

Details

To enhance YugabyteDB's performance, optimization occurs in the buffering of reads and writes in the PostgreSQL layer before sending them to DocDB in a single request. This reduces the number of round trips between PostgreSQL and DocDB, resulting in improved efficiency.

  • The table contains multiple foreign keys, with one of them being self-referencing.
  • During write operations, there are typically at least two statements. One of these writes impacts the foreign key check for another write operation.

Examples

An example schema on the database is as follows:

CREATE TABLE company(k INT PRIMARY KEY)"));
CREATE TABLE employee(
      k INT PRIMARY KEY, company_fk INT, manager_fk INT,
      UNIQUE(k, company_fk),
      FOREIGN KEY (company_fk) REFERENCES company(k),
      FOREIGN KEY (manager_fk, company_fk) REFERENCES employee(company_fk, k)))
);

Query Which will provide an error:
INSERT INTO employee VALUES (1, 1, NULL), (2, 1, 1), (3, 1, 1));

Query that results in an error:

INSERT INTO employee VALUES (1, 1, NULL), (2, 1, 1), (3, 1, 1));