Thread: [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

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

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

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