Re: [HACKERS] GSoC 2017: Foreign Key Arrays - Mailing list pgsql-hackers

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@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