Thread: Worries about delayed-commit semantics

Worries about delayed-commit semantics

From
Tom Lane
Date:
I've been reflecting a bit about whether the notion of deferred fsync
for transaction commits is really safe.  The proposed patch tries to
ensure that no consequences of a committed transaction can reach disk
before the commit WAL record is fsync'd, but ISTM there are potential
holes in what it's doing.  In particular the path that concerns me is

(1) transaction A commits with deferred fsync;

(2) transaction B observes some effect of A (eg, a committed-good tuple);

(3) transaction B makes a change that is contingent on the observation.

If B's changes were to reach disk in advance of A's commit record, we'd
have a risk of logical inconsistency.  The patch is doing what it can
to prevent *direct* effects of A from reaching disk before the commit
record does, but it doesn't (and I think cannot) extend this to indirect
effects perpetrated by other transactions.  An example of the sort of
risk I'm worried about is a REINDEX omitting an index entry for a tuple
that it sees as committed dead by A.

Now this may be safe anyway, but it requires analysis that I don't
recall anyone having put forward.  The cases that I can see are:

1. Ordinary WAL-logged change in a shared buffer page.  The change will
not be allowed to reach disk before the associated WAL record does, and
that WAL record must follow A's commit, so we're safe.

2. Non-WAL-logged change in a temp table.  Could reach disk in advance
of A's commit, but we don't care since temp table contents don't survive
crashes anyway.

3. Non-WAL-logged change made via one of the paths we have introduced
to avoid WAL overhead for bulk updates.  In these cases it's entirely
possible for the data to reach disk before A's commit, because B will
fsync it down to disk without any sort of interlock, as soon as it
finishes the bulk update.  However, I believe it's the case that all
these paths are designed to write data that no other transaction can see
until after B commits.  That commit must follow A's in the WAL log,
so until it has reached disk, the contents of the bulk-updated file
are unimportant after a crash.

So I think it's probably all OK, but this is a sufficiently long chain
of reasoning that it had better be checked over by multiple people and
recorded as part of the design implications of the patch.  Does anyone
think any of this is wrong, or too fragile to survive future code
changes?  Are there cases I've missed?

BTW: I really dislike the name "transaction guarantee" for the feature;
it sounds like marketing-speak, not to mention overpromising what we
can deliver.  Postgres can't "guarantee" anything in the face of
untrustworthy disk hardware, for instance.  I'd much rather use names
derived from "deferred commit" or "delayed commit" or some such.
        regards, tom lane


Re: Worries about delayed-commit semantics

From
"Simon Riggs"
Date:
On Thu, 2007-06-21 at 18:15 -0400, Tom Lane wrote:

> BTW: I really dislike the name "transaction guarantee" for the
> feature; it sounds like marketing-speak, not to mention overpromising
> what we can deliver. 

There is no marketing speak there, nor any overpromising of what is
delivered. I really don't know where you get that idea.

The patch says exactly what it does: it reduces the level of guarantee
provided by a transaction commit and thereby causes almost-certain data
loss in the event of a crash, for transactions that have used the
feature. How can 'you get less' be an overpromise? 

So the purpose of the name was to be explicit about the loss of
robustness that is being traded for performance. If I'd wanted to give
it a marketing name, it would be called 'fast commit'.

>  Postgres can't "guarantee" anything in the face of
> untrustworthy disk hardware, for instance.  

The "guarantee" PostgreSQL currently offers is the ACID durability
guarantee. Postgres has for many years differentiated itself from MySQL
on the basis of the certainty of the commit action.

True, disk hardware can nullify the commit guarantee.

> I'd much rather use names
> derived from "deferred commit" or "delayed commit" or some such.

I'm happy with various names and even had a specific post on the
subject.

Deferred Commit is the best description of the feature to me, but
doesn't highlight the dangers of using it. Am I being too cautious?
Would users understand that "deferred commit" would cause data loss? If
yes, then I'm perfectly happy with that name.

No feedback means name change to deferred commit.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com




Re: Worries about delayed-commit semantics

From
"Simon Riggs"
Date:
On Thu, 2007-06-21 at 18:15 -0400, Tom Lane wrote:
> I've been reflecting a bit about whether the notion of deferred fsync
> for transaction commits is really safe.  The proposed patch tries to
> ensure that no consequences of a committed transaction can reach disk
> before the commit WAL record is fsync'd, but ISTM there are potential
> holes in what it's doing.  In particular the path that concerns me is
> 
> (1) transaction A commits with deferred fsync;
> 
> (2) transaction B observes some effect of A (eg, a committed-good tuple);
> 
> (3) transaction B makes a change that is contingent on the observation.
> 
> If B's changes were to reach disk in advance of A's commit record, we'd
> have a risk of logical inconsistency.  

B's changes cannot reach disk before B's commit record. That is the
existing WAL-before-data rule implemented by the buffer manager.

If B can see A's changes, then A has written a commit record to the log
that is definitely before B's commit record. So B's commit will also
commit A's changes to WAL when it flushes at EOX. So whether A is a
guaranteed transaction or not, B can always rely on those changes.

I agree this feels unsafe when you first think about it, and was the
reason for me taking months before publishing the idea.

> The patch is doing what it can
> to prevent *direct* effects of A from reaching disk before the commit
> record does, but it doesn't (and I think cannot) extend this to indirect
> effects perpetrated by other transactions.  An example of the sort of
> risk I'm worried about is a REINDEX omitting an index entry for a tuple
> that it sees as committed dead by A.
> 
> Now this may be safe anyway, but it requires analysis that I don't
> recall anyone having put forward.  The cases that I can see are:
> 
> 1. Ordinary WAL-logged change in a shared buffer page.  The change will
> not be allowed to reach disk before the associated WAL record does, and
> that WAL record must follow A's commit, so we're safe.
> 
> 2. Non-WAL-logged change in a temp table.  Could reach disk in advance
> of A's commit, but we don't care since temp table contents don't survive
> crashes anyway.
> 
> 3. Non-WAL-logged change made via one of the paths we have introduced
> to avoid WAL overhead for bulk updates.  In these cases it's entirely
> possible for the data to reach disk before A's commit, because B will
> fsync it down to disk without any sort of interlock, as soon as it
> finishes the bulk update.  However, I believe it's the case that all
> these paths are designed to write data that no other transaction can see
> until after B commits.  That commit must follow A's in the WAL log,
> so until it has reached disk, the contents of the bulk-updated file
> are unimportant after a crash.
> 
> So I think it's probably all OK, but this is a sufficiently long chain
> of reasoning that it had better be checked over by multiple people and
> recorded as part of the design implications of the patch.  Does anyone
> think any of this is wrong, or too fragile to survive future code
> changes?  Are there cases I've missed?

I've done the analysis, but perhaps I should finish the docs now to aid
with review of the patch on the points you make.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com




Re: Worries about delayed-commit semantics

From
Gregory Stark
Date:
"Tom Lane" <tgl@sss.pgh.pa.us> writes:

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> I've been reflecting a bit about whether the notion of deferred fsync
> for transaction commits is really safe.  The proposed patch tries to
> ensure that no consequences of a committed transaction can reach disk
> before the commit WAL record is fsync'd, but ISTM there are potential
> holes in what it's doing.  In particular the path that concerns me is
>
> (1) transaction A commits with deferred fsync;
>
> (2) transaction B observes some effect of A (eg, a committed-good tuple);
>
> (3) transaction B makes a change that is contingent on the observation.
>
> If B's changes were to reach disk in advance of A's commit record, we'd
> have a risk of logical inconsistency.  The patch is doing what it can
> to prevent *direct* effects of A from reaching disk before the commit
> record does, but it doesn't (and I think cannot) extend this to indirect
> effects perpetrated by other transactions.  An example of the sort of
> risk I'm worried about is a REINDEX omitting an index entry for a tuple
> that it sees as committed dead by A.
>
> Now this may be safe anyway, but it requires analysis that I don't
> recall anyone having put forward.  The cases that I can see are:

I think Simon did try to put all this in writing when he first proposed it.
It's worth going through again with the actual implementation to be sure all
the same guarantees hold.

> So I think it's probably all OK, but this is a sufficiently long chain
> of reasoning that it had better be checked over by multiple people and
> recorded as part of the design implications of the patch.  Does anyone
> think any of this is wrong, or too fragile to survive future code
> changes?  Are there cases I've missed?

I think the logic you describe is not quite as subtle as you make it out to
be. Certainly it's a bit surprising at first but it all boils down to the
basic idea of how transactions and WAL records work: We never allow any other
transactions to see the effects of our transaction until the commit record is
fsynced to WAL. 

So now we're poking a hole in that but we certainly have to ensure that any
transactions that do see the results of our deferred commit themselves don't
record any visible effects until both their commit and ours hit WAL. The
essential point in Simon's approach that guarantees that is that when you
fsync you fsync all work that came before you. So committing a transaction
also commits all deferred commits that you might depend on.

> BTW: I really dislike the name "transaction guarantee" for the feature;
> it sounds like marketing-speak, not to mention overpromising what we
> can deliver.  Postgres can't "guarantee" anything in the face of
> untrustworthy disk hardware, for instance.  I'd much rather use names
> derived from "deferred commit" or "delayed commit" or some such.

Well from an implementation point of view we're delaying or deferring the
commit. But from a user's point of view the important thing for them to
realize is that a committed record could be lost.

Perhaps we should just not come up with a new name and reuse the fsync
variable. That way users of old installs which have fsync=off silently get
this new behaviour. I'm not sure I like that idea since I use fsync=off to run
cpu overhead tests here. But from a user's point of view it's probably the
"right" thing. This is really what fsync=off should always have been doing.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com



Re: Worries about delayed-commit semantics

From
"Joshua D. Drake"
Date:
Tom Lane wrote:
> I've been reflecting a bit about whether the notion of deferred fsync
> for transaction commits is really safe.  The proposed patch tries to
> ensure that no consequences of a committed transaction can reach disk
> before the commit WAL record is fsync'd, but ISTM there are potential
> holes in what it's doing.  In particular the path that concerns me is

> BTW: I really dislike the name "transaction guarantee" for the feature;
> it sounds like marketing-speak, not to mention overpromising what we
> can deliver.  Postgres can't "guarantee" anything in the face of

Ahh but it can. :). PostgreSQL can guarantee that "if" the hardware is 
not faulty and the OS does what it is supposed to do... etc..

And yes, it is marketing but life is marketing, getting girlfriends is 
marketing. What matters is that once the marketing is over, you can 
stand up to the hype.

> untrustworthy disk hardware, for instance.  I'd much rather use names
> derived from "deferred commit" or "delayed commit" or some such.

Honestly, I prefer these names as well as it seems directly related 
versus transaction guarantee which sounds to be more like us saying, if 
we turn it off our transactions are bogus.

Joshua D. Drake

> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 


-- 
      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/



Re: Worries about delayed-commit semantics

From
Gregory Stark
Date:
"Joshua D. Drake" <jd@commandprompt.com> writes:

> Tom Lane wrote:
>
>> untrustworthy disk hardware, for instance.  I'd much rather use names
>> derived from "deferred commit" or "delayed commit" or some such.
>
> Honestly, I prefer these names as well as it seems directly related versus
> transaction guarantee which sounds to be more like us saying, if we turn it off
> our transactions are bogus.

Hm, another possibility: "synchronous_commit = off"

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com



Re: Worries about delayed-commit semantics

From
"Simon Riggs"
Date:
On Fri, 2007-06-22 at 14:29 +0100, Gregory Stark wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> 
> > Tom Lane wrote:
> >
> >> untrustworthy disk hardware, for instance.  I'd much rather use names
> >> derived from "deferred commit" or "delayed commit" or some such.
> >
> > Honestly, I prefer these names as well as it seems directly related versus
> > transaction guarantee which sounds to be more like us saying, if we turn it off
> > our transactions are bogus.

That was the intention..., but name change accepted.

> Hm, another possibility: "synchronous_commit = off"

Ooo, I like that. Any other takers?

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com




Re: Worries about delayed-commit semantics

From
"Joshua D. Drake"
Date:
Simon Riggs wrote:
> On Fri, 2007-06-22 at 14:29 +0100, Gregory Stark wrote:
>> "Joshua D. Drake" <jd@commandprompt.com> writes:
>>
>>> Tom Lane wrote:
>>>
>>>> untrustworthy disk hardware, for instance.  I'd much rather use names
>>>> derived from "deferred commit" or "delayed commit" or some such.
>>> Honestly, I prefer these names as well as it seems directly related versus
>>> transaction guarantee which sounds to be more like us saying, if we turn it off
>>> our transactions are bogus.
> 
> That was the intention..., but name change accepted.
> 
>> Hm, another possibility: "synchronous_commit = off"
> 
> Ooo, I like that. Any other takers?

I like "synchronous_commit = off", it even has a little girlfriend 
getting spin while being accurate :)

Joshua D. Drake




-- 
      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/



Re: Worries about delayed-commit semantics

From
Richard Huxton
Date:
Joshua D. Drake wrote:
> Simon Riggs wrote:
>> On Fri, 2007-06-22 at 14:29 +0100, Gregory Stark wrote:
>>> "Joshua D. Drake" <jd@commandprompt.com> writes:
>>>
>>>> Tom Lane wrote:
>>>>
>>>>> untrustworthy disk hardware, for instance.  I'd much rather use names
>>>>> derived from "deferred commit" or "delayed commit" or some such.
>>>> Honestly, I prefer these names as well as it seems directly related 
>>>> versus
>>>> transaction guarantee which sounds to be more like us saying, if we 
>>>> turn it off
>>>> our transactions are bogus.
>>
>> That was the intention..., but name change accepted.
>>
>>> Hm, another possibility: "synchronous_commit = off"
>>
>> Ooo, I like that. Any other takers?
> 
> I like "synchronous_commit = off", it even has a little girlfriend 
> getting spin while being accurate :)

Or perhaps "sync_on_commit = off"?
Less girlfriend-speak perhaps:"no_sync_on_commit = on"

--   Richard Huxton  Archonet Ltd


Re: Worries about delayed-commit semantics

From
Tom Lane
Date:
"Simon Riggs" <simon@2ndquadrant.com> writes:
> On Fri, 2007-06-22 at 14:29 +0100, Gregory Stark wrote:
>> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Hm, another possibility: "synchronous_commit = off"

> Ooo, I like that. Any other takers?

OK with me
        regards, tom lane


Re: Worries about delayed-commit semantics

From
PFC
Date:
> So now we're poking a hole in that but we certainly have to ensure that
> any
> transactions that do see the results of our deferred commit themselves
> don't
> record any visible effects until both their commit and ours hit WAL. The
> essential point in Simon's approach that guarantees that is that when you
> fsync you fsync all work that came before you. So committing a
> transaction
> also commits all deferred commits that you might depend on.
>
>> BTW: I really dislike the name "transaction guarantee" for the feature;
>> it sounds like marketing-speak, not to mention overpromising what we
>> can deliver.  Postgres can't "guarantee" anything in the face of
>> untrustworthy disk hardware, for instance.  I'd much rather use names
>> derived from "deferred commit" or "delayed commit" or some such.
>
> Well from an implementation point of view we're delaying or deferring the
> commit. But from a user's point of view the important thing for them to
> realize is that a committed record could be lost.
>
> Perhaps we should just not come up with a new name and reuse the fsync
> variable. That way users of old installs which have fsync=off silently
> get
> this new behaviour. I'm not sure I like that idea since I use fsync=off
> to run
> cpu overhead tests here. But from a user's point of view it's probably
> the
> "right" thing. This is really what fsync=off should always have been
> doing.
Say you call them SOFT COMMIT and HARD COMMIT...HARD COMMIT fsyncs, obviously.Does SOFT COMMIT fflush() the WAL (so
it'spostgres-crash-safe) or not ?(just in case some user C function misbehaves and crashes) 
Do we get a config param to set default_commit_mode=hard or soft ?
By the way InnoDB has a similar option where you set
innodb_flush_log_on_commit (or something). However you cannot set it on a
per-transaction basis. So, on a e-commerce site, for instance, most
transactions will be "unimportant" (ie. no need to fsync, ACI only, like
incrementing products view counts, add to cart, etc) but some transactions
will have to be guaranteed (full ACID) like recording that an order has
been submitted / paid / shipped. But with InnoDB you can't choose this on
a per-transaction basis, so it's all or nothing.




Re: Worries about delayed-commit semantics

From
Andrew Dunstan
Date:

Joshua D. Drake wrote:
>
> I like "synchronous_commit = off", it even has a little girlfriend 
> getting spin while being accurate :)
>

In my experience, *_commit = off rarely gets you a girlfriend ...

cheers

andrew


Re: Worries about delayed-commit semantics

From
Bruce Momjian
Date:
Simon Riggs wrote:
> On Fri, 2007-06-22 at 14:29 +0100, Gregory Stark wrote:
> > "Joshua D. Drake" <jd@commandprompt.com> writes:
> > 
> > > Tom Lane wrote:
> > >
> > >> untrustworthy disk hardware, for instance.  I'd much rather use names
> > >> derived from "deferred commit" or "delayed commit" or some such.
> > >
> > > Honestly, I prefer these names as well as it seems directly related versus
> > > transaction guarantee which sounds to be more like us saying, if we turn it off
> > > our transactions are bogus.
> 
> That was the intention..., but name change accepted.
> 
> > Hm, another possibility: "synchronous_commit = off"
> 
> Ooo, I like that. Any other takers?

Yea, I like that too but I am now realizing that we are not really
deferring or delaying the "COMMIT" command but rather the recovery of
the commit.  GUC as full_commit_recovery?

--  Bruce Momjian  <bruce@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Worries about delayed-commit semantics

From
"Joshua D. Drake"
Date:
Bruce Momjian wrote:
> Simon Riggs wrote:
>> On Fri, 2007-06-22 at 14:29 +0100, Gregory Stark wrote:
>>> "Joshua D. Drake" <jd@commandprompt.com> writes:
>>>
>>>> Tom Lane wrote:
>>>>
>>>>> untrustworthy disk hardware, for instance.  I'd much rather use names
>>>>> derived from "deferred commit" or "delayed commit" or some such.
>>>> Honestly, I prefer these names as well as it seems directly related versus
>>>> transaction guarantee which sounds to be more like us saying, if we turn it off
>>>> our transactions are bogus.
>> That was the intention..., but name change accepted.
>>
>>> Hm, another possibility: "synchronous_commit = off"
>> Ooo, I like that. Any other takers?
> 
> Yea, I like that too but I am now realizing that we are not really
> deferring or delaying the "COMMIT" command but rather the recovery of
> the commit.  GUC as full_commit_recovery?

recovery is a bad word I think. It is related too closely to failure.

Sincerely,

Joshua D. Drake


> 


-- 
      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/



Re: Worries about delayed-commit semantics

From
Bruce Momjian
Date:
Joshua D. Drake wrote:
> Bruce Momjian wrote:
> > Simon Riggs wrote:
> >> On Fri, 2007-06-22 at 14:29 +0100, Gregory Stark wrote:
> >>> "Joshua D. Drake" <jd@commandprompt.com> writes:
> >>>
> >>>> Tom Lane wrote:
> >>>>
> >>>>> untrustworthy disk hardware, for instance.  I'd much rather use names
> >>>>> derived from "deferred commit" or "delayed commit" or some such.
> >>>> Honestly, I prefer these names as well as it seems directly related versus
> >>>> transaction guarantee which sounds to be more like us saying, if we turn it off
> >>>> our transactions are bogus.
> >> That was the intention..., but name change accepted.
> >>
> >>> Hm, another possibility: "synchronous_commit = off"
> >> Ooo, I like that. Any other takers?
> > 
> > Yea, I like that too but I am now realizing that we are not really
> > deferring or delaying the "COMMIT" command but rather the recovery of
> > the commit.  GUC as full_commit_recovery?
> 
> recovery is a bad word I think. It is related too closely to failure.

commit_stability?  reliable_commit?

--  Bruce Momjian  <bruce@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Worries about delayed-commit semantics

From
PFC
Date:
On Fri, 22 Jun 2007 16:43:00 +0200, Bruce Momjian <bruce@momjian.us> wrote:

> Simon Riggs wrote:
>> On Fri, 2007-06-22 at 14:29 +0100, Gregory Stark wrote:
>> > "Joshua D. Drake" <jd@commandprompt.com> writes:
>> >
>> > > Tom Lane wrote:
>> > >
>> > >> untrustworthy disk hardware, for instance.  I'd much rather use
>> names
>> > >> derived from "deferred commit" or "delayed commit" or some such.
>> > >
>> > > Honestly, I prefer these names as well as it seems directly related
>> versus
>> > > transaction guarantee which sounds to be more like us saying, if we
>> turn it off
>> > > our transactions are bogus.
>>
>> That was the intention..., but name change accepted.
>>
>> > Hm, another possibility: "synchronous_commit = off"
>>
>> Ooo, I like that. Any other takers?
>
> Yea, I like that too but I am now realizing that we are not really
> deferring or delaying the "COMMIT" command but rather the recovery of
> the commit.  GUC as full_commit_recovery?
>
commit_waits_for_fsync =
force_yes    : makes all commits "hard"yes    : commits are "hard" unless specified otherwise [default]no    : commits
are"soft" unless specified otherwise [should replace   
fsync=off use case]force_no    : makes all commits "soft" (controller with write cache
"emulator")
the force_yes and force_no are for benchmarking purposes mostly, ie. once
your app is tuned to specify which commits have to be guaranteed ("hard")
and which don't ("soft") you can then bench it with force_yes and force_no
to see how much you gained, and how much you'd gain by buying a write
cache controller...


Re: Worries about delayed-commit semantics

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> Joshua D. Drake wrote:
>>>> Hm, another possibility: "synchronous_commit = off"

>>>> Ooo, I like that. Any other takers?

>>> Yea, I like that too but I am now realizing that we are not really
>>> deferring or delaying the "COMMIT" command but rather the recovery of
>>> the commit.  GUC as full_commit_recovery?
>> 
>> recovery is a bad word I think. It is related too closely to failure.

> commit_stability?  reliable_commit?

What's wrong with synchronous_commit?  It's accurate and simple.
        regards, tom lane


Re: Worries about delayed-commit semantics

From
"Simon Riggs"
Date:
On Fri, 2007-06-22 at 10:52 -0400, Bruce Momjian wrote:

> commit_stability?  reliable_commit?

commit_durability?

That then relates it directly to the D in ACID.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com




Re: Worries about delayed-commit semantics

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Joshua D. Drake wrote:
> >>>> Hm, another possibility: "synchronous_commit = off"
> 
> >>>> Ooo, I like that. Any other takers?
> 
> >>> Yea, I like that too but I am now realizing that we are not really
> >>> deferring or delaying the "COMMIT" command but rather the recovery of
> >>> the commit.  GUC as full_commit_recovery?
> >> 
> >> recovery is a bad word I think. It is related too closely to failure.
> 
> > commit_stability?  reliable_commit?
> 
> What's wrong with synchronous_commit?  It's accurate and simple.

That is fine too.

--  Bruce Momjian  <bruce@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Worries about delayed-commit semantics

From
"Florian G. Pflug"
Date:
PFC wrote:
> On Fri, 22 Jun 2007 16:43:00 +0200, Bruce Momjian <bruce@momjian.us> wrote:
>> Simon Riggs wrote:
>>> On Fri, 2007-06-22 at 14:29 +0100, Gregory Stark wrote:
>>> > "Joshua D. Drake" <jd@commandprompt.com> writes:
>>> > > Tom Lane wrote:
>>> > >> untrustworthy disk hardware, for instance.  I'd much rather use names
>>> > >> derived from "deferred commit" or "delayed commit" or some such.
>>> > >
>>> > > Honestly, I prefer these names as well as it seems directly related versus
>>> > > transaction guarantee which sounds to be more like us saying, if we turn it off
>>> > > our transactions are bogus.
>>>
>>> That was the intention..., but name change accepted.
>>>
>>> > Hm, another possibility: "synchronous_commit = off"
>>>
>>> Ooo, I like that. Any other takers?
>>
>> Yea, I like that too but I am now realizing that we are not really
>> deferring or delaying the "COMMIT" command but rather the recovery of
>> the commit.  GUC as full_commit_recovery?
> 
>     commit_waits_for_fsync =
> 
>     force_yes    : makes all commits "hard"
>     yes    : commits are "hard" unless specified otherwise [default]
>     no    : commits are "soft" unless specified otherwise [should 
> replace fsync=off use case]
>     force_no    : makes all commits "soft" (controller with write cache 
> "emulator")

I think you got the last line backwards - without the fsync() after
a commit, you can't be sure that the data made it into the controller
cache. To be safe you *always* need the fsync() - but it will probably
be much cheaper if your controller doesn't have to actually write to
the disks, but can cache in battery-backed ram instead. Therefore,
if you own such a controller, you probably don't need deferred commits.

BTW, I like synchronous_commit too - but maybe asynchronous_commit
would be even better, with inverted semantics of course.
The you'd have "asynchronous_commit = off" as default.



Re: Worries about delayed-commit semantics

From
Michael Glaesemann
Date:
On Jun 22, 2007, at 9:23 , Richard Huxton wrote:

> Or perhaps "sync_on_commit = off"?

Or switch it around...

sink_on_commit = on

(sorry for the noise)

Michael Glaesemann
grzm seespotcode net




Re: Worries about delayed-commit semantics

From
Richard Huxton
Date:
Bruce Momjian wrote:
> Tom Lane wrote:
>> What's wrong with synchronous_commit?  It's accurate and simple.
> 
> That is fine too.

My concern would be that it can be read two ways:
1. When you commit, sync (something or other - unspecified)
2. Synchronise commits (to each other? to something else?)*

It's obvious to people on the -hackers list what we're talking about, 
but is it so clear to a newbie, perhaps non-English speaker?

* I can see people thinking this means something like "commit_delay".

--   Richard Huxton  Archonet Ltd


Re: Worries about delayed-commit semantics

From
"Florian G. Pflug"
Date:
Richard Huxton wrote:
> Bruce Momjian wrote:
>> Tom Lane wrote:
>>> What's wrong with synchronous_commit?  It's accurate and simple.
>>
>> That is fine too.
> 
> My concern would be that it can be read two ways:
> 1. When you commit, sync (something or other - unspecified)
> 2. Synchronise commits (to each other? to something else?)*
> 
> It's obvious to people on the -hackers list what we're talking about, 
> but is it so clear to a newbie, perhaps non-English speaker?
> 
> * I can see people thinking this means something like "commit_delay".

OTOH, the concept of synchronous vs. asynchronous (function) calls
should be pretty well-known among database programmers and administrators.
And (at least to me), this is really what this is about - the commit
happens asynchronously, at the convenience of the database, and not
the instant that I requested it.

greetings, Florian Pflug



Re: Worries about delayed-commit semantics

From
Tom Lane
Date:
Richard Huxton <dev@archonet.com> writes:
>> Tom Lane wrote:
>>> What's wrong with synchronous_commit?  It's accurate and simple.

> My concern would be that it can be read two ways:
> 1. When you commit, sync (something or other - unspecified)
> 2. Synchronise commits (to each other? to something else?)*

Well, that's a fair point.  deferred_commit would avoid that objection.

I'm not sure it's real important though --- with practically all of the
postgresql.conf variables, you really need to read the manual to know
exactly what they do.
        regards, tom lane


Re: Worries about delayed-commit semantics

From
Greg Smith
Date:
On Fri, 22 Jun 2007, Tom Lane wrote:

> What's wrong with synchronous_commit?  It's accurate and simple.

It's kind of a big word that not a lot of people understand the subtleties 
of, and I'd be concerned it will sow confusion with the terminology used 
for WAL synchronous writes.

When I explain to people the difference between transactions that have 
just been committed and written to disk (but possibly still sitting in a 
buffer) vs. ones that are known to have made it all the way through to the 
platters via fsync, the word I use is that the writes have been confirmed. 
If I were picking a GUC name to describe the current behavior I'd want to 
call it "confirmed_commit=on".  I think people easily understand the idea 
that just because something wasn't confirmed, that doesn't mean it didn't 
happen, you just can't be sure--and therefore there's a possibility it 
could be lost.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD