Re: [PERFORM] DELETE vs TRUNCATE explanation

Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> writes:
> On 07/16/2012 02:29 AM, Tom Lane wrote:
>> Yeah, you have a point there. It's not real clear that switching fsync
>> from off to on is an operation that we can make any guarantees about,
>> short of executing something like the code recently added to initdb
>> to force-sync the entire PGDATA tree.

> There's one way that doesn't have any housekeeping cost to Pg. It's
> pretty bad manners if there's anybody other than Pg on the system though:
> sync()

Yeah, I thought about that: if we could document that issuing a manual
sync after turning fsync on leaves you in a guaranteed-good state once
the sync is complete, it'd probably be fine. However, I'm not convinced
that we could promise that with a straight face. In the first place,
PG has only very weak guarantees about how quickly all processes in the
system will absorb a GUC update. In the second place, I'm not entirely
sure that there aren't race conditions around checkpoints and the fsync
request queue (particularly if we do what Jeff is suggesting and
suppress queuing requests at the upstream end). It might be all right,
or it might be all right after expending some work, but the whole thing
is not an area where I think anyone wants to spend time. I think it'd
be much safer to document that the correct procedure is "stop the
database, do a manual sync, enable fsync in postgresql.conf, restart the
database". And if that's what we're documenting, we lose little or
nothing by marking fsync as PGC_POSTMASTER.

regards, tom lane