-------- 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.kla...@aklaver.com To: JP Jacoupy <jpjaco...@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.kla...@aklaver.com