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

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
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-06 15:10:13
Message-ID: 20181106151013.45x75xvh6jpxsqpu@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

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.)

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.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2018-11-06 17:16:01 Re: BUG #15212: Default values in partition tables don't work as expected and allow NOT NULL violation
Previous Message PG Bug reporting form 2018-11-06 12:51:58 BUG #15488: Unexpected behaviour of to_tsverctor and ts_query

Browse pgsql-hackers by date

  From Date Subject
Next Message Surafel Temesgen 2018-11-06 15:23:19 Re: pg_dump multi VALUES INSERT
Previous Message Tom Lane 2018-11-06 15:03:14 Re: Doc patch on psql output formats