|
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.
| 