|
On 2023-04-19 21:42, Tom Lane wrote:
Jay Stanley < beansboy@xxxxxxxxxx> 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
|
