Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ppF6d-0003UV-EI for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 21:12:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ppF6c-0002fc-AK for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 21:12:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ppF6b-0002eb-Ue for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 21:12:45 +0000 Received: from smtprelay0241.b.hostedemail.com ([64.98.42.241] helo=relay.b.hostedemail.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ppF6W-003u1J-Lk for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 21:12:44 +0000 Received: from omf16.b.hostedemail.com (omf16 [10.200.6.79]) by unirelay06.b.hostedemail.com (Postfix) with ESMTP id 62B298154C for ; Wed, 19 Apr 2023 21:12:37 +0000 (UTC) Received: from [HIDDEN] (Authenticated sender: beansboy@cruzio.com) by omf16.b.hostedemail.com (Postfix) with ESMTPA id 871A01C0005 for ; Wed, 19 Apr 2023 21:12:36 +0000 (UTC) MIME-Version: 1.0 Date: Wed, 19 Apr 2023 21:12:36 +0000 From: Jay Stanley To: pgsql-general@lists.postgresql.org Subject: Question about accessing partitions whose name includes the schema name and a period - is this correct? Message-ID: X-Sender: beansboy@cruzio.com Content-Type: multipart/alternative; boundary="=_27b6146717694014a7c21b9303b9c5f8" X-Rspamd-Queue-Id: 871A01C0005 X-Spam-Status: No, score=-1.49 X-Stat-Signature: k1mcgkhsua6qy383974eyjomj1e6g7pt X-Rspamd-Server: rspamout06 X-Session-Marker: 6265616E73626F79406372757A696F2E636F6D X-Session-ID: U2FsdGVkX18oQEN2g+pZJV+3MxXbxTe01o9I8VekNWY= X-HE-Tag: 1681938756-732017 X-HE-Meta: U2FsdGVkX1+xXqIHD3pQXEEfN7OD4TzfszJDqcP9dwilH6jkqv3+JYT/x3UPMHgz7Ny1YvoqlrpBpuppLPXGfg== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_27b6146717694014a7c21b9303b9c5f8 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed 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: 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"; 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. --=_27b6146717694014a7c21b9303b9c5f8 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8

I've come across some interesting behavior with regards to creating a pa= rtition of a table that includes the schema name and a period in the beginn= ing, so that the resulting name is like "my_schema"."my_schema.my_table_sho= uld_not_work".

After created it, most SQL won't access it at all, even when double-quot= ing the table name exactly, though drop seems to work.  It's very repe= atable 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 us= ing 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 pri= mary 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_i= nherits i ON i.inhparent =3D pc.oid
JOIN pg_class pt ON pt.oid =3D i.i= nhrelid
AND pc.relnamespace::regnamespace::text =3D 'my_schema';


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

call my_schema.test();

SELECT pt.relname AS partition_name
FROM pg_class pc
JOIN pg_i= nherits i ON i.inhparent =3D pc.oid
JOIN pg_class pt ON pt.oid =3D i.i= nhrelid
AND pc.relnamespace::regnamespace::text =3D 'my_schema';


alter table "my_schema"."my_schema.my_table_should_not_work" renam= e to 'fixed;

alter table my_schema.my_table detach partition "my_schema.my_table_shou= ld_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.rel= name AS partition_name
FROM pg_class pc
JOIN pg_inherits i ON i.i= nhparent =3D pc.oid
JOIN pg_class pt ON pt.oid =3D i.inhrelid
AND= pc.relnamespace::regnamespace::text =3D 'my_schema';
    &n= bsp;        partition_name         =      
-----------------------------------------
&n= bsp;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_shoul= d_not_work";
ERROR:  relation "my_schema.my_schema.my_table_shoul= d_not_work" does not exist


Does anyone have any insights regarding how this partition could b= e renamed or detached?  I've reviewed the mailing list archive and oth= er 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.

--=_27b6146717694014a7c21b9303b9c5f8--