Re: ARCHIVE TABLES (was: possible TODO: read-only - Mailing list pgsql-hackers

From Hannu Krosing
Subject Re: ARCHIVE TABLES (was: possible TODO: read-only
Date
Msg-id 1115114214.4852.4.camel@fuji.krosing.net
Whole thread Raw
In response to Re: ARCHIVE TABLES (was: possible TODO: read-only tables, select from indexes only.)  ("Jim C. Nasby" <decibel@decibel.org>)
List pgsql-hackers
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@skype.net>



pgsql-hackers by date:

Previous
From: Hannu Krosing
Date:
Subject: Re: Feature freeze date for 8.1
Next
From: Hannu Krosing
Date:
Subject: Re: ARCHIVE TABLES (was: possible TODO: read-only