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
