pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jay Stanley <beansboy@cruzio.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?
Date: Wed, 19 Apr 2023 22:50:29 +0000
Message-ID: <2ed2c4ff76982806b708e8b9ae428a40@cruzio.com> (raw)
In-Reply-To: <3024332.1681940550@sss.pgh.pa.us>
References: <f32e666f0a7696422f501e0d7dbbc2de@cruzio.com>
	<3024332.1681940550@sss.pgh.pa.us>



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

> Jay Stanley <beansboy@cruzio.com> 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

view thread (5+ messages)  latest in thread

Message-ID: <2ed2c4ff76982806b708e8b9ae428a40@cruzio.com>
Permalink:  ../2ed2c4ff76982806b708e8b9ae428a40@cruzio.com/
Also on:    postgresql.org/message-id/2ed2c4ff76982806b708e8b9ae428a40@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, tgl@sss.pgh.pa.us
  Subject: Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?
  In-Reply-To: <2ed2c4ff76982806b708e8b9ae428a40@cruzio.com>
  References: <f32e666f0a7696422f501e0d7dbbc2de@cruzio.com> <3024332.1681940550@sss.pgh.pa.us> <2ed2c4ff76982806b708e8b9ae428a40@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