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