I am developing software which launches multiple, concurrent clients to connect to a PostgreSQL (12) database. When each client starts, the first thing it does upon connecting to PostgreSQL is to run the schema creation script.
This script has been written idempotently -- at least in principle -- such that the multiple clients shouldn't trip over themselves. By in large, this works fine. However, PostgreSQL sometimes detects deadlocks and affected client(s) crash. Looking through the logging, I believe these occur under such a sequence:
- Client A: Begin schema creation transaction
- Client A: Finish schema creation transaction
- Client B: Begin schema creation transaction
- Client A: New transaction that uses schema (select from view)
- Client A and B now in deadlock
The logs aren't 100% clear and I can't reproduce this deterministically, but that seems to be what's going on: Client A is trying to
SELECT from a view defined by the schema, but it's deadlocking because Client B is trying to recreate that view (
CREATE OR REPLACE VIEW) in the schema script.
Is it possible to ensure that the schema creation script runs exclusively? Or, is there some other solution (e.g., rather than
CREATE OR REPLACE VIEW, I only
CREATE VIEW once I've determined it doesn't already exist)?