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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: juergen+postgresql(at)strobel(dot)info
Subject: BUG #15212: Default values in partition tables don't work as expected and allow NOT NULL violation
Date: 2018-05-28 00:30:21
Message-ID: 152746742177.1291.9847032632907407358@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-bugs pgsql-hackers

The following bug has been logged on the website:

Bug reference: 15212
Logged by: Jürgen Strobel
Email address: juergen+postgresql(at)strobel(dot)info
PostgreSQL version: 10.4
Operating system: Debian
Description:

I found unexpected behavior when playing around with declarative
partitioning.

First, any way to define defaults on (child) partition tables is silently
ignored when inserting into the master table, but not when inserting into
the child table. The easiest way to reproduce this is:

jue=> create table ptest (a int, b int) partition by list (a);
CREATE TABLE
jue=> create table ptest1 partition of ptest (b default 7) for values in
(1);
CREATE TABLE
jue=> insert into ptest (a) values (1);
INSERT 0 1
jue=> table ptest;
a | b
---+---
1 |
(1 row)

jue=> insert into ptest1 (a) values (1);
INSERT 0 1
jue=> table ptest;
a | b
---+---
1 |
1 | 7
(2 rows)

Second, this is a way to violate a NOT NULL constraint, presumably because a
default value should be applied later but isn't:

jue=> create table ptest (a int, b int not null) partition by list (a);
CREATE TABLE
jue=> create table ptest1 partition of ptest (b default 7) for values in
(1);
CREATE TABLE
jue=> insert into ptest (a) values (1);
INSERT 0 1
jue=> select * from ptest where b is null;
a | b
---+---
1 |
(1 row)

The same happens for defaults using nextval(sequence), either if specified
directly or as SERIAL columns with ALTER TABLE ... ATTACH PARTITION. My
current workaround is to use a before-row trigger to apply the default
value.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2018-05-28 00:46:33 Re: SP-GiST failing to complete SP-GiST index build
Previous Message Peter Geoghegan 2018-05-28 00:24:37 Re: SP-GiST failing to complete SP-GiST index build

Browse pgsql-bugs by date

  From Date Subject
Next Message Huong Dangminh 2018-05-28 02:24:35 RE: PostgreSQL 2018-05-10 Security Update Release
Previous Message Vik Fearing 2018-05-25 21:45:58 Re: Primary key error in INFORMATION_SCHEMA views