ARCHIVE TABLES (was: possible TODO: read-only tables, select from indexes only.)

Third repeat post. Sorry if previous ones suddenly pop up too.

ARCHIVE TABLES

this is a further development ofthe "read-only tables" proposal,
WHich answers Tom's question - 'how such a tables comes to exists'
and inspired by someones (can't find the mail, sorry) suggestion
to think of them as archive tables, not R/O tables.

So ARCHIVE TABLE is is a table with the following properties

1) The ONLY operations allowed on it are APPEND (i.e. INSERT or COPY)
and SELECT.
Forbidden ops are UPDATE and DELETE. TRUNCATE should probably
allowed too.

2) data in heap is dense, each new APPEND adds a set of one or more
tuples with continuous tids, all bigger than existing any existing
tid.

no system colums are stored in each tuple, WITH OIDS is not
supported for archive tables.

3) tuple visibility is determined by tid of last tuple of last
successfully committed APPEND .

Any tuple with tid > LastValidTid is not visible.

The LastValidTid is stored for each archive table.

4) 0nly one session can APPEND to an archive table at any one time.

5) if the transaction doing the insert is rolled back, the table is
locked for further APPENDS until indexes are cleaned up (by VACUUM
or REINDEX) and the end of heap is truncated to its place before
the failed transaction.

An altervative behaviour is to do the cleanup immediately on
rollback, but even this has to lock the table until its done,
to be crash-proof.

6) Vacuum on an archive table should

- examine only tuples with tid > LastValidTid
- clean up their index entries
- modyfi last valid page and truncate table to its old length
corresponding to LastValidTid

(4, 5 & 6 are needed for 3 to work reliably in case of rollbacked
transactions.)

7) everything else (constraints, triggers, indexes, rules, statistics)
should be the same as for normal tables.

Even inheriting an ARCHIVE table from ordinary table should be
allowed.

This kind of setup allows the following features

- index-only scans for cases where all columns needed are in index.
visibility can be determined from tid without consulting the heap.

- smaller table sizes due to not storing visibility info with each
tuple

which are often desirable for BusinessIntelligence/DataWarehousing
databases and other systems with huge fact tables.

I don't think that Tom's concern about pervasiveness of TupleHeader is
unsurmountable.

I hope that the only <stupid hopeful grin> thing needing to change is
visibility checks when fetching the tuples from heap or index, from that
point on it should be possible to handle them as if the data is coming
from a view.

Also I hope that index structure does not have to change at all, only a
new access methods should be added, namely

* archive-indexscan (replaces ordinary index-scan)

* archive-index-only-scan (new)

* archive-seqscan (replaces ordinary seqscan)

and planner/executor must be teached to use these.

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