BUG #15113: alter table .. add column .. default null leads to table rewrite

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: alexey(dot)ermakov(at)dataegret(dot)com
Subject: BUG #15113: alter table .. add column .. default null leads to table rewrite
Date: 2018-03-15 06:41:01
Message-ID: 152109606107.1215.5847972621624822423@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15113
Logged by: Alexey Ermakov
Email address: alexey(dot)ermakov(at)dataegret(dot)com
PostgreSQL version: 9.6.8
Operating system: Linux


I've recently discovered that following 2 alters produces different

alter table xx add column yy1 character varying(10) default null;
alter table xx add column yy2 character varying(10);

yy1 | character varying(10) | default NULL::character varying
| extended | |
yy2 | character varying(10) |
| extended | |

First one will lead to table rewrite which might be surprising since there
is no such difference for types without constraints, last one won't.

I found this comment which I think explains why this happens in
* An exception occurs when the new column is of a domain type: the
* might have a NOT NULL constraint, or a check constraint that
* rejects nulls. If there are any domain constraints then we construct
* an explicit NULL default value that will be passed through
* CoerceToDomain processing. (This is a tad inefficient, since it
* rewriting the table which we really don't have to do, but the present
* design of domain processing doesn't offer any simple way of checking
* the constraints more directly.)

Perhaps something need to be changed to handle such cases better.

Alexey Ermakov

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2018-03-15 09:24:55 BUG #15114: logical decoding Segmentation fault
Previous Message shailesh singh 2018-03-15 06:37:25 Enahancement in pgsql