Re: [PATCH] Automatic HASH and LIST partition creation

>
>
> 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
> <https://github.com/pgpartman/pg_partman/blob/master/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) ;

Result:
---
city_prt1
city_prt2
...
city_other

--
Best regards,
Pavel Borisov

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