sql: INSERT with self-referencing FK check issues

[BramGruneir]

When inserting in a self-referential table, you can't insert multiple values in a single statement if they rely on a value earlier in the statement.

CREATE TABLE self (
  id INT PRIMARY KEY
 ,other_id INT REFERENCES self
);

CREATE TABLE
Time: 9.248944ms

INSERT INTO self VALUES (1,NULL), (2, 1);

pq: foreign key violation: value [1] not found in self@primary [id]

But this works.

INSERT INTO self VALUES (1,NULL);
INSERT INTO self VALUES (2, 1);

This is due to each individual insert statement using a single batch and batches can't read previous writes.
However, if we were to use a single batch per inserted element, it would drastically slow down the inserts overall.

Another, slightly different version of this problem:

CREATE TABLE bah (
  id INT NOT NULL,
  id2 INT NULL,
  CONSTRAINT "primary" PRIMARY KEY (id ASC),
  CONSTRAINT aaa FOREIGN KEY (id2) REFERENCES bah (id),
  INDEX bah_auto_index_aaa (id2 ASC),
  FAMILY "primary" (id, id2)
)

INSERT INTO bah VALUES (1,1);
pq: foreign key violation: value [1] not found in bah@primary [id]

This fails because we eagerly check the FKs instead of waiting to the end of the statement. But even if we did wait to check at the end of the statement, then we would not yet have the batch committed and the FK check would still fail.
That being said, it might be possible to elide this FK check if the values match in the mutation path.

[BramGruneir]

Oh, and to add to this, it does work on postgres.

[knz]

This may have been fixed on #23373 . testing.

[knz]

nope, not fixed.

[BramGruneir]

Updated this based on #32610 and merging that issue into this.

[knz]

Solution is described (and will be implemented by fixing) #33475.

[asubiotto]

#33475 is not closed so assuming this can be closed as well.