Re: "...integer[] references..." = error

On Friday, September 6, 2002, at 08:52 , Stephan Szabo wrote:

>
> On Fri, 6 Sep 2002, Joel Rodrigues wrote:
>
>> Thanks for the info & hints guys. Sad really that the most
>> obvious construct does not work. So, though I can get away with
>> skipping the REFERENCES bit by using a VIEW. I'd still like to
>> have some sort of referential integrity checking. I'm puzzled
>> about how to achieve this. I thought I'd do a CHECK with a
>> subquery expression, but, "Currently, CHECK expressions cannot
>> contain subselects". And it's not even on the TODO list. Foiled
>> again !
>>
>>
>> A bit of searching on Google Groups reveals that at least a few
>> people have attempted to use "...integer[] references...". Hate
>> to use the "o" word again, but it is really such an obvious
>> construct both in it's conception and (optimistic)
>> implementation.
>
> Not really. There are some performance issues and such surrounding
> it. Arrays are positional and not multisets, so you get some
> wierdness at the conversion.
>
> For example:
> I have (3,4) in an array. I update it to (4). For foreign
> key purposes, is this effectively a delete of 3 (ie no
> check required) or a delete of 4 and a change of 3->4.
> If the array has 100 elements and I remove the first one,
> do I do 99 foreign key checks or do I try to determine
> that's all that happened. What if the rest of the elements
> were randomly assorted?
>

Forgot to comment:

By "not really", you're referring to implementing the feature in
PostgreSQL, right ? Because what I referred to by "obvious" is
the need for this feature. And thinking about what you elaborate
on in your example, it seems to me that some restricted use of
such a construct (...integer[] references someID...) should be
included in the feature set. And yes I think the best and most
sensible solution would be to do in your example of a 100
element array, would be to do 99 foreign key checks. I don't see
what the problem could be, other than a minuscule performance
hit perhaps depending on the machine you're running on.

By the same token, CHECK could be allowed some restricted use of
subselects. But that item on the TODO list probably does deal
with this issue - I'll ask hackers about that.

Anyway, I'll mention this on pgsql-hackers, and shift to trying
to find a fudge for achieving some kind of referential
integerity/ constraint checking within a stock PostgreSQL 7.2.2
install, i.e. no contrib modules.

Cheers,
Joel