Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Hi Mark,

On Wed, Jan 27, 2021, at 22:36, Mark Rofail wrote:
> Vectors as refrencing columns are not supported and out of scope of this patch. Try to use arrays.

OK, not a biggie, but I think the user at least should get an error message
immediately when trying to add the foreign key on an incompatible column,
just like we would get if e.g. trying to add a fk on numeric[] -> smallint
or some other incompatible combination.

The first error message looks good:

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

CREATE TABLE b (
b_id integer NOT NULL,
a_ids numeric[] NOT NULL,
PRIMARY KEY (b_id)
);

joel=# ALTER TABLE b ADD FOREIGN KEY (EACH ELEMENT OF a_ids) REFERENCES a(a_id);
ERROR: foreign key constraint "b_a_ids_fkey" cannot be implemented
DETAIL: Key column "a_ids" has element type numeric which does not have a default btree operator class that's compatible with class "int2_ops".

But you don't get any error message if a_ids is instead int2vector:

CREATE TABLE b (
b_id integer NOT NULL,
a_ids int2vector NOT NULL,
PRIMARY KEY (b_id)
);

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

It's silently added without any error.

The error first comes when you try to insert data:

INSERT INTO a (a_id) VALUES (1);
INSERT INTO a (a_id) VALUES (2);
INSERT INTO b (b_id, a_ids) VALUES (3, '1 2'::int2vector);

ERROR: operator does not exist: smallint[] pg_catalog.<@ int2vector
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)

This means, as a user, I might not be aware of the vector restriction when adding the foreign keys
for my existing schema, and think everything is fine, and not realize there is a problem until
new data arrives.

/Joel