create_parent not working if p_parent_table capitalized

[ogurashi]

Returns

ERROR:  Unable to find given parent table in system catalogs. Please create parent table first: Table.Table

Can probably be resolved by changing

FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid
WHERE n.nspname = split_part(p_parent_table, '.', 1)::name
AND c.relname = split_part(p_parent_table, '.', 2)::name;

To

FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid
WHERE n.nspname = LOWER(split_part(p_parent_table, '.', 1)::name)
AND c.relname = LOWER(split_part(p_parent_table, '.', 2)::name);

[keithf4]

partman has had mixed-case support for quite a long time. Can you please share the version of partman, the schema of the table and the command that you used to run create_parent()?

github688=# \d "Table"."Table" 
                    Partitioned table "Table.Table"
   Column   |           Type           | Collation | Nullable | Default 
------------+--------------------------+-----------+----------+---------
 id         | integer                  |           |          | 
 created_at | timestamp with time zone |           | not null | 
Partition key: RANGE (created_at)
Number of partitions: 0

github688=# select partman.create_parent('Table.Table', 'created_at', '1 day');
 create_parent 
---------------
 t
(1 row)

github688=# \d+ "Table"."Table" 
                                               Partitioned table "Table.Table"
   Column   |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 id         | integer                  |           |          |         | plain   |             |              | 
 created_at | timestamp with time zone |           | not null |         | plain   |             |              | 
Partition key: RANGE (created_at)
Partitions: "Table"."Table_p20240909" FOR VALUES FROM ('2024-09-09 00:00:00-04') TO ('2024-09-10 00:00:00-04'),
            "Table"."Table_p20240910" FOR VALUES FROM ('2024-09-10 00:00:00-04') TO ('2024-09-11 00:00:00-04'),
            "Table"."Table_p20240911" FOR VALUES FROM ('2024-09-11 00:00:00-04') TO ('2024-09-12 00:00:00-04'),
            "Table"."Table_p20240912" FOR VALUES FROM ('2024-09-12 00:00:00-04') TO ('2024-09-13 00:00:00-04'),
            "Table"."Table_p20240913" FOR VALUES FROM ('2024-09-13 00:00:00-04') TO ('2024-09-14 00:00:00-04'),
            "Table"."Table_p20240914" FOR VALUES FROM ('2024-09-14 00:00:00-04') TO ('2024-09-15 00:00:00-04'),
            "Table"."Table_p20240915" FOR VALUES FROM ('2024-09-15 00:00:00-04') TO ('2024-09-16 00:00:00-04'),
            "Table"."Table_p20240916" FOR VALUES FROM ('2024-09-16 00:00:00-04') TO ('2024-09-17 00:00:00-04'),
            "Table"."Table_p20240917" FOR VALUES FROM ('2024-09-17 00:00:00-04') TO ('2024-09-18 00:00:00-04'),
            "Table"."Table_default" DEFAULT

[ogurashi]

Apologies for the delay. I did not get a notification somehow.

I have 5.0.1 on postgres 16.3

The example you gave works fine, I'm talking more about a case when I have the table table.table and I am trying to partition it like below

=# CREATE TABLE t_table.tables_ (datetime timestamp with time zone NOT NULL) PARTITION BY RANGE(datetime); 

CREATE TABLE

=# select partman.create_parent('t_table.Tables_', 'datetime', '1 day');
ERROR:  Unable to find given parent table in system catalogs. Please create parent table first: t_table.Tables_
CONTEXT: PL/pgSQL function partman.create_parent(text,text,text,text,text,integer,text,boolean,text,text[],text,boolean,text) line 84 at RAISE
DETAIL: 
HINT: 
CONTEXT:  PL/pgSQL function partman.create_parent(text,text,text,text,text,integer,text,boolean,text,text[],text,boolean,text) line 629 at RAISE

t=# select partman.create_parent('t_table.Tables_', 'datetime', '1 day');

=# select partman.create_parent('t_table.tables_', 'datetime', '1 day');

 create_parent 
---------------
 t
(1 row)

It's not a big deal, but the psql client allows me to query t_table.Tables so I was getting confused why it wasn't finding the table - the fix is also quite simple - happy to make a PR!

[keithf4]

The table you created is not mixed case

CREATE TABLE t_table.tables_

So when you ask it to partition t_table.Tables_, that table does not exist because partman is case sensitive for the parent table names. While psql automatically collapses object names in standard SQL statements to lower case (unless you double-quote them), to be able to support mixed case/special characters, partman is case-sensitive when you give the table names.

[keithf4]

When you do

SELECT * FROM t_table.Tables_

in psql, it is automatically converting it to lower case to match the table that is not mixed case.

If you instead did

SELECT * FROM "t_table"."Tables_"

you would get the same error you're getting from partman

[ogurashi]

The table you created is not mixed case

CREATE TABLE t_table.tables_

So when you ask it to partition t_table.Tables_, that table does not exist because partman is case sensitive for the parent table names. While psql automatically collapses object names in standard SQL statements to lower case (unless you double-quote them), to be able to support mixed case/special characters, partman is case-sensitive when you give the table names.

Clear. Makes sense. Could just be a skill issue since i'm used to making queries without the quotation marks around the table names and could not understand why my table was not being found.

[keithf4]

Yeah, psql collapsing all object name cases to lower case is not something people find very intuitive at first. Glad I was able to help clear things up.