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 <alvhe...@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)
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 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.c
index 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.c
index 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.out
index 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.sql
index 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);
