Re: Datatypes and performance

Just because all reported commits go in doesn't mean there won't be any
data loss.

If you pull the power cord, the DB should be in a consistent state barring
hardware and other issues. However there can be data loss, because the
clients might have been inserting data which may not be able to be
reproduced again, but which was not committed nor reported as committed,
and the clients may no longer have a copy of the data. So how does the app
or user deal with that? That can determine if data is lost holistically.

For example, if you were in the process of inserting 1 million rows from a
raw source, haven't committed and someone pulls the plug, the transaction
will not be committed. Or a prospective customer clicks "Submit Order", and
you pull the plug just then, customer sees the browser spin for a minute or
so, times out, gives up goes somewhere else, and you don't get the order.

Given I've seen lots of things not work as they should, 100% guarantees
seem like wishful thinking. I'd just believe that with Fsync on, the
probability of commits being saved to disk is very high. Whereas with fsync
off, the probability is a lot lower especially if the O/S is prone to
taking 30 seconds or longer to sync outstanding data to disk.

Also, if power was lost in the middle of an fsync I'm not sure what
actually happens. I think there are some checks, but they often assume
atomicity of an operation at a particular level. Even if that holds true,
the hardware could still fail on you - writing something whilst running out
of power is not a good situation.

Regards,
Link.

At 05:11 PM 7/7/2003 +0200, Mattias Kregert wrote:

> > > If I turn fsync on and then pull the power cord while a
> > > number of clients are doing lots of inserts/updates and stuff,
> > > will the fsync then guarantee that no data will be lost or
> > > corrupted?
> > You are surely kidding, aren't you ?
> >
> > Karsten
>
>No. No kidding.
>Just to clarify, what I mean is: With FSYNC enabled, after a power
>failure, after "pg_ctl start" and replay of xact log etc; Are COMMITTED
>transactions guaranteed to be intact, and are UNCOMMITTED transactions
>guaranteed not to appear in the tables?
>
>If the answer is "yes", then I understand the use of FSYNC.
>
>If the answer is "no", then i don't see the point in using FSYNC at all.
>
>/* m */
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org