From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_dump is broken for partition tablespaces
Date: Wed, 17 Apr 2019 17:39:01 -0400
Message-ID: <20190417213901.GA6555@alvherre.pgsql> (raw)
In-Reply-To: <CAKJS1f_tzREh1ck9W55khwo1dZJGBSmQ_72H+iYWqUWbCzQi5Q@mail.gmail.com>
On 2019-Apr-17, David Rowley wrote:
> On Mon, 15 Apr 2019 at 15:26, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> >
> > On 2019-Apr-15, David Rowley wrote:
> >
> > > To be honest, if I'd done a better job of thinking through the
> > > implications of this tablespace inheritance in ca4103025d, then I'd
> > > probably have not bothered submitting a patch for it. We could easily
> > > revert that, but we'd still be left with the same behaviour in
> > > partitioned indexes, which is in PG11.
> >
> > Well, I suppose if we do decide to revert it for tables, we should do it
> > for both tables and indexes. But as I said, I'm not yet convinced that
> > this is the best way forward.
>
> Ok. Any ideas or suggestions on how we move on from here? It seems
> like a bit of a stalemate.
Well, here's my proposed patch. I'm now fairly happy with how this
looks now, concerning partitioned tables.
This is mostly what was already discussed:
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.
2. When creating a partition using the CREATE TABLE PARTITION OF syntax,
the TABLESPACE clause has highest precedence; if that is not given,
the partitioned table's tablespace is used; if that is set to 0 (the
default), default_tablespace is used; if that's set to empty or a
nonexistant tablespace, the database's default tablespace is used.
This is (I think) what Andres proposed in
https://postgr.es/m/20190306223741.lolaaimhkkp4kict@alap3.anarazel.de
3. Partitioned relations can have the database tablespace in
pg_class.reltablespace, as opposed to storage-bearing relations which
cannot. This is useful to be able to put partitions in the database
tablespace even if the default_tablespace is set to something else.
4. For partitioned tables, ALTER TABLE .. SET TABLESPACE DEFAULT is
available as suggested by David, which makes future partition
creations target default_tablespace or the database's tablespace.
5. Recreating indexes during table-rewriting ALTER TABLE resulted in
broken indexes. We already had some adhesive tape in place to make
that work for regular indexes (commit bd673e8e864a); my approach to
fix it for partitioned indexes is to temporarily reset
default_tablespace to empty.
As for Tom's question in https://postgr.es/m/12678.1555252685@sss.pgh.pa.us :
> It's possible that Alvaro's patch is also broken, but I haven't had time
> to review it. The immediate question is what happens when somebody makes
> a partitioned table in template1 and then does CREATE DATABASE with a
> tablespace option. Does the partitioned table end up in the same
> tablespace as ordinary tables do?
Note that partitioned don't have any files, so they don't end up
anywhere; when a partition is created, the target tablespace is
determined using four rules instead of three (see #2 above) so yes, they
do end up in the same places as ordinary tables. Note that even if you
do put a partitioned table in some tablespace, you will not later run
afoul of this check:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot assign new default tablespace \"%s\"",
tablespacename),
errdetail("There is a conflict because database \"%s\" already has some tables in this tablespace.",
dbtemplate)));
src/backend/commands/dbcommands.c:435
because that check uses ReadDir() and raise an error if any entry is
found; but partitioned tables don't have files in directory, so nothing
happens. (Of course, it will hit if you have a partition in that
tablespace, but that's also the case for regular tables.)
(I propose to commit both 0002 and 0003 as a single unit that fixes the
whole problem, rather than attacking backend and pg_dump separately.
0001 appears logically separate and I would push on its own.)
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:download all (3 files, .tar.gz)v3-0001-psql-display-tablespace-for-partitioned-indexes.patch (text/x-diff, 2.5K) download
From c9217e46ff424d68b9b2fdbba009c949665045ef Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Fri, 12 Apr 2019 16:58:26 -0400
Subject: [PATCH v3 1/3] psql: display tablespace for partitioned indexes
Nothing was shown previously.
---
src/bin/psql/describe.c | 3 ++-
src/test/regress/input/tablespace.source | 1 +
src/test/regress/output/tablespace.source | 8 ++++++++
3 files changed, 11 insertions(+), 1 deletion(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.cindex 3a04b0673a3..8a269016333 100644--- a/src/bin/psql/describe.c+++ b/src/bin/psql/describe.c@@ -3306,7 +3306,8 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
if (relkind == RELKIND_RELATION ||
relkind == RELKIND_MATVIEW ||
relkind == RELKIND_INDEX ||
- relkind == RELKIND_PARTITIONED_TABLE)+ relkind == RELKIND_PARTITIONED_TABLE ||+ relkind == RELKIND_PARTITIONED_INDEX)
{
/*
* We ignore the database default tablespace so that users not using
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.sourceindex 47ae73af95f..14ce0e7e04f 100644--- a/src/test/regress/input/tablespace.source+++ b/src/test/regress/input/tablespace.source@@ -63,6 +63,7 @@ CREATE INDEX part_a_idx ON testschema.part (a) TABLESPACE regress_tblspace;
CREATE TABLE testschema.part2 PARTITION OF testschema.part FOR VALUES IN (2);
SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
where c.reltablespace = t.oid AND c.relname LIKE 'part%_idx';
+\d testschema.part_a_idx
-- check that default_tablespace doesn't affect ALTER TABLE index rebuilds
CREATE TABLE testschema.test_default_tab(id bigint) TABLESPACE regress_tblspace;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.sourceindex 29d6d2232be..8ebe08b9b28 100644--- a/src/test/regress/output/tablespace.source+++ b/src/test/regress/output/tablespace.source@@ -94,6 +94,14 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
part_a_idx | regress_tblspace
(3 rows)
+\d testschema.part_a_idx+Partitioned index "testschema.part_a_idx"+ Column | Type | Key? | Definition +--------+---------+------+------------+ a | integer | yes | a+btree, for table "testschema.part"+Tablespace: "regress_tblspace"+
-- check that default_tablespace doesn't affect ALTER TABLE index rebuilds
CREATE TABLE testschema.test_default_tab(id bigint) TABLESPACE regress_tblspace;
INSERT INTO testschema.test_default_tab VALUES (1);
--
2.17.1
v3-0002-Make-pg_dump-emit-ATTACH-PARTITION-instead-of-PAR.patch (text/x-diff, 9.8K) download
From a3273cbb577e25a34851aabfca9f0afc23607106 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Mon, 8 Apr 2019 16:40:05 -0400
Subject: [PATCH v3 2/3] 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.cindex 452f30760bb..a7987790df5 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.plindex 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
v3-0003-fix-behavior-to-consider-default_tablespace.patch (text/x-diff, 44.6K) download
From 92015d294b31140c96e46221a3992e22645161ed Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Wed, 10 Apr 2019 12:41:31 -0400
Subject: [PATCH v3 3/3] 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 | 53 ++--
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 | 103 +++++++-
src/test/regress/output/tablespace.source | 291 +++++++++++++++++++++-
19 files changed, 527 insertions(+), 44 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgmlindex 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.sgmlindex 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.yindex 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.cindex 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.cindex 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.cindex 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.cindex d48a947f7c6..f490fd31751 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,10 @@ 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 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 +669,31 @@ 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);+ tablespaceId = GetDefaultTablespace(stmt->relation->relpersistence,+ partitioned);
}
else
{
- tablespaceId = GetDefaultTablespace(stmt->relation->relpersistence);+ tablespaceId = GetDefaultTablespace(stmt->relation->relpersistence,+ partitioned);
/* note InvalidOid is OK in this case */
}
- /* 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 +827,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 +1000,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 +11210,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 +11242,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 +11265,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 +11862,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 +11882,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 relation")));++ tablespaceId = InvalidOid;+ }
tab->newTableSpace = tablespaceId;
}
@@ -12245,8 +12267,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 +12282,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.cindex 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.cindex 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.cindex 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.cindex 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.yindex 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.cindex 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.cindex 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.cindex a7987790df5..6fafa5ee2ba 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.hindex 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.hindex 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.sourceindex 14ce0e7e04f..8bc742faebd 100644--- a/src/test/regress/input/tablespace.source+++ b/src/test/regress/input/tablespace.source@@ -50,11 +50,34 @@ CREATE TABLE testschema.part12 PARTITION OF testschema.part FOR VALUES IN(1,2) P
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;+
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 +93,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 +122,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 +192,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_tablespacce is handled correctly by multi-command+-- ALTER TABLE that includes a tablespace-preserving rewrite+CREATE TABLE testschema.test_tab(a int, b 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;+\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.sourceindex 8ebe08b9b28..b5841c51735 100644--- a/src/test/regress/output/tablespace.source+++ b/src/test/regress/output/tablespace.source@@ -67,18 +67,23 @@ CREATE TABLE testschema.part12 PARTITION OF testschema.part FOR VALUES IN(1,2) P
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.+SET default_tablespace TO regress_tblspace;+CREATE TABLE testschema.part12_3 PARTITION OF testschema.part12 FOR VALUES IN (3);+-- Ensure part12_1 defaulted to regress_tblspace, and part12_2 and part12_3 both+-- defaulted to pg_default.
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 | + part12 | pg_default
part12_1 | regress_tblspace
part12_2 |
-(4 rows)+ part12_3 | +(5 rows)+SET default_tablespace TO '';
DROP TABLE testschema.part;
-- partitioned index
CREATE TABLE testschema.part (a int) PARTITION BY LIST (a);
@@ -107,6 +112,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 +129,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 +163,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 +201,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 +228,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 +247,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 +464,62 @@ SELECT * FROM testschema.test_tab;
1
(1 row)
+DROP TABLE testschema.test_tab;+-- check that default_tablespacce is handled correctly by multi-command+-- ALTER TABLE that includes a tablespace-preserving rewrite+CREATE TABLE testschema.test_tab(a int, b 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;+\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
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-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, david.rowley@2ndquadrant.com, andres@anarazel.de, tgl@sss.pgh.pa.us, pgsql-hackers@lists.postgresql.org
Subject: Re: pg_dump is broken for partition tablespaces
In-Reply-To: <20190417213901.GA6555@alvherre.pgsql>
* 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