The following bug has been logged on the website:
Bug reference: 15394
Logged by: Kim Carlsen
Email address: krc@hiper.dk
PostgreSQL version: 10.5
Operating system: Ubuntu 18.04
Description:
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
The manual 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
This has been previously posted in pgsql-general and pgsql-bugs, my
apologies for repeating the request.