Re: AW: Vacuum only with 20% old tuples

Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at> writes:
>>>> It does not postpone anything. WAL only logs what it does:
>>>>
>>>> 1. log i now start to rename file
>>>> 2. rename file
>>
>> How do we log *rename* ?

> No, step 2 was meant to do the actual rename.
> You log before and after the actual rename.

The trouble with depending on WAL to handle undo of external operations
(ie, filesystem calls as opposed to DB record updates) is that you don't
know all the facts if you have to recover/undo the operation. Consider
the following not-too-implausible example:

begin transaction;
alter table foo rename to bar;
create table foo ...;
delete table bar;
<crash>

If we try to implement this by log entries, we will have log entries
for rename, create, delete. We scan the log and come upon the rename.
Hmm, there's a file foo and no file bar ... looks like the rename didn't
get done, so do it. Ooops.

Related nasty scenarios arise when there's a file there of the right
name but the wrong contents, created by non-database operations.
(Consider the risks involved with a table named "core", for example.)

An even more serious problem is that you can't guarantee to be able
to either repeat or undo an external operation, because there are
factors not under your control (kernel permission checks, existing
files getting in the way, etc). Sure, failures like that are not
very likely, but that'll be cold comfort to someone whose database
gets corrupted because a file rename needs to be undone and can't be.

>> If *rename* is possible,are OID filenames necessary in the
>> first place ?

> no

I think that OID filenames will be a far more reliable solution to
the alter-table-rename problem than anything that involves trying
to actually rename the files. There are other benefits too, like
not being tied to filesystem-based naming restrictions for tables.

regards, tom lane