[ psql - review request ] review request for \d+ tablename, \d+ indexname indenting - Mailing list pgsql-hackers

From 쿼리트릭스
Subject [ psql - review request ] review request for \d+ tablename, \d+ indexname indenting
Date
Msg-id CAO5pyYNrA3Fit6_oV_xFfpGw3SLc5qnZEcNq=NqaQMcS9MAgWw@mail.gmail.com
Whole thread Raw
Responses Re: [ psql - review request ] review request for \d+ tablename, \d+ indexname indenting
List pgsql-hackers
Hi, We are Query Tricks.
We are a project team created to provide better usability for PostgreSQL DBAs and users.
and I'm Hyunhee Ryu, a member of the project team.

There is something I would like you to consider introducing in a new version of the release.
This is related to \d+ table_name and \d+ index_name in psql, especially related to lookup lists in partition tables.
We conducted the test based on PostgreSQL 14, 15 version.

The existing partition table list is printed in this format.
-- Current Partition Table List
postgres=# \d+ p_quarter_check
                                     Partitioned table "public.p_quarter_check"
 Column |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer               |           | not null |         | plain    |             |              |
 dept   | character varying(10) |           |          |         | extended |             |              |
 name   | character varying(20) |           |          |         | extended |             |              |
 in_d   | date                  |           | not null |         | plain    |             |              |
 etc    | text                  |           |          |         | extended |             |              |
Partition key: RANGE (in_d)
Indexes:
    "parent_idx01" btree (id)
Partitions: in_p_q1 FOR VALUES FROM ('2023-01-01') TO ('2023-04-01'), PARTITIONED,
            in_p_q2 FOR VALUES FROM ('2023-04-01') TO ('2023-07-01'), PARTITIONED,
            in_p_q3 FOR VALUES FROM ('2023-07-01') TO ('2023-10-01'), PARTITIONED,
            in_p_q4 FOR VALUES FROM ('2023-10-01') TO ('2024-01-01'), PARTITIONED

It doesn't matter in the normal partition structure, but I felt uncomfortable looking up the list when there were additional subpartitions.
So to improve this inconvenience, I wrote an SQL query to query the partition table and partition index in the format below when querying the partition table and partition index in psql.

-- After Patch Partition Table List
postgres=# \d+ p_quarter_check
                                      Partitioned table "public.p_quarter_check"
 Column |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer               |           | not null |         | plain    |             |              |
 dept   | character varying(10) |           |          |         | extended |             |              |
 name   | character varying(20) |           |          |         | extended |             |              |
 in_d   | date                  |           | not null |         | plain    |             |              |
 etc    | text                  |           |          |         | extended |             |              |
Partition key: RANGE (in_d)
Indexes:
    "parent_idx01" btree (id)
Partitions: in_p_q1 FOR VALUES FROM ('2023-01-01') TO ('2023-04-01'), PARTITIONED,
                in_p_y202301 FOR VALUES FROM ('2023-01-01') TO ('2023-02-01'),
                in_p_y202302 FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'),
                in_p_y202303 FOR VALUES FROM ('2023-03-01') TO ('2023-04-01'),
            in_p_q2 FOR VALUES FROM ('2023-04-01') TO ('2023-07-01'), PARTITIONED,
                in_p_y202304 FOR VALUES FROM ('2023-04-01') TO ('2023-05-01'),
                in_p_y202305 FOR VALUES FROM ('2023-05-01') TO ('2023-06-01'),
                in_p_y202306 FOR VALUES FROM ('2023-06-01') TO ('2023-07-01'),
            in_p_q3 FOR VALUES FROM ('2023-07-01') TO ('2023-10-01'), PARTITIONED,
                in_p_y202307 FOR VALUES FROM ('2023-07-01') TO ('2023-08-01'),
                in_p_y202308 FOR VALUES FROM ('2023-08-01') TO ('2023-09-01'),
                in_p_y202309 FOR VALUES FROM ('2023-09-01') TO ('2023-10-01'),
            in_p_q4 FOR VALUES FROM ('2023-10-01') TO ('2024-01-01'), PARTITIONED,
                in_p_y202310 FOR VALUES FROM ('2023-10-01') TO ('2023-11-01'),
                in_p_y202311 FOR VALUES FROM ('2023-11-01') TO ('2023-12-01'),
                in_p_y202312 FOR VALUES FROM ('2023-12-01') TO ('2024-01-01')

Partition Index also wrote the SQL syntax so that you can look up the list with an intuitive structure.
--Current Partition Index
postgres=# \d+ parent_idx01
           Partitioned index "public.parent_idx01"
 Column |  Type   | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
 id     | integer | yes  | id         | plain   |
btree, for table "public.p_quarter_check"
Partitions: in_p_q1_id_idx, PARTITIONED,
            in_p_q2_id_idx, PARTITIONED,
            in_p_q3_id_idx, PARTITIONED,
            in_p_q4_id_idx, PARTITIONED
Access method: btree

-- After Patch  Partition Index
postgres=# \d+ parent_idx01
            Partitioned index "public.parent_idx01"
 Column |  Type   | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
 id     | integer | yes  | id         | plain   |
btree, for table "public.p_quarter_check"
Partitions: in_p_q1_id_idx, PARTITIONED,
                in_p_y202301_id_idx,
                in_p_y202302_id_idx,
                in_p_y202303_id_idx,
            in_p_q2_id_idx, PARTITIONED,
                in_p_y202304_id_idx,
                in_p_y202305_id_idx,
                in_p_y202306_id_idx,
            in_p_q3_id_idx, PARTITIONED,
                in_p_y202307_id_idx,
                in_p_y202308_id_idx,
                in_p_y202309_id_idx,
            in_p_q4_id_idx, PARTITIONED,
                in_p_y202310_id_idx,
                in_p_y202311_id_idx,
                in_p_y202312_id_idx
Access method: btree

I attached the queries used to create the partition and the queries I wrote to look up the list to the mail.
This is the patch applied to line 3370 of the 'describe.c' source file.
Based on this SQL syntax and patch file, I would like you to review the query \d+ Partition_table_name and \d+ Partition_index_name so that the SQL is reflected.

If you are not asking for a review in this way, please let me know how to proceed.
Please give me a positive answer and I will wait for your feedback.
Have a nice day.

From  Query Tricks / Hyunhee Ryu. 
Attachment

pgsql-hackers by date:

Previous
From: o.tselebrovskiy@postgrespro.ru
Date:
Subject: Error in calculating length of encoded base64 string
Next
From: "Daniel Verite"
Date:
Subject: Re: Order changes in PG16 since ICU introduction