Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux