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

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