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 1ppFZa-0004lK-Hl for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 21:42:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ppFZZ-0005HZ-FY for pgsql-general@arkaria.postgresql.org; Wed, 19 Apr 2023 21:42:41 +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 1ppFZZ-0005HO-4Q for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 21:42:41 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ppFZS-003uHo-AN for pgsql-general@lists.postgresql.org; Wed, 19 Apr 2023 21:42:40 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 33JLgUWK3024333; Wed, 19 Apr 2023 17:42:30 -0400 From: Tom Lane To: Jay Stanley 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: References: Comments: In-reply-to Jay Stanley message dated "Wed, 19 Apr 2023 21:12:36 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3024331.1681940550.1@sss.pgh.pa.us> Date: Wed, 19 Apr 2023 17:42:30 -0400 Message-ID: <3024332.1681940550@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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