Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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 <joel(at)compiler(dot)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
>