Re: Arrays and foreign keys

On Fri, 11 Aug 2000, Chris Bitmead wrote:

> You could probably do some kind of quick hack with regular indexes, just
> have more than one entry for each tuple when indexing arrays.

Maybe, it depends on how the code is structured.  Plus, it may mean
changes to the stuff that handles arrays as well, since you're not
indexing the data value, but the set (actually, not a set i guess since
there's nothing preventing duplicates) that's there, so {1,2}->{1,3} means
an index delete for the 2 and index insert for the 3.

> > You could kind of store the information in a secondary relation, but that
> > seems like a major point of locking contention, plus it'd either end up
> > being the reverse index (element->array of oids) or the normalized,
> > element->oid rows at which point are you better off than if you
> > normalized the original relation.
> > 
> > Does any version of SQL have meaningful arrays, and do they actually
> > specify any behavior for this?  Or for that matter, what about other
> > dbs.  What do they do with these cases...
> 
> All ODBMSes by necessity support arrays. I'm not aware of any attempt to
> index them in this way or support referential integrity. It would
> probably be a postgresql first.

Well, one of Jan's concerns was defining all of this behavior in a way
that was different from a current or reasonably likely spec (I'd guess he
was most concerned with SQL, but...).  

I think perhaps we're overreaching for the moment.  The ri stuff isn't
even completely finished for the cases that are specified by the SQL
specification, and there are still problems with what's there, so we
should probably get it working with an eye towards this possible
direction.

And whatever is done should leave arrays with the same meaning they
currently have for people who use them in other ways.  I'm almost
thinking that we want a set rather than an array here where sets have
different semantics that make more sense for this sort of behavior.
It just seems to make more sense to me that a set would be indexed
by its elements than array, since position is supposed to be meaningful
for arrays, and that set(1,2) is equal to the set(2,1) whereas the
indexes aren't.  Of course, I guess that's not much different from
the normalized table case.