From: Marius RAICU <mariusraicu@laposte.net>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: pgsql-hackers@lists.postgresql.org
Cc: pgsql-bugs@lists.postgresql.org, tender wang <tndrwang@gmail.com>
Subject: Re: BUG #18167: cannot create partitioned tables when default_tablespace is set
Date: Thu, 2 Nov 2023 13:50:55 +0100
Message-ID: <e83d6036-2461-4e76-8108-35bca155357e@laposte.net> (raw)
In-Reply-To: <CAHewXNmE8uRHFBiMXT2o0s19=xHpyc0gTLOf1cdc1C6BwqAwGQ@mail.gmail.com>
References: <18167-fcc9f9f33cddf04d@postgresql.org>
<202310250745.x6mzvzbsk4bz@alvherre.pgsql>
<CAHewXNmE8uRHFBiMXT2o0s19=xHpyc0gTLOf1cdc1C6BwqAwGQ@mail.gmail.com>
Hello Alvaro, all,
I have done some research regarding this bug.
Basically, we forbid the creation of partitioned tables and indexes if a
default_tablespace is specified in postgresql.conf.
In tablespace.c, the comment says:
"Don't allow specifying that when creating a partitioned table, however,
since the result is confusing."
I did not see why the result is confusing.
I just disabled the checks in tablespace.c, tablecmds.c and indexcmds.c
and now it works.
I modified the expected result in tests, and the tests are passing too.
See the attached patch.
Regards,
Marius Raicu
On 10/25/23 11:58, tender wang wrote:
>
>
> Alvaro Herrera <alvherre@alvh.no-ip.org> 于2023年10月25日周三 17:41写道：
>
> On 2023-Oct-24, PG Bug reporting form wrote:
>
> > marius@[local]:5434/postgres=# show default_tablespace;
> > default_tablespace
> > --------------------
> > tblspc1
> > (1 row)
> >
> > marius@[local]:5434/postgres=# create tablespace tblspc1 location
> > '/home/marius/pgcode/tblspc1';
> > CREATE TABLESPACE
> > marius@[local]:5434/postgres=# create database test tablespace
> tblspc1;
> > CREATE DATABASE
> > marius@[local]:5434/postgres=# \c test
> > You are now connected to database "test" as user "marius".
> > marius@[local]:5434/test=# create table toto(id numeric)
> partition by
> > list(id);
> > ERROR: cannot specify default tablespace for partitioned relations
>
> Oh, so the problem here is that *both* default_tablespace and the
> database's tablespace are set, and then a partitioned table creation
> fails when it doesn't specify any tablespace? That indeed sounds
> like a
> bug. I'll have a look, thanks. I'm surprised it took so long for
> this
> to be reported.
>
> Oh, interesting issue!
> I found another two case:
> First: default_tablespace not set and create part rel failed
> postgres=# create tablespace tbsp3 location '/tender/pgsql/tbsp3';
> CREATE TABLESPACE
> postgres=# create database test3 tablespace tbsp3;
> CREATE DATABASE
> postgres=# \c test3
> You are now connected to database "test3" as user "gpadmin".
> test3=# show default_tablespace ;
> default_tablespace
> --------------------
>
> (1 row)
>
> test3=# create table part1(a int) partition by list(a) tablespace tbsp3;
> ERROR: cannot specify default tablespace for partitioned relations
>
> Second: default_tablespace and database's tablespace both set, but
> part rel created
> test3=# set default_tablespace = tbsp2;
> SET
> test3=# create table part1(a int) partition by list(a);
> CREATE TABLE
>
> I'm not sure if the above two cases are a bug. If the document could
> provide detailed explanations, that would be great.
>
> --
> Álvaro Herrera 48°01'N 7°57'E —
> https://www.EnterpriseDB.com/ <https://www.EnterpriseDB.com/;
> "Someone said that it is at least an order of magnitude more work
> to do
> production software than a prototype. I think he is wrong by at least
> an order of magnitude." (Brian Kernighan)
>
>
>
>
Attachments:partitioned_and_tablespaces.patch (text/x-patch, 6.1K) downloaddiff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex c160d8a301..afba303e13 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -758,10 +758,12 @@ DefineIndex(Oid tableId,
if (stmt->tableSpace)
{
tablespaceId = get_tablespace_oid(stmt->tableSpace, false);
+ /*
if (partitioned && tablespaceId == MyDatabaseTableSpace)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot specify default tablespace for partitioned relations")));
+ */
}
else
{
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 416a98e7ce..1a50c9c479 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -790,11 +790,12 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (stmt->tablespacename)
{
tablespaceId = get_tablespace_oid(stmt->tablespacename, false);
-+ /*
if (partitioned && tablespaceId == MyDatabaseTableSpace)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot specify default tablespace for partitioned relations")));
+ */
}
else if (stmt->partbound)
{
diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.cindex 13b0dee146..f73c6a1286 100644--- a/src/backend/commands/tablespace.c+++ b/src/backend/commands/tablespace.c@@ -1133,9 +1133,7 @@ check_default_tablespace(char **newval, void **extra, GucSource source)
* GetDefaultTablespace -- get the OID of the current default tablespace
*
* Temporary objects have different default tablespaces, hence the
- * relpersistence parameter must be specified. Also, for partitioned tables,- * we disallow specifying the database default, so that needs to be specified- * too.+ * relpersistence parameter must be specified.
*
* May return InvalidOid to indicate "use the database's default tablespace".
*
@@ -1172,16 +1170,15 @@ GetDefaultTablespace(char relpersistence, bool partitioned)
/*
* Allow explicit specification of database's default tablespace in
- * default_tablespace without triggering permissions checks. Don't allow- * specifying that when creating a partitioned table, however, since the- * result is confusing.+ * default_tablespace without triggering permissions checks.
*/
if (result == MyDatabaseTableSpace)
{
- if (partitioned)+ /* if (partitioned)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot specify default tablespace for partitioned relations")));
+ */
result = InvalidOid;
}
return result;
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.outindex 9aabb85349..b9070d269f 100644--- a/src/test/regress/expected/tablespace.out+++ b/src/test/regress/expected/tablespace.out@@ -396,17 +396,14 @@ Partitions: testschema.part1_a_idx,
testschema.part2_a_idx
Tablespace: "regress_tblspace"
--- partitioned rels cannot specify the default tablespace. These fail:+-- partitioned rels can specify the default tablespace. These should not fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
-ERROR: cannot specify default tablespace for partitioned relations-CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE pg_default) PARTITION BY LIST (a);-ERROR: cannot specify default tablespace for partitioned relations+CREATE TABLE testschema.dflt2 (a int PRIMARY KEY USING INDEX TABLESPACE pg_default) PARTITION BY LIST (a);
SET default_tablespace TO 'pg_default';
-CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE regress_tblspace;-ERROR: cannot specify default tablespace for partitioned relations-CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE regress_tblspace) PARTITION BY LIST (a);-ERROR: cannot specify default tablespace for partitioned relations--- but these work:+CREATE TABLE testschema.dflt3 (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE regress_tblspace;+CREATE TABLE testschema.dflt4 (a int PRIMARY KEY USING INDEX TABLESPACE regress_tblspace) PARTITION BY LIST (a);+DROP TABLE testschema.dflt, testschema.dflt2, testschema.dflt3, testschema.dflt4;+-- and these work:
CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE regress_tblspace) PARTITION BY LIST (a) TABLESPACE regress_tblspace;
SET default_tablespace TO '';
CREATE TABLE testschema.dflt2 (a int PRIMARY KEY) PARTITION BY LIST (a);
diff --git a/src/test/regress/sql/tablespace.sql b/src/test/regress/sql/tablespace.sqlindex d274d9615e..7bd2b72a69 100644--- a/src/test/regress/sql/tablespace.sql+++ b/src/test/regress/sql/tablespace.sql@@ -231,13 +231,14 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
\d testschema.part_a_idx
\d+ testschema.part_a_idx
--- partitioned rels cannot specify the default tablespace. These fail:+-- partitioned rels can specify the default tablespace. These should not fail:
CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
-CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE pg_default) PARTITION BY LIST (a);+CREATE TABLE testschema.dflt2 (a int PRIMARY KEY USING INDEX TABLESPACE pg_default) PARTITION BY LIST (a);
SET default_tablespace TO 'pg_default';
-CREATE TABLE testschema.dflt (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE regress_tblspace;-CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE regress_tblspace) PARTITION BY LIST (a);--- but these work:+CREATE TABLE testschema.dflt3 (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE regress_tblspace;+CREATE TABLE testschema.dflt4 (a int PRIMARY KEY USING INDEX TABLESPACE regress_tblspace) PARTITION BY LIST (a);+DROP TABLE testschema.dflt, testschema.dflt2, testschema.dflt3, testschema.dflt4;+-- and these work:
CREATE TABLE testschema.dflt (a int PRIMARY KEY USING INDEX TABLESPACE regress_tblspace) PARTITION BY LIST (a) TABLESPACE regress_tblspace;
SET default_tablespace TO '';
CREATE TABLE testschema.dflt2 (a int PRIMARY KEY) PARTITION BY LIST (a);
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: mariusraicu@laposte.net, alvherre@alvh.no-ip.org, pgsql-hackers@lists.postgresql.org, tndrwang@gmail.com
Subject: Re: BUG #18167: cannot create partitioned tables when default_tablespace is set
In-Reply-To: <e83d6036-2461-4e76-8108-35bca155357e@laposte.net>
* 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