The first future partition is created with wrong index name using partman.create_parent

[yulia-simkin-deel]

Hi,
We use pg_partman of version 5.1.0.
We try to convert the regular table to be partitioned using the attached scripts.
I noticed that the index name of first future partition is created with the wrong name and my script doesn't succeed to rename it. It happens only for this partition, other future partition indexes are created with correct name.

The wrong index name is screening_logs_partitioned_p2025110_screening_id_created_at_idx.
It should be screening_logs_partitioned_p20251101_screening_id_created_at (1 is missing in the partition name, regarding a new added suffix, it's ok - we will rename the parent table index name to have the correct suffix).
Can you explain the root case, seems like a bug. I tested it for several times and got the same result.
Thanks!

Original table:

CREATE TABLE public.screening_logs (
	screening_id int4 NULL,
	created_at timestamptz NOT NULL DEFAULT now(),
	status varchar(255) NULL
	CONSTRAINT screening_logs_screening_id_fkey FOREIGN KEY (screening_id) REFERENCES public."Screenings"(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX screening_logs_screening_id_created_at ON public.screening_logs(screening_id, created_at DESC);

DO $$
DECLARE
	l_first_future_partition TEXT;
BEGIN

    SET time zone 'UTC';

    SELECT to_char((date_trunc('month', now()) + INTERVAL '1 month'),'YYYYMMDD') INTO l_first_future_partition;

    CREATE TABLE public.screening_logs_backup AS TABLE public.screening_logs;
    
    CREATE TABLE public.screening_logs_partitioned(
	screening_id int4 NULL,
	created_at timestamptz NOT NULL DEFAULT now(),
	status varchar(255) NULL
	) PARTITION BY RANGE(created_at);

    CREATE UNIQUE INDEX screening_logs_partitioned_screening_id_created_at ON public.screening_logs_partitioned USING btree (screening_id, created_at DESC);

    ALTER TABLE public.screening_logs_partitioned ADD CONSTRAINT screening_logs_partitioned_screening_id_fkey FOREIGN KEY (screening_id) REFERENCES public."Screenings"(id) ON DELETE CASCADE;

    CREATE TABLE public.screening_logs_template (LIKE public.screening_logs);

    PERFORM partman.create_parent(
    p_parent_table := 'public.screening_logs_partitioned', 
    p_control := 'created_at', 
    p_interval := '1 month', 
    p_template_table:= 'public.screening_logs_template', 
    p_premake := 1, 
    p_start_partition := l_first_future_partition,
    p_default_table := false);

    UPDATE partman.part_config 
    SET parent_table = 'public.screening_logs', 
        premake = 3, inherit_privileges = TRUE, 
        infinite_time_partitions = TRUE,
        retention = '6 months',
        retention_keep_table = FALSE,
        retention_keep_index = FALSE 
        WHERE parent_table = 'public.screening_logs_partitioned';

    LOCK TABLE public.screening_logs IN ACCESS EXCLUSIVE MODE;
    LOCK TABLE public.screening_logs_partitioned IN ACCESS EXCLUSIVE MODE;

    ALTER TABLE public.screening_logs RENAME TO screening_logs_default;

    ALTER TABLE public.screening_logs_partitioned RENAME TO screening_logs;
    
    EXECUTE FORMAT('ALTER TABLE public.screening_logs_partitioned_p%1$s RENAME TO screening_logs_p%1$s',l_first_future_partition);

    ALTER TABLE public.screening_logs ATTACH PARTITION public.screening_logs_default DEFAULT;
	
	ALTER TABLE public.screening_logs RENAME CONSTRAINT screening_logs_partitioned_screening_id_fkey TO screening_logs_screening_id_fkey;
	
	ALTER INDEX public.screening_logs_screening_id_created_at RENAME TO screening_logs_default_screening_id_created_at_idx;
	
	ALTER INDEX public.screening_logs_partitioned_screening_id_created_at RENAME TO screening_logs_screening_id_created_at;

    COMMIT;

END; $$

Second script:
DO $$

BEGIN

    SET time zone 'UTC';

    CALL partman.partition_data_proc('public.screening_logs', p_loop_count := 200);
    PERFORM partman.run_maintenance('public.screening_logs');

END; $$

[keithf4]

You are running into the 64 byte (not character) limit in PostgreSQL object names. For automatic naming for indexes like this, I believe it will truncate the original object name to add on the column and idx suffixes.

[keithf4]

https://www.postgresql.org/docs/current/datatype-character.html

[keithf4]

Here's an example to show you that it's PG doing the truncation

keith=#     CREATE TABLE public.screening_logs_partitioned(
        screening_id int4 NULL,
        created_at timestamptz NOT NULL DEFAULT now(),
        status varchar(255) NULL
        ) PARTITION BY RANGE(created_at);
CREATE TABLE

keith=# select partman.create_parent(
    p_parent_table := 'public.screening_logs_partitioned', 
    p_control := 'created_at', 
    p_interval := '1 month',  
    p_premake := 1, 
    p_default_table := false);
 create_parent 
---------------
 t
(1 row)

keith=# \d+ screening_logs_partitioned
                                     Partitioned table "public.screening_logs_partitioned"
    Column    |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 screening_id | integer                  |           |          |         | plain    |             |              | 
 created_at   | timestamp with time zone |           | not null | now()   | plain    |             |              | 
 status       | character varying(255)   |           |          |         | extended |             |              | 
Partition key: RANGE (created_at)
Not-null constraints:
    "screening_logs_partitioned_created_at_not_null" NOT NULL "created_at"
Partitions: screening_logs_partitioned_p20250901 FOR VALUES FROM ('2025-09-01 00:00:00-04') TO ('2025-10-01 00:00:00-04'),
            screening_logs_partitioned_p20251001 FOR VALUES FROM ('2025-10-01 00:00:00-04') TO ('2025-11-01 00:00:00-04'),
            screening_logs_partitioned_p20251101 FOR VALUES FROM ('2025-11-01 00:00:00-04') TO ('2025-12-01 00:00:00-05')

keith=# create index on screening_logs_partitioned_p20250901 (screening_id,created_at);
CREATE INDEX

keith=# \d+ screening_logs_partitioned_p20250901
                                      Table "public.screening_logs_partitioned_p20250901"
    Column    |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 screening_id | integer                  |           |          |         | plain    |             |              | 
 created_at   | timestamp with time zone |           | not null | now()   | plain    |             |              | 
 status       | character varying(255)   |           |          |         | extended |             |              | 
Partition of: screening_logs_partitioned FOR VALUES FROM ('2025-09-01 00:00:00-04') TO ('2025-10-01 00:00:00-04')
Partition constraint: ((created_at IS NOT NULL) AND (created_at >= '2025-09-01 00:00:00-04'::timestamp with time zone) AND (created_at < '2025-10-01 00:00:00-04'::timestamp with time zone))
Indexes:
    "screening_logs_partitioned_p2025090_screening_id_created_at_idx" btree (screening_id, created_at)
Not-null constraints:
    "screening_logs_partitioned_created_at_not_null" NOT NULL "created_at" (inherited)
Access method: heap

[keithf4]

For the automatic naming, PG should handle naming collisions if they occur. Can see that here just making the same index again

keith=# create index on screening_logs_partitioned_p20250901 (screening_id,created_at);
CREATE INDEX

keith=# \d+ screening_logs_partitioned_p20250901
                                      Table "public.screening_logs_partitioned_p20250901"
    Column    |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 screening_id | integer                  |           |          |         | plain    |             |              | 
 created_at   | timestamp with time zone |           | not null | now()   | plain    |             |              | 
 status       | character varying(255)   |           |          |         | extended |             |              | 
Partition of: screening_logs_partitioned FOR VALUES FROM ('2025-09-01 00:00:00-04') TO ('2025-10-01 00:00:00-04')
Partition constraint: ((created_at IS NOT NULL) AND (created_at >= '2025-09-01 00:00:00-04'::timestamp with time zone) AND (created_at < '2025-10-01 00:00:00-04'::timestamp with time zone))
Indexes:
    "screening_logs_partitioned_p2025090_screening_id_created_at_idx" btree (screening_id, created_at)
    "screening_logs_partitioned_p202509_screening_id_created_at_idx1" btree (screening_id, created_at)
Not-null constraints:
    "screening_logs_partitioned_created_at_not_null" NOT NULL "created_at" (inherited)
Access method: heap