Re: Question about pg_class column relpartbound

On Mon, Apr 2, 2018 at 9:12 AM, Garry Chen <gc92(at)cornell(dot)edu> wrote:

> Hi All,
>
> I have a question about pg_class column relpartbound would like to get
> some information. I am running postgresql v10, from pg_class column
> relpartbound I can see the internal representation of the partition. Is
> there a way or how to interpreter the internal representation? Below is
> what I see from replartbound. Thank you very much,
>
>
>
> "{PARTITIONBOUNDSPEC :strategy r :listdatums <>
> :lowerdatums ({PARTITIONRANGEDATUM :kind 0 :value {CONST :consttype 701
> :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true :constisnull
> false :location 125 :constvalue 8 [ 0 0 0 0 -88 -122 8 65 ]} :location
> 125}) :upperdatums ({PARTITIONRANGEDATUM :kind 0 :value {CONST :consttype
> 701 :consttypmod -1 :constcollid 0 :constlen 8 :constbyval true
> :constisnull false :location 139 :constvalue 8 [ 0 0 0 0 -56 -119 8 65 ]}
> :location 139}) :location 119}"
>
>
>
>
>
> Garry
>

I ran into the need to try and interpret this column as well and it turns
out there's an easier way to have postgres do it for you. You can use one
of two functions to get a more readable version of the partition bounds of
a natively partitioned table:

pg_catalog.pg_get_expr()
pg_catalog.pg_get_partition_constraintdef()

For the first one you feed it the relpartbound column along with the oid of
the associated table. This will output essentially the partition definition
that you see when you run \d+ on any partitioned table. For example:

select pg_catalog.pg_get_expr(relpartbound, oid) from pg_class where oid =
'db_development_p2017_11_30_1500'::regclass;
pg_get_expr
--------------------------------------------------------------------------
FOR VALUES FROM ('2017-11-30 15:00:00-05') TO ('2017-11-30 15:02:00-05')

The other function gives a more traditional output of the partition
constraint:

select
pg_get_partition_constraintdef('public.db_development_p2017_11_30_1500'::regclass);

pg_get_partition_constraintdef

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(("timestamp" IS NOT NULL) AND ("timestamp" >= '2017-11-30
15:00:00-05'::timestamp with time zone) AND ("timestamp" < '2017-11-30
15:02:00-05'::timestamp with time zone))

I found both of these functions by turning on the ECHO_HIDDEN variable in
psql and then just running \d+ on the given table. This shows the internal
queries that postgres is using to produce the output that you see

\set ECHO_HIDDEN on
\d+ db_development_p2017_11_30_1500

One of the queries shown from running the above is this:

********* QUERY **********
SELECT inhparent::pg_catalog.regclass,
pg_catalog.pg_get_expr(c.relpartbound, inhrelid),
pg_catalog.pg_get_partition_constraintdef(inhrelid)
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_inherits i ON c.oid = inhrelid
WHERE c.oid = '48709' AND c.relispartition;
**************************

Hope that helps

Keith
https://www.keithf4.com/