Thread: BUG #15968: Create table if not exists throws "relation already exists" while running in parallel transactions

The following bug has been logged on the website:

Bug reference:      15968
Logged by:          Vladimir Aleshin
Email address:      vovik0134@gmail.com
PostgreSQL version: 9.6.14
Operating system:   Linux
Description:

I'm using inheritance based partitioning within before insert trigger for
dynamically partition creation


Setup script:


create table base_table (
    id bigint primary key,
    dt timestamptz not null,
    data text
);

create view base_table_v as select * from base_table;


create or replace function partition_router()
    returns trigger language plpgsql security definer as
$function$
declare
    v_partition_min_dt timestamptz;
    v_partition_max_dt timestamptz;
    v_partition_name text;
    v_row base_table;
begin
    if tg_op = 'UPDATE' then
        raise exception 'update operation is not supported';
    end if;

    v_partition_min_dt := date_trunc('day', new.dt);
    v_partition_max_dt := date_trunc('day', new.dt + interval '1 day');

    v_partition_name := format('partition_table_%s',
to_char(v_partition_min_dt, 'YYYY_MM_DD'));

    loop
        begin
            execute format($$
                insert into %I (id, dt, data)
                values ($1, $2, $3) on conflict do nothing
                returning id, dt, data
            $$, v_partition_name)
            using new.id, new.dt, new.data
            into v_row.id, v_row.dt, v_row.data;

            exit;
        exception when undefined_table then
            execute format($$
                create table if not exists %I (
                    id   bigint primary key,
                    dt   timestamptz null,
                    data text not null
                    check ('%s'::timestamptz <= dt and dt <
'%s'::timestamptz)
                ) inherits (base_table);
            $$, v_partition_name, v_partition_min_dt, v_partition_max_dt);
        end;
    end loop;

    return case when v_row.id is not null then v_row else null end;
end;
$function$;

create trigger partition_router_trg
    instead of insert or update on base_table_v
    for each row execute procedure partition_router();


Reproduce scenario:

Env:
$ uname -a
Linux 66eb0fb650bc 4.15.0-54-generic #58-Ubuntu SMP Mon Jun 24 10:55:24 UTC
2019 x86_64 GNU/Linux

$ psql -c "select version()"
                                                           version
                           
--------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.14 on x86_64-pc-linux-gnu (Debian 9.6.14-1.pgdg80+1),
compiled by gcc (Debian 4.9.2-10+d
eb8u2) 4.9.2, 64-bit
(1 row)

$ psql -c "show default_transaction_isolation"
 default_transaction_isolation 
-------------------------------
 read committed
(1 row)



If there ara inserts in parallel transactions that should create new
partition on of the transactions fails with error

T1 - transaction one, T2 - transaction two

T1: postgres=# begin;
T1: BEGIN
T1: postgres=# insert into base_table_v (id, dt, data) values (1, now(),
'Hello');   NOTICE:  merging column "id" with inherited definition
T1: NOTICE:  merging column "dt" with inherited definition
T1: NOTICE:  merging column "data" with inherited definition
T1: INSERT 0 1
T2: BEGIN
T2: postgres=# insert into base_table_v (id, dt, data) values (2, now(),
'World'); -- waits on lock
T1: postgres=# commit;
T1: COMMIT
T2: NOTICE:  merging column "id" with inherited definition
T2: NOTICE:  merging column "dt" with inherited definition
T2: NOTICE:  merging column "data" with inherited definition
T2: ERROR:  relation "partition_table_2019_08_20" already exists
T2: CONTEXT:  SQL statement "
T2:                 create table if not exists partition_table_2019_08_20
(
T2:                     id   bigint primary key,
T2:                     dt   timestamptz null,
T2:                     data text not null
T2:                     check ('2019-08-20 00:00:00+03'::timestamptz <= dt
and dt < '2019-08-21 00:00:00+03'::timestamptz)
T2:                 ) inherits (base_table);
T2:             "
T2: PL/pgSQL function partition_router() line 29 at EXECUTE

I'm using if not exists clause, so I expect that T2 should skip creation and
do insert after lock is released