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

On Sun, 8 Sep 2002, Joel Rodrigues wrote:

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

Mostly on implementation, however there are some specification
level things as yet left unsaid dealing with arrays of arrays
and multi-column keys. I'm not sure whether
{{1,2},{3,4}} -> {2,1},{3,4} (two rows in the pk table,
one in the fk) satisfies the constraint or not. As arrays
not really, but since we're already treating an array
as a multiset here, do we do that on both sides? Also,
for multi-column foreign keys say a key like ({1,2},{3,4})
is that 2 combinations or 4? If 4 then the do all the checks
become problematic as it does a number of checks equal to all of
the array lengths (well, distinct values) multiplied together if there
were multiple arrays and doing the more intelligent check (or
deciding to do it for arrays over a certain length or something) could
lower that greatly. It's likely to be done, probably not for a few
versions as for many cases there is a workaround (using a details
table) and there are bugs or at least misfeatures to kill first.

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

For some subset, you can get the general effect using trigger
functions. You'd need to determine which actions on the tables
involved could violate and write the appropriate functions.