-------- 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@aklaver.com
To: JP Jacoupy <jpjacoupy@protonmail.com>, pgsql-general@postgresql.org <pgsql-general@postgresql.org>
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@aklaver.com
