From: Jay Stanley <beansboy@cruzio.com>
To: pgsql-general@lists.postgresql.org
Subject: Question about accessing partitions whose name includes the schema name and a period - is this correct?
Date: Wed, 19 Apr 2023 21:12:36 +0000
Message-ID: <f32e666f0a7696422f501e0d7dbbc2de@cruzio.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: beansboy@cruzio.com
Subject: Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?
In-Reply-To: <f32e666f0a7696422f501e0d7dbbc2de@cruzio.com>
References: <f32e666f0a7696422f501e0d7dbbc2de@cruzio.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox