Re: [PATCH] Automatic HASH and LIST partition creation

>
> Sorry for not being clear earlier, I mean the partition name
> 'tablename_partnum' can conflict with any existing table name.
> As per current impemetation, if I do the following it results in the table
> name conflict.
>
> postgres=# create table tbl_test_5_1(i int);
> CREATE TABLE
> postgres=# CREATE TABLE tbl_test_5 (i int) PARTITION BY LIST((tbl_test_5))
>
> CONFIGURATION (values in
> ('(1)'::tbl_test_5), ('(3)'::tbl_test_5) default partition tbl_default_5);
> ERROR: relation "tbl_test_5_1" already exists
>

Basically, it's the same thing when you try to create two tables with the
same name. It is not specific to partition creation and common for every
case that using any defaults, they can conflict with something existing.
And in this case this conflict is explicitly processes as I see from output
message.

In fact in PG there are other places when names are done in default way
e.g. in aggregates regression test it is not surprise to find in PG13:

explain (costs off)
select min(f1), max(f1) from minmaxtest;
QUERY PLAN
---------------------------------------------------------------------------------------------
Result
InitPlan 1 (returns $0)
-> Limit
-> Merge Append
Sort Key: minmaxtest.f1
-> Index Only Scan using minmaxtesti on minmaxtest
minmaxtest_1
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest1i on minmaxtest1
minmaxtest_2
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest2i on
minmaxtest2 minmaxtest_3
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest3i on minmaxtest3
minmaxtest_4
InitPlan 2 (returns $1)
-> Limit
-> Merge Append
Sort Key: minmaxtest_5.f1 DESC
-> Index Only Scan Backward using minmaxtesti on
minmaxtest minmaxtest_6
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest1i on
minmaxtest1 minmaxtest_7
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest2i on minmaxtest2
minmaxtest_8
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest3i on
minmaxtest3 minmaxtest_9

where minmaxtest_<number> are the temporary relations
and minmaxtest<number> are real partition names (last naming is unrelated
to first)

Overall I don't see much trouble in any form of automatic naming. But there
may be a convenience to provide fixed user-specified prefix to partition
names.

Thank you,
--
Best regards,
Pavel Borisov

Postgres Professional: http://postgrespro.com <http://www.postgrespro.com>