Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9653ca2

Browse files
committed
Fix partitioned index creation with foreign partitions
When a partitioned tables contains foreign tables as partitions, it isnot possible to implement unique or primary key indexes -- but whenregular indexes are created, there is no reason to do anything otherthan ignoring such partitions. We were raising errors upon encounteringthe foreign partitions, which is unfriendly and doesn't protect againstany actual problems.Relax this restriction so that index creation is allowed on partitionedtables containing foreign partitions, becoming a no-op on them. (We maylater want to redefine this so that the FDW is told to create theindexes on the foreign side.) This applies to CREATE INDEX, as well asALTER TABLE / ATTACH PARTITION and CREATE TABLE / PARTITION OF.Backpatch to 11, where indexes on partitioned tables were introduced.Discussion:https://postgr.es/m/15724-d5a58fa9472eef4f@postgresql.orgAuthor: Álvaro HerreraReviewed-by: Amit Langote
1 parent0e08a3a commit9653ca2

File tree

7 files changed

+172
-9
lines changed

7 files changed

+172
-9
lines changed

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -861,7 +861,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
861861
as if <command>ALTER INDEX ATTACH PARTITION</command> had been executed.
862862
Note that if the existing table is a foreign table, it is currently not
863863
allowed to attach the table as a partition of the target table if there
864-
are indexes on the target table. (See also
864+
are<literal>UNIQUE</literal>indexes on the target table. (See also
865865
<xref linkend="sql-createforeigntable"/>.)
866866
</para>
867867

‎doc/src/sgml/ref/create_foreign_table.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -169,8 +169,8 @@ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ]
169169
See the similar form of
170170
<xref linkend="sql-createtable"/> for more details.
171171
Note that it is currently not allowed to create the foreign table as a
172-
partition of the parent table if there areindexes on the parent table.
173-
(See also
172+
partition of the parent table if there are<literal>UNIQUE</literal>
173+
indexes on the parent table.(See also
174174
<link linkend="sql-altertable"><command>ALTER TABLE ATTACH PARTITION</command></link>.)
175175
</para>
176176
</listitem>

‎src/backend/commands/indexcmds.c

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -922,6 +922,26 @@ DefineIndex(Oid relationId,
922922
intmaplen;
923923

924924
childrel=heap_open(childRelid,lockmode);
925+
926+
/*
927+
* Don't try to create indexes on foreign tables, though.
928+
* Skip those if a regular index, or fail if trying to create
929+
* a constraint index.
930+
*/
931+
if (childrel->rd_rel->relkind==RELKIND_FOREIGN_TABLE)
932+
{
933+
if (stmt->unique||stmt->primary)
934+
ereport(ERROR,
935+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
936+
errmsg("cannot create unique index on partitioned table \"%s\"",
937+
RelationGetRelationName(rel)),
938+
errdetail("Table \"%s\" contains partitions that are foreign tables.",
939+
RelationGetRelationName(rel))));
940+
941+
heap_close(childrel,lockmode);
942+
continue;
943+
}
944+
925945
childidxs=RelationGetIndexList(childrel);
926946
attmap=
927947
convert_tuples_by_name_map(RelationGetDescr(childrel),

‎src/backend/commands/tablecmds.c

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -939,6 +939,22 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
939939
IndexStmt*idxstmt;
940940
OidconstraintOid;
941941

942+
if (rel->rd_rel->relkind==RELKIND_FOREIGN_TABLE)
943+
{
944+
if (idxRel->rd_index->indisunique)
945+
ereport(ERROR,
946+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
947+
errmsg("cannot create foreign partition of partitioned table \"%s\"",
948+
RelationGetRelationName(parent)),
949+
errdetail("Table \"%s\" contains indexes that are unique.",
950+
RelationGetRelationName(parent))));
951+
else
952+
{
953+
index_close(idxRel,AccessShareLock);
954+
continue;
955+
}
956+
}
957+
942958
attmap=convert_tuples_by_name_map(RelationGetDescr(rel),
943959
RelationGetDescr(parent),
944960
gettext_noop("could not convert row type"));
@@ -15013,6 +15029,34 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
1501315029
i++;
1501415030
}
1501515031

15032+
/*
15033+
* If we're attaching a foreign table, we must fail if any of the indexes
15034+
* is a constraint index; otherwise, there's nothing to do here. Do this
15035+
* before starting work, to avoid wasting the effort of building a few
15036+
* non-unique indexes before coming across a unique one.
15037+
*/
15038+
if (attachrel->rd_rel->relkind==RELKIND_FOREIGN_TABLE)
15039+
{
15040+
foreach(cell,idxes)
15041+
{
15042+
Oididx=lfirst_oid(cell);
15043+
RelationidxRel=index_open(idx,AccessShareLock);
15044+
15045+
if (idxRel->rd_index->indisunique||
15046+
idxRel->rd_index->indisprimary)
15047+
ereport(ERROR,
15048+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
15049+
errmsg("cannot attach foreign table \"%s\" as partition of partitioned table \"%s\"",
15050+
RelationGetRelationName(attachrel),
15051+
RelationGetRelationName(rel)),
15052+
errdetail("Table \"%s\" contains unique indexes.",
15053+
RelationGetRelationName(rel))));
15054+
index_close(idxRel,AccessShareLock);
15055+
}
15056+
15057+
gotoout;
15058+
}
15059+
1501615060
/*
1501715061
* For each index on the partitioned table, find a matching one in the
1501815062
* partition-to-be; if one is not found, create one.
@@ -15112,6 +15156,7 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
1511215156
index_close(idxRel,AccessShareLock);
1511315157
}
1511415158

15159+
out:
1511515160
/* Clean up. */
1511615161
for (i=0;i<list_length(attachRelIdxs);i++)
1511715162
index_close(attachrelIdxRels[i],AccessShareLock);

‎src/backend/tcop/utility.c

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1342,10 +1342,16 @@ ProcessUtilitySlow(ParseState *pstate,
13421342

13431343
if (relkind!=RELKIND_RELATION&&
13441344
relkind!=RELKIND_MATVIEW&&
1345-
relkind!=RELKIND_PARTITIONED_TABLE)
1345+
relkind!=RELKIND_PARTITIONED_TABLE&&
1346+
relkind!=RELKIND_FOREIGN_TABLE)
1347+
elog(ERROR,"unexpected relkind \"%c\" on partition \"%s\"",
1348+
relkind,stmt->relation->relname);
1349+
1350+
if (relkind==RELKIND_FOREIGN_TABLE&&
1351+
(stmt->unique||stmt->primary))
13461352
ereport(ERROR,
1347-
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
1348-
errmsg("cannot create index on partitioned table \"%s\"",
1353+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1354+
errmsg("cannot createuniqueindex on partitioned table \"%s\"",
13491355
stmt->relation->relname),
13501356
errdetail("Table \"%s\" contains partitions that are foreign tables.",
13511357
stmt->relation->relname)));

‎src/test/regress/expected/foreign_data.out

Lines changed: 54 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -752,10 +752,62 @@ ERROR: foreign-data wrapper "dummy" has no handler
752752
CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a);
753753
CREATE FOREIGN TABLE ft_part1
754754
PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0;
755-
CREATE INDEX ON lt1 (a); -- ERROR
756-
ERROR: cannot create index on partitioned table "lt1"
755+
CREATE INDEX ON lt1 (a); -- skips partition
756+
CREATE UNIQUE INDEX ON lt1 (a); -- ERROR
757+
ERROR: cannot create unique index on partitioned table "lt1"
757758
DETAIL: Table "lt1" contains partitions that are foreign tables.
759+
ALTER TABLE lt1 ADD PRIMARY KEY (a); -- ERROR
760+
ERROR: cannot create unique index on partitioned table "lt1"
761+
DETAIL: Table "lt1" contains partitions that are foreign tables.
762+
DROP TABLE lt1;
763+
CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a);
764+
CREATE INDEX ON lt1 (a);
765+
CREATE FOREIGN TABLE ft_part1
766+
PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0;
767+
CREATE FOREIGN TABLE ft_part2 (a INT) SERVER s0;
768+
ALTER TABLE lt1 ATTACH PARTITION ft_part2 FOR VALUES FROM (1000) TO (2000);
769+
DROP FOREIGN TABLE ft_part1, ft_part2;
770+
CREATE UNIQUE INDEX ON lt1 (a);
771+
ALTER TABLE lt1 ADD PRIMARY KEY (a);
772+
CREATE FOREIGN TABLE ft_part1
773+
PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; -- ERROR
774+
ERROR: cannot create foreign partition of partitioned table "lt1"
775+
DETAIL: Table "lt1" contains indexes that are unique.
776+
CREATE FOREIGN TABLE ft_part2 (a INT NOT NULL) SERVER s0;
777+
ALTER TABLE lt1 ATTACH PARTITION ft_part2
778+
FOR VALUES FROM (1000) TO (2000); -- ERROR
779+
ERROR: cannot attach foreign table "ft_part2" as partition of partitioned table "lt1"
780+
DETAIL: Table "lt1" contains unique indexes.
781+
DROP TABLE lt1;
782+
DROP FOREIGN TABLE ft_part2;
783+
CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a);
784+
CREATE INDEX ON lt1 (a);
785+
CREATE TABLE lt1_part1
786+
PARTITION OF lt1 FOR VALUES FROM (0) TO (1000)
787+
PARTITION BY RANGE (a);
788+
CREATE FOREIGN TABLE ft_part_1_1
789+
PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0;
790+
CREATE FOREIGN TABLE ft_part_1_2 (a INT) SERVER s0;
791+
ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200);
792+
CREATE UNIQUE INDEX ON lt1 (a);
793+
ERROR: cannot create unique index on partitioned table "lt1"
794+
DETAIL: Table "lt1" contains partitions that are foreign tables.
795+
ALTER TABLE lt1 ADD PRIMARY KEY (a);
796+
ERROR: cannot create unique index on partitioned table "lt1_part1"
797+
DETAIL: Table "lt1_part1" contains partitions that are foreign tables.
798+
DROP FOREIGN TABLE ft_part_1_1, ft_part_1_2;
799+
CREATE UNIQUE INDEX ON lt1 (a);
800+
ALTER TABLE lt1 ADD PRIMARY KEY (a);
801+
CREATE FOREIGN TABLE ft_part_1_1
802+
PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0;
803+
ERROR: cannot create foreign partition of partitioned table "lt1_part1"
804+
DETAIL: Table "lt1_part1" contains indexes that are unique.
805+
CREATE FOREIGN TABLE ft_part_1_2 (a INT NOT NULL) SERVER s0;
806+
ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200);
807+
ERROR: cannot attach foreign table "ft_part_1_2" as partition of partitioned table "lt1_part1"
808+
DETAIL: Table "lt1_part1" contains unique indexes.
758809
DROP TABLE lt1;
810+
DROP FOREIGN TABLE ft_part_1_2;
759811
-- ALTER FOREIGN TABLE
760812
COMMENT ON FOREIGN TABLE ft1 IS 'foreign table';
761813
COMMENT ON FOREIGN TABLE ft1 IS NULL;

‎src/test/regress/sql/foreign_data.sql

Lines changed: 41 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -319,9 +319,49 @@ EXPLAIN SELECT * FROM ft1; -- ERROR
319319
CREATETABLElt1 (aINT) PARTITION BY RANGE (a);
320320
CREATE FOREIGN TABLE ft_part1
321321
PARTITION OF lt1 FORVALUESFROM (0) TO (1000) SERVER s0;
322-
CREATEINDEXON lt1 (a);-- ERROR
322+
CREATEINDEXON lt1 (a);-- skips partition
323+
CREATEUNIQUE INDEXON lt1 (a);-- ERROR
324+
ALTERTABLE lt1 ADDPRIMARY KEY (a);-- ERROR
323325
DROPTABLE lt1;
324326

327+
CREATETABLElt1 (aINT) PARTITION BY RANGE (a);
328+
CREATEINDEXON lt1 (a);
329+
CREATE FOREIGN TABLE ft_part1
330+
PARTITION OF lt1 FORVALUESFROM (0) TO (1000) SERVER s0;
331+
CREATE FOREIGN TABLE ft_part2 (aINT) SERVER s0;
332+
ALTERTABLE lt1 ATTACH PARTITION ft_part2 FORVALUESFROM (1000) TO (2000);
333+
DROP FOREIGN TABLE ft_part1, ft_part2;
334+
CREATEUNIQUE INDEXON lt1 (a);
335+
ALTERTABLE lt1 ADDPRIMARY KEY (a);
336+
CREATE FOREIGN TABLE ft_part1
337+
PARTITION OF lt1 FORVALUESFROM (0) TO (1000) SERVER s0;-- ERROR
338+
CREATE FOREIGN TABLE ft_part2 (aINTNOT NULL) SERVER s0;
339+
ALTERTABLE lt1 ATTACH PARTITION ft_part2
340+
FORVALUESFROM (1000) TO (2000);-- ERROR
341+
DROPTABLE lt1;
342+
DROP FOREIGN TABLE ft_part2;
343+
344+
CREATETABLElt1 (aINT) PARTITION BY RANGE (a);
345+
CREATEINDEXON lt1 (a);
346+
CREATETABLElt1_part1
347+
PARTITION OF lt1 FORVALUESFROM (0) TO (1000)
348+
PARTITION BY RANGE (a);
349+
CREATE FOREIGN TABLE ft_part_1_1
350+
PARTITION OF lt1_part1 FORVALUESFROM (0) TO (100) SERVER s0;
351+
CREATE FOREIGN TABLE ft_part_1_2 (aINT) SERVER s0;
352+
ALTERTABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FORVALUESFROM (100) TO (200);
353+
CREATEUNIQUE INDEXON lt1 (a);
354+
ALTERTABLE lt1 ADDPRIMARY KEY (a);
355+
DROP FOREIGN TABLE ft_part_1_1, ft_part_1_2;
356+
CREATEUNIQUE INDEXON lt1 (a);
357+
ALTERTABLE lt1 ADDPRIMARY KEY (a);
358+
CREATE FOREIGN TABLE ft_part_1_1
359+
PARTITION OF lt1_part1 FORVALUESFROM (0) TO (100) SERVER s0;
360+
CREATE FOREIGN TABLE ft_part_1_2 (aINTNOT NULL) SERVER s0;
361+
ALTERTABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FORVALUESFROM (100) TO (200);
362+
DROPTABLE lt1;
363+
DROP FOREIGN TABLE ft_part_1_2;
364+
325365
-- ALTER FOREIGN TABLE
326366
COMMENTON FOREIGN TABLE ft1 IS'foreign table';
327367
COMMENTON FOREIGN TABLE ft1 ISNULL;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp