I am developing software that launches multiple simultaneous clients to connect to a PostgreSQL database (12). When each client starts up, the first thing it does when connecting to PostgreSQL is to run the schema creation script.
This script was written in an idempotent way – at least in principle – so that the multiple clients do not have to trip over themselves. Basically, it works well. However, PostgreSQL sometimes detects deadlocks and crashes of affected customers. Looking through the logging, I think these happen in such a sequence:
- Client A: start the schema creation transaction
- Client A: complete the schema creation transaction
- Client B: start the schema creation transaction
- Client A: new transaction using the schema (select in the view)
- Customers A and B are now at an impasse
The logs are not 100% clear and I cannot reproduce that deterministically, but that seems to be what is happening: Client A is trying to
SELECT from a view defined by the schema, but it is a blockage because client B tries to recreate this view (
CREATE OR REPLACE VIEW) in the schema script.
Is there any way to make sure that the schema creation script runs exclusively? Or, is there another solution (for example, rather than
CREATE OR REPLACE VIEW, I only
CREATE VIEW once I have determined that it does not already exist)?