Обсуждение: Question about accessing partitions whose name includes the schema name and a period - is this correct?

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.  It's very repeatable at least in versions up to v14.5.

Here's a script that demonstrates the issue -- in pl/pgsql, I'm able to create a partition on a table that has the schema and period included by using the format statement, then I'm unable to access the partition at all:

<begin>

drop schema if exists my_schema cascade;

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);

SELECT pt.relname AS partition_name
FROM pg_class pc
JOIN pg_inherits i ON i.inhparent = pc.oid
JOIN pg_class pt ON pt.oid = i.inhrelid
AND pc.relnamespace::regnamespace::text = 'my_schema';

create procedure my_schema.test()
language plpgsql
as $BODY$
begin
  execute format('create table %I partition of %I.%I for values from (%s) to (%s)','my_schema.my_table_should_not_work','my_schema','my_table','100','200');
end;
$BODY$;

call my_schema.test();

SELECT pt.relname AS partition_name
FROM pg_class pc
JOIN pg_inherits i ON i.inhparent = pc.oid
JOIN pg_class pt ON pt.oid = i.inhrelid
AND pc.relnamespace::regnamespace::text = 'my_schema';

alter table "my_schema"."my_schema.my_table_should_not_work" rename to 'fixed;

alter table my_schema.my_table detach partition "my_schema.my_table_should_not_work";

select count(*) from "my_schema"."my_schema.my_table_should_not_work";

<end>

When run, the partition name ends up like this:

SELECT pt.relname AS partition_name
FROM pg_class pc
JOIN pg_inherits i ON i.inhparent = pc.oid
JOIN pg_class pt ON pt.oid = i.inhrelid
AND pc.relnamespace::regnamespace::text = 'my_schema';
             partition_name              
-----------------------------------------
 my_table_default
 my_table_default_pkey
 my_table_1
 my_table_1_pkey
 my_schema.my_table_should_not_work
 my_schema.my_table_should_not_work_pkey
(6 rows)

and, none of the 'alter table' SQL works, similar to this error: it just can't find it, even with correct quoting:

SQL> select count(*) from "my_schema"."my_schema.my_table_should_not_work";
ERROR:  relation "my_schema.my_schema.my_table_should_not_work" does not exist

Does anyone have any insights regarding how this partition could be renamed or detached?  I've reviewed the mailing list archive and other sources and haven't been able to find anything similar.

Thanks everyone!

-Jay Stanley, DBA
-Cycorp: The Why behind AI

Long-time lurker, first-time poster.

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

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

> On 20/04/2023 00:50 CEST Jay Stanley <beansboy@cruzio.com> wrote:
>
> 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_...

The command you're looking for is ALTER TABLE DETACH PARTITION.

DROP PARTITION means dropping the column named "partition".  The syntax error
comes from the parser expecting an optional CASCADE or RESTRICT after DROP PARTITION.

--
Erik

Jay Stanley <beansboy@cruzio.com> writes:
> 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...
>                                                        ^

I'm not sure why the error cursor is pointing there, but
"drop partition" is not a valid subcommand.  Try "detach
partition".

            regards, tom lane