Hi, For Array-containselem-gin-v4.patch , one small comment: + * array_contains_elem : checks an array for a spefific element
typo: specific Cheers On Thu, Feb 4, 2021 at 4:03 PM Mark Rofail <markm.rof...@gmail.com> wrote: > Hello Joel, > > No error, even though bigint[] isn't compatible with smallint. >> > I added a check to compart the element type of the fkoperand and the type > of the pkoperand should be the same > Please try v18 attached below, you should get the following message > ``` > ERROR: foreign key constraint "fktableviolating_ftest1_fkey" cannot be > implemented > DETAIL: Specified columns have element types smallint and bigint which > are not homogenous. > ``` > > Changelog (FK arrays): > - v18 (compatible with current master 2021-02-54, commit > c34787f910585f82320f78b0afd53a6a170aa229) > * add check for operand compatibility at constraint creation > > Changelog (FK arrays Elem addon) > - v4 (compatible with FK arrays v18) > * re-add Composite Type support > > I believe we should start merging these two patches as one, due to the > Elem addon's benefits. such as adding Composite Type support. > > /Mark > > On Thu, Feb 4, 2021 at 9:00 AM Joel Jacobson <j...@compiler.org> wrote: > >> 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 >> >
