pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedsynchronous_commit off
5+ messages / 4 participants
[nested] [flat]
* synchronous_commit off
@ 2011-08-01 13:29 Anibal David Acosta <aa@devshock.com>
2011-08-01 19:52 ` Re: synchronous_commit off Greg Smith <greg@2ndQuadrant.com>
2011-08-03 00:08 ` Re: synchronous_commit off lars hofhansl <lhofhansl@yahoo.com>
0 siblings, 2 replies; 5+ messages in thread
From: Anibal David Acosta @ 2011-08-01 13:29 UTC (permalink / raw)
To: pgsql-performance
Can a transaction committed asynchronously report an error, duplicate key or
something like that, causing a client with a OK transaction but server with
a FAILED transaction.
Thanks
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: synchronous_commit off
2011-08-01 13:29 synchronous_commit off Anibal David Acosta <aa@devshock.com>
@ 2011-08-01 19:52 ` Greg Smith <greg@2ndQuadrant.com>
2011-08-01 20:05 ` Re: synchronous_commit off Anibal David Acosta <aa@devshock.com>
2011-08-01 23:49 ` Re: synchronous_commit off Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 2 replies; 5+ messages in thread
From: Greg Smith @ 2011-08-01 19:52 UTC (permalink / raw)
To: pgsql-performance
On 08/01/2011 09:29 AM, Anibal David Acosta wrote:
>
> Can a transaction committed asynchronously report an error, duplicate
> key or something like that, causing a client with a OK transaction but
> server with a FAILED transaction.
>
>
No. You are turning off the wait for the transaction to hit disk before
returning to the client, but all the validation checks are done before
that. The sole risk with synchronous_commit off is that a client will
get COMMIT, but the server will lose the transaction completely--when
there's a crash before it's written to disk.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: synchronous_commit off
2011-08-01 13:29 synchronous_commit off Anibal David Acosta <aa@devshock.com>
2011-08-01 19:52 ` Re: synchronous_commit off Greg Smith <greg@2ndQuadrant.com>
@ 2011-08-01 20:05 ` Anibal David Acosta <aa@devshock.com>
1 sibling, 0 replies; 5+ messages in thread
From: Anibal David Acosta @ 2011-08-01 20:05 UTC (permalink / raw)
To: 'Greg Smith' <greg@2ndQuadrant.com>; pgsql-performance
the application doesn't manage money or something really really critical, so
I can live with the "in case of crash" that is not a normal behavior J
Thanks.
De: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] En nombre de Greg Smith
Enviado el: lunes, 01 de agosto de 2011 03:53 p.m.
Para: pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] synchronous_commit off
On 08/01/2011 09:29 AM, Anibal David Acosta wrote:
Can a transaction committed asynchronously report an error, duplicate key or
something like that, causing a client with a OK transaction but server with
a FAILED transaction.
No. You are turning off the wait for the transaction to hit disk before
returning to the client, but all the validation checks are done before that.
The sole risk with synchronous_commit off is that a client will get COMMIT,
but the server will lose the transaction completely--when there's a crash
before it's written to disk.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: synchronous_commit off
2011-08-01 13:29 synchronous_commit off Anibal David Acosta <aa@devshock.com>
2011-08-01 19:52 ` Re: synchronous_commit off Greg Smith <greg@2ndQuadrant.com>
@ 2011-08-01 23:49 ` Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 0 replies; 5+ messages in thread
From: Craig Ringer @ 2011-08-01 23:49 UTC (permalink / raw)
To: Greg Smith <greg@2ndQuadrant.com>; +Cc: pgsql-performance
On 2/08/2011 3:52 AM, Greg Smith wrote:
> On 08/01/2011 09:29 AM, Anibal David Acosta wrote:
>>
>> Can a transaction committed asynchronously report an error, duplicate
>> key or something like that, causing a client with a OK transaction
>> but server with a FAILED transaction.
>>
>>
>
> No. You are turning off the wait for the transaction to hit disk
> before returning to the client, but all the validation checks are done
> before that. The sole risk with synchronous_commit off is that a
> client will get COMMIT, but the server will lose the transaction
> completely--when there's a crash before it's written to disk.
What about an I/O error during write, or other HW-level issues that
might cause a transaction to fail to commit while others proceed fine?
--
Craig Ringer
POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: synchronous_commit off
2011-08-01 13:29 synchronous_commit off Anibal David Acosta <aa@devshock.com>
@ 2011-08-03 00:08 ` lars hofhansl <lhofhansl@yahoo.com>
1 sibling, 0 replies; 5+ messages in thread
From: lars hofhansl @ 2011-08-03 00:08 UTC (permalink / raw)
To: Anibal David Acosta <aa@devshock.com>; pgsql-performance
No: The commit has the same guarantees as a synchronous commit w.r.t. data consistency. The commit can only fail (as a whole) due to hardware problems or postgres backend crashes.
And yes: The client commit returns, but the server can fail later and not persist the transaction and it will be lost (again as a whole).
Your application should be able to tolerate losing the latest committed transactions if you use this.
The difference to fsync=off is that a server crash will leave the database is a consistent state with just the latest transactions lost.
________________________________
From: Anibal David Acosta <aa@devshock.com>
To: pgsql-performance@postgresql.org
Sent: Monday, August 1, 2011 6:29 AM
Subject: [PERFORM] synchronous_commit off
Can a transaction committed asynchronously report an error, duplicate key or something like that, causing a client with a OK transaction but server with a FAILED transaction.
Thanks
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-08-03 00:08 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-01 13:29 synchronous_commit off Anibal David Acosta <aa@devshock.com>
2011-08-01 19:52 ` Greg Smith <greg@2ndQuadrant.com>
2011-08-01 20:05 ` Anibal David Acosta <aa@devshock.com>
2011-08-01 23:49 ` Craig Ringer <craig@postnewspapers.com.au>
2011-08-03 00:08 ` lars hofhansl <lhofhansl@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox