PostgreSQL15. ProcSignalBarrier deadlock in DROP TABLESPACE

Question

I creating a tablespace

create tablespace test_tablespace location '/path/to/tablespace/dir';

Next, I create a table and fill it with data

create table test_table (id numeric, file_name varchar) tablespace test_tablespace;

insert into test_table (id, file_name) values (1,'test_field1'),(2,'test_field2'),(3,'test_field3');

After that, I create a function to delete a table and tablespace using dblink and run it:

CREATE OR REPLACE FUNCTION test_func(par_tablespace_name varchar)
    RETURNS void
    LANGUAGE 'plpgsql'
AS $BODY$
begin
  perform dblink_connect_u('port=5432 user=postgres dbname=test_db');
  perform dblink_exec('drop table test_table');
  perform dblink_exec('drop tablespace '|| par_tablespace_name );
  perform dblink_disconnect();
end;
$BODY$;

select test_func('test_tablespace');

This leads to a deadlock.

In pg_stat_activity:

pid	wait_event_type	wait_event	query
609024	null	null	select test_func('test_tablespace');
610910	IPC	ProcSignalBarrier	drop tablespace test_tablespace

pg_blocking_pids(610910) is null

In postgres log:

[610910] STATEMENT: drop tablespace test_tablespace [610910] LOG: still waiting for backend with PID 609024 to accept ProcSignalBarrier

In PostgreSQL 13 it worked fine. If the tablespace is empty (without datafiles), then it runs without problems. Why does this happen and how can I delete a tablespace through a function in another session?

Answer 1

This is probably caused by commit 4eb2176318d, as mentioned in the comment. DROP TABLESPACE is waiting for a confirmation from the session that performs the dblink_exec() call, but that won't be sent until the function call is done. Can you confirm that the operation works if you connect with dblink from a different cluster?

You'll probably have to think of a different way to do things. If you say that you have no problems if the tablespace is empty, perhaps you can close the dblink connection between dropping the tables and dropping the tablespace.