Re: [HACKERS] pg_class.relpartbound definition overly brittle - Mailing list pgsql-hackers

> On May 31, 2017, at 3:17 PM, Andres Freund <andres@anarazel.de> wrote:
>
> On 2017-05-31 15:06:06 -0700, Mark Dilger wrote:
>> That's cold comfort, given that most users will be looking at the pg_class
>> table and not writing C code that compares Node objects.  I wrote a bit of
>> regression test logic that checks, and sure enough the relpartbound field
>> shows up as unequal:
>>
relpartbound                                               -------------------------------------------- 
>> SELECT a.relpartbound, b.relpartbound, a.relpartbound = b.relpartbound, a.relpartbound::text = b.relpartbound::text
>>    FROM pg_class a, pg_class b
>>    WHERE a.relname = 'acct_partitioned_1'
>>      AND b.relname = 'acct_partitioned_2';
>>
relpartbound                                                            |
                                                                    relpartbound
                  | ?column? | ?column? 
>>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------
>> {PARTITIONBOUNDSPEC :strategy l :listdatums ({CONST :consttype 23000 :consttypmod -1 :constcollid 0 :constlen 2
:constbyvaltrue :constisnull false :location -1 :constvalue 2 [ 0 0 0 0 0 0 0 0 ]}) :lowerdatums <> :upperdatums <>
:location82} | {PARTITIONBOUNDSPEC :strategy l :listdatums ({CONST :consttype 23000 :consttypmod -1 :constcollid 0
:constlen2 :constbyval true :constisnull false :location -1 :constvalue 2 [ 0 0 0 0 0 0 0 0 ]}) :lowerdatums <>
:upperdatums<> :location 73} | f        | f       
>> (1 row)
>
> Normal users aren't going to make sense of node trees in the first
> place.  You should use pg_get_expr for it:
> postgres[3008][1]=# SELECT pg_get_expr(relpartbound, oid) FROM pg_class WHERE relpartbound IS NOT NULL;
> ┌──────────────────────┐
> │     pg_get_expr      │
> ├──────────────────────┤
> │ FOR VALUES IN (1, 2) │
> └──────────────────────┘
> (1 row)

I concede that mitigates the problem somewhat, though I still think a user may look
at pg_class, see there is a column that appears to show the partition boundaries,
and then decide to check whether two tables have the same partition boundaries
by comparing those fields, without passing them first through pg_get_expr(), a
function they may never have heard of.

To me, it seems odd to immortalize a SQL parsing field in the catalog definition of
the relation, but perhaps that's just my peculiar sensibilities.  If the community is more
on your side, I'm not going to argue it.

Mark Dilger