0

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:

  1. Client A: Begin schema creation transaction
  2. Client A: Finish schema creation transaction
  3. Client B: Begin schema creation transaction
  4. 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)?

Improve this question
0

2 Answers 2

Reset to default
2

From the comments to my question, the problem can be summarised, per @DanielVérité, as "concurrent DDL is bad; concurrent DDL and DML is really bad". I was not aware of that -- even with the care I took to make the DDL idempotent -- but it's a good thing to know. So, that leaves me with two broad solutions:

  1. Don't do the DDL concurrently (e.g., have a single client instantiate the schema).

    This is probably the "easiest" solution from an application development perspective -- and I see it as my backup plan -- but it has consequences on how the application is architectured. I only want to take this route if I have to.

  2. Use advisory locks, on @a_horse_with_no_name's suggestion.

    This requires a minor modification to my application's database library only, so no large architectural changes or changes to the schema. Its downside is that I have to acquire and release the lock on every transaction (to make sure it isn't already locked by the DDL section of the code), which necessarily serialises all transactions across clients.

I am experimenting with option 2 and it appears to be working. I need to give it a few more runs through to convince myself that the deadlocking has gone and what performance impact the serialisation has (anecdotally, so far, it's negligible). I'll report back with results when I have them...

Improve this answer
1
  • ...I'm seeing no appreciable difference in performance Commented Jan 16, 2020 at 13:37
0

As your trying to create a schema aka DDL commands advisory locks I do not think will work

The best solution i can think of is create a table that tracks the schema creation or queries the pg_catalog directly then wrap this call into a procedure

with a procedure you can begin and commit transactions

Here is my thought process this has not been tested 

create table schema_status (
name_of_schema char(55),
is_being_created bool default false,
);

Create or replace Procedure create_schema(pname_schema text)
language plpgsql
as
$code$

_check bool;

begin
    select is_being_created into _check from schema_status where 
        name_of_schema = pname_schema;
    if found then
        if _check then
            raise exception 'Schema % is being created', pname_schema;
        else
            raise exception 'Schema % has been created', pname_schema;
        end if;   
    end if ;
    begin ;
        insert into schema_status (pname_schema, true);
    commit;

    begin ;
        Call he Create scheam funciton ;
    commit; 
    update schema_status set is_being_created = false where
          name_of_schema = pname_schema;
end;
$code$

Next just call this procedure

call create_schema('new_one');
Improve this answer
4
  • 1
    Why does DDL preclude using advisory locks? Experimentally (see my answer), it seems to work... Commented Jan 15, 2020 at 12:52
  • pg_advisory locks are designed for DML not DDL, the schema does not exist so what is it locking? the advisory locks take in an integer, so its intended to be a primary key or some other integer value that all the clients know and can see the record is locked. Not sure what value is being used to set the lock, but if it is just integer 1 in the function, then all clients will be blocked from schema creation even if its not duplicated Commented Jan 15, 2020 at 17:06
  • 1
    Advisory locks are not "designed" for DML - they are there for any client to synchronize access to something. It's completely irrelevant if the client uses that to synchronize DML statements, DDL statements or uploading videos to YouTube. Quote from the manual: "PostgreSQL provides a means for creating locks that have application-defined meanings" - nothing about DML or "primary keys" Commented Jan 16, 2020 at 13:55
  • 1
    directly below the quote shows a use example SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; and how one can shoot themselves in the foot. I'll quote the manual "These are called advisory locks, because the system does not enforce their use" its intended design is to be used with DML in replacement of explicit locking. Commented Jan 16, 2020 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.