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
existingany 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 >
LastValidTidis 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
cleanedup (by VACUUM or REINDEX) and the end of heap is truncated to its place before the failed transaction.
Analtervative 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
toits 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.
Eveninheriting 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
consultingthe 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@skype.net>
