list partition constraint shape

Hi.

I recently posted to the list about a couple of problems I saw when using
array type column as the partition key.  One of them was that the internal
partition constraint expression that we generate for list partitions is of
a form that the backend would reject if the partition key column is an
array instead of a scalar.  See for example:

create table p (a int[]) partition by list (a);
create table p1 partition of p for values in ('{1}');
create table p2 partition of p for values in ('{2, 3}', '{4, 5}');

insert into p values ('{1}');
INSERT 0 1
insert into p values ('{2, 3}'), ('{4, 5}');
INSERT 0 2

\d+ p1
...
Partition of: p FOR VALUES IN ('{1}')
Partition constraint: ((a IS NOT NULL) AND ((a)::anyarray
OPERATOR(pg_catalog.=) ANY (ARRAY['{1}'::integer[]])))

\d+ p2
...
Partition of: p FOR VALUES IN ('{2,3}', '{4,5}')
Partition constraint: ((a IS NOT NULL) AND ((a)::anyarray
OPERATOR(pg_catalog.=) ANY (ARRAY['{2,3}'::integer[], '{4,5}'::integer[]])))

Try copy-pasting the p1's constraint into SQL:

In a select query:

select tableoid::regclass, (a)::anyarray OPERATOR(pg_catalog.=) ANY
(ARRAY['{1}'::integer[]]) from p;
ERROR:  operator does not exist: integer[] pg_catalog.= integer
LINE 1: select tableoid::regclass, (a)::anyarray OPERATOR(pg_catalog...
                                                 ^
HINT:  No operator matches the given name and argument types. You might
need to add explicit type casts.

Or use in a check constraint:

alter table p1 add constraint check_a check ((a)::anyarray
OPERATOR(pg_catalog.=) ANY (ARRAY['{1}'::integer[]]));
ERROR:  operator does not exist: integer[] pg_catalog.= integer
HINT:  No operator matches the given name and argument types. You might
need to add explicit type casts.

That's because, as Tom pointed out [1], ANY/ALL expect the LHS to be a
scalar, whereas in this case a is an int[].  So, the partitioning code is
internally generating an expression that would not get through the parser.
 I think it's better that we fix that.

Attached patch is an attempt at that.  With the patch, instead of
internally generating an ANY/ALL expression, generate an OR expression
instead.  So:

\d+ p1
...
Partition of: p FOR VALUES IN ('{1}')
Partition constraint: ((a IS NOT NULL) AND ((a)::anyarray
OPERATOR(pg_catalog.=) '{1}'::integer[]))

\d+ p2
...
Partition of: p FOR VALUES IN ('{2,3}', '{4,5}')
Partition constraint: ((a IS NOT NULL) AND (((a)::anyarray
OPERATOR(pg_catalog.=) '{2,3}'::integer[]) OR ((a)::anyarray
OPERATOR(pg_catalog.=) '{4,5}'::integer[])))

The expressions above get through the parser just fine:

select tableoid::regclass, (a)::anyarray OPERATOR(pg_catalog.=)
'{1}'::integer[] from p;
 tableoid | ?column?
|---------+----------
 p1       | t
 p2       | f
 p2       | f
(3 rows)

alter table p1 add constraint check_a check ((a)::anyarray
OPERATOR(pg_catalog.=) '{1}'::integer[]);
ALTER TABLE

\d+ p1
...
Check constraints:
    "check_a" CHECK (a = '{1}'::integer[])


Will add the patch to the next CF.

Thanks,
Amit

[1] https://www.postgresql.org/message-id/7677.1512743642%40sss.pgh.pa.us