Re: Synchronous Commit, WAL archiving and statement_timeout

[JP Jacoupy <jpjacoupy@xxxxxxxxxxxxxx>]

-------- Original Message --------

Subject: Re: [GENERAL] Synchronous Commit, WAL archiving and statement_timeout

Local Time: 3 février 2017 1:15 AM

UTC Time: 3 février 2017 00:15

From: adrian.klaver@xxxxxxxxxxx

To: JP Jacoupy <jpjacoupy@xxxxxxxxxxxxxx>, pgsql-general@xxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxx>

On 02/02/2017 09:15 AM, JP Jacoupy wrote:

>

> Hello,

>

> I noticed something strange and I would like to understand what's

> happening.

>

> I have the following environment:

> - 2 PostgreSQL instance running in hot-standby with synchronous commit

> activated.

There have been many changes in replication over the years/versions, so

it would be helpful to know what Postgres version you are using?

Sorry, forgot to specify.
I'm running Postgres 9.4.4 under CentOS 6.6

> (further called Master & Slave)

> - The archiving of the WAL files is activated on the master running

> every 5 minutes

> - Slave is down

>

> I set the statement_timeout inside my ~/.psqlrc:

> $ cat ~/.psqlrc

> set statement_timeout = 1;

> commit;

>

> When running an UPDATE statement (via psql) on the master, it hangs

> (psql seems to

> wait a response from the slave) ignoring any value I set in my .psqlrc

> and the update is

> done and written on the Master. Furthermore if I try (doing the same

> things in a small

> script with a timeout on the call to PQexec) to make a call to PQcancel

> it does nothing

> on the Master.

>

> I expected the statement to timeout because the synchronous_commit

> wouldn't work

https://www.postgresql.org/docs/9.4/static/warm-standby.html#SYNCHRONOUS-REPLICATION

"25.2.8.3. Planning for High Availability

Commits made when synchronous_commit is set to on or remote_write will

wait until the synchronous standby responds. The response may never

occur if the last, or only, standby should crash."

> since the Slave is down while rollbacking on the Master.

As I understand this, the commit on the master should wait the response from the slaves and might come under the hammer of the statement_timeout.

Is there anything I could do to prevent this hang (except switching to asynchronous commit)?

--

Adrian Klaver

adrian.klaver@xxxxxxxxxxx