Re: [HACKERS] GSoC 2017: Foreign Key Arrays

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(dot)rofail(at)gmail(dot)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 <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
>>
>