# list partition constraint shape

From: Amit Langote Pg Hackers list partition constraint shape 2017-12-13 07:38:10 97267195-e235-89d1-a41a-c110198dfce9@lab.ntt.co.jp Raw Message | Whole Thread | Download mbox | Resend email 2017-12-13 07:38:10 from Amit Langote 📎  2018-01-23 11:13:52 from Etsuro Fujita   2018-01-25 09:44:48 from Amit Langote 📎    2018-01-25 12:17:00 from Etsuro Fujita     2018-01-26 01:15:49 from Amit Langote 📎      2018-01-26 12:31:58 from Etsuro Fujita 📎       2018-01-29 00:50:50 from Amit Langote        2018-01-29 06:15:14 from Etsuro Fujita 📎         2018-01-29 06:22:20 from Amit Langote          2018-01-31 21:08:55 from Robert Haas           2018-02-01 01:05:24 from Amit Langote pgsql-hackers

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

\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

Attachment Content-Type Size
0001-Emit-list-partition-constraint-as-OR-expression.patch text/plain 8.1 KB

### Browse pgsql-hackers by date

From Date Subject
Next Message Andres Freund 2017-12-13 08:05:06 Re: [HACKERS] Something for the TODO list: deprecating abstime and friends
Previous Message Michael Paquier 2017-12-13 07:35:53 Re: [HACKERS] replace GrantObjectType with ObjectType