Postgres Pro
Downloads
Home   >   mailing lists

Thread: synchronous_commit off

synchronous_commit off

From
"Anibal David Acosta"
Date:

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

 

Re: synchronous_commit off

From
Greg Smith
Date:
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

Re: synchronous_commit off

From
"Anibal David Acosta"
Date:

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

Re: synchronous_commit off

From
Craig Ringer
Date:
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/

Re: synchronous_commit off

From
lars hofhansl
Date:
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