pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jay Stanley <beansboy@cruzio.com>
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 17:42:30 -0400
Message-ID: <3024332.1681940550@sss.pgh.pa.us> (raw)
In-Reply-To: <f32e666f0a7696422f501e0d7dbbc2de@cruzio.com>
References: <f32e666f0a7696422f501e0d7dbbc2de@cruzio.com>

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





view thread (5+ messages)  latest in thread

Message-ID: <3024332.1681940550@sss.pgh.pa.us>
Permalink:  ../3024332.1681940550@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3024332.1681940550@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, beansboy@cruzio.com
  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: <f32e666f0a7696422f501e0d7dbbc2de@cruzio.com> <3024332.1681940550@sss.pgh.pa.us>

* 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