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(at)cruzio(dot)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