Somehow the earlier patches missed qualifying pg_get_expr() by
pg_catalog. Fixed it along with annotating the partitioned partition
as ", PARTITIONED".
On Fri, Nov 3, 2017 at 6:09 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote:
>>
>> Right now, we could do that if we order the list by bound expression;
>> lexically DEFAULT would come before FOR VALUES ... . But that's not
>> future-safe; we may have a bound expression starting with A, B or C.
>> Beyond that it really gets tricky to order the partitions by bounds.
>
> I was just thinking in changing the query to be "order by
> is_the_default_partition, partition_name" instead of just "order by
> partition_name". Sorting by bounds rather than name (a feature whose
> worth should definitely be discussed separately IMV) sounds a lot more
> complicated.
Right now we don't have a catalog column or a SQL function which can
tell whether a given partition is default partition based on the
partition bounds or otherwise. That's what it seemed when you
suggested ordering by "is_the_default_partition". Instead I have
ordered the partitions by pg_catalog.pg_get_expr(...) = 'DEFAULT'. We
can introduce a SQL function which takes child and parent oids and
return true if it's default partition and use that here, but that
seems more than what you are suggesting here. I have added that as a
separate patch.
If we tackle the problem of listing partitions by their bounds
somehow, DEFAULT partition listing would be tackled anyway. So, may be
we should leave it outside the scope of this patch.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
From 398003b2d5f6b54e6cdd8542f653786987ef3bfe Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.ba...@enterprisedb.com>
Date: Mon, 4 Sep 2017 09:56:41 +0530
Subject: [PATCH 1/2] Improve \d+ output of a partitioned table
While displaying partitions in \d+ output of a partitioned table
annotate the partitioned partitions as "PARTITIONED".
For a partitioned table show the number of partitions even if it's 0.
Ashutosh Bapat and Amit Langote.
---
src/bin/psql/describe.c | 34 +++++++++++++++++++++++-----
src/test/regress/expected/create_table.out | 13 +++++++----
src/test/regress/expected/foreign_data.out | 3 +++
src/test/regress/expected/insert.out | 17 ++++++++++++++
src/test/regress/sql/create_table.sql | 2 +-
src/test/regress/sql/insert.sql | 4 ++++
6 files changed, 61 insertions(+), 12 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index b7b978a..44c5089 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2870,7 +2870,9 @@ describeOneTableDetails(const char *schemaname,
/* print child tables (with additional info if partitions) */
if (pset.sversion >= 100000)
printfPQExpBuffer(&buf,
- "SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid)"
+ "SELECT c.oid::pg_catalog.regclass,"
+ " pg_catalog.pg_get_expr(c.relpartbound, c.oid),"
+ " c.relkind"
" FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
" WHERE c.oid=i.inhrelid AND i.inhparent = '%s'"
" ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid);
@@ -2893,7 +2895,18 @@ describeOneTableDetails(const char *schemaname,
else
tuples = PQntuples(result);
- if (!verbose)
+ /*
+ * For a partitioned table with no partitions, always print the number
+ * of partitions as zero, even when verbose output is expected.
+ * Otherwise, we will not print "Partitions" section for a partitioned
+ * table without any partitions.
+ */
+ if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE && tuples == 0)
+ {
+ printfPQExpBuffer(&buf, _("Number of partitions: %d"), tuples);
+ printTableAddFooter(&cont, buf.data);
+ }
+ else if (!verbose)
{
/* print the number of child tables, if any */
if (tuples > 0)
@@ -2925,12 +2938,21 @@ describeOneTableDetails(const char *schemaname,
}
else
{
+ char *partitioned_note;
+
+ if (*PQgetvalue(result, i, 2) == RELKIND_PARTITIONED_TABLE)
+ partitioned_note = ", PARTITIONED";
+ else
+ partitioned_note = "";
+
if (i == 0)
- printfPQExpBuffer(&buf, "%s: %s %s",
- ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1));
+ printfPQExpBuffer(&buf, "%s: %s %s%s",
+ ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1),
+ partitioned_note);
else
- printfPQExpBuffer(&buf, "%*s %s %s",
- ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1));
+ printfPQExpBuffer(&buf, "%*s %s %s%s",
+ ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1),
+ partitioned_note);
}
if (i < tuples - 1)
appendPQExpBufferChar(&buf, ',');
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 60ab28a..ac6f576 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -428,13 +428,15 @@ ERROR: cannot inherit from partitioned table "partitioned2"
c | text | | |
d | text | | |
Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "C")
+Number of partitions: 0
-\d partitioned2
- Table "public.partitioned2"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
- a | integer | | |
+\d+ partitioned2
+ Table "public.partitioned2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
Partition key: LIST (((a + 1)))
+Number of partitions: 0
DROP TABLE partitioned, partitioned2;
--
@@ -788,5 +790,6 @@ SELECT obj_description('parted_col_comment'::regclass);
a | integer | | | | plain | | Partition key
b | text | | | | extended | |
Partition key: LIST (a)
+Number of partitions: 0
DROP TABLE parted_col_comment;
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 331f7a9..d2c184f 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1898,6 +1898,7 @@ DROP FOREIGN TABLE pt2_1;
c2 | text | | | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
+Number of partitions: 0
CREATE FOREIGN TABLE pt2_1 (
c1 integer NOT NULL,
@@ -1982,6 +1983,7 @@ ALTER TABLE pt2 ALTER c2 SET NOT NULL;
c2 | text | | not null | | extended | |
c3 | date | | | | plain | |
Partition key: LIST (c1)
+Number of partitions: 0
\d+ pt2_1
Foreign table "public.pt2_1"
@@ -2011,6 +2013,7 @@ ALTER TABLE pt2 ADD CONSTRAINT pt2chk1 CHECK (c1 > 0);
Partition key: LIST (c1)
Check constraints:
"pt2chk1" CHECK (c1 > 0)
+Number of partitions: 0
\d+ pt2_1
Foreign table "public.pt2_1"
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index b715619..b1d9c7a 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -382,6 +382,23 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p
part_null | | 1 | 1
(9 rows)
+-- test \d+ output on a table which has both partitioned and unpartitioned
+-- partitions
+\d+ list_parted
+ Table "public.list_parted"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | integer | | | | plain | |
+Partition key: LIST (lower(a))
+Partitions: part_aa_bb FOR VALUES IN ('aa', 'bb'),
+ part_cc_dd FOR VALUES IN ('cc', 'dd'),
+ part_default DEFAULT, PARTITIONED,
+ part_ee_ff FOR VALUES IN ('ee', 'ff'), PARTITIONED,
+ part_gg FOR VALUES IN ('gg'), PARTITIONED,
+ part_null FOR VALUES IN (NULL),
+ part_xx_yy FOR VALUES IN ('xx', 'yy'), PARTITIONED
+
-- cleanup
drop table range_parted, list_parted;
-- test that a default partition added as the first partition accepts any value
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index df6a6d7..f526b88 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -421,7 +421,7 @@ CREATE TABLE fail () INHERITS (partitioned2);
-- Partition key in describe output
\d partitioned
-\d partitioned2
+\d+ partitioned2
DROP TABLE partitioned, partitioned2;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index d741514..0e4399d 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -222,6 +222,10 @@ insert into list_parted select 'gg', s.a from generate_series(1, 9) s(a);
insert into list_parted (b) values (1);
select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_parted group by 1, 2 order by 1;
+-- test \d+ output on a table which has both partitioned and unpartitioned
+-- partitions
+\d+ list_parted
+
-- cleanup
drop table range_parted, list_parted;
--
1.7.9.5
From 405607115bf578e46cce65389c492aebd0f055fb Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.ba...@enterprisedb.com>
Date: Mon, 6 Nov 2017 11:49:00 +0530
Subject: [PATCH 2/2] Separate default partition from rest of the partitions.
---
src/bin/psql/describe.c | 3 ++-
src/test/regress/expected/insert.out | 4 ++--
2 files changed, 4 insertions(+), 3 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 44c5089..9916710 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2875,7 +2875,8 @@ describeOneTableDetails(const char *schemaname,
" c.relkind"
" FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
" WHERE c.oid=i.inhrelid AND i.inhparent = '%s'"
- " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid);
+ " ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
+ " c.oid::pg_catalog.regclass::pg_catalog.text;", oid);
else if (pset.sversion >= 80300)
printfPQExpBuffer(&buf,
"SELECT c.oid::pg_catalog.regclass"
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index b1d9c7a..6b4abf8 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -393,11 +393,11 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p
Partition key: LIST (lower(a))
Partitions: part_aa_bb FOR VALUES IN ('aa', 'bb'),
part_cc_dd FOR VALUES IN ('cc', 'dd'),
- part_default DEFAULT, PARTITIONED,
part_ee_ff FOR VALUES IN ('ee', 'ff'), PARTITIONED,
part_gg FOR VALUES IN ('gg'), PARTITIONED,
part_null FOR VALUES IN (NULL),
- part_xx_yy FOR VALUES IN ('xx', 'yy'), PARTITIONED
+ part_xx_yy FOR VALUES IN ('xx', 'yy'), PARTITIONED,
+ part_default DEFAULT, PARTITIONED
-- cleanup
drop table range_parted, list_parted;
--
1.7.9.5
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
