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.

Attached is a patch for fixing this issue.

Best regards,
Etsuro Fujita
*** a/src/backend/commands/copy.c
--- b/src/backend/commands/copy.c
***************
*** 2709,2715 **** CopyFrom(CopyState cstate)
  					check_partition_constr = false;
  
  				/* Check the constraints of the tuple */
! 				if (cstate->rel->rd_att->constr || check_partition_constr)
  					ExecConstraints(resultRelInfo, slot, estate, true);
  
  				if (useHeapMultiInsert)
--- 2709,2716 ----
  					check_partition_constr = false;
  
  				/* Check the constraints of the tuple */
! 				if (resultRelInfo->ri_RelationDesc->rd_att->constr ||
! 					check_partition_constr)
  					ExecConstraints(resultRelInfo, slot, estate, true);
  
  				if (useHeapMultiInsert)

Reply via email to