Re: constrains of array

> Hi.
>
> I use version from cvs.
>
> I want to have array of referencies to another table, so I do:
> cms=# create table a (a int primary key); create table b (b int[]
> references a);
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for
> table 'a'
> CREATE
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
>
> But now I can't add anything to table b:
> cms=# insert into a values (1);
> INSERT 52069 1
> cms=# insert into b values ('{1}');
> ERROR: Unable to identify an operator '=' for types 'int4' and '_int4'
> You will have to retype this query using an explicit cast
>
> I expect either of following:
> 1) REFERENCIES from array to scalar should create correct trigger (check
> every entity of array)
Actually, no. Technically, it should be seeing if the exact same array is
on the other side. AFAIK, There is no definition of the RI constraint
in the spec that works that way. We've talked about implementing such
a beast as an extension, but there are some issues about indexing that
we need to look at before we can do that in general.

> 2) It should be error in *creation* of table if there is no comparasion
> operator for constrain check
Possibly, although it currently doesn't to allow you to add the operator
after you do the references. The benefits of that might be outweighed by
the problems if you don't add the operator.

> 3) Error in insert should say something about trigger, otherwise user have
> to guess what is wrong with his query.
Definately true, but unfortunately i'm not sure if it's possible in the
current implementation (it does another query inside the trigger, and
that's what's erroring -- I don't think it knows it's in the trigger at
that time).