Re: [HACKERS] GSoC 2017: Foreign Key Arrays

Hi again,

I found a similar problem with int2vector columns:

CREATE TABLE catalog_clone.pg_index AS SELECT * FROM pg_catalog.pg_index;
CREATE TABLE catalog_clone.pg_attribute AS SELECT attrelid,attnum FROM pg_catalog.pg_attribute;
ALTER TABLE catalog_clone.pg_attribute ADD UNIQUE (attrelid, attnum);
ALTER TABLE catalog_clone.pg_index ADD FOREIGN KEY (indrelid, EACH ELEMENT OF indkey) REFERENCES catalog_clone.pg_attribute (attrelid, attnum);

SELECT indexrelid,indrelid,indkey FROM catalog_clone.pg_index WHERE cardinality(indkey) > 1 LIMIT 1;
indexrelid | indrelid | indkey
------------+----------+--------
2837 | 2836 | 1 2
(1 row)

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...
^
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($2) y) OPERATOR(pg_catalog.=) (SELECT pg_catalog.count(*) FROM (SELECT 1 FROM ONLY "catalog_clone"."pg_attribute" x WHERE "attrelid" OPERATOR(pg_catalog.=) $1 AND $2 OPERATOR(pg_catalog. @>) ARRAY["attnum"] FOR KEY SHARE OF x) z)

This made me wonder if there are any more of these "vector" columns than the oidvector and int2vector.

It appears they are the only two:

SELECT DISTINCT data_type, udt_name from information_schema.columns WHERE udt_name LIKE '%vector' ORDER BY 1,2;
data_type | udt_name
-----------+------------
ARRAY | int2vector
ARRAY | oidvector
(2 rows)

/Joel