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

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 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.


--
Adrian Klaver
adrian.klaver@aklaver.com