Oddity in COPY FROM handling of check constraints on partition tables

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Oddity in COPY FROM handling of check constraints on partition tables

Etsuro Fujita
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

copy-from-check-constraint-fix.patch (687 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Amit Langote-2
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


Reply | Threaded
Open this post in threaded view
|

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

Ashutosh Bapat
On Wed, Mar 28, 2018 at 6:58 AM, Amit Langote
<[hidden email]> wrote:
>> 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!
>

+1

>> Attached is a patch for fixing this issue.
>
> That looks good to me.  This one would need to be back-patched to v10.
Thanks. Please add to the next commitfest so that it doesn't get lost.
We can not add this to v11 open items since it isn't a v11 bug
exactly.


--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Reply | Threaded
Open this post in threaded view
|

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

Etsuro Fujita
In reply to this post by Amit Langote-2
(2018/03/28 10:28), Amit Langote wrote:
>> Attached is a patch for fixing this issue.
>
> That looks good to me.  This one would need to be back-patched to v10.

Thanks for the review!

Best regards,
Etsuro Fujita

Reply | Threaded
Open this post in threaded view
|

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

Etsuro Fujita
In reply to this post by Ashutosh Bapat
(2018/03/28 18:51), Ashutosh Bapat wrote:
> On Wed, Mar 28, 2018 at 6:58 AM, Amit Langote
> <[hidden email]>  wrote:

>>> Attached is a patch for fixing this issue.
>>
>> That looks good to me.  This one would need to be back-patched to v10.
> Thanks. Please add to the next commitfest so that it doesn't get lost.
> We can not add this to v11 open items since it isn't a v11 bug
> exactly.

OK, done.

Best regards,
Etsuro Fujita

Reply | Threaded
Open this post in threaded view
|

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

Robert Haas
In reply to this post by Etsuro Fujita
On Tue, Mar 27, 2018 at 9:00 AM, Etsuro Fujita
<[hidden email]> wrote:
> Attached is a patch for fixing this issue.

This no longer applies.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Reply | Threaded
Open this post in threaded view
|

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

Etsuro Fujita
(2018/05/17 0:27), Robert Haas wrote:
> On Tue, Mar 27, 2018 at 9:00 AM, Etsuro Fujita
> <[hidden email]>  wrote:
>> Attached is a patch for fixing this issue.
>
> This no longer applies.

The patch has already been committed by you [1].  Thanks for committing!

Best regards,
Etsuro Fujita

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=cfbecf8100ecb83c07c2017f843b0642580416bf;hp=870d89608e5f891275d0b752560c827dbce3d7b4

Reply | Threaded
Open this post in threaded view
|

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

Robert Haas
On Wed, May 16, 2018 at 11:30 PM, Etsuro Fujita
<[hidden email]> wrote:
>>> Attached is a patch for fixing this issue.
>> This no longer applies.
> The patch has already been committed by you [1].  Thanks for committing!

Well, that's embarrassing.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company