Conflict between recovery thread and client queries on a hot standby replica

From: Kim Rose Carlsen <krc(at)hiper(dot)dk>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Conflict between recovery thread and client queries on a hot standby replica
Date: 2018-09-09 21:05:30
Message-ID: 3F6412A6-9BBC-4409-8F8F-0DB631E9BE75@hiper.dk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

Hi

It seems that the recovery thread(streaming replication) and sql queries against a postgresql hot standby can produce a deadlock.

If you have conflicting statements running on master and slave, you can end up with queries on the slave waiting for locks of the recovery thread and the recovery thread waiting for locks on the sql thread. The locks are never resolved or any client aborted. This happens when using

max_standby_streaming_delay=-1

Which does state that the recovery thread can wait indefinitely on a SQL thread, but I would still expect dead locks to be detected and handled.

How to reproduce:

- Setup pgsql master/slave with streaming replication

- On the slave "set max_standby_streaming_delay=-1"

- Start a thread on master that does the following

BEGIN;
CREATE OR REPLACE VIEW va AS SELECT 1;
CREATE OR REPLACE VIEW vb as SELECT 2;
COMMIT;
- Start multiple threads on the slave that does the following (with 5 workers running, a deadlock is produced within minutes)
SELECT * FROM vb;
SELECT * FROM va;

Expected behaviour:

The 5 threads will continue be able to query the views va, vb

Observed behaviour:

The 5 threads are blocked from reading the tables, after some time. They are never allowed access to the views before all queries are aborted and restarted.

I have build a docker compose file to reproduce my observation.

https://github.com/kimc78/postgresql-slave-deadlock

Best regards
Kim Carlsen

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2018-09-09 22:22:24 Re: BUG #15376: Postgres sql 9.4.19 pg_upgrade stops with error The source cluster was not shut down cleanly.
Previous Message Tom Lane 2018-09-09 20:10:51 Re: BUG #15367: Crash in pg_fe_scram_free when using foreign tables

Browse pgsql-general by date

  From Date Subject
Next Message Ahmed, Nawaz 2018-09-10 04:19:37 RE: connection error
Previous Message Kim Rose Carlsen 2018-09-09 20:00:04 Conflict between recovery thread and client queries on a hot standby replica