Re: BUG #15212: Default values in partition tables don't work as expected and allow NOT NULL violation

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Amit Langote <amitlangote09(at)gmail(dot)com>
Subject: Re: BUG #15212: Default values in partition tables don't work as expected and allow NOT NULL violation
Date: 2018-11-07 01:45:57
Message-ID: f9ac27e1-067d-ea4b-14d9-824769a93ee7@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-bugs pgsql-hackers

Hi,

On 2018/11/07 0:10, Alvaro Herrera wrote:
> Looking over the stuff in gram.y (to make sure there's nothing that
> could be lost), I noticed that we're losing the COLLATE clause when they
> are added to columns in partitions. I would expect part1 to end up with
> collation es_CL, or alternatively that the command throws an error:
>
> 55462 10.6 138851=# create table part (a text collate "en_US") partition by range (a);
> CREATE TABLE
> Duración: 23,511 ms
> 55462 10.6 138851=# create table part1 partition of part (a collate "es_CL") for values from ('ca') to ('cu');
> CREATE TABLE
> Duración: 111,551 ms
> 55462 10.6 138851=# \d part
> Tabla «public.part»
> Columna │ Tipo │ Collation │ Nullable │ Default
> ─────────┼──────┼───────────┼──────────┼─────────
> a │ text │ en_US │ │
> Partition key: RANGE (a)
> Number of partitions: 1 (Use \d+ to list them.)
>
> 55462 10.6 138851=# \d part1
> Tabla «public.part1»
> Columna │ Tipo │ Collation │ Nullable │ Default
> ─────────┼──────┼───────────┼──────────┼─────────
> a │ text │ en_US │ │
> Partition of: part FOR VALUES FROM ('ca') TO ('cu')
>
>
> (This case is particularly bothersome because the column is the
> partition key, so the partition range bounds would differ depending on
> which collation is used to compare. I assume we'd always want to use
> the parent table's collation; so there's even a stronger case for
> raising an error if it doesn't match the parent's. However, this case
> could arise for other columns too, where it's not *so* bad, but still
> not completely correct I think.)

Thank you for investigating.

I think the result in this case should be an error, just as it would in
the regular inheritance case.

create table parent (a text);
create table child (a text collate "en_US") inherits (parent);
NOTICE: merging column "a" with inherited definition
ERROR: column "a" has a collation conflict
DETAIL: "default" versus "en_US"

In fact, I see that ATTACH PARTITION rejects a partition if collations
don't match.

create table part (a text collate "en_US") partition by range (a);
create table part1 (a text collate "es_CL");
alter table part attach partition part1 for values from ('ca') to ('cu');
ERROR: child table "part1" has different collation for column "a"

> This happens on unpatched code, and doesn't seem covered by any tests.
> However, this seems an independent bug that isn't affected by this
> patch.
>
> The only other things there are deferrability markers, which seem to be
> propagated in a relatively sane fashion (but no luck if you want to add
> foreign keys with mismatching deferrability than the parent's -- you
> just get a dupe, and there's no way in the grammar to change the flags
> for the existing constraint). But you can add a UNIQUE DEFERRED
> constraint in a partition that wasn't there in the parent, for example.

Looking again at MergeAttributes, it seems that the fix for disallowing
mismatching collations is not that invasive. PFA a patch that applies on
top of your 0001-Revise-attribute-handling-code-on-partition-creation.patch.

I haven't looked closely at the cases involving deferrability markers though.

Thanks,
Amit

Attachment Content-Type Size
0002-Disallow-creating-partitions-with-mismatching-collat.patch text/plain 3.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2018-11-07 01:56:00 Re: Doc patch on psql output formats
Previous Message Michael Paquier 2018-11-07 01:17:37 Re: First-draft release notes for back-branch releases

Browse pgsql-bugs by date

  From Date Subject
Next Message Etsuro Fujita 2018-11-07 03:44:33 Re: BUG #15449: file_fdw using program cause exit code error when using LIMIT
Previous Message M Zav. 2018-11-07 00:36:18 postgres_fdw