pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.

view thread (5+ messages)  latest in thread

Message-ID: <f32e666f0a7696422f501e0d7dbbc2de@cruzio.com>
Permalink:  ../f32e666f0a7696422f501e0d7dbbc2de@cruzio.com/
Also on:    postgresql.org/message-id/f32e666f0a7696422f501e0d7dbbc2de@cruzio.com

 · 

reply

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