Thread: BUG #16771: Server process killed by signal 9, after recovery drop tablespace failed

The following bug has been logged on the website:

Bug reference:      16771
Logged by:          Bo Chen
Email address:      bchen90@163.com
PostgreSQL version: 11.8
Operating system:   euleros v2r7 x86_64
Description:

hi 

I encounter a problem of  drop tablespace failed for reasion 'tablespace is
not empty'. The problem occurs  in the following scenarios:

I start a transaction to create some table located in an alreaedy created
tablespace, before the transaction commits, the process of creating data is
killed by somebady with signal 9. when the database recoveried I try to drop
the tablesapce, it failed for 'tablespace is not empty'.

Does this bug need to be resolved ?  



regards,  ChenBo 


Following  is the scenarios:

postgres=# start transaction;
START TRANSACTION
postgres=# create table test1(id text) tablespace test;
create table test2(id text) tablespace test;
create table test3(id text) tablespace test;
create table test4(id text) tablespace test;
create table test5(id text) tablespace test;
create table test6(id text) tablespace test;
create table test7(id text) tablespace test;
create table test8(id text) tablespace test;
create table test9(id text) tablespace test;CREATE TABLE
postgres=# CREATE TABLE
postgres=# CREATE TABLE
postgres=# CREATE TABLE
postgres=# CREATE TABLE
postgres=# CREATE TABLE
postgres=# CREATE TABLE
postgres=# CREATE TABLE
postgres=# 
CREATE TABLE
postgres=# 2020-12-11 19:10:54.408 UTC [2909] LOG:  server process (PID
2921) was terminated by signal 9: Killed



[postgres@host-192-168-0-7 data]$ psql -d postgres -U postgres -p 5432
psql (11.8)
Type "help" for help.

postgres=# drop tablespace test;
2020-12-11 19:11:36.518 UTC [3299] ERROR:  tablespace "test" is not empty
2020-12-11 19:11:36.518 UTC [3299] STATEMENT:  drop tablespace test;
ERROR:  tablespace "test" is not empty
postgres=# select * from test1;
2020-12-11 19:11:48.684 UTC [3299] ERROR:  relation "test1" does not exist
at character 15
2020-12-11 19:11:48.684 UTC [3299] STATEMENT:  select * from test1;
ERROR:  relation "test1" does not exist
LINE 1: select * from test1;
                      ^
postgres=#

Hello.

At Fri, 11 Dec 2020 11:34:47 +0000, PG Bug reporting form <noreply@postgresql.org> wrote in 
> The following bug has been logged on the website:
> 
> Bug reference:      16771
> Logged by:          Bo Chen
> Email address:      bchen90@163.com
> PostgreSQL version: 11.8
> Operating system:   euleros v2r7 x86_64
> Description:        
> 
> hi 
> 
> I encounter a problem of  drop tablespace failed for reasion 'tablespace is
> not empty'. The problem occurs  in the following scenarios:
> 
> I start a transaction to create some table located in an alreaedy created
> tablespace, before the transaction commits, the process of creating data is
> killed by somebady with signal 9. when the database recoveried I try to drop
> the tablesapce, it failed for 'tablespace is not empty'.
> 
> Does this bug need to be resolved ?  

It seems to be a known behavior that hasn't been fixed for a long time.

It comes from a mechanism in PostgreSQL called "pending deletes", by
which deletion of underlynig files is delayed until commit/abort
time. Since the "to-be-deleted at abort" infomation is not persistent,
it should be lost if the server crashed before the transaction ends.

I happend to be proposing a patch [*1] for another issue and I
realized that the approach could solve this issue as well.

*1: https://www.postgresql.org/message-id/20201225.091252.53717619425847881.horikyota.ntt%40gmail.com

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center