Synchronous Commit, WAL archiving and statement_timeout
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.
(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
since the Slave is down while rollbacking on the Master.
Import Notes
Reply to msg id not found: Ueqm8D8Ic5UWY-Y8JbW71a_91V1OOvBlyZof0Mtbr2Tivbh9Sn5YVehYT2TwFMEp11Bkmaq6CWdTCYPtvbWjfo3KGpamWqTcg39AskWB3Ck=@protonmail.comReference msg id not found: Ueqm8D8Ic5UWY-Y8JbW71a_91V1OOvBlyZof0Mtbr2Tivbh9Sn5YVehYT2TwFMEp11Bkmaq6CWdTCYPtvbWjfo3KGpamWqTcg39AskWB3Ck=@protonmail.com
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?
(further called Master & Slave)
- The archiving of the WAL files is activated on the master running
every 5 minutes
- Slave is downI 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.
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Feb 3, 2017 at 2:15 AM, JP Jacoupy <jpjacoupy@protonmail.com> wrote:
I expected the statement to timeout because the synchronous_commit wouldn't
work since the Slave is down while rollbacking on the Master.
Queries being stuck because of synchronous replication are already
committed locally on the primary when they are waiting for the standby
to confirm that the transaction has been committed as well there,
before sending back confirmation to the client. So statement_timeout
has no effect on in such situations.
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.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 downI 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