> 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.
No, you have to have two for each. One before and one after each execution.
> 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.
No again. You come upon "starting rename operation" and then either no more log records (backend abort)
or log record "rename succeeded"
orlog record "rename failed" --> transaction abort
In this scenario you can decide what to do without second guessing.
> 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.)
I have always been asking for an extension for different object files
like .dat .idx .tmp ... , but this problem is imho present with any filename
be it oid's or named files.
> 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.
Imho in such an event I rather have my db bail out (or switch to readonly
mode).
Any further modifications would give me the shivers in such an unstable
environment.
The dba then fixes the environment and restarts the postmaster.
>
> >> If *rename* is possible,are OID filenames necessary in the
> >> first place ?
>
> > no
this was only an answer to the question, not a suggestion.
> 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.
I was not arguing against oid filenames in this thread, only against
the addition of a version number in the filename.
Andreas
