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