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

Commit1848b73

Browse files
committed
Teach \d+ to show partitioning constraints.
The fact that we didn't have this in the first place is likely whythe problem fixed byf8bffe9escaped detection.Patch by Amit Langote, reviewed and slightly adjusted by me.Discussion:http://postgr.es/m/CA+TgmoYWnV2GMnYLG-Czsix-E1WGAbo4D+0tx7t9NdfYBDMFsA@mail.gmail.com
1 parentf8bffe9 commit1848b73

File tree

9 files changed

+193
-22
lines changed

9 files changed

+193
-22
lines changed

‎src/backend/catalog/partition.c

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -976,6 +976,35 @@ RelationGetPartitionQual(Relation rel)
976976
returngenerate_partition_qual(rel);
977977
}
978978

979+
/*
980+
* get_partition_qual_relid
981+
*
982+
* Returns an expression tree describing the passed-in relation's partition
983+
* constraint.
984+
*/
985+
Expr*
986+
get_partition_qual_relid(Oidrelid)
987+
{
988+
Relationrel=heap_open(relid,AccessShareLock);
989+
Expr*result=NULL;
990+
List*and_args;
991+
992+
/* Do the work only if this relation is a partition. */
993+
if (rel->rd_rel->relispartition)
994+
{
995+
and_args=generate_partition_qual(rel);
996+
if (list_length(and_args)>1)
997+
result=makeBoolExpr(AND_EXPR,and_args,-1);
998+
else
999+
result=linitial(and_args);
1000+
}
1001+
1002+
/* Keep the lock. */
1003+
heap_close(rel,NoLock);
1004+
1005+
returnresult;
1006+
}
1007+
9791008
/*
9801009
* Append OIDs of rel's partitions to the list 'partoids' and for each OID,
9811010
* append pointer rel to the list 'parents'.

‎src/backend/utils/adt/ruleutils.c

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
#include"access/sysattr.h"
2525
#include"catalog/dependency.h"
2626
#include"catalog/indexing.h"
27+
#include"catalog/partition.h"
2728
#include"catalog/pg_aggregate.h"
2829
#include"catalog/pg_am.h"
2930
#include"catalog/pg_authid.h"
@@ -1728,6 +1729,37 @@ pg_get_partkeydef_worker(Oid relid, int prettyFlags,
17281729
returnbuf.data;
17291730
}
17301731

1732+
/*
1733+
* pg_get_partition_constraintdef
1734+
*
1735+
* Returns partition constraint expression as a string for the input relation
1736+
*/
1737+
Datum
1738+
pg_get_partition_constraintdef(PG_FUNCTION_ARGS)
1739+
{
1740+
OidrelationId=PG_GETARG_OID(0);
1741+
Expr*constr_expr;
1742+
intprettyFlags;
1743+
List*context;
1744+
char*consrc;
1745+
1746+
constr_expr=get_partition_qual_relid(relationId);
1747+
1748+
/* Quick exit if not a partition */
1749+
if (constr_expr==NULL)
1750+
PG_RETURN_NULL();
1751+
1752+
/*
1753+
* Deparse and return the constraint expression.
1754+
*/
1755+
prettyFlags=PRETTYFLAG_INDENT;
1756+
context=deparse_context_for(get_relation_name(relationId),relationId);
1757+
consrc=deparse_expression_pretty((Node*)constr_expr,context, false,
1758+
false,prettyFlags,0);
1759+
1760+
PG_RETURN_TEXT_P(string_to_text(consrc));
1761+
}
1762+
17311763
/*
17321764
* pg_get_constraintdef
17331765
*

‎src/bin/psql/describe.c

Lines changed: 31 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1858,13 +1858,26 @@ describeOneTableDetails(const char *schemaname,
18581858
PGresult*result;
18591859
char*parent_name;
18601860
char*partdef;
1861+
char*partconstraintdef=NULL;
18611862

1862-
printfPQExpBuffer(&buf,
1863-
"SELECT inhparent::pg_catalog.regclass, pg_get_expr(c.relpartbound, inhrelid)"
1864-
" FROM pg_catalog.pg_class c"
1865-
" JOIN pg_catalog.pg_inherits"
1866-
" ON c.oid = inhrelid"
1867-
" WHERE c.oid = '%s' AND c.relispartition;",oid);
1863+
/* If verbose, also request the partition constraint definition */
1864+
if (verbose)
1865+
printfPQExpBuffer(&buf,
1866+
"SELECT inhparent::pg_catalog.regclass,"
1867+
"pg_get_expr(c.relpartbound, inhrelid),"
1868+
"pg_get_partition_constraintdef(inhrelid)"
1869+
" FROM pg_catalog.pg_class c"
1870+
" JOIN pg_catalog.pg_inherits"
1871+
" ON c.oid = inhrelid"
1872+
" WHERE c.oid = '%s' AND c.relispartition;",oid);
1873+
else
1874+
printfPQExpBuffer(&buf,
1875+
"SELECT inhparent::pg_catalog.regclass,"
1876+
"pg_get_expr(c.relpartbound, inhrelid)"
1877+
" FROM pg_catalog.pg_class c"
1878+
" JOIN pg_catalog.pg_inherits"
1879+
" ON c.oid = inhrelid"
1880+
" WHERE c.oid = '%s' AND c.relispartition;",oid);
18681881
result=PSQLexec(buf.data);
18691882
if (!result)
18701883
gotoerror_return;
@@ -1873,9 +1886,21 @@ describeOneTableDetails(const char *schemaname,
18731886
{
18741887
parent_name=PQgetvalue(result,0,0);
18751888
partdef=PQgetvalue(result,0,1);
1889+
1890+
if (PQnfields(result)==3)
1891+
partconstraintdef=PQgetvalue(result,0,2);
1892+
18761893
printfPQExpBuffer(&tmpbuf,_("Partition of: %s %s"),parent_name,
18771894
partdef);
18781895
printTableAddFooter(&cont,tmpbuf.data);
1896+
1897+
if (partconstraintdef)
1898+
{
1899+
printfPQExpBuffer(&tmpbuf,_("Partition constraint: %s"),
1900+
partconstraintdef);
1901+
printTableAddFooter(&cont,tmpbuf.data);
1902+
}
1903+
18791904
PQclear(result);
18801905
}
18811906
}

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201705122
56+
#defineCATALOG_VERSION_NO201705131
5757

5858
#endif

‎src/include/catalog/partition.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -80,6 +80,7 @@ extern List *get_qual_from_partbound(Relation rel, Relation parent, Node *bound)
8080
externList*map_partition_varattnos(List*expr,inttarget_varno,
8181
Relationpartrel,Relationparent);
8282
externList*RelationGetPartitionQual(Relationrel);
83+
externExpr*get_partition_qual_relid(Oidrelid);
8384

8485
/* For tuple routing */
8586
externPartitionDispatch*RelationGetPartitionDispatchInfo(Relationrel,

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1992,6 +1992,8 @@ DATA(insert OID = 3415 ( pg_get_statisticsextdef PGNSP PGUID 12 1 0 0 0 f f
19921992
DESCR("index description");
19931993
DATA(insert OID = 3352 ( pg_get_partkeydef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partkeydef _null_ _null_ _null_ ));
19941994
DESCR("partition key description");
1995+
DATA(insert OID = 3408 ( pg_get_partition_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_partition_constraintdef _null_ _null_ _null_ ));
1996+
DESCR("partition constraint description");
19951997
DATA(insert OID = 1662 ( pg_get_triggerdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_triggerdef _null_ _null_ _null_ ));
19961998
DESCR("trigger description");
19971999
DATA(insert OID = 1387 ( pg_get_constraintdef PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 25 "26" _null_ _null_ _null_ _null_ _null_ pg_get_constraintdef _null_ _null_ _null_ ));

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

Lines changed: 76 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -546,6 +546,7 @@ CREATE TABLE part_forced_oids PARTITION OF oids_parted FOR VALUES FROM (1) TO (1
546546
--------+---------+-----------+----------+---------+---------+--------------+-------------
547547
a | integer | | not null | | plain | |
548548
Partition of: oids_parted FOR VALUES FROM (1) TO (10)
549+
Partition constraint: ((a >= 1) AND (a < 10))
549550
Has OIDs: yes
550551

551552
DROP TABLE oids_parted, part_forced_oids;
@@ -643,29 +644,43 @@ CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR
643644
-- create a level-2 partition
644645
CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
645646
-- Partition bound in describe output
646-
\d part_b
647-
Table "public.part_b"
648-
Column | Type | Collation | Nullable | Default
649-
--------+---------+-----------+----------+---------
650-
a | text | | |
651-
b | integer | | not null | 1
647+
\d+ part_b
648+
Table "public.part_b"
649+
Column | Type | Collation | Nullable | Default| Storage | Stats target | Description
650+
--------+---------+-----------+----------+---------+----------+--------------+-------------
651+
a | text | | | | extended | |
652+
b | integer | | not null | 1 | plain | |
652653
Partition of: parted FOR VALUES IN ('b')
654+
Partition constraint: ((a IS NOT NULL) AND (a = ANY (ARRAY['b'::text])))
653655
Check constraints:
654656
"check_a" CHECK (length(a) > 0)
655657
"part_b_b_check" CHECK (b >= 0)
656658

657659
-- Both partition bound and partition key in describe output
658-
\d part_c
659-
Table "public.part_c"
660-
Column | Type | Collation | Nullable | Default
661-
--------+---------+-----------+----------+---------
662-
a | text | | |
663-
b | integer | | not null | 0
660+
\d+ part_c
661+
Table "public.part_c"
662+
Column | Type | Collation | Nullable | Default| Storage | Stats target | Description
663+
--------+---------+-----------+----------+---------+----------+--------------+-------------
664+
a | text | | | | extended | |
665+
b | integer | | not null | 0 | plain | |
664666
Partition of: parted FOR VALUES IN ('c')
667+
Partition constraint: ((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])))
665668
Partition key: RANGE (b)
666669
Check constraints:
667670
"check_a" CHECK (length(a) > 0)
668-
Number of partitions: 1 (Use \d+ to list them.)
671+
Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
672+
673+
-- a level-2 partition's constraint will include the parent's expressions
674+
\d+ part_c_1_10
675+
Table "public.part_c_1_10"
676+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
677+
--------+---------+-----------+----------+---------+----------+--------------+-------------
678+
a | text | | | | extended | |
679+
b | integer | | not null | 0 | plain | |
680+
Partition of: part_c FOR VALUES FROM (1) TO (10)
681+
Partition constraint: ((a IS NOT NULL) AND (a = ANY (ARRAY['c'::text])) AND (b >= 1) AND (b < 10))
682+
Check constraints:
683+
"check_a" CHECK (length(a) > 0)
669684

670685
-- Show partition count in the parent's describe output
671686
-- Tempted to include \d+ output listing partitions with bound info but
@@ -682,6 +697,54 @@ Check constraints:
682697
"check_a" CHECK (length(a) > 0)
683698
Number of partitions: 3 (Use \d+ to list them.)
684699

700+
-- check that we get the expected partition constraints
701+
CREATE TABLE range_parted4 (a int, b int, c int) PARTITION BY RANGE (abs(a), abs(b), c);
702+
CREATE TABLE unbounded_range_part PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED);
703+
\d+ unbounded_range_part
704+
Table "public.unbounded_range_part"
705+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
706+
--------+---------+-----------+----------+---------+---------+--------------+-------------
707+
a | integer | | | | plain | |
708+
b | integer | | | | plain | |
709+
c | integer | | not null | | plain | |
710+
Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED)
711+
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL))
712+
713+
DROP TABLE unbounded_range_part;
714+
CREATE TABLE range_parted4_1 PARTITION OF range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED);
715+
\d+ range_parted4_1
716+
Table "public.range_parted4_1"
717+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
718+
--------+---------+-----------+----------+---------+---------+--------------+-------------
719+
a | integer | | | | plain | |
720+
b | integer | | | | plain | |
721+
c | integer | | not null | | plain | |
722+
Partition of: range_parted4 FOR VALUES FROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED)
723+
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND (abs(a) <= 1))
724+
725+
CREATE TABLE range_parted4_2 PARTITION OF range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED);
726+
\d+ range_parted4_2
727+
Table "public.range_parted4_2"
728+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
729+
--------+---------+-----------+----------+---------+---------+--------------+-------------
730+
a | integer | | | | plain | |
731+
b | integer | | | | plain | |
732+
c | integer | | not null | | plain | |
733+
Partition of: range_parted4 FOR VALUES FROM (3, 4, 5) TO (6, 7, UNBOUNDED)
734+
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 3) OR ((abs(a) = 3) AND (abs(b) > 4)) OR ((abs(a) = 3) AND (abs(b) = 4) AND (c >= 5))) AND ((abs(a) < 6) OR ((abs(a) = 6) AND (abs(b) <= 7))))
735+
736+
CREATE TABLE range_parted4_3 PARTITION OF range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);
737+
\d+ range_parted4_3
738+
Table "public.range_parted4_3"
739+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
740+
--------+---------+-----------+----------+---------+---------+--------------+-------------
741+
a | integer | | | | plain | |
742+
b | integer | | | | plain | |
743+
c | integer | | not null | | plain | |
744+
Partition of: range_parted4 FOR VALUES FROM (6, 8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED)
745+
Partition constraint: ((abs(a) IS NOT NULL) AND (abs(b) IS NOT NULL) AND ((abs(a) > 6) OR ((abs(a) = 6) AND (abs(b) >= 8))) AND (abs(a) <= 9))
746+
747+
DROP TABLE range_parted4;
685748
-- cleanup
686749
DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
687750
-- comments on partitioned tables columns

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1844,6 +1844,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
18441844
c2 | text | | | | | extended | |
18451845
c3 | date | | | | | plain | |
18461846
Partition of: pt2 FOR VALUES IN (1)
1847+
Partition constraint: ((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1])))
18471848
Server: s0
18481849
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
18491850

@@ -1914,6 +1915,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
19141915
c2 | text | | | | | extended | |
19151916
c3 | date | | | | | plain | |
19161917
Partition of: pt2 FOR VALUES IN (1)
1918+
Partition constraint: ((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1])))
19171919
Server: s0
19181920
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
19191921

@@ -1941,6 +1943,7 @@ Partitions: pt2_1 FOR VALUES IN (1)
19411943
c2 | text | | | | | extended | |
19421944
c3 | date | | not null | | | plain | |
19431945
Partition of: pt2 FOR VALUES IN (1)
1946+
Partition constraint: ((c1 IS NOT NULL) AND (c1 = ANY (ARRAY[1])))
19441947
Check constraints:
19451948
"p21chk" CHECK (c2 <> ''::text)
19461949
Server: s0

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

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -598,17 +598,33 @@ CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR
598598
CREATETABLEpart_c_1_10 PARTITION OF part_c FORVALUESFROM (1) TO (10);
599599

600600
-- Partition bound in describe output
601-
\d part_b
601+
\d+ part_b
602602

603603
-- Both partition bound and partition key in describe output
604-
\d part_c
604+
\d+ part_c
605+
606+
-- a level-2 partition's constraint will include the parent's expressions
607+
\d+ part_c_1_10
605608

606609
-- Show partition count in the parent's describe output
607610
-- Tempted to include \d+ output listing partitions with bound info but
608611
-- output could vary depending on the order in which partition oids are
609612
-- returned.
610613
\d parted
611614

615+
-- check that we get the expected partition constraints
616+
CREATETABLErange_parted4 (aint, bint, cint) PARTITION BY RANGE (abs(a), abs(b), c);
617+
CREATETABLEunbounded_range_part PARTITION OF range_parted4 FORVALUESFROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED, UNBOUNDED);
618+
\d+ unbounded_range_part
619+
DROPTABLE unbounded_range_part;
620+
CREATETABLErange_parted4_1 PARTITION OF range_parted4 FORVALUESFROM (UNBOUNDED, UNBOUNDED, UNBOUNDED) TO (1, UNBOUNDED, UNBOUNDED);
621+
\d+ range_parted4_1
622+
CREATETABLErange_parted4_2 PARTITION OF range_parted4 FORVALUESFROM (3,4,5) TO (6,7, UNBOUNDED);
623+
\d+ range_parted4_2
624+
CREATETABLErange_parted4_3 PARTITION OF range_parted4 FORVALUESFROM (6,8, UNBOUNDED) TO (9, UNBOUNDED, UNBOUNDED);
625+
\d+ range_parted4_3
626+
DROPTABLE range_parted4;
627+
612628
-- cleanup
613629
DROPTABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
614630

0 commit comments

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