Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?

On 2023-04-19 21:42, Tom Lane wrote:

Jay Stanley <beansboy@cruzio.com> writes:

I've come across some interesting behavior with regards to creating a
partition of a table that includes the schema name and a period in the
beginning, so that the resulting name is like
"my_schema"."my_schema.my_table_should_not_work".
After created it, most SQL won't access it at all, even when
double-quoting the table name exactly, though drop seems to work.

I think this has little to do with the funny table names, and much
to do with your being careless about which schema the partitions
end up in.  We intentionally don't constrain partitions to live
in the same schema as their parent.  So when you do

create schema my_schema;

create table my_schema.my_table(
   i  bigint not null primary key,
   dat  text)
   partition by range(i);

create table my_table_default partition of my_schema.my_table DEFAULT;
create table my_table_1 partition of my_schema.my_table for values from
(1) to (100);

the parent "my_table" is in "my_schema", but the partitions are
(probably) in schema "public".  Your catalog-investigation query
doesn't show that, adding to your confusion.  The commands
that don't work for you are failing because you assume the
partitions are in "my_schema", except in some places where
you leave that off, and then it does work because public
is in your search_path.

            regards, tom lane

 

 

Thanks, Tom!

> the parent "my_table" is in "my_schema", but the partitions are (probably) in schema "public".

You are correct -- that example is putting the partition in the first schema in the search_path (cycdba in this case) - I apologies for the not ideal example.  The name of the partition created in the search_path schema does contain the schema name: 

postgres=# select oid,relname,relnamespace,relnamespace::regnamespace::text as text_schema,reltype from pg_class where relname like '%should_not_work%';
  oid   |                 relname                 | relnamespace | text_schema | reltype 
--------+-----------------------------------------+--------------+-------------+---------
 184482 | my_schema.my_table_should_not_work_pkey |        16612 | cycdba      |       0
 184479 | my_schema.my_table_should_not_work      |        16612 | cycdba      |  184481
(2 rows)

Modifying my example a bit, I can make it end up in my_schema:
postgres=#create procedure my_schema.test()
language plpgsql
as $BODY$
begin
  execute format('create table %I.%I partition of %I.%I for values from (%s) to (%s)','my_schema','my_schema.my_table_should_not_work','my_schema','my_table','100','200');
end;
$BODY$;
postgres-# postgres-# postgres$# postgres$# postgres$# postgres$# CREATE PROCEDURE
postgres=# call my_schema.test();
postgres=# CALL
postgres=# select oid,relname,relnamespace,relnamespace::regnamespace::text as text_schema,reltype from pg_class where relname like '%should_not_work%';
  oid   |                 relname                 | relnamespace | text_schema | reltype 
--------+-----------------------------------------+--------------+-------------+---------
 184978 | my_schema.my_table_should_not_work_pkey |       184954 | my_schema   |       0
 184975 | my_schema.my_table_should_not_work      |       184954 | my_schema   |  184977
(2 rows)

After re-testing, I found that double-quoting the table name works for inserts, updates, and deletes: example

postgres=# insert into "my_schema"."my_schema.my_table_should_not_work" (i,dat) values (101,'test');
INSERT 0 1

However, it's failing on partition-management SQL like:

postgres=# alter table my_schema.my_table drop partition "my_schema"."my_schema.my_table_should_not_work";
ERROR:  syntax error at or near ""my_schema""
LINE 1: alter table my_schema.my_table drop partition "my_schema"."m...
                                                      ^
-or-

postgres=# alter table my_schema.my_table drop partition my_schema."my_schema.my_table_should_not_work";
ERROR:  syntax error at or near "my_schema"
LINE 1: alter table my_schema.my_table drop partition my_schema."my_...
           

I noticed this while maintaining an in-house partition management procedure which was updated from constructing the 'create table... partition' sql using plpgsql format(), rather than constructing it without using format() using more naiive string concatenations.

-jay