Re: [SQL] arrays

"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