sql: self-referencing constraint fails

[donbowman]

Describe the problem

The following sql passes on postgresql, fails on cockroachdb (with foreign key violation). I believe it should pass since the table + constraint are satisfied at the end of the insert.

DROP TABLE IF EXISTS u1;
CREATE TABLE u1 (
	id INT8 NOT NULL,
	created_by_id INT8 NOT NULL,
	CONSTRAINT "primary" PRIMARY KEY (id)
);

ALTER TABLE u1 ADD CONSTRAINT fk_u1_45307c95a3 FOREIGN KEY (created_by_id) REFERENCES u1 (id);

insert into u1(id, created_by_id) values(1,  1);
-- pq: foreign key violation: value [1] not found in u1@primary [id] (txn=c99d4c71-d762-4c6b-8292-f9dd02c96bed)

To Reproduce

This happens on an empty cockroachdb, just run the above sql.

Expected behavior

I expected to get 1 row inserted. In postgresql, I get:

postgrse=# insert into u1(id, organization_id, created_by_id) values(1, 1,  1);
INSERT 0 1
postgres=# select * from u1;
 id | organization_id | created_by_id 
----+-----------------+---------------
  1 |               1 |             1
(1 row)

Additional data / screenshots

Environment:
CockroachDB OSS v19.1.4 (x86_64-unknown-linux-gnu, built 2019/08/06 15:34:13, go1.11.6)
ubuntu 18.04
single node cluster
client app is cockroach sql

[knz]

cc @dt @jordanlewis for triage

[donbowman]

any insight on this?
is this the same as #20041

[jordanlewis]

Hi @donbowman, yes, it's the same as the issue you linked. We were attempting to get a fix for this available in upcoming release major (19.2) but ran into trouble near the end. It won't be available until 20.1. I think in the meantime, there's no way that you can have a row that refers to itself via an FK relationship. I'm going to close this as a duplicate but feel free to keep responding here - I'll follow up with any further questions you have.

[donbowman]

OK thank you.
for anyone else searching and finding this, the application was zammad

[knz]

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