From: | JP Jacoupy <jpjacoupy(at)protonmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Synchronous Commit, WAL archiving and statement_timeout |
Date: | 2017-02-03 09:18:40 |
Message-ID: | JEuehc-G14iN-u6krqpayvP9H23mG5mC1N8W0qW6CvVNn4tn-D86qFfwdWCAHxPJRRDBsTFE-F-rcBAYzhhPKScIDkkzWVFTcfRNLGRsPRk=@protonmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-------- 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(dot)klaver(at)aklaver(dot)com
To: JP Jacoupy <jpjacoupy(at)protonmail(dot)com>, pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Nikolai Zhubr | 2017-02-03 10:52:26 | Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary). |
Previous Message | Jehan-Guillaume de Rorthais | 2017-02-03 08:48:06 | Re: pgbouncer increase pool_size, reload does not work |