Re: Arrays and foreign keys

Stephan Szabo wrote:

> > Ah yes. I guess that's a problem crying out for a new indexing solution.
>
> Yeah, and it would probably need some associated cost estimation stuff,
> since you'd need to know something about the element value rarity
> instead of the array value rarity if you wanted to make intelligent guesses
> as to whether the index scan is better than the sequential scan.

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

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