Thread

1. constrains of array

   Alexander Klimov <ask@wisdom.weizmann.ac.il> — 2000-12-07T10:42:16Z

   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)
    or
   2) It should be error in *creation* of table if there is no comparasion
   operator for constrain check
    or
   3) Error in insert should say something about trigger, otherwise user have
   to guess what is wrong with his query. 
   
   Regards,
   ASK
   
   
   

2. Re: constrains of array

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-12-12T18:20:53Z

   
   > 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).
   
   
   

3. Re: constrains of array

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-12T19:04:32Z

   Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
   >> 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.
   
   I can't see any good reason not to require the operator to pre-exist.
   In fact, there's a good argument that we should require the two columns
   to have the exact same datatype.  Otherwise, equality may be a pretty
   fuzzy concept.  Think about varchar vs bpchar comparison, for example
   --- shall we consider trailing blanks significant?  Which column will
   drive the choice?
   
   In any case, it's certainly a bad idea that the system accepted an
   FK constraint relating int[] to int.
   
   			regards, tom lane
   
   

4. Re: constrains of array

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-12-12T19:40:35Z

   On Tue, 12 Dec 2000, Tom Lane wrote:
   
   > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
   > >> 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.
   > 
   > I can't see any good reason not to require the operator to pre-exist.
   
   The only case I could see would be if there was some case where you had
   equality operators that needed to be defined after the table that had
   the references constraint (not sure if that could ever happen).  You
   could use alter table in these cases though.
   
   > In fact, there's a good argument that we should require the two columns
   > to have the exact same datatype.  Otherwise, equality may be a pretty
   > fuzzy concept.  Think about varchar vs bpchar comparison, for example
   > --- shall we consider trailing blanks significant?  Which column will
   > drive the choice?
   I think the spec only requires them to be comparable I believe (I'd
   assume that the match predicate rules would apply), so would an equality
   operator be sufficient to tell that?
   
   
   

5. Re: constrains of array

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-12T20:44:09Z

   Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
   >> In fact, there's a good argument that we should require the two columns
   >> to have the exact same datatype.
   
   > I think the spec only requires them to be comparable
   
   Oh, in that case never mind ... but I'd still favor checking for
   existence of the comparison operator right away.
   
   			regards, tom lane
   
   

6. Re: constrains of array

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-12-12T21:06:04Z

   On Tue, 12 Dec 2000, Tom Lane wrote:
   
   > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
   > >> In fact, there's a good argument that we should require the two columns
   > >> to have the exact same datatype.
   > 
   > > I think the spec only requires them to be comparable
   > 
   > Oh, in that case never mind ... but I'd still favor checking for
   > existence of the comparison operator right away.
   
   Okay, will do. :)