From: | Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Oddity in COPY FROM handling of check constraints on partition tables |
Date: | 2018-03-27 13:00:36 |
Message-ID: | 5ABA4074.1090500@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
copy-from-check-constraint-fix.patch | text/x-diff | 667 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2018-03-27 13:02:26 | Re: Problem while setting the fpw with SIGHUP |
Previous Message | Tomas Vondra | 2018-03-27 12:54:44 | Re: pg_class.reltuples of brin indexes |