Re: Oddity in COPY FROM handling of check constraints on partition tables

Fujita-san,

On 2018/03/27 22:00, Etsuro Fujita wrote:
> Hi,
>
> While updating the tuple-routing-for-foreign-partitions patch, I noticed
> oddity in the COPY FROM handling of check constraints on partition
> tables. Here is an example:
>
> postgres=# create table pt (a int, b int) partition by list (a);
> CREATE TABLE
> postgres=# create table p1 partition of pt for values in (1);
> CREATE TABLE
> postgres=# alter table p1 add check (b > 0);
> ALTER TABLE
> postgres=# copy pt from '/home/pgsql/copy_data.csv' (format csv,
> delimiter ',');
> COPY 1
> postgres=# select tableoid::regclass, * from pt;
> tableoid | a | b
> ----------+---+----
> p1 | 1 | -1
> (1 row)
>
> where the file '/home/pgsql/copy_data.csv' has a single row data
>
> $ cat /home/pgsql/copy_data.csv
> 1,-1
>
> which violates the constraint on the column b (ie, b > 0), so this
> should abort. The reason for that is because CopyFrom looks at the
> parent relation's constraints, not the partition's constraints, when
> checking the constraint against the input row.

Good catch, thanks!

> Attached is a patch for fixing this issue.

That looks good to me. This one would need to be back-patched to v10.

Thanks,
Amit