Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On Tue, Feb 2, 2021, at 13:51, Mark Rofail wrote:
>Array-ELEMENT-foreign-key-v17.patch

When working on my pit tool, I found another implicit type casts problem.

First an example to show a desired error message:

CREATE TABLE a (
a_id smallint,
PRIMARY KEY (a_id)
);

CREATE TABLE b (
b_id bigint,
a_ids text[],
PRIMARY KEY (b_id)
);

ALTER TABLE b ADD FOREIGN KEY (EACH ELEMENT OF a_ids) REFERENCES a;

The below error message is good:

ERROR: foreign key constraint "b_a_ids_fkey" cannot be implemented
DETAIL: Key column "a_ids" has element type text which does not have a default btree operator class that's compatible with class "int2_ops".

But if we instead make a_ids a bigint[], we don't get any error:

DROP TABLE b;

CREATE TABLE b (
b_id bigint,
a_ids bigint[],
PRIMARY KEY (b_id)
);

ALTER TABLE b ADD FOREIGN KEY (EACH ELEMENT OF a_ids) REFERENCES a;

No error, even though bigint[] isn't compatible with smallint.

We do get an error when trying to insert into the table:

INSERT INTO a (a_id) VALUES (1);
INSERT INTO b (b_id, a_ids) VALUES (2, ARRAY[1]);

ERROR: operator does not exist: smallint[] pg_catalog.<@ bigint[]
LINE 1: ..."."a" x WHERE ARRAY ["a_id"]::pg_catalog.anyarray OPERATOR(p...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT 1 WHERE (SELECT pg_catalog.count(DISTINCT y) FROM pg_catalog.unnest($1) y) OPERATOR(pg_catalog.=) (SELECT pg_catalog.count(*) FROM (SELECT 1 FROM ONLY "public"."a" x WHERE ARRAY ["a_id"]::pg_catalog.anyarray OPERATOR(pg_catalog.<@) $1::pg_catalog.anyarray FOR KEY SHARE OF x) z)

I wonder if we can come up with some general way to detect these
problems already at constraint creation time,
instead of having to wait for data to get the error,
similar to why compile time error are preferred over run time errors.

/Joel