On 2019-Apr-17, Alvaro Herrera wrote:
> On 2019-Apr-17, Tom Lane wrote:
>
> > Alvaro Herrera <alvhe...@2ndquadrant.com> writes:
> > > 1. pg_dump now uses regular CREATE TABLE followed by ALTER TABLE / ATTACH
> > > PARTITION when creating partitions, rather than CREATE TABLE
> > > PARTITION OF. pg_dump --binary-upgrade was already doing that, so
> > > this part mostly removes some code. In order to make the partitions
> > > reach the correct tablespace, the "default_tablespace" GUC is used.
> > > No TABLESPACE clause is added to the dump. This is David's patch
> > > near the start of the thread.
> >
> > This idea seems reasonable independently of all else, simply on the grounds
> > of reducing code duplication. It also has the advantage that if you try
> > to do a selective restore of just a partition, and the parent partitioned
> > table isn't around, you can still do it (with an ignorable error).
>
> I'll get this part pushed, then.
After looking at it again, I found that there's no significant
duplication reduction -- the patch simply duplicates one block in a
different location, putting half of the original code in each. And if we
reject the idea of separating tablespaces, there's no reason to do
things that way. So ISTM if we don't want the tablespace thing, we
should not apply this part. FWIW, we got quite a few positive votes for
handling tablespaces this way for partitioned tables [1] [2], so I
resist the idea that we have to revert the initial commit, as some seem
to be proposing.
After re-reading the thread one more time, I found one more pretty
reasonable point that Andres was complaining about, and I made things
work the way he described. Namely, if you do this:
SET default_tablespace TO 'foo';
CREATE TABLE part (a int) PARTITION BY LIST (a);
SET default_tablespace TO 'bar';
CREATE TABLE part1 PARTITION OF part FOR VALUES IN (1);
then the partition must end up in tablespace bar, not in tablespace foo:
the reason is that the default_tablespace is not "strong enough" to
stick with the partitioned table. The partition would only end up in
tablespace foo in this case:
CREATE TABLE part (a int) PARTITION BY LIST (a) TABLESPACE foo;
CREATE TABLE part1 PARTITION OF part FOR VALUES IN (1);
i.e. when the tablespace is explicitly indicated in the CREATE TABLE
command for the partitioned table. Of course, you can still add a
TABLESPACE clause to the partition to override it (and you can change
the parent table's tablespace later.)
So here's a proposed v5.
I would appreciate others' eyes on this patch.
[1]
https://postgr.es/m/cakjs1f9sxvzqdrgd1teosfd6jbmm0ueaa14_8mrvcwe19tu...@mail.gmail.com
[2]
https://www.postgresql.org/message-id/flat/CAKJS1f9PXYcT%2Bj%3DoyL-Lquz%3DScNwpRtmD7u9svLASUygBdbN8w%40mail.gmail.com
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 2e42c31985fd1ed38af7806c25539c6dba600dc1 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Mon, 8 Apr 2019 16:40:05 -0400
Subject: [PATCH v5 1/2] Make pg_dump emit ATTACH PARTITION instead of
PARTITION OF
ca41030 modified how the TABLESPACE option worked with partitioned tables
so that it was possible to specify and store a TABLESPACE for a partitioned
table. This setting served only to mark what the default tablespace should
be for new partitions which were created with the CREATE TABLE PARTITION OF
syntax.
The problem with this was that pg_dump used the PARTITION OF syntax which
meant that if a partition had been explicitly defined to have pg_default
as the tablespace then since reltablespace is set to 0 in this case, pg_dump
would emit a CREATE TABLE .. PARTITION OF statement and cause the partition
to default to the partitioned table's tablespace rather than the one it was
meant to be located in.
We can work around this problem by simply performing the CREATE TABLE first
then perform an ATTACH PARTITION later. This bypasses the check of the
parent partitioned table's tablespace in DefineRelation() as the table is
not a partition when it is defined.
Doing this also means that when restoring partitions they now maintain
their original column ordering rather than switch to their partitioned
table's column ordering. This is perhaps minor, but it is noteworthy
pg_dump in binary upgrade mode already worked this way, so it turns out
this commit removes more code than it adds.
---
src/bin/pg_dump/pg_dump.c | 118 ++++++++++++++-----------------
src/bin/pg_dump/t/002_pg_dump.pl | 12 +++-
2 files changed, 61 insertions(+), 69 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 7cfb67fd36e..bacb4de5c36 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8618,9 +8618,11 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
* Normally this is always true, but it's false for dropped columns, as well
* as those that were inherited without any local definition. (If we print
* such a column it will mistakenly get pg_attribute.attislocal set to true.)
- * However, in binary_upgrade mode, we must print all such columns anyway and
- * fix the attislocal/attisdropped state later, so as to keep control of the
- * physical column order.
+ *
+ * In binary_upgrade mode, we must print all columns and fix the attislocal/
+ * attisdropped state later, so as to keep control of the physical column
+ * order. We also do this for partitions, for a different reason: we want
+ * them to be created independently, and ATTACH PARTITION used afterwards.
*
* This function exists because there are scattered nonobvious places that
* must be kept in sync with this decision.
@@ -8630,7 +8632,9 @@ shouldPrintColumn(DumpOptions *dopt, TableInfo *tbinfo, int colno)
{
if (dopt->binary_upgrade)
return true;
- return (tbinfo->attislocal[colno] && !tbinfo->attisdropped[colno]);
+ if (tbinfo->attisdropped[colno])
+ return false;
+ return (tbinfo->attislocal[colno] || tbinfo->ispartition);
}
@@ -15599,27 +15603,6 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
if (tbinfo->reloftype && !dopt->binary_upgrade)
appendPQExpBuffer(q, " OF %s", tbinfo->reloftype);
- /*
- * If the table is a partition, dump it as such; except in the case of
- * a binary upgrade, we dump the table normally and attach it to the
- * parent afterward.
- */
- if (tbinfo->ispartition && !dopt->binary_upgrade)
- {
- TableInfo *parentRel = tbinfo->parents[0];
-
- /*
- * With partitions, unlike inheritance, there can only be one
- * parent.
- */
- if (tbinfo->numParents != 1)
- fatal("invalid number of parents %d for table \"%s\"",
- tbinfo->numParents, tbinfo->dobj.name);
-
- appendPQExpBuffer(q, " PARTITION OF %s",
- fmtQualifiedDumpable(parentRel));
- }
-
if (tbinfo->relkind != RELKIND_MATVIEW)
{
/* Dump the attributes */
@@ -15648,12 +15631,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
(!tbinfo->inhNotNull[j] ||
dopt->binary_upgrade));
- /*
- * Skip column if fully defined by reloftype or the
- * partition parent.
- */
- if ((tbinfo->reloftype || tbinfo->ispartition) &&
- !has_default && !has_notnull && !dopt->binary_upgrade)
+ /* Skip column if fully defined by reloftype */
+ if (tbinfo->reloftype && !has_default && !has_notnull &&
+ !dopt->binary_upgrade)
continue;
/* Format properly if not first attr */
@@ -15676,7 +15656,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* clean things up later.
*/
appendPQExpBufferStr(q, " INTEGER /* dummy */");
- /* Skip all the rest, too */
+ /* and skip to the next column */
continue;
}
@@ -15685,11 +15665,9 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
*
* In binary-upgrade mode, we always include the type. If
* we aren't in binary-upgrade mode, then we skip the type
- * when creating a typed table ('OF type_name') or a
- * partition ('PARTITION OF'), since the type comes from
- * the parent/partitioned table.
+ * when creating a typed table ('OF type_name').
*/
- if (dopt->binary_upgrade || (!tbinfo->reloftype && !tbinfo->ispartition))
+ if (dopt->binary_upgrade || !tbinfo->reloftype)
{
appendPQExpBuffer(q, " %s",
tbinfo->atttypnames[j]);
@@ -15746,23 +15724,19 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
if (actual_atts)
appendPQExpBufferStr(q, "\n)");
- else if (!((tbinfo->reloftype || tbinfo->ispartition) &&
- !dopt->binary_upgrade))
+ else if (!(tbinfo->reloftype && !dopt->binary_upgrade))
{
/*
- * We must have a parenthesized attribute list, even though
- * empty, when not using the OF TYPE or PARTITION OF syntax.
+ * No attributes? we must have a parenthesized attribute list,
+ * even though empty, when not using the OF TYPE syntax.
*/
appendPQExpBufferStr(q, " (\n)");
}
- if (tbinfo->ispartition && !dopt->binary_upgrade)
- {
- appendPQExpBufferChar(q, '\n');
- appendPQExpBufferStr(q, tbinfo->partbound);
- }
-
- /* Emit the INHERITS clause, except if this is a partition. */
+ /*
+ * Emit the INHERITS clause, except if this is a partition. When
+ * in binary upgrade mode we'll do this later in this function.
+ */
if (numParents > 0 &&
!tbinfo->ispartition &&
!dopt->binary_upgrade)
@@ -15937,30 +15911,16 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferStr(q, "::pg_catalog.regclass;\n");
}
- if (numParents > 0)
+ if (numParents > 0 && !tbinfo->ispartition)
{
- appendPQExpBufferStr(q, "\n-- For binary upgrade, set up inheritance and partitioning this way.\n");
+ appendPQExpBufferStr(q, "\n-- For binary upgrade, set up inheritance this way.\n");
for (k = 0; k < numParents; k++)
{
TableInfo *parentRel = parents[k];
- /* In the partitioning case, we alter the parent */
- if (tbinfo->ispartition)
- appendPQExpBuffer(q,
- "ALTER TABLE ONLY %s ATTACH PARTITION ",
- fmtQualifiedDumpable(parentRel));
- else
- appendPQExpBuffer(q, "ALTER TABLE ONLY %s INHERIT ",
- qualrelname);
-
- /* Partition needs specifying the bounds */
- if (tbinfo->ispartition)
- appendPQExpBuffer(q, "%s %s;\n",
- qualrelname,
- tbinfo->partbound);
- else
- appendPQExpBuffer(q, "%s;\n",
- fmtQualifiedDumpable(parentRel));
+ appendPQExpBuffer(q, "ALTER TABLE ONLY %s INHERIT %s;\n",
+ qualrelname,
+ fmtQualifiedDumpable(parentRel));
}
}
@@ -15973,6 +15933,32 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
}
}
+ /*
+ * For partitioned tables, emit the ATTACH PARTITION clause. Note
+ * that we always want to create partitions in this way instead using
+ * CREATE TABLE .. PARTITION OF, because doing it the other way would
+ * result in an incorrect tablespace setting for the partition, if the
+ * partitioned table has one and the partitioned table is created in
+ * a different one.
+ */
+ if (tbinfo->ispartition)
+ {
+ /*
+ * With partitions, unlike inheritance, there can only be one
+ * parent.
+ */
+ if (tbinfo->numParents != 1)
+ fatal("invalid number of parents %d for table \"%s\"",
+ tbinfo->numParents, tbinfo->dobj.name);
+
+ /* Perform ALTER TABLE on the parent */
+ appendPQExpBuffer(q, "ALTER TABLE ONLY %s ATTACH PARTITION ",
+ fmtQualifiedDumpable(parents[0]));
+
+ /* specify the bounds */
+ appendPQExpBuffer(q, "%s %s;\n", qualrelname, tbinfo->partbound);
+ }
+
/*
* In binary_upgrade mode, arrange to restore the old relfrozenxid and
* relminmxid of all vacuumable relations. (While vacuum.c processes
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 5721882b3b2..5c1acd014bf 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -733,7 +733,12 @@ my %tests = (
\QALTER TABLE ONLY dump_test.measurement ATTACH PARTITION dump_test_second_schema.measurement_y2006m2 \E
\QFOR VALUES FROM ('2006-02-01') TO ('2006-03-01');\E\n
/xm,
- like => { binary_upgrade => 1, },
+ like => {
+ %full_runs,
+ role => 1,
+ section_pre_data => 1,
+ binary_upgrade => 1,
+ },
},
'ALTER TABLE test_table CLUSTER ON test_table_pkey' => {
@@ -2322,12 +2327,13 @@ my %tests = (
\)\n
\QFOR VALUES FROM ('2006-02-01') TO ('2006-03-01');\E\n
/xm,
- like => {
+ like => {},
+ unlike => {
%full_runs,
role => 1,
section_pre_data => 1,
+ binary_upgrade => 1,
},
- unlike => { binary_upgrade => 1, },
},
'CREATE TABLE test_fourth_table_zero_col' => {
--
2.17.1
>From 105a025bceda256a5074d02694b8907008fd4628 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Wed, 10 Apr 2019 12:41:31 -0400
Subject: [PATCH v5 2/2] fix behavior to consider default_tablespace
---
doc/src/sgml/ref/alter_table.sgml | 19 +-
doc/src/sgml/ref/create_table.sgml | 5 +-
src/backend/bootstrap/bootparse.y | 2 +
src/backend/catalog/heap.c | 11 +-
src/backend/commands/indexcmds.c | 23 +-
src/backend/commands/matview.c | 2 +-
src/backend/commands/tablecmds.c | 66 +++--
src/backend/commands/tablespace.c | 14 +-
src/backend/nodes/copyfuncs.c | 2 +
src/backend/nodes/equalfuncs.c | 2 +
src/backend/nodes/outfuncs.c | 4 +
src/backend/parser/gram.y | 9 +
src/backend/parser/parse_utilcmd.c | 2 +
src/backend/utils/adt/ruleutils.c | 19 +-
src/bin/pg_dump/pg_dump.c | 4 +-
src/include/commands/tablespace.h | 2 +-
src/include/nodes/parsenodes.h | 4 +
src/test/regress/input/tablespace.source | 109 +++++++-
src/test/regress/output/tablespace.source | 319 +++++++++++++++++++++-
19 files changed, 570 insertions(+), 48 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 49b081aad29..572ca517cb5 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -73,7 +73,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
CLUSTER ON <replaceable class="parameter">index_name</replaceable>
SET WITHOUT CLUSTER
SET WITHOUT OIDS
- SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
+ SET TABLESPACE { <replaceable class="parameter">new_tablespace</replaceable> | <literal>DEFAULT</literal> }
SET { LOGGED | UNLOGGED }
SET ( <replaceable class="parameter">storage_parameter</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )
RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] )
@@ -639,10 +639,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
moves the data file(s) associated with the table to the new tablespace.
Indexes on the table, if any, are not moved; but they can be moved
separately with additional <literal>SET TABLESPACE</literal> commands.
+ </para>
+
+ <para>
When applied to a partitioned table, nothing is moved, but any
partitions created afterwards with
<command>CREATE TABLE PARTITION OF</command> will use that tablespace,
unless the <literal>TABLESPACE</literal> clause is used to override it.
+ Specifying <literal>DEFAULT</literal> as the target tablespace results in
+ partitions honoring the <literal>default_tablespace</literal> setting, or
+ the database's default tablespace. <literal>DEFAULT</literal> can only
+ be specified for partitioned relations.
</para>
<para>
@@ -654,11 +661,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
then the command will fail if it is unable to acquire all of the locks
required immediately. Note that system catalogs are not moved by this
command; use <command>ALTER DATABASE</command> or explicit
- <command>ALTER TABLE</command> invocations instead if desired. The
- <literal>information_schema</literal> relations are not considered part
- of the system catalogs and will be moved.
- See also
- <xref linkend="sql-createtablespace"/>.
+ <command>ALTER TABLE</command> invocations instead if desired.
+ Partitioned tables are not affected by this form (but partitions are).
+ The <literal>information_schema</literal> relations are not considered
+ part of the system catalogs and will be moved.
+ See also <xref linkend="sql-createtablespace"/>.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 77c254862b6..e826940b6da 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1266,8 +1266,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<xref linkend="guc-temp-tablespaces"/> if the table is temporary. For
partitioned tables, since no storage is required for the table itself,
the tablespace specified here only serves to mark the default tablespace
- for any newly created partitions when no other tablespace is explicitly
- specified.
+ for any newly created partitions when no explicit
+ <literal>TABLESPACE</literal> clause is specified during partition
+ creation.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y
index 6d7e11645d2..4f2587d74af 100644
--- a/src/backend/bootstrap/bootparse.y
+++ b/src/backend/bootstrap/bootparse.y
@@ -314,6 +314,7 @@ Boot_DeclareIndexStmt:
stmt->transformed = false;
stmt->concurrent = false;
stmt->if_not_exists = false;
+ stmt->reset_default_tblspc = false;
/* locks and races need not concern us in bootstrap mode */
relationId = RangeVarGetRelid(stmt->relation, NoLock,
@@ -363,6 +364,7 @@ Boot_DeclareUniqueIndexStmt:
stmt->transformed = false;
stmt->concurrent = false;
stmt->if_not_exists = false;
+ stmt->reset_default_tblspc = false;
/* locks and races need not concern us in bootstrap mode */
relationId = RangeVarGetRelid(stmt->relation, NoLock,
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 6b77eff0af1..550c8e39818 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -386,9 +386,18 @@ heap_create(const char *relname,
* tablespace, the pg_class entry will still match where CREATE DATABASE
* will put the physically copied relation.
*
+ * We make an exception for partitioned relations, though: for them,
+ * reltablespace does not really indicate a storage location (there isn't
+ * any storage for them) but instead it only determines where storage for
+ * partitions is to be created later. We must make this targetted even
+ * with the default tablespace, so that partitions are created in the
+ * right location even if the database's tablespace is later changed.
+ *
* Yes, this is a bit of a hack.
*/
- if (reltablespace == MyDatabaseTableSpace)
+ if (reltablespace == MyDatabaseTableSpace &&
+ relkind != RELKIND_PARTITIONED_TABLE &&
+ relkind != RELKIND_PARTITIONED_INDEX)
reltablespace = InvalidOid;
/*
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index a1c91b5fb87..70b3177564b 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -467,8 +467,21 @@ DefineIndex(Oid relationId,
LOCKTAG heaplocktag;
LOCKMODE lockmode;
Snapshot snapshot;
+ int save_nestlevel = -1;
int i;
+ /*
+ * Some callers need us to run with an empty default_tablespace; this is a
+ * necessary hack to be able to reproduce catalog state accurately when
+ * recreating indexes after table-rewriting ALTER TABLE.
+ */
+ if (stmt->reset_default_tblspc)
+ {
+ save_nestlevel = NewGUCNestLevel();
+ (void) set_config_option("default_tablespace", "",
+ PGC_USERSET, PGC_S_SESSION,
+ GUC_ACTION_SAVE, true, 0, false);
+ }
/*
* Start progress report. If we're building a partition, this was already
@@ -625,7 +638,8 @@ DefineIndex(Oid relationId,
}
else
{
- tablespaceId = GetDefaultTablespace(rel->rd_rel->relpersistence);
+ tablespaceId = GetDefaultTablespace(rel->rd_rel->relpersistence,
+ partitioned);
/* note InvalidOid is OK in this case */
}
@@ -980,6 +994,13 @@ DefineIndex(Oid relationId,
ObjectAddressSet(address, RelationRelationId, indexRelationId);
+ /*
+ * Revert to original default_tablespace. Must do this before any return
+ * from this function, but after index_create, so this is a good time.
+ */
+ if (save_nestlevel >= 0)
+ AtEOXact_GUC(true, save_nestlevel);
+
if (!OidIsValid(indexRelationId))
{
table_close(rel, NoLock);
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 2aac63296bf..99bf3c29f27 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -284,7 +284,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
/* Concurrent refresh builds new data in temp tablespace, and does diff. */
if (concurrent)
{
- tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP);
+ tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP, false);
relpersistence = RELPERSISTENCE_TEMP;
}
else
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d48a947f7c6..33995a4b595 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -556,6 +556,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
Oid tablespaceId;
Relation rel;
TupleDesc descriptor;
+ bool partitioned;
List *inheritOids;
List *old_constraints;
List *rawDefaults;
@@ -592,6 +593,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
relkind = RELKIND_PARTITIONED_TABLE;
}
+ partitioned = relkind == RELKIND_PARTITIONED_TABLE;
/*
* Look up the namespace in which we are supposed to create the relation,
@@ -656,6 +658,17 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
/*
* Select tablespace to use. If not specified, use default tablespace
* (which may in turn default to database's default).
+ *
+ * For partitioned tables, only an explicit TABLESPACE clause and the
+ * parent's tablespace are considered, not default_tablespace. This
+ * avoids the surprising behavior that if a partitioned table is created
+ * under a certain default_tablespace, then any partition created
+ * afterwards would end up in that tablespace, instead of following the
+ * partition-creation-time default_tablespace, which may have changed.
+ *
+ * For partitions, the TABLESPACE clause takes precedence, followed by the
+ * default_tablespace GUC, followed by the parent partitioned table's
+ * reltablespace.
*/
if (stmt->tablespacename)
{
@@ -663,31 +676,33 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
else if (stmt->partbound)
{
- HeapTuple tup;
-
/*
* For partitions, when no other tablespace is specified, we default
* the tablespace to the parent partitioned table's.
*/
Assert(list_length(inheritOids) == 1);
- tup = SearchSysCache1(RELOID,
- DatumGetObjectId(linitial_oid(inheritOids)));
- tablespaceId = ((Form_pg_class) GETSTRUCT(tup))->reltablespace;
+ tablespaceId = get_rel_tablespace(linitial_oid(inheritOids));
- if (!OidIsValid(tablespaceId))
- tablespaceId = GetDefaultTablespace(stmt->relation->relpersistence);
-
- ReleaseSysCache(tup);
+ if (!OidIsValid(tablespaceId) && !partitioned)
+ tablespaceId = GetDefaultTablespace(stmt->relation->relpersistence,
+ partitioned);
}
- else
+ else if (!partitioned)
{
- tablespaceId = GetDefaultTablespace(stmt->relation->relpersistence);
+ tablespaceId = GetDefaultTablespace(stmt->relation->relpersistence,
+ partitioned);
/* note InvalidOid is OK in this case */
}
+ else
+ tablespaceId = InvalidOid;
- /* Check permissions except when using database's default */
- if (OidIsValid(tablespaceId) && tablespaceId != MyDatabaseTableSpace)
+ /*
+ * Check permissions except when using database's default, or when
+ * creating partitioned relations.
+ */
+ if (OidIsValid(tablespaceId) && tablespaceId != MyDatabaseTableSpace &&
+ !partitioned)
{
AclResult aclresult;
@@ -821,7 +836,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
{
accessMethod = stmt->accessMethod;
- if (relkind == RELKIND_PARTITIONED_TABLE)
+ if (partitioned)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("specifying a table access method is not supported on a partitioned table")));
@@ -994,7 +1009,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
* Process the partitioning specification (if any) and store the partition
* key information into the catalog.
*/
- if (stmt->partspec)
+ if (partitioned)
{
ParseState *pstate;
char strategy;
@@ -11204,6 +11219,7 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
if (!rewrite)
TryReuseIndex(oldId, stmt);
+ stmt->reset_default_tblspc = true;
/* keep the index's comment */
stmt->idxcomment = GetComment(oldId, RelationRelationId, 0);
@@ -11235,6 +11251,7 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
/* keep any comment on the index */
indstmt->idxcomment = GetComment(indoid,
RelationRelationId, 0);
+ indstmt->reset_default_tblspc = true;
cmd->subtype = AT_ReAddIndex;
tab->subcmds[AT_PASS_OLD_INDEX] =
@@ -11257,6 +11274,7 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
if (con->contype == CONSTR_FOREIGN &&
!rewrite && tab->rewrite == 0)
TryReuseForeignKey(oldId, con);
+ con->reset_default_tblspc = true;
cmd->subtype = AT_ReAddConstraint;
tab->subcmds[AT_PASS_OLD_CONSTR] =
lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd);
@@ -11853,6 +11871,8 @@ ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, const char *tablespacen
{
Oid tablespaceId;
+ if (tablespacename != NULL)
+ {
/* Check that the tablespace exists */
tablespaceId = get_tablespace_oid(tablespacename, false);
@@ -11871,6 +11891,17 @@ ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, const char *tablespacen
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("cannot have multiple SET TABLESPACE subcommands")));
+ }
+ else
+ {
+ if (rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE &&
+ rel->rd_rel->relkind != RELKIND_PARTITIONED_INDEX)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use SET TABLESPACE DEFAULT on non-partitioned relations")));
+
+ tablespaceId = InvalidOid;
+ }
tab->newTableSpace = tablespaceId;
}
@@ -12245,8 +12276,7 @@ ATExecSetTableSpaceNoStorage(Relation rel, Oid newTableSpace)
* No work if no change in tablespace.
*/
oldTableSpace = rel->rd_rel->reltablespace;
- if (newTableSpace == oldTableSpace ||
- (newTableSpace == MyDatabaseTableSpace && oldTableSpace == 0))
+ if (newTableSpace == oldTableSpace)
{
InvokeObjectPostAlterHook(RelationRelationId, reloid, 0);
return;
@@ -12261,7 +12291,7 @@ ATExecSetTableSpaceNoStorage(Relation rel, Oid newTableSpace)
rd_rel = (Form_pg_class) GETSTRUCT(tuple);
/* update the pg_class row */
- rd_rel->reltablespace = (newTableSpace == MyDatabaseTableSpace) ? InvalidOid : newTableSpace;
+ rd_rel->reltablespace = newTableSpace;
CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
InvokeObjectPostAlterHook(RelationRelationId, reloid, 0);
diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c
index 3784ea4b4fa..e87b29ce73b 100644
--- a/src/backend/commands/tablespace.c
+++ b/src/backend/commands/tablespace.c
@@ -1107,15 +1107,19 @@ check_default_tablespace(char **newval, void **extra, GucSource source)
* relpersistence parameter must be specified.
*
* May return InvalidOid to indicate "use the database's default tablespace".
+ * However, when used for partitioned tables, don't return InvalidOid
+ * when the database default tablespace is specified.
*
* Note that caller is expected to check appropriate permissions for any
- * result other than InvalidOid.
+ * result other than InvalidOid. (Partitioned relations again excepted:
+ * since they don't store anything, the permission check would only be made
+ * when a partition is created.)
*
* This exists to hide (and possibly optimize the use of) the
* default_tablespace GUC variable.
*/
Oid
-GetDefaultTablespace(char relpersistence)
+GetDefaultTablespace(char relpersistence, bool partitioned)
{
Oid result;
@@ -1141,9 +1145,11 @@ GetDefaultTablespace(char relpersistence)
/*
* Allow explicit specification of database's default tablespace in
- * default_tablespace without triggering permissions checks.
+ * default_tablespace without triggering permissions checks. Refrain from
+ * doing this for partitioned tables: they must preserve the exact
+ * tablespace used.
*/
- if (result == MyDatabaseTableSpace)
+ if (result == MyDatabaseTableSpace && !partitioned)
result = InvalidOid;
return result;
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 8f51315bee8..780d7ab00b5 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2919,6 +2919,7 @@ _copyConstraint(const Constraint *from)
COPY_NODE_FIELD(options);
COPY_STRING_FIELD(indexname);
COPY_STRING_FIELD(indexspace);
+ COPY_SCALAR_FIELD(reset_default_tblspc);
COPY_STRING_FIELD(access_method);
COPY_NODE_FIELD(where_clause);
COPY_NODE_FIELD(pktable);
@@ -3475,6 +3476,7 @@ _copyIndexStmt(const IndexStmt *from)
COPY_SCALAR_FIELD(transformed);
COPY_SCALAR_FIELD(concurrent);
COPY_SCALAR_FIELD(if_not_exists);
+ COPY_SCALAR_FIELD(reset_default_tblspc);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 68b51f3de71..4f2ebe5118e 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1347,6 +1347,7 @@ _equalIndexStmt(const IndexStmt *a, const IndexStmt *b)
COMPARE_SCALAR_FIELD(transformed);
COMPARE_SCALAR_FIELD(concurrent);
COMPARE_SCALAR_FIELD(if_not_exists);
+ COMPARE_SCALAR_FIELD(reset_default_tblspc);
return true;
}
@@ -2593,6 +2594,7 @@ _equalConstraint(const Constraint *a, const Constraint *b)
COMPARE_NODE_FIELD(options);
COMPARE_STRING_FIELD(indexname);
COMPARE_STRING_FIELD(indexspace);
+ COMPARE_SCALAR_FIELD(reset_default_tblspc);
COMPARE_STRING_FIELD(access_method);
COMPARE_NODE_FIELD(where_clause);
COMPARE_NODE_FIELD(pktable);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 82ca6826ab1..387e4b9b716 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2649,6 +2649,7 @@ _outIndexStmt(StringInfo str, const IndexStmt *node)
WRITE_BOOL_FIELD(transformed);
WRITE_BOOL_FIELD(concurrent);
WRITE_BOOL_FIELD(if_not_exists);
+ WRITE_BOOL_FIELD(reset_default_tblspc);
}
static void
@@ -3491,6 +3492,7 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_NODE_FIELD(options);
WRITE_STRING_FIELD(indexname);
WRITE_STRING_FIELD(indexspace);
+ WRITE_BOOL_FIELD(reset_default_tblspc);
/* access_method and where_clause not currently used */
break;
@@ -3501,6 +3503,7 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_NODE_FIELD(options);
WRITE_STRING_FIELD(indexname);
WRITE_STRING_FIELD(indexspace);
+ WRITE_BOOL_FIELD(reset_default_tblspc);
/* access_method and where_clause not currently used */
break;
@@ -3511,6 +3514,7 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_NODE_FIELD(options);
WRITE_STRING_FIELD(indexname);
WRITE_STRING_FIELD(indexspace);
+ WRITE_BOOL_FIELD(reset_default_tblspc);
WRITE_STRING_FIELD(access_method);
WRITE_NODE_FIELD(where_clause);
break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b51f12dc232..bc377b68d10 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2495,6 +2495,13 @@ alter_table_cmd:
n->name = $3;
$$ = (Node *)n;
}
+ | SET TABLESPACE DEFAULT
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetTableSpace;
+ n->name = NULL;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> SET (...) */
| SET reloptions
{
@@ -7363,6 +7370,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->initdeferred = false;
n->transformed = false;
n->if_not_exists = false;
+ n->reset_default_tblspc = false;
$$ = (Node *)n;
}
| CREATE opt_unique INDEX opt_concurrently IF_P NOT EXISTS index_name
@@ -7390,6 +7398,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->initdeferred = false;
n->transformed = false;
n->if_not_exists = true;
+ n->reset_default_tblspc = false;
$$ = (Node *)n;
}
;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 674f4b98f40..f0e08387c02 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1390,6 +1390,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Oid heapRelid, Relation source_idx,
index->transformed = true; /* don't need transformIndexStmt */
index->concurrent = false;
index->if_not_exists = false;
+ index->reset_default_tblspc = false;
/*
* We don't try to preserve the name of the source index; instead, just
@@ -1972,6 +1973,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
index->transformed = false;
index->concurrent = false;
index->if_not_exists = false;
+ index->reset_default_tblspc = constraint->reset_default_tblspc;
/*
* If it's ALTER TABLE ADD CONSTRAINT USING INDEX, look up the index and
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0c7a533e697..1e3bcb47b86 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1429,12 +1429,13 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
Oid tblspc;
tblspc = get_rel_tablespace(indexrelid);
- if (!OidIsValid(tblspc))
- tblspc = MyDatabaseTableSpace;
- if (isConstraint)
- appendStringInfoString(&buf, " USING INDEX");
- appendStringInfo(&buf, " TABLESPACE %s",
- quote_identifier(get_tablespace_name(tblspc)));
+ if (OidIsValid(tblspc))
+ {
+ if (isConstraint)
+ appendStringInfoString(&buf, " USING INDEX");
+ appendStringInfo(&buf, " TABLESPACE %s",
+ quote_identifier(get_tablespace_name(tblspc)));
+ }
}
/*
@@ -2170,6 +2171,12 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
pfree(options);
}
+ /*
+ * Print the tablespace, unless it's the database default.
+ * This is to help ALTER TABLE usage of this facility,
+ * which needs this behavior to recreate exact catalog
+ * state.
+ */
tblspc = get_rel_tablespace(indexId);
if (OidIsValid(tblspc))
appendStringInfo(&buf, " USING INDEX TABLESPACE %s",
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bacb4de5c36..b3146c662bb 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -15938,8 +15938,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* that we always want to create partitions in this way instead using
* CREATE TABLE .. PARTITION OF, because doing it the other way would
* result in an incorrect tablespace setting for the partition, if the
- * partitioned table has one and the partitioned table is created in
- * a different one.
+ * partitioned table has one and the partitioned table is created in a
+ * different one.
*/
if (tbinfo->ispartition)
{
diff --git a/src/include/commands/tablespace.h b/src/include/commands/tablespace.h
index 003c874c228..273e31ccdfb 100644
--- a/src/include/commands/tablespace.h
+++ b/src/include/commands/tablespace.h
@@ -49,7 +49,7 @@ extern Oid AlterTableSpaceOptions(AlterTableSpaceOptionsStmt *stmt);
extern void TablespaceCreateDbspace(Oid spcNode, Oid dbNode, bool isRedo);
-extern Oid GetDefaultTablespace(char relpersistence);
+extern Oid GetDefaultTablespace(char relpersistence, bool partitioned);
extern void PrepareTempTablespaces(void);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 94c0b7a9dd5..94898891629 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2153,6 +2153,8 @@ typedef struct Constraint
List *options; /* options from WITH clause */
char *indexname; /* existing index to use; otherwise NULL */
char *indexspace; /* index tablespace; NULL for default */
+ bool reset_default_tblspc; /* reset default_tablespace prior to
+ * creating the index */
/* These could be, but currently are not, used for UNIQUE/PKEY: */
char *access_method; /* index access method; NULL for default */
Node *where_clause; /* partial index predicate */
@@ -2772,6 +2774,8 @@ typedef struct IndexStmt
bool transformed; /* true when transformIndexStmt is finished */
bool concurrent; /* should this be a concurrent index build? */
bool if_not_exists; /* just do nothing if index already exists? */
+ bool reset_default_tblspc; /* reset default_tablespace prior to
+ * executing */
} IndexStmt;
/* ----------------------
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 14ce0e7e04f..88cf6d88347 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -46,15 +46,42 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
-- partitioned table
CREATE TABLE testschema.part (a int) PARTITION BY LIST (a);
-CREATE TABLE testschema.part12 PARTITION OF testschema.part FOR VALUES IN(1,2) PARTITION BY LIST (a) TABLESPACE regress_tblspace;
+CREATE TABLE testschema.part12 PARTITION OF testschema.part FOR VALUES IN(1,2)
+ PARTITION BY LIST (a) TABLESPACE regress_tblspace;
CREATE TABLE testschema.part12_1 PARTITION OF testschema.part12 FOR VALUES IN (1);
ALTER TABLE testschema.part12 SET TABLESPACE pg_default;
CREATE TABLE testschema.part12_2 PARTITION OF testschema.part12 FOR VALUES IN (2);
--- Ensure part12_1 defaulted to regress_tblspace and part12_2 defaulted to pg_default.
+
+CREATE TABLE testschema.part3 PARTITION OF testschema.part FOR VALUES IN (3);
+CREATE TABLE testschema.part4 PARTITION OF testschema.part FOR VALUES IN (4)
+ TABLESPACE regress_tblspace;
+
+SET default_tablespace TO pg_default;
+CREATE TABLE testschema.part5 PARTITION OF testschema.part FOR VALUES IN (5);
+CREATE TABLE testschema.part6 PARTITION OF testschema.part FOR VALUES IN (6)
+ TABLESPACE regress_tblspace;
+
+ALTER TABLE testschema.part SET TABLESPACE regress_tblspace;
+SET default_tablespace TO pg_global;
+CREATE TABLE testschema.part7 PARTITION OF testschema.part FOR VALUES IN (7);
+CREATE TABLE testschema.part8 PARTITION OF testschema.part FOR VALUES IN (8)
+ TABLESPACE pg_default;
+
+ALTER TABLE testschema.part SET TABLESPACE DEFAULT;
+SET default_tablespace TO regress_tblspace;
+CREATE TABLE testschema.part9 PARTITION OF testschema.part FOR VALUES IN (9);
+
+CREATE TABLE testschema.part10 PARTITION OF testschema.part FOR VALUES IN (10)
+ TABLESPACE pg_default;
+-- default_tablespace is ignored
+CREATE TABLE testschema.part11 PARTITION OF testschema.part FOR VALUES IN (11)
+ PARTITION BY LIST (a);
+
SELECT relname, spcname FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid
where c.relname LIKE 'part%' order by relname;
DROP TABLE testschema.part;
+SET default_tablespace TO '';
-- partitioned index
CREATE TABLE testschema.part (a int) PARTITION BY LIST (a);
@@ -70,19 +97,28 @@ CREATE TABLE testschema.test_default_tab(id bigint) TABLESPACE regress_tblspace;
INSERT INTO testschema.test_default_tab VALUES (1);
CREATE INDEX test_index1 on testschema.test_default_tab (id);
CREATE INDEX test_index2 on testschema.test_default_tab (id) TABLESPACE regress_tblspace;
+ALTER TABLE testschema.test_default_tab ADD CONSTRAINT test_index3 PRIMARY KEY (id);
+ALTER TABLE testschema.test_default_tab ADD CONSTRAINT test_index4 UNIQUE (id) USING INDEX TABLESPACE regress_tblspace;
+
\d testschema.test_index1
\d testschema.test_index2
+\d testschema.test_index3
+\d testschema.test_index4
-- use a custom tablespace for default_tablespace
SET default_tablespace TO regress_tblspace;
-- tablespace should not change if no rewrite
ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint;
\d testschema.test_index1
\d testschema.test_index2
+\d testschema.test_index3
+\d testschema.test_index4
SELECT * FROM testschema.test_default_tab;
-- tablespace should not change even if there is an index rewrite
ALTER TABLE testschema.test_default_tab ALTER id TYPE int;
\d testschema.test_index1
\d testschema.test_index2
+\d testschema.test_default_tab_id_key
+\d testschema.test_default_tab_pkey
SELECT * FROM testschema.test_default_tab;
-- now use the default tablespace for default_tablespace
SET default_tablespace TO '';
@@ -90,12 +126,64 @@ SET default_tablespace TO '';
ALTER TABLE testschema.test_default_tab ALTER id TYPE int;
\d testschema.test_index1
\d testschema.test_index2
+\d testschema.test_default_tab_id_key
+\d testschema.test_default_tab_pkey
-- tablespace should not change even if there is an index rewrite
ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint;
\d testschema.test_index1
\d testschema.test_index2
+\d testschema.test_default_tab_id_key
+\d testschema.test_default_tab_pkey
DROP TABLE testschema.test_default_tab;
+-- check that default_tablespace doesn't affect ALTER TABLE index rebuilds
+-- (this time with a partitioned table)
+CREATE TABLE testschema.test_default_tab_p(id bigint, val bigint)
+ PARTITION BY LIST (id) TABLESPACE regress_tblspace;
+CREATE TABLE testschema.test_default_tab_p1 PARTITION OF testschema.test_default_tab_p
+ FOR VALUES IN (1);
+INSERT INTO testschema.test_default_tab_p VALUES (1);
+CREATE INDEX test_index1 on testschema.test_default_tab_p (val);
+CREATE INDEX test_index2 on testschema.test_default_tab_p (val) TABLESPACE regress_tblspace;
+ALTER TABLE testschema.test_default_tab_p ADD CONSTRAINT test_index3 PRIMARY KEY (id);
+ALTER TABLE testschema.test_default_tab_p ADD CONSTRAINT test_index4 UNIQUE (id) USING INDEX TABLESPACE regress_tblspace;
+
+\d testschema.test_index1
+\d testschema.test_index2
+\d testschema.test_index3
+\d testschema.test_index4
+-- use a custom tablespace for default_tablespace
+SET default_tablespace TO regress_tblspace;
+-- tablespace should not change if no rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE bigint;
+\d testschema.test_index1
+\d testschema.test_index2
+\d testschema.test_index3
+\d testschema.test_index4
+SELECT * FROM testschema.test_default_tab_p;
+-- tablespace should not change even if there is an index rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE int;
+\d testschema.test_index1
+\d testschema.test_index2
+\d testschema.test_index3
+\d testschema.test_index4
+SELECT * FROM testschema.test_default_tab_p;
+-- now use the default tablespace for default_tablespace
+SET default_tablespace TO '';
+-- tablespace should not change if no rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE int;
+\d testschema.test_index1
+\d testschema.test_index2
+\d testschema.test_index3
+\d testschema.test_index4
+-- tablespace should not change even if there is an index rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE bigint;
+\d testschema.test_index1
+\d testschema.test_index2
+\d testschema.test_index3
+\d testschema.test_index4
+DROP TABLE testschema.test_default_tab_p;
+
-- check that default_tablespace affects index additions in ALTER TABLE
CREATE TABLE testschema.test_tab(id int) TABLESPACE regress_tblspace;
INSERT INTO testschema.test_tab VALUES (1);
@@ -108,6 +196,23 @@ ALTER TABLE testschema.test_tab ADD CONSTRAINT test_tab_pkey PRIMARY KEY (id);
SELECT * FROM testschema.test_tab;
DROP TABLE testschema.test_tab;
+-- check that default_tablespace is handled correctly by multi-command
+-- ALTER TABLE that includes a tablespace-preserving rewrite
+CREATE TABLE testschema.test_tab(a int, b int, c int);
+SET default_tablespace TO regress_tblspace;
+ALTER TABLE testschema.test_tab ADD CONSTRAINT test_tab_unique UNIQUE (a);
+CREATE INDEX test_tab_a_idx ON testschema.test_tab (a);
+SET default_tablespace TO '';
+CREATE INDEX test_tab_b_idx ON testschema.test_tab (b);
+\d testschema.test_tab_unique
+\d testschema.test_tab_a_idx
+\d testschema.test_tab_b_idx
+ALTER TABLE testschema.test_tab ALTER b TYPE bigint, ADD UNIQUE (c);
+\d testschema.test_tab_unique
+\d testschema.test_tab_a_idx
+\d testschema.test_tab_b_idx
+DROP TABLE testschema.test_tab;
+
-- let's try moving a table from one place to another
CREATE TABLE testschema.atable AS VALUES (1), (2);
CREATE UNIQUE INDEX anindex ON testschema.atable(column1);
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 8ebe08b9b28..89c7dd79d53 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -63,23 +63,54 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
-- partitioned table
CREATE TABLE testschema.part (a int) PARTITION BY LIST (a);
-CREATE TABLE testschema.part12 PARTITION OF testschema.part FOR VALUES IN(1,2) PARTITION BY LIST (a) TABLESPACE regress_tblspace;
+CREATE TABLE testschema.part12 PARTITION OF testschema.part FOR VALUES IN(1,2)
+ PARTITION BY LIST (a) TABLESPACE regress_tblspace;
CREATE TABLE testschema.part12_1 PARTITION OF testschema.part12 FOR VALUES IN (1);
ALTER TABLE testschema.part12 SET TABLESPACE pg_default;
CREATE TABLE testschema.part12_2 PARTITION OF testschema.part12 FOR VALUES IN (2);
--- Ensure part12_1 defaulted to regress_tblspace and part12_2 defaulted to pg_default.
+CREATE TABLE testschema.part3 PARTITION OF testschema.part FOR VALUES IN (3);
+CREATE TABLE testschema.part4 PARTITION OF testschema.part FOR VALUES IN (4)
+ TABLESPACE regress_tblspace;
+SET default_tablespace TO pg_default;
+CREATE TABLE testschema.part5 PARTITION OF testschema.part FOR VALUES IN (5);
+CREATE TABLE testschema.part6 PARTITION OF testschema.part FOR VALUES IN (6)
+ TABLESPACE regress_tblspace;
+ALTER TABLE testschema.part SET TABLESPACE regress_tblspace;
+SET default_tablespace TO pg_global;
+CREATE TABLE testschema.part7 PARTITION OF testschema.part FOR VALUES IN (7);
+CREATE TABLE testschema.part8 PARTITION OF testschema.part FOR VALUES IN (8)
+ TABLESPACE pg_default;
+ALTER TABLE testschema.part SET TABLESPACE DEFAULT;
+SET default_tablespace TO regress_tblspace;
+CREATE TABLE testschema.part9 PARTITION OF testschema.part FOR VALUES IN (9);
+
+CREATE TABLE testschema.part10 PARTITION OF testschema.part FOR VALUES IN (10)
+ TABLESPACE pg_default;
+-- default_tablespace is ignored
+CREATE TABLE testschema.part11 PARTITION OF testschema.part FOR VALUES IN (11)
+ PARTITION BY LIST (a);
SELECT relname, spcname FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid
where c.relname LIKE 'part%' order by relname;
relname | spcname
----------+------------------
part |
- part12 |
+ part10 |
+ part11 |
+ part12 | pg_default
part12_1 | regress_tblspace
part12_2 |
-(4 rows)
+ part3 |
+ part4 | regress_tblspace
+ part5 |
+ part6 | regress_tblspace
+ part7 | regress_tblspace
+ part8 |
+ part9 | regress_tblspace
+(13 rows)
DROP TABLE testschema.part;
+SET default_tablespace TO '';
-- partitioned index
CREATE TABLE testschema.part (a int) PARTITION BY LIST (a);
CREATE TABLE testschema.part1 PARTITION OF testschema.part FOR VALUES IN (1);
@@ -107,6 +138,8 @@ CREATE TABLE testschema.test_default_tab(id bigint) TABLESPACE regress_tblspace;
INSERT INTO testschema.test_default_tab VALUES (1);
CREATE INDEX test_index1 on testschema.test_default_tab (id);
CREATE INDEX test_index2 on testschema.test_default_tab (id) TABLESPACE regress_tblspace;
+ALTER TABLE testschema.test_default_tab ADD CONSTRAINT test_index3 PRIMARY KEY (id);
+ALTER TABLE testschema.test_default_tab ADD CONSTRAINT test_index4 UNIQUE (id) USING INDEX TABLESPACE regress_tblspace;
\d testschema.test_index1
Index "testschema.test_index1"
Column | Type | Key? | Definition
@@ -122,6 +155,21 @@ btree, for table "testschema.test_default_tab"
btree, for table "testschema.test_default_tab"
Tablespace: "regress_tblspace"
+\d testschema.test_index3
+ Index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index4
+ Index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
-- use a custom tablespace for default_tablespace
SET default_tablespace TO regress_tblspace;
-- tablespace should not change if no rewrite
@@ -141,6 +189,21 @@ btree, for table "testschema.test_default_tab"
btree, for table "testschema.test_default_tab"
Tablespace: "regress_tblspace"
+\d testschema.test_index3
+ Index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab"
+
+\d testschema.test_index4
+ Index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab"
+Tablespace: "regress_tblspace"
+
SELECT * FROM testschema.test_default_tab;
id
----
@@ -164,6 +227,8 @@ btree, for table "testschema.test_default_tab"
btree, for table "testschema.test_default_tab"
Tablespace: "regress_tblspace"
+\d testschema.test_default_tab_id_key
+\d testschema.test_default_tab_pkey
SELECT * FROM testschema.test_default_tab;
id
----
@@ -189,6 +254,8 @@ btree, for table "testschema.test_default_tab"
btree, for table "testschema.test_default_tab"
Tablespace: "regress_tblspace"
+\d testschema.test_default_tab_id_key
+\d testschema.test_default_tab_pkey
-- tablespace should not change even if there is an index rewrite
ALTER TABLE testschema.test_default_tab ALTER id TYPE bigint;
\d testschema.test_index1
@@ -206,7 +273,195 @@ btree, for table "testschema.test_default_tab"
btree, for table "testschema.test_default_tab"
Tablespace: "regress_tblspace"
+\d testschema.test_default_tab_id_key
+\d testschema.test_default_tab_pkey
DROP TABLE testschema.test_default_tab;
+-- check that default_tablespace doesn't affect ALTER TABLE index rebuilds
+-- (this time with a partitioned table)
+CREATE TABLE testschema.test_default_tab_p(id bigint, val bigint)
+ PARTITION BY LIST (id) TABLESPACE regress_tblspace;
+CREATE TABLE testschema.test_default_tab_p1 PARTITION OF testschema.test_default_tab_p
+ FOR VALUES IN (1);
+INSERT INTO testschema.test_default_tab_p VALUES (1);
+CREATE INDEX test_index1 on testschema.test_default_tab_p (val);
+CREATE INDEX test_index2 on testschema.test_default_tab_p (val) TABLESPACE regress_tblspace;
+ALTER TABLE testschema.test_default_tab_p ADD CONSTRAINT test_index3 PRIMARY KEY (id);
+ALTER TABLE testschema.test_default_tab_p ADD CONSTRAINT test_index4 UNIQUE (id) USING INDEX TABLESPACE regress_tblspace;
+\d testschema.test_index1
+Partitioned index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+
+\d testschema.test_index2
+Partitioned index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+Partitioned index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab_p"
+
+\d testschema.test_index4
+Partitioned index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab_p"
+Tablespace: "regress_tblspace"
+
+-- use a custom tablespace for default_tablespace
+SET default_tablespace TO regress_tblspace;
+-- tablespace should not change if no rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE bigint;
+\d testschema.test_index1
+Partitioned index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+
+\d testschema.test_index2
+Partitioned index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+Partitioned index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab_p"
+
+\d testschema.test_index4
+Partitioned index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab_p"
+Tablespace: "regress_tblspace"
+
+SELECT * FROM testschema.test_default_tab_p;
+ id | val
+----+-----
+ 1 |
+(1 row)
+
+-- tablespace should not change even if there is an index rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE int;
+\d testschema.test_index1
+Partitioned index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ val | integer | yes | val
+btree, for table "testschema.test_default_tab_p"
+
+\d testschema.test_index2
+Partitioned index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ val | integer | yes | val
+btree, for table "testschema.test_default_tab_p"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+Partitioned index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab_p"
+
+\d testschema.test_index4
+Partitioned index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab_p"
+Tablespace: "regress_tblspace"
+
+SELECT * FROM testschema.test_default_tab_p;
+ id | val
+----+-----
+ 1 |
+(1 row)
+
+-- now use the default tablespace for default_tablespace
+SET default_tablespace TO '';
+-- tablespace should not change if no rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE int;
+\d testschema.test_index1
+Partitioned index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ val | integer | yes | val
+btree, for table "testschema.test_default_tab_p"
+
+\d testschema.test_index2
+Partitioned index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ val | integer | yes | val
+btree, for table "testschema.test_default_tab_p"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+Partitioned index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab_p"
+
+\d testschema.test_index4
+Partitioned index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab_p"
+Tablespace: "regress_tblspace"
+
+-- tablespace should not change even if there is an index rewrite
+ALTER TABLE testschema.test_default_tab_p ALTER val TYPE bigint;
+\d testschema.test_index1
+Partitioned index "testschema.test_index1"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+
+\d testschema.test_index2
+Partitioned index "testschema.test_index2"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ val | bigint | yes | val
+btree, for table "testschema.test_default_tab_p"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_index3
+Partitioned index "testschema.test_index3"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+primary key, btree, for table "testschema.test_default_tab_p"
+
+\d testschema.test_index4
+Partitioned index "testschema.test_index4"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ id | bigint | yes | id
+unique, btree, for table "testschema.test_default_tab_p"
+Tablespace: "regress_tblspace"
+
+DROP TABLE testschema.test_default_tab_p;
-- check that default_tablespace affects index additions in ALTER TABLE
CREATE TABLE testschema.test_tab(id int) TABLESPACE regress_tblspace;
INSERT INTO testschema.test_tab VALUES (1);
@@ -235,6 +490,62 @@ SELECT * FROM testschema.test_tab;
1
(1 row)
+DROP TABLE testschema.test_tab;
+-- check that default_tablespace is handled correctly by multi-command
+-- ALTER TABLE that includes a tablespace-preserving rewrite
+CREATE TABLE testschema.test_tab(a int, b int, c int);
+SET default_tablespace TO regress_tblspace;
+ALTER TABLE testschema.test_tab ADD CONSTRAINT test_tab_unique UNIQUE (a);
+CREATE INDEX test_tab_a_idx ON testschema.test_tab (a);
+SET default_tablespace TO '';
+CREATE INDEX test_tab_b_idx ON testschema.test_tab (b);
+\d testschema.test_tab_unique
+ Index "testschema.test_tab_unique"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ a | integer | yes | a
+unique, btree, for table "testschema.test_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_tab_a_idx
+ Index "testschema.test_tab_a_idx"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ a | integer | yes | a
+btree, for table "testschema.test_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_tab_b_idx
+ Index "testschema.test_tab_b_idx"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ b | integer | yes | b
+btree, for table "testschema.test_tab"
+
+ALTER TABLE testschema.test_tab ALTER b TYPE bigint, ADD UNIQUE (c);
+\d testschema.test_tab_unique
+ Index "testschema.test_tab_unique"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ a | integer | yes | a
+unique, btree, for table "testschema.test_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_tab_a_idx
+ Index "testschema.test_tab_a_idx"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ a | integer | yes | a
+btree, for table "testschema.test_tab"
+Tablespace: "regress_tblspace"
+
+\d testschema.test_tab_b_idx
+ Index "testschema.test_tab_b_idx"
+ Column | Type | Key? | Definition
+--------+--------+------+------------
+ b | bigint | yes | b
+btree, for table "testschema.test_tab"
+
DROP TABLE testschema.test_tab;
-- let's try moving a table from one place to another
CREATE TABLE testschema.atable AS VALUES (1), (2);
--
2.17.1
