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 1ppGdL-0007PV-Md for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 22:50:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ppGdK-0006ku-DO for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 22:50:38 +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 1ppGdK-0006kl-1I for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 22:50:38 +0000 Received: from smtprelay0240.b.hostedemail.com ([64.98.42.240] 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 1ppGdF-003vKD-Vc for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 22:50:36 +0000 Received: from omf20.b.hostedemail.com (omf20 [10.200.6.83]) by unirelay02.b.hostedemail.com (Postfix) with ESMTP id 8DAC8A1369; Wed, 19 Apr 2023 22:50:30 +0000 (UTC) Received: from [HIDDEN] (Authenticated sender: beansboy@cruzio.com) by omf20.b.hostedemail.com (Postfix) with ESMTPA id 8413380004; Wed, 19 Apr 2023 22:50:29 +0000 (UTC) MIME-Version: 1.0 Date: Wed, 19 Apr 2023 22:50:29 +0000 From: Jay Stanley To: Tom Lane Cc: pgsql-general@lists.postgresql.org Subject: Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? In-Reply-To: <3024332.1681940550@sss.pgh.pa.us> References: <3024332.1681940550@sss.pgh.pa.us> Message-ID: <2ed2c4ff76982806b708e8b9ae428a40@cruzio.com> X-Sender: beansboy@cruzio.com Content-Type: multipart/alternative; boundary="=_b2c895ca9449b9f1b01f61e814a4fc50" X-Rspamd-Queue-Id: 8413380004 X-Spam-Status: No, score=-3.90 X-Stat-Signature: ygf4q13kzspmxhpeszk168jiwueq88pd X-Rspamd-Server: rspamout08 X-Session-Marker: 6265616E73626F79406372757A696F2E636F6D X-Session-ID: U2FsdGVkX19neMvkJqiLyI8/Q9qxIQE7lsIwpzIM6zc= X-HE-Tag: 1681944629-368506 X-HE-Meta: U2FsdGVkX18Gyazl5AkrbixKMWMOYbL3IMeHj1734vS6UtlqS+LiH5twmUjdKiTJyzLDFMJLE7D5z77/VfgKYg== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_b2c895ca9449b9f1b01f61e814a4fc50 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed On 2023-04-19 21:42, Tom Lane wrote: > Jay Stanley writes: > >> 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. > > I think this has little to do with the funny table names, and much > to do with your being careless about which schema the partitions > end up in. We intentionally don't constrain partitions to live > in the same schema as their parent. So when you do > >> 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); > > the parent "my_table" is in "my_schema", but the partitions are > (probably) in schema "public". Your catalog-investigation query > doesn't show that, adding to your confusion. The commands > that don't work for you are failing because you assume the > partitions are in "my_schema", except in some places where > you leave that off, and then it does work because public > is in your search_path. > > regards, tom lane Thanks, Tom! > the parent "my_table" is in "my_schema", but the partitions are > (probably) in schema "public". You are correct -- that example is putting the partition in the first schema in the search_path (cycdba in this case) - I apologies for the not ideal example. The name of the partition created in the search_path schema does contain the schema name: postgres=# select oid,relname,relnamespace,relnamespace::regnamespace::text as text_schema,reltype from pg_class where relname like '%should_not_work%'; oid | relname | relnamespace | text_schema | reltype --------+-----------------------------------------+--------------+-------------+--------- 184482 | my_schema.my_table_should_not_work_pkey | 16612 | cycdba | 0 184479 | my_schema.my_table_should_not_work | 16612 | cycdba | 184481 (2 rows) Modifying my example a bit, I can make it end up in my_schema: postgres=#create procedure my_schema.test() language plpgsql as $BODY$ begin execute format('create table %I.%I partition of %I.%I for values from (%s) to (%s)','my_schema','my_schema.my_table_should_not_work','my_schema','my_table','100','200'); end; $BODY$; postgres-# postgres-# postgres$# postgres$# postgres$# postgres$# CREATE PROCEDURE postgres=# call my_schema.test(); postgres=# CALL postgres=# select oid,relname,relnamespace,relnamespace::regnamespace::text as text_schema,reltype from pg_class where relname like '%should_not_work%'; oid | relname | relnamespace | text_schema | reltype --------+-----------------------------------------+--------------+-------------+--------- 184978 | my_schema.my_table_should_not_work_pkey | 184954 | my_schema | 0 184975 | my_schema.my_table_should_not_work | 184954 | my_schema | 184977 (2 rows) After re-testing, I found that double-quoting the table name works for inserts, updates, and deletes: example postgres=# insert into "my_schema"."my_schema.my_table_should_not_work" (i,dat) values (101,'test'); INSERT 0 1 However, it's failing on partition-management SQL like: postgres=# alter table my_schema.my_table drop partition "my_schema"."my_schema.my_table_should_not_work"; ERROR: syntax error at or near ""my_schema"" LINE 1: alter table my_schema.my_table drop partition "my_schema"."m... ^ -or- postgres=# alter table my_schema.my_table drop partition my_schema."my_schema.my_table_should_not_work"; ERROR: syntax error at or near "my_schema" LINE 1: alter table my_schema.my_table drop partition my_schema."my_... I noticed this while maintaining an in-house partition management procedure which was updated from constructing the 'create table... partition' sql using plpgsql format(), rather than constructing it without using format() using more naiive string concatenations. -jay --=_b2c895ca9449b9f1b01f61e814a4fc50 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8

On 2023-04-19 21:42, Tom Lane wrote:

=  
=  

Thanks, Tom!

> the parent "my_table" is in "my_schema", but the partitions are (pr= obably) in schema "public".

You are correct -- that example is putting the partition in the first sc= hema in the search_path (cycdba in this case) - I apologies for the not ide= al example.  The name of the partition created in the search_path sche= ma does contain the schema name: 

postgres=3D# select oid,relname,relnamespace,relnamespace::regnamespace:= :text as text_schema,reltype from pg_class where relname like '%should_not_= work%';
  oid   |             =     relname               &nbs= p; | relnamespace | text_schema | reltype 
--------+-------------= ----------------------------+--------------+-------------+---------
&n= bsp;184482 | my_schema.my_table_should_not_work_pkey |      =  16612 | cycdba      |       0
&nb= sp;184479 | my_schema.my_table_should_not_work      |  =      16612 | cycdba      |  184481
(2 rows)

Modifying my example a bit, I can make it end up in my_schema:
post= gres=3D#create procedure my_schema.test()
language plpgsql
as $BO= DY$
begin
  execute format('create table %I.%I partition of = %I.%I for values from (%s) to (%s)','my_schema','my_schema.my_table_should_= not_work','my_schema','my_table','100','200');
end;
$BODY$;
= postgres-# postgres-# postgres$# postgres$# postgres$# postgres$# CREATE PR= OCEDURE
postgres=3D# call my_schema.test();
postgres=3D# CALL
postgres=3D# select oid,relname,relnamespace,relnamespace::regnamespace::= text as text_schema,reltype from pg_class where relname like '%should_not_w= ork%';
  oid   |             &= nbsp;   relname                = ; | relnamespace | text_schema | reltype 
--------+--------------= ---------------------------+--------------+-------------+---------
&nb= sp;184978 | my_schema.my_table_should_not_work_pkey |       = 184954 | my_schema   |       0
 184975 | my_s= chema.my_table_should_not_work      |       1= 84954 | my_schema   |  184977
(2 rows)

After re-testing, I found that double-quoting the table name works for i= nserts, updates, and deletes: example

postgres=3D# insert into "my_schema"."my_schema.my_table_should_not_work= " (i,dat) values (101,'test');
INSERT 0 1

However, it's failing on partition-management SQL like:


postgres=3D# alter table my_schema.my_table drop partition "my_sch= ema"."my_schema.my_table_should_not_work";
ERROR:  syntax error a= t or near ""my_schema""
LINE 1: alter table my_schema.my_table drop pa= rtition "my_schema"."m...
            &n= bsp;                     =                     ^
-or-

postgres=3D# alter table my_schema.my_table drop partition my_schema."my= _schema.my_table_should_not_work";
ERROR:  syntax error at or nea= r "my_schema"
LINE 1: alter table my_schema.my_table drop partition my= _schema."my_...
           

I noticed this while maintaining an in-house partition management proced= ure which was updated from constructing the 'create table... partition' sql= using plpgsql format(), rather than constructing it without using format()= using more naiive string concatenations.

-jay

--=_b2c895ca9449b9f1b01f61e814a4fc50--