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

Commit1c7c317

Browse files
committed
Clarify use of temporary tables within partition trees
Since their introduction, partition trees have been a bit lossyregarding temporary relations. Inheritance trees respect the followingpatterns:1) a child relation can be temporary if the parent is permanent.2) a child relation can be temporary if the parent is temporary.3) a child relation cannot be permanent if the parent is temporary.4) The use of temporary relations also imply that when both parent andchild need to be from the same sessions.Partitions share many similar patterns with inheritance, however thehandling of the partition bounds make the situation a bit tricky forcase 1) as the partition code bases a lot of its lookup code uponPartitionDesc which does not really look after relpersistence. Thiscauses for example a temporary partition created by session A to bevisible by another session B, preventing this session B to create anextra partition which overlaps with the temporary one created by A witha non-intuitive error message. There could be use-cases where mixingpermanent partitioned tables with temporary partitions make sense, butthat would be a new feature. Partitions respect 2), 3) and 4) already.It is a bit depressing to see those error checks happening inMergeAttributes() whose purpose is different, but that's left as futurerefactoring work.Back-patch down to 10, which is where partitioning has been introduced,except that default partitions do not apply there. Documentation alsoincludes limitations related to the use of temporary tables withpartition trees.Reported-by: David RowleyAuthor: Amit Langote, Michael PaquierReviewed-by: Ashutosh Bapat, Amit Langote, Michael PaquierDiscussion:https://postgr.es/m/CAKJS1f94Ojk0og9GMkRHGt8wHTW=ijq5KzJKuoBoqWLwSVwGmw@mail.gmail.com
1 parentc992dca commit1c7c317

File tree

10 files changed

+125
-0
lines changed

10 files changed

+125
-0
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3353,6 +3353,16 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
33533353
on individual partitions, not the partitioned table.
33543354
</para>
33553355
</listitem>
3356+
3357+
<listitem>
3358+
<para>
3359+
Mixing temporary and permanent relations in the same partition tree is
3360+
not allowed. Hence, if the partitioned table is permanent, so must be
3361+
its partitions and likewise if the partitioned table is temporary. When
3362+
using temporary relations, all members of the partition tree have to be
3363+
from the same session.
3364+
</para>
3365+
</listitem>
33563366
</itemizedlist>
33573367
</para>
33583368
</sect3>

‎src/backend/commands/tablecmds.c

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1985,6 +1985,19 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
19851985
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
19861986
errmsg("inherited relation \"%s\" is not a table or foreign table",
19871987
parent->relname)));
1988+
1989+
/*
1990+
* If the parent is permanent, so must be all of its partitions. Note
1991+
* that inheritance allows that case.
1992+
*/
1993+
if (is_partition&&
1994+
relation->rd_rel->relpersistence!=RELPERSISTENCE_TEMP&&
1995+
relpersistence==RELPERSISTENCE_TEMP)
1996+
ereport(ERROR,
1997+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1998+
errmsg("cannot create a temporary relation as partition of permanent relation \"%s\"",
1999+
RelationGetRelationName(relation))));
2000+
19882001
/* Permanent rels cannot inherit from temporary ones */
19892002
if (relpersistence!=RELPERSISTENCE_TEMP&&
19902003
relation->rd_rel->relpersistence==RELPERSISTENCE_TEMP)
@@ -14135,6 +14148,14 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
1413514148
RelationGetRelationName(rel),
1413614149
RelationGetRelationName(attachrel))));
1413714150

14151+
/* If the parent is permanent, so must be all of its partitions. */
14152+
if (rel->rd_rel->relpersistence!=RELPERSISTENCE_TEMP&&
14153+
attachrel->rd_rel->relpersistence==RELPERSISTENCE_TEMP)
14154+
ereport(ERROR,
14155+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
14156+
errmsg("cannot attach a temporary relation as partition of permanent relation \"%s\"",
14157+
RelationGetRelationName(rel))));
14158+
1413814159
/* Temp parent cannot have a partition that is itself not a temp */
1413914160
if (rel->rd_rel->relpersistence==RELPERSISTENCE_TEMP&&
1414014161
attachrel->rd_rel->relpersistence!=RELPERSISTENCE_TEMP)

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3894,3 +3894,16 @@ alter table defpart_attach_test_d add check (a > 1);
38943894
alter table defpart_attach_test attach partition defpart_attach_test_d default;
38953895
INFO: partition constraint for table "defpart_attach_test_d" is implied by existing constraints
38963896
drop table defpart_attach_test;
3897+
-- check combinations of temporary and permanent relations when attaching
3898+
-- partitions.
3899+
create table perm_part_parent (a int) partition by list (a);
3900+
create temp table temp_part_parent (a int) partition by list (a);
3901+
create table perm_part_child (a int);
3902+
create temp table temp_part_child (a int);
3903+
alter table temp_part_parent attach partition perm_part_child default; -- error
3904+
ERROR: cannot attach a permanent relation as partition of temporary relation "temp_part_parent"
3905+
alter table perm_part_parent attach partition temp_part_child default; -- error
3906+
ERROR: cannot attach a temporary relation as partition of permanent relation "perm_part_parent"
3907+
alter table temp_part_parent attach partition temp_part_child default; -- ok
3908+
drop table perm_part_parent cascade;
3909+
drop table temp_part_parent cascade;

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -899,3 +899,13 @@ Partitions: boolspart_f FOR VALUES IN (false),
899899
boolspart_t FOR VALUES IN (true)
900900

901901
drop table boolspart;
902+
-- partitions mixing temporary and permanent relations
903+
create table perm_parted (a int) partition by list (a);
904+
create temporary table temp_parted (a int) partition by list (a);
905+
create table perm_part partition of temp_parted default; -- error
906+
ERROR: cannot create a permanent relation as partition of temporary relation "temp_parted"
907+
create temp table temp_part partition of perm_parted default; -- error
908+
ERROR: cannot create a temporary relation as partition of permanent relation "perm_parted"
909+
create temp table temp_part partition of temp_parted default; -- ok
910+
drop table perm_parted cascade;
911+
drop table temp_parted cascade;

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2045,6 +2045,17 @@ TRUNCATE fd_pt2; -- ERROR
20452045
ERROR: "fd_pt2_1" is not a table
20462046
DROP FOREIGN TABLE fd_pt2_1;
20472047
DROP TABLE fd_pt2;
2048+
-- foreign table cannot be part of partition tree made of temporary
2049+
-- relations.
2050+
CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a);
2051+
CREATE FOREIGN TABLE foreign_part PARTITION OF temp_parted DEFAULT
2052+
SERVER s0; -- ERROR
2053+
ERROR: cannot create a permanent relation as partition of temporary relation "temp_parted"
2054+
CREATE FOREIGN TABLE foreign_part (a int) SERVER s0;
2055+
ALTER TABLE temp_parted ATTACH PARTITION foreign_part DEFAULT; -- ERROR
2056+
ERROR: cannot attach a permanent relation as partition of temporary relation "temp_parted"
2057+
DROP FOREIGN TABLE foreign_part;
2058+
DROP TABLE temp_parted;
20482059
-- Cleanup
20492060
DROP SCHEMA foreign_schema CASCADE;
20502061
DROP ROLE regress_test_role; -- ERROR

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

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3086,3 +3086,24 @@ NOTICE: drop cascades to 2 other objects
30863086
\set VERBOSITY default
30873087
reset enable_partition_pruning;
30883088
reset constraint_exclusion;
3089+
-- Check pruning for a partition tree containing only temporary relations
3090+
create temp table pp_temp_parent (a int) partition by list (a);
3091+
create temp table pp_temp_part_1 partition of pp_temp_parent for values in (1);
3092+
create temp table pp_temp_part_def partition of pp_temp_parent default;
3093+
explain (costs off) select * from pp_temp_parent where true;
3094+
QUERY PLAN
3095+
------------------------------------
3096+
Append
3097+
-> Seq Scan on pp_temp_part_1
3098+
-> Seq Scan on pp_temp_part_def
3099+
(3 rows)
3100+
3101+
explain (costs off) select * from pp_temp_parent where a = 2;
3102+
QUERY PLAN
3103+
------------------------------------
3104+
Append
3105+
-> Seq Scan on pp_temp_part_def
3106+
Filter: (a = 2)
3107+
(3 rows)
3108+
3109+
drop table pp_temp_parent;

‎src/test/regress/sql/alter_table.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2573,3 +2573,15 @@ alter table defpart_attach_test_d add check (a > 1);
25732573
altertable defpart_attach_test attach partition defpart_attach_test_d default;
25742574

25752575
droptable defpart_attach_test;
2576+
2577+
-- check combinations of temporary and permanent relations when attaching
2578+
-- partitions.
2579+
createtableperm_part_parent (aint) partition by list (a);
2580+
create temp table temp_part_parent (aint) partition by list (a);
2581+
createtableperm_part_child (aint);
2582+
create temp table temp_part_child (aint);
2583+
altertable temp_part_parent attach partition perm_part_child default;-- error
2584+
altertable perm_part_parent attach partition temp_part_child default;-- error
2585+
altertable temp_part_parent attach partition temp_part_child default;-- ok
2586+
droptable perm_part_parent cascade;
2587+
droptable temp_part_parent cascade;

‎src/test/regress/sql/create_table.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -726,3 +726,12 @@ create table boolspart_t partition of boolspart for values in (true);
726726
createtableboolspart_f partition of boolspart forvaluesin (false);
727727
\d+ boolspart
728728
droptable boolspart;
729+
730+
-- partitions mixing temporary and permanent relations
731+
createtableperm_parted (aint) partition by list (a);
732+
create temporary table temp_parted (aint) partition by list (a);
733+
createtableperm_part partition of temp_parted default;-- error
734+
create temp table temp_part partition of perm_parted default;-- error
735+
create temp table temp_part partition of temp_parted default;-- ok
736+
droptable perm_parted cascade;
737+
droptable temp_parted cascade;

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -805,6 +805,16 @@ TRUNCATE fd_pt2; -- ERROR
805805
DROP FOREIGN TABLE fd_pt2_1;
806806
DROPTABLE fd_pt2;
807807

808+
-- foreign table cannot be part of partition tree made of temporary
809+
-- relations.
810+
CREATE TEMP TABLE temp_parted (aint) PARTITION BY LIST (a);
811+
CREATE FOREIGN TABLE foreign_part PARTITION OF temp_parted DEFAULT
812+
SERVER s0;-- ERROR
813+
CREATE FOREIGN TABLE foreign_part (aint) SERVER s0;
814+
ALTERTABLE temp_parted ATTACH PARTITION foreign_part DEFAULT;-- ERROR
815+
DROP FOREIGN TABLE foreign_part;
816+
DROPTABLE temp_parted;
817+
808818
-- Cleanup
809819
DROPSCHEMA foreign_schema CASCADE;
810820
DROP ROLE regress_test_role;-- ERROR

‎src/test/regress/sql/partition_prune.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -813,3 +813,11 @@ drop table inh_lp cascade;
813813

814814
reset enable_partition_pruning;
815815
reset constraint_exclusion;
816+
817+
-- Check pruning for a partition tree containing only temporary relations
818+
create temp table pp_temp_parent (aint) partition by list (a);
819+
create temp table pp_temp_part_1 partition of pp_temp_parent forvaluesin (1);
820+
create temp table pp_temp_part_def partition of pp_temp_parent default;
821+
explain (costs off)select*from pp_temp_parentwhere true;
822+
explain (costs off)select*from pp_temp_parentwhere a=2;
823+
droptable pp_temp_parent;

0 commit comments

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