2. One suggestion for generation of partition names is to append a unique id to
avoid conflicts.
Can you please give an example of such a conflict? I agree that current naming scheme is far from perfect, but I think that 'tablename'_partnum provides unique name for each partition.
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
I don't mind adding some specific suffix for generated partitions, although it still may conflict with existing table names. The main disadvantage of this idea, is that it reduces number of symbols available for table name, which can lead to something like this:
CREATE TABLE parteddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd (a text, b int NOT NULL DEFAULT 0, CONSTRAINT check_aa CHECK (length(a) > 0)) PARTITION BY LIST (a) CONFIGURATION (VALUES IN ('a','b'),('c','d') DEFAULT PARTITION parted_def) ;; NOTICE: identifier "parteddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd" will be truncated to "partedddddddddddddddddddddddddddddddddddddddddddddddddddddddddd" ERROR: relation "partedddddddddddddddddddddddddddddddddddddddddddddddddddddddddd" already exists doc/pg_partman.md#naming-length-limits
It seems to me that a working idea is to add a prefix to partitions is to give the possibility to specify it for users. So the user will be able to choose appropriate and not very long suffix to avoid conflicts.
Maybe like this:
CREATE TABLE city (a text) PARTITION BY LIST (a) CONFIGURATION (VALUES IN ('a','b'),('c','d') DEFAULT PARTITION city_other PREFIX _prt) ;
