Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Hi Mark,

On Wed, Jan 27, 2021, at 20:34, Mark Rofail wrote:
>Here it is.
>Array-ELEMENT-foreign-key-v15.patch

Thanks.

I've tested it and notice there still seems to be a problem with int2vector?

Reading your previous comment a few messages ago,
it sounds like this was fixed, but perhaps not?

This is the comment that made me think it was fixed:

On Sun, Jan 24, 2021, at 21:46, Mark Rofail wrote:
>Hello again Joel,
>>UPDATE catalog_clone.pg_index SET indkey = '1 2 12345'::int2vector WHERE indexrelid = 2837;
>>ERROR: operator does not exist: int2vector pg_catalog.@> smallint[]
>>LINE 1: ...WHERE "attrelid" OPERATOR(pg_catalog.=) $1 AND $2 OPERATOR(p...
>I apologize for my rash response, I did not quite understand your example at first, it appears the UPDATE statement is >neither over the referencing nor the referenced columns, I understand the problem now, please disregard the previous >email. Thank you for this find, please find the fix below
>
>Changelog:
>- v14 (compatible with current master 2021-01-24, commit 0c1e8845f28bd07ad381c8b0d6701575d967b88e)

Here is a small test causing that still fails on v15:

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

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);

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)

/Joel