Re: [SQL] arrays

seems to me it is another example of sql86/92 issue: there is 
no standard regard to those object-relational feature. 
Informix  is our relative and of course the best (and
we are going to be better :-), but Orocle's array has maxim length 
and unfortunately they are the most powerful. and
everybody, at least ambitious ones,  have a snob inside (and of 
course have jargon excuses for that (I'm :-). That is the reason 
to be careful/cautious about any non-sql86/92 feature and this 
attitude is good for all oss.  
Actually, I guess, that is at least one of the reason why 
it is not at the top priority: let's make the sql2 first, sql3, 
if she will come, can wait.    

On Sat, 4 Dec 1999, Tom Lane wrote:

> "John M. Flinchbaugh" <glynis@butterfly.hjsoft.com> writes:
> > On Sat, 4 Dec 1999, Stephen Davies wrote:
> >> Arrays do have a role in life: even in a "proper relational" world.
> 
> > sorry, that is incorrect.  in a proper system, you don't run out of
> > ``slots''.  say you give each publication an array to hold 3
> > authors.  what happens when that 4th author comes along, then a
> > fifth?  you always have a +1 problem.
> 
> Actually, Postgres arrays are variable-size, so that argument loses much
> of its force.  AFAICT, even if you specify a particular size in the
> table declaration, Postgres ignores it and makes each tuple's array just
> big enough to hold whatever gets stored there.
> 
> I have used arrays in some cases where they seemed to be just the right
> thing and made the database design much cleaner than it would have been
> otherwise.  For example, in a futures-trading transaction database,
> I made the fill price and contract count be arrays, so that I could
> represent "split fill" orders (you to broker: "Buy 10 June S&P futures";
> broker to you: "OK, I got 6 at 1415.5 and 4 at 1415.6").  Normalizing
> this problem into separate transaction and price tables with an
> association table is obviously ridiculous; nor did I like the choice of
> representing a split fill as two or more separate transaction entries.
> That wastes space for duplicate storage of the other columns, and also
> means that you have to work harder to group together several database
> records as a single "logical trade".  One record per trade with the
> ability to store multiple fill prices is the right design here.
> 
> Arrays have lots of problems though.  For one, there's no clean way to
> do something like "find all the records that have FOO in any element of
> this array column".  We could invent some operators that do that kind of
> thing, but they're not there now.  Also, you can't expect to put a
> really large number of items in an array, because of the system's limit
> on overall tuple length; but that limit will go away someday (fairly
> soon I think).
> 
> There is some ancient code in the backend for storing big arrays
> out-of-line as Large Objects, which'd eliminate the tuple-length issue.
> But I think that code isn't getting compiled now, much less tested
> or used regularly.  So it's most likely broken.
> 
> The real problem with arrays in Postgres is that they need a lot of
> work put into them, and no one has stepped up to the plate to do it.
> Fixing the LOARRAY code, allowing NULL array elements, adding functions
> to do searches within arrays, etc, etc, are all things that could and
> should get done, but they're not high on the priority list of any active
> developer.  Arrays need to get adopted by somebody.
> 
>             regards, tom lane
> 
> ************
>