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

Commit55ed3de

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 parent65e6d42 commit55ed3de

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
@@ -845,7 +845,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
845845
as if <command>ALTER INDEX ATTACH PARTITION</command> had been executed.
846846
Note that if the existing table is a foreign table, it is currently not
847847
allowed to attach the table as a partition of the target table if there
848-
are indexes on the target table. (See also
848+
are<literal>UNIQUE</literal>indexes on the target table. (See also
849849
<xref linkend="sql-createforeigntable"/>.)
850850
</para>
851851

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

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

‎src/backend/commands/indexcmds.c

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

10871087
childrel=table_open(childRelid,lockmode);
1088+
1089+
/*
1090+
* Don't try to create indexes on foreign tables, though.
1091+
* Skip those if a regular index, or fail if trying to create
1092+
* a constraint index.
1093+
*/
1094+
if (childrel->rd_rel->relkind==RELKIND_FOREIGN_TABLE)
1095+
{
1096+
if (stmt->unique||stmt->primary)
1097+
ereport(ERROR,
1098+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1099+
errmsg("cannot create unique index on partitioned table \"%s\"",
1100+
RelationGetRelationName(rel)),
1101+
errdetail("Table \"%s\" contains partitions that are foreign tables.",
1102+
RelationGetRelationName(rel))));
1103+
1104+
table_close(childrel,lockmode);
1105+
continue;
1106+
}
1107+
10881108
childidxs=RelationGetIndexList(childrel);
10891109
attmap=
10901110
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
@@ -1069,6 +1069,22 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
10691069
IndexStmt *idxstmt;
10701070
OidconstraintOid;
10711071

1072+
if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
1073+
{
1074+
if (idxRel->rd_index->indisunique)
1075+
ereport(ERROR,
1076+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1077+
errmsg("cannot create foreign partition of partitioned table \"%s\"",
1078+
RelationGetRelationName(parent)),
1079+
errdetail("Table \"%s\" contains indexes that are unique.",
1080+
RelationGetRelationName(parent))));
1081+
else
1082+
{
1083+
index_close(idxRel, AccessShareLock);
1084+
continue;
1085+
}
1086+
}
1087+
10721088
attmap = convert_tuples_by_name_map(RelationGetDescr(rel),
10731089
RelationGetDescr(parent),
10741090
gettext_noop("could not convert row type"));
@@ -15722,6 +15738,34 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
1572215738
i++;
1572315739
}
1572415740

15741+
/*
15742+
* If we're attaching a foreign table, we must fail if any of the indexes
15743+
* is a constraint index; otherwise, there's nothing to do here. Do this
15744+
* before starting work, to avoid wasting the effort of building a few
15745+
* non-unique indexes before coming across a unique one.
15746+
*/
15747+
if (attachrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
15748+
{
15749+
foreach(cell, idxes)
15750+
{
15751+
Oididx = lfirst_oid(cell);
15752+
RelationidxRel = index_open(idx, AccessShareLock);
15753+
15754+
if (idxRel->rd_index->indisunique ||
15755+
idxRel->rd_index->indisprimary)
15756+
ereport(ERROR,
15757+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
15758+
errmsg("cannot attach foreign table \"%s\" as partition of partitioned table \"%s\"",
15759+
RelationGetRelationName(attachrel),
15760+
RelationGetRelationName(rel)),
15761+
errdetail("Table \"%s\" contains unique indexes.",
15762+
RelationGetRelationName(rel))));
15763+
index_close(idxRel, AccessShareLock);
15764+
}
15765+
15766+
goto out;
15767+
}
15768+
1572515769
/*
1572615770
* For each index on the partitioned table, find a matching one in the
1572715771
* partition-to-be; if one is not found, create one.
@@ -15824,6 +15868,7 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
1582415868
index_close(idxRel, AccessShareLock);
1582515869
}
1582615870

15871+
out:
1582715872
/* Clean up. */
1582815873
for (i = 0; i < list_length(attachRelIdxs); i++)
1582915874
index_close(attachrelIdxRels[i], AccessShareLock);

‎src/backend/tcop/utility.c

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

13481348
if (relkind!=RELKIND_RELATION&&
13491349
relkind!=RELKIND_MATVIEW&&
1350-
relkind!=RELKIND_PARTITIONED_TABLE)
1350+
relkind!=RELKIND_PARTITIONED_TABLE&&
1351+
relkind!=RELKIND_FOREIGN_TABLE)
1352+
elog(ERROR,"unexpected relkind \"%c\" on partition \"%s\"",
1353+
relkind,stmt->relation->relname);
1354+
1355+
if (relkind==RELKIND_FOREIGN_TABLE&&
1356+
(stmt->unique||stmt->primary))
13511357
ereport(ERROR,
1352-
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
1353-
errmsg("cannot create index on partitioned table \"%s\"",
1358+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1359+
errmsg("cannot createuniqueindex on partitioned table \"%s\"",
13541360
stmt->relation->relname),
13551361
errdetail("Table \"%s\" contains partitions that are foreign tables.",
13561362
stmt->relation->relname)));

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

Lines changed: 54 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -748,10 +748,62 @@ ERROR: foreign-data wrapper "dummy" has no handler
748748
CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a);
749749
CREATE FOREIGN TABLE ft_part1
750750
PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0;
751-
CREATE INDEX ON lt1 (a); -- ERROR
752-
ERROR: cannot create index on partitioned table "lt1"
751+
CREATE INDEX ON lt1 (a); -- skips partition
752+
CREATE UNIQUE INDEX ON lt1 (a); -- ERROR
753+
ERROR: cannot create unique index on partitioned table "lt1"
753754
DETAIL: Table "lt1" contains partitions that are foreign tables.
755+
ALTER TABLE lt1 ADD PRIMARY KEY (a); -- ERROR
756+
ERROR: cannot create unique index on partitioned table "lt1"
757+
DETAIL: Table "lt1" contains partitions that are foreign tables.
758+
DROP TABLE lt1;
759+
CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a);
760+
CREATE INDEX ON lt1 (a);
761+
CREATE FOREIGN TABLE ft_part1
762+
PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0;
763+
CREATE FOREIGN TABLE ft_part2 (a INT) SERVER s0;
764+
ALTER TABLE lt1 ATTACH PARTITION ft_part2 FOR VALUES FROM (1000) TO (2000);
765+
DROP FOREIGN TABLE ft_part1, ft_part2;
766+
CREATE UNIQUE INDEX ON lt1 (a);
767+
ALTER TABLE lt1 ADD PRIMARY KEY (a);
768+
CREATE FOREIGN TABLE ft_part1
769+
PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0; -- ERROR
770+
ERROR: cannot create foreign partition of partitioned table "lt1"
771+
DETAIL: Table "lt1" contains indexes that are unique.
772+
CREATE FOREIGN TABLE ft_part2 (a INT NOT NULL) SERVER s0;
773+
ALTER TABLE lt1 ATTACH PARTITION ft_part2
774+
FOR VALUES FROM (1000) TO (2000); -- ERROR
775+
ERROR: cannot attach foreign table "ft_part2" as partition of partitioned table "lt1"
776+
DETAIL: Table "lt1" contains unique indexes.
777+
DROP TABLE lt1;
778+
DROP FOREIGN TABLE ft_part2;
779+
CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a);
780+
CREATE INDEX ON lt1 (a);
781+
CREATE TABLE lt1_part1
782+
PARTITION OF lt1 FOR VALUES FROM (0) TO (1000)
783+
PARTITION BY RANGE (a);
784+
CREATE FOREIGN TABLE ft_part_1_1
785+
PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0;
786+
CREATE FOREIGN TABLE ft_part_1_2 (a INT) SERVER s0;
787+
ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200);
788+
CREATE UNIQUE INDEX ON lt1 (a);
789+
ERROR: cannot create unique index on partitioned table "lt1"
790+
DETAIL: Table "lt1" contains partitions that are foreign tables.
791+
ALTER TABLE lt1 ADD PRIMARY KEY (a);
792+
ERROR: cannot create unique index on partitioned table "lt1_part1"
793+
DETAIL: Table "lt1_part1" contains partitions that are foreign tables.
794+
DROP FOREIGN TABLE ft_part_1_1, ft_part_1_2;
795+
CREATE UNIQUE INDEX ON lt1 (a);
796+
ALTER TABLE lt1 ADD PRIMARY KEY (a);
797+
CREATE FOREIGN TABLE ft_part_1_1
798+
PARTITION OF lt1_part1 FOR VALUES FROM (0) TO (100) SERVER s0;
799+
ERROR: cannot create foreign partition of partitioned table "lt1_part1"
800+
DETAIL: Table "lt1_part1" contains indexes that are unique.
801+
CREATE FOREIGN TABLE ft_part_1_2 (a INT NOT NULL) SERVER s0;
802+
ALTER TABLE lt1_part1 ATTACH PARTITION ft_part_1_2 FOR VALUES FROM (100) TO (200);
803+
ERROR: cannot attach foreign table "ft_part_1_2" as partition of partitioned table "lt1_part1"
804+
DETAIL: Table "lt1_part1" contains unique indexes.
754805
DROP TABLE lt1;
806+
DROP FOREIGN TABLE ft_part_1_2;
755807
-- ALTER FOREIGN TABLE
756808
COMMENT ON FOREIGN TABLE ft1 IS 'foreign table';
757809
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
@@ -318,9 +318,49 @@ EXPLAIN SELECT * FROM ft1; -- ERROR
318318
CREATETABLElt1 (aINT) PARTITION BY RANGE (a);
319319
CREATE FOREIGN TABLE ft_part1
320320
PARTITION OF lt1 FORVALUESFROM (0) TO (1000) SERVER s0;
321-
CREATEINDEXON lt1 (a);-- ERROR
321+
CREATEINDEXON lt1 (a);-- skips partition
322+
CREATEUNIQUE INDEXON lt1 (a);-- ERROR
323+
ALTERTABLE lt1 ADDPRIMARY KEY (a);-- ERROR
322324
DROPTABLE lt1;
323325

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

0 commit comments

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