Partitions: \d vs \d+

Started by Jesper Pedersenover 8 years ago8 messages

jesper.pedersen@redhat.com

over 8 years ago

Hi,

Using hash partitions I noticed that \d gives

D=# \d T_p63
Table "public.T_p63"
Column | Type | Collation | Nullable | Default
---------------+---------------+-----------+----------+---------

Partition of: T FOR VALUES WITH (modulus 64, remainder 63)
No partition constraint
Indexes:
"T_p63" btree (X, Y)

where as \d+ gives

Partition of: T FOR VALUES WITH (modulus 64, remainder 63)
Partition constraint: satisfies_hash_partition(64, 63,
hashint4extended(X, '8816678312871386367'::bigint))
Indexes:
"T_p63" btree (X, Y)

E.g. "No partition constraint" vs. "Partition constraint:
satisfies_hash_partition(...)".

Current master (7769fc000) with [1]https://commitfest.postgresql.org/14/1059/ and [2]https://commitfest.postgresql.org/14/1089/.

[1]: https://commitfest.postgresql.org/14/1059/
[2]: https://commitfest.postgresql.org/14/1089/

Best regards,
Jesper

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Maksim Milyutin

milyutinma@gmail.com

over 8 years ago

In reply to: Jesper Pedersen (#1)

1 attachment(s)

Re: Partitions: \d vs \d+

Hi!

On 28.09.2017 16:02, Jesper Pedersen wrote:

Hi,

Using hash partitions I noticed that \d gives

D=# \d T_p63
               Table "public.T_p63"
    Column     |     Type      | Collation | Nullable | Default
---------------+---------------+-----------+----------+---------

<remove>

Partition of: T FOR VALUES WITH (modulus 64, remainder 63)
No partition constraint
Indexes:
    "T_p63" btree (X, Y)

where as \d+ gives

D=# \d+ T_p63
               Table "public.T_p63"
    Column     |     Type      | Collation | Nullable | Default |
Storage | Stats target | Description
---------------+---------------+-----------+----------+---------+----------+--------------+-------------

<remove>

Partition of: T FOR VALUES WITH (modulus 64, remainder 63)
Partition constraint: satisfies_hash_partition(64, 63,
hashint4extended(X, '8816678312871386367'::bigint))
Indexes:
    "T_p63" btree (X, Y)

E.g. "No partition constraint" vs. "Partition constraint:
satisfies_hash_partition(...)".

I also noticed ambiguity in printing "No partition constraint" in
non-verbose mode and "Partition constraint:..." in verbose one for
partition tables regardless of the type of partition.
Attached small patch removes any output about partition constraint in
non-verbose mode.

--
Regards,
Maksim Milyutin

Attachments:

psql_def_part_fix.patchtext/x-patch; name=psql_def_part_fix.patchDownload

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d22ec68..b301219 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1900,13 +1900,16 @@ describeOneTableDetails(const char *schemaname,
 							  partdef);
 			printTableAddFooter(&cont, tmpbuf.data);
 
-			/* If there isn't any constraint, show that explicitly */
-			if (partconstraintdef == NULL || partconstraintdef[0] == '\0')
-				printfPQExpBuffer(&tmpbuf, _("No partition constraint"));
-			else
-				printfPQExpBuffer(&tmpbuf, _("Partition constraint: %s"),
-								  partconstraintdef);
-			printTableAddFooter(&cont, tmpbuf.data);
+			if (verbose)
+			{
+				/* If there isn't any constraint, show that explicitly */
+				if (partconstraintdef == NULL || partconstraintdef[0] == '\0')
+					printfPQExpBuffer(&tmpbuf, _("No partition constraint"));
+				else
+					printfPQExpBuffer(&tmpbuf, _("Partition constraint: %s"),
+									  partconstraintdef);
+				printTableAddFooter(&cont, tmpbuf.data);
+			}
 
 			PQclear(result);
 		}

Jesper Pedersen

jesper.pedersen@redhat.com

over 8 years ago

In reply to: Maksim Milyutin (#2)

Re: Partitions: \d vs \d+

On 09/28/2017 09:19 AM, Maksim Milyutin wrote:

E.g. "No partition constraint" vs. "Partition constraint:
satisfies_hash_partition(...)".

I also noticed ambiguity in printing "No partition constraint" in
non-verbose mode and "Partition constraint:..." in verbose one for
partition tables regardless of the type of partition.
Attached small patch removes any output about partition constraint in
non-verbose mode.

Yeah, that could be one way.

It should likely be backported to REL_10_STABLE, so the question is if
we are too late in the release cycle to change that output.

Best regards,
Jesper

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Maksim Milyutin

milyutinma@gmail.com

over 8 years ago

In reply to: Jesper Pedersen (#3)

Re: Partitions: \d vs \d+

On 28.09.2017 16:29, Jesper Pedersen wrote:

On 09/28/2017 09:19 AM, Maksim Milyutin wrote:

E.g. "No partition constraint" vs. "Partition constraint:
satisfies_hash_partition(...)".

I also noticed ambiguity in printing "No partition constraint" in
non-verbose mode and "Partition constraint:..." in verbose one for
partition tables regardless of the type of partition.
Attached small patch removes any output about partition constraint in
non-verbose mode.

Yeah, that could be one way.

It should likely be backported to REL_10_STABLE, so the question is if
we are too late in the release cycle to change that output.

I want to prepare more complete patch for "Partition constraint" output.
For example, I encountered the primitive output with repetitive
conjuncts for subpartition whose parent is partitioned by the same key:

Partition constraint: (/(i IS NOT NULL)/ AND (i >= 30) AND (i < 40) AND
/(i IS NOT NULL)/ AND (i = ANY (ARRAY[30, 31])))

--
Regards,
Maksim Milyutin

Amit Langote

Langote_Amit_f8@lab.ntt.co.jp

over 8 years ago

In reply to: Jesper Pedersen (#3)

Re: Partitions: \d vs \d+

On 2017/09/28 22:29, Jesper Pedersen wrote:

On 09/28/2017 09:19 AM, Maksim Milyutin wrote:

E.g. "No partition constraint" vs. "Partition constraint:
satisfies_hash_partition(...)".

I also noticed ambiguity in printing "No partition constraint" in
non-verbose mode and "Partition constraint:..." in verbose one for
partition tables regardless of the type of partition.
Attached small patch removes any output about partition constraint in
non-verbose mode.

Yeah, that could be one way.

It should likely be backported to REL_10_STABLE, so the question is if we
are too late in the release cycle to change that output.

I think the default partition commit [1]https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/bin/psql/describe.c;h=d22ec68431e231d9c781c2256a6030d66e0fd09d;hp=6fb9bdd063583fb8b60ad282aeb5256df67942e4;hb=6f6b99d1335be8ea1b74581fc489a97b109dd08a;hpb=2cf15ec8b1cb29bea149559700566a21a790b6d3 introduced some change around
that code, so the behavior is new in 11dev and I think it needs a fix like
the one that Maksim proposed.

When I check with REL_10_STABLE tip, I find things to be normal:

create table p (a int) partition by list (a);
create table p1 partition of p for values in (1);

\d+ p1
Table "public.p1"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
Partition of: p FOR VALUES IN (1)
Partition constraint: ((a IS NOT NULL) AND (a = ANY (ARRAY[1]https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/bin/psql/describe.c;h=d22ec68431e231d9c781c2256a6030d66e0fd09d;hp=6fb9bdd063583fb8b60ad282aeb5256df67942e4;hb=6f6b99d1335be8ea1b74581fc489a97b109dd08a;hpb=2cf15ec8b1cb29bea149559700566a21a790b6d3)))

Thanks,
Amit

[1]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/bin/psql/describe.c;h=d22ec68431e231d9c781c2256a6030d66e0fd09d;hp=6fb9bdd063583fb8b60ad282aeb5256df67942e4;hb=6f6b99d1335be8ea1b74581fc489a97b109dd08a;hpb=2cf15ec8b1cb29bea149559700566a21a790b6d3
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/bin/psql/describe.c;h=d22ec68431e231d9c781c2256a6030d66e0fd09d;hp=6fb9bdd063583fb8b60ad282aeb5256df67942e4;hb=6f6b99d1335be8ea1b74581fc489a97b109dd08a;hpb=2cf15ec8b1cb29bea149559700566a21a790b6d3

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Amit Langote

Langote_Amit_f8@lab.ntt.co.jp

over 8 years ago

In reply to: Maksim Milyutin (#2)

Re: Partitions: \d vs \d+

On 2017/09/28 22:19, Maksim Milyutin wrote:

I also noticed ambiguity in printing "No partition constraint" in
non-verbose mode and "Partition constraint:..." in verbose one for
partition tables regardless of the type of partition.
Attached small patch removes any output about partition constraint in
non-verbose mode.

Patch looks good.

So, we should be looking at partconstraintdef only when verbose is true,
because that's only when we set it to a valid value. Now, if
partconstraintdef is NULL even after verbose is true, that means backend
returned that there exists no constraint for that partition, which I
thought would be true for a default partition (because the commit that
introduced default partitions also introduced "No partition constraint"),
but it's really not.

For example, \d and \d+ show contradictory outputs for a default partition.

create table p (a int) partition by list (a);
create table p1 partition of p for values in (1);
create table pd partition of p default;

Perhaps, there is no case when "No partition constraint" should be output,
but I may be missing something.

Thanks,
Amit

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Maksim Milyutin

milyutinma@gmail.com

over 8 years ago

In reply to: Amit Langote (#6)

Re: Partitions: \d vs \d+

On 29.09.2017 04:33, Amit Langote wrote:

So, we should be looking at partconstraintdef only when verbose is true,
because that's only when we set it to a valid value. Now, if
partconstraintdef is NULL even after verbose is true, that means backend
returned that there exists no constraint for that partition, which I
thought would be true for a default partition (because the commit that
introduced default partitions also introduced "No partition constraint"),
but it's really not.

For example, \d and \d+ show contradictory outputs for a default partition.

create table p (a int) partition by list (a);
create table p1 partition of p for values in (1);
create table pd partition of p default;

\d pd
Table "public.pd"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Partition of: p DEFAULT
No partition constraint

\d+ pd
Table "public.pd"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
Partition of: p DEFAULT
Partition constraint: (NOT ((a IS NOT NULL) AND (a = ANY (ARRAY[1]))))

Perhaps, there is no case when "No partition constraint" should be output,
but I may be missing something.

Anyhow, we have to protect ourselves from empty output from
*pg_get_partition_constraintdef*. And printing *No partition constraint*
would be good point to start to examine why we didn't get any constraint
definition.

--
Regards,
Maksim Milyutin

Robert Haas

robertmhaas@gmail.com

over 8 years ago

In reply to: Amit Langote (#6)

Re: Partitions: \d vs \d+

On Thu, Sep 28, 2017 at 9:33 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:

Perhaps, there is no case when "No partition constraint" should be output,
but I may be missing something.

The case arises when a partitioned table has a default partition but
no other partitions.

I have committed the patch.

In v10, it's impossible to have a partition with no partition
constraint, and if it does happen due to some bug, the worst that will
happen is \d will just print nothing, rather than explicitly printing
that there's no constraint. That's not a serious problem and it
shouldn't happen anyway, so no back-patch.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers