Re: ARCHIVE TABLES (was: possible TODO: read-only

On E, 2005-05-02 at 15:59 -0500, Jim C. Nasby wrote:
> Out of curiosity, what would be required to allow deletes (but not
> updates)?

Deletes *are* the problem (and update is internally a delete+insert).
Allowing deletes means no more selects from indexes only as tid can't be
used for determining visibility.

> My thinking is that you'd want *some* way to be able to prune
> data. Since you won't want to store an entire XID/CID for the delete, I
> think it would be acceptable to keep a table of XID/CID values for
> deletes and just store a pointer to that table in the tuple header. This
> means you would be limited (perhaps severely) in the number of deletes
> you could issue between vacuums, but for this instance that seems
> perfectly reasonable. It might be worth using this same technique for
> inserts as well. If the only inserting into the table is from some
> nightly bulk process, you certainly don't need to store 4 (or is it 8)
> bytes of inserting transaction data with each tuple.

The idea was using archive tables in partitioned tables if you need to
prune data.

> Also, how does this allow for index scans without touching the heap?
> AFAIK when a tuple is inserted but not committed it is already in the
> index.

You can check if tid (tuple id) is bigger than last valid (committed)
tuple id in the table.

--
Hannu Krosing <hannu(at)skype(dot)net>