Re: Data archiving/warehousing idea

Jochem van Dieten wrote:
> On 2/1/07, Chris Dunlop wrote:
>> In maillist.postgres.dev, you wrote:
>> Rather than writing in-place, perhaps the SET ARCHIVE would
>> create a on-disk copy of the table.
>
> Just like CLUSTER does now: create an on-disk copy first and swap the
> relfilenodes of the files and flush the relcache.

IIRC, cluster currently needs to take on exclusive lock of the table, thus
preventing any concurrent selects. I assume it would be the same for
"alter table ... set archive".
For a large readonly table - the ones that "set archive" would be used for -
rewriting the whole table might easily
take a few hours, if not days. Blocking reads for such a long time might
be unacceptable in a lot of environments, severely limiting the use-cases
for "alter table ... set archive"

I think that both "cluster" and "truncate" should in theory only need to
prevent concurrent updates, not concurrent selects. AFAIK, the reason they
need to take an exclusive lock is because there is no way to let other backend
see the "old" relfilenode entry in pg_class until the cluster/truncate commits.
So I believe that this limitation would first have to be removed, before a
"alter table ... set archive" would become really usefull...

Just my 0.02 eurocents.
greetings, Florian Pflug