Re: Deleting a table file does not raise an error when the table is touched afterwards, why?

Daniel Westermann <daniel(dot)westermann(at)dbi-services(dot)com> writes:
> - if the above is correct why does PostgreSQL only write a partial file back to disk/wal? For me this still seems dangerous as potentially nobody will notice it

In quiescent circumstances, Postgres wouldn't have written anything at
all, and the file would have disappeared completely at server shutdown,
and you would have gotten some sort of file-not-found error when you tried
the "count(*)" after restarting. I hypothesize that you did an unclean
shutdown leading to replaying some amount of WAL at restart, and that WAL
included writing at least one block of the file (perhaps as a result of a
hint-bit update, or some other not-user-visible maintenance operation,
rather than anything you did explicitly). The WAL replay code will
recreate the file if it doesn't exist on-disk --- this is important for
robustness. Then you'd have a file that exists on-disk but is partially
filled with empty pages, which matches the observed behavior. Depending
on various details you haven't provided, this might be indistinguishable
from a valid database state.

> - PostgreSQL assumes that someone with write access to the files knows what she/he is doing. ok, but still, in the real world cases like this happen (for whatever reason)

[ shrug... ] There's also an implied contract that you don't do "rm -rf /",
or shoot the disk drive full of holes with a .45, or various other
unrecoverable actions. We're not really prepared to expend large amounts
of developer effort, or large amounts of runtime overhead, to detect such
cases. (In particular, the fact that all-zero pages are a valid state is
unfortunate from this perspective, but it's more or less forced by
robustness concerns associated with table-extension behavior. Most users
would not thank us for making table extension slower in order to issue a
more intelligible error for examples like this one.)

regards, tom lane