Re: [HACKERS] LONG

> Bruce Momjian wrote:
> 
> > Should we use large objects for this, and beef them up.  Seems that
> > would be a good way.  I have considered putting them in a hash
> > bucket/directory tree for faster access to lots of large objects.
> >
> > There is a lot to say about storing long tuples outside the tables
> > because long tuples fill cache buffers and make short fields longer to
> > access.
> 
>     I  thought  to  use  a  regular table. Of course, it will eat
>     buffers, but managing external files or  even  large  objects
>     for  it  IMHO  isn't  that  simple,  if  you take transaction
>     commit/abort and MVCC problematic into account too. And  IMHO
>     this  is  something  that must be covered, because I meant to
>     create a DATATYPE that can be used as a replacement for  TEXT
>     if that's too small, so it must behave as a regular datatype,
>     without any restrictions WRT beeing able to rollback etc.


OK, I have thought about your idea, and I like it very much.  In fact,
it borders on genius.

Our/my original idea was to chain tuple in the main table.  That has
some disadvantages:
More complex tuple handling of chained tuplesRequires more tuple storage overhead for housekeeping of chaining
dataSequentialscan of table has to read those large fieldsVacuum has to keep the tuples chained as they are moved
 
Your system would be:
CREATE TABLE pg_long (    refoid    OID,    attno    int2,    line    int4,    attdata    VARCHAR(8000);
CREATE INDEX pg_long_idx ON pg_long (refoid, attno, line);

You keep the long data out of the table.  When updating the tuple, you
mark the pg_long tuples as superceeded with the transaction id, and just
keep going.   No need to do anything special.  Vacuum will remove
superceeded tuples automatically while processing pg_long if the
transaction was committed.

The pg_long_idx index will allow rapid access to tuple long data.

This approach seems better than tuple chaining because it uses our
existing code more efficiently.  You keep long data out of the main
table, and allow use of existing tools to access the long data.  

In fact, you may decide to just extent varchar() and text to allow use
of long tuples.  Set the varlena VARLEN field to some special value like
-1, and when you see that, you go to pg_long to get the data.  Seems
very easy.  You could get fancy and keep data in the table in most
cases, but if the tuple length exceeds 8k, go to all the varlena fields
and start moving data into pg_long.  That way, a table with three 4k
columns could be stored without the user even knowing pg_long is
involved, but for shorter tuples, they are stored in the main table.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026