pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Question about accessing partitions whose name includes the schema name  and a period - is this correct?
5+ messages / 3 participants
[nested] [flat]

* Question about accessing partitions whose name includes the schema name  and a period - is this correct?
@ 2023-04-19 21:12 Jay Stanley <beansboy@cruzio.com>
  2023-04-19 21:42 ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Jay Stanley @ 2023-04-19 21:12 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org



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.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?
  2023-04-19 21:12 Question about accessing partitions whose name includes the schema name  and a period - is this correct? Jay Stanley <beansboy@cruzio.com>
@ 2023-04-19 21:42 ` Tom Lane <tgl@sss.pgh.pa.us>
  2023-04-19 22:50   ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Jay Stanley <beansboy@cruzio.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2023-04-19 21:42 UTC (permalink / raw)
  To: Jay Stanley <beansboy@cruzio.com>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?
  2023-04-19 21:12 Question about accessing partitions whose name includes the schema name  and a period - is this correct? Jay Stanley <beansboy@cruzio.com>
  2023-04-19 21:42 ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-04-19 22:50   ` Jay Stanley <beansboy@cruzio.com>
  2023-04-20 01:18     ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Erik Wienhold <ewie@ewie.name>
  2023-04-20 04:03     ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 5+ messages in thread

From: Jay Stanley @ 2023-04-19 22:50 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org



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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?
  2023-04-19 21:12 Question about accessing partitions whose name includes the schema name  and a period - is this correct? Jay Stanley <beansboy@cruzio.com>
  2023-04-19 21:42 ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Tom Lane <tgl@sss.pgh.pa.us>
  2023-04-19 22:50   ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Jay Stanley <beansboy@cruzio.com>
@ 2023-04-20 01:18     ` Erik Wienhold <ewie@ewie.name>
  1 sibling, 0 replies; 5+ messages in thread

From: Erik Wienhold @ 2023-04-20 01:18 UTC (permalink / raw)
  To: Jay Stanley <beansboy@cruzio.com>; +Cc: pgsql-general@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>

> On 20/04/2023 00:50 CEST Jay Stanley <beansboy@cruzio.com> wrote:
>
> 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_...

The command you're looking for is ALTER TABLE DETACH PARTITION.

DROP PARTITION means dropping the column named "partition".  The syntax error
comes from the parser expecting an optional CASCADE or RESTRICT after DROP PARTITION.

--
Erik





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Question about accessing partitions whose name includes the schema name and a period - is this correct?
  2023-04-19 21:12 Question about accessing partitions whose name includes the schema name  and a period - is this correct? Jay Stanley <beansboy@cruzio.com>
  2023-04-19 21:42 ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Tom Lane <tgl@sss.pgh.pa.us>
  2023-04-19 22:50   ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Jay Stanley <beansboy@cruzio.com>
@ 2023-04-20 04:03     ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2023-04-20 04:03 UTC (permalink / raw)
  To: Jay Stanley <beansboy@cruzio.com>; +Cc: pgsql-general@lists.postgresql.org

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

I'm not sure why the error cursor is pointing there, but
"drop partition" is not a valid subcommand.  Try "detach
partition".

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2023-04-20 04:03 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-19 21:12 Question about accessing partitions whose name includes the schema name  and a period - is this correct? Jay Stanley <beansboy@cruzio.com>
2023-04-19 21:42 ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-19 22:50   ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Jay Stanley <beansboy@cruzio.com>
2023-04-20 01:18     ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Erik Wienhold <ewie@ewie.name>
2023-04-20 04:03     ` Re: Question about accessing partitions whose name includes the schema name and a period - is this correct? Tom Lane <tgl@sss.pgh.pa.us>

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