Re: swapping relfilenodes (was: Re: locks in CREATE TRIGGER,

Andrew - Supernews wrote:
> On 2005-03-23, Neil Conway <neilc(at)samurai(dot)com> wrote:
> > - swap the relfilenodes of the old and temporary heap relations
>
> While discussing this one further on IRC, I noticed the following:
>
> Everywhere I could find that currently replaces the relfilenode of a
> relation does so while holding an AccessExclusive lock, and assumes that
> this is sufficient to ensure that the old relfilenode can be killed when
> the transaction commits. This is not correct.
>
> Example:
>
> - backend A begins a serializable transaction
> - backend B truncates a table (and commits)
> - backend A, still in the same transaction, accesses the truncated table
>
> Currently backend A sees the truncated table as empty, which is obviously
> not right. This is obviously related to any attempt to weaken the locking
> on other operations that modify relfilenodes, because doing it right implies
> a mechanism to defer the removals past the commit of the modifying
> transaction and up to the point where the old data can no longer be seen by
> a live transaction.

This is a good point. While DELETE keeps the old rows around and VACUUM
perserves them until the serialized transaction commits, truncate does
not keep the old rows around.

In fact, would a truncate during a backup cause the backup to be
inconsistent because it wouldn't be a true snapshot of the database at
backup start time? Seems so.

The docs mention:

TRUNCATE cannot be used if there are foreign-key refer-
ences to the table from other tables. Checking validity in
such cases would require table scans, and the whole point
is not to do one.

so it doesn't make the referential integrity inconsistent.

Perhaps we should document this.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073