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 |
Thread: | |
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
Description:
Hello,
I've recently discovered that following 2 alters produces different
results:
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
https://github.com/postgres/postgres/blob/REL_10_STABLE/src/backend/commands/tablecmds.c#L5323:
* An exception occurs when the new column is of a domain type: the
domain
* might have a NOT NULL constraint, or a check constraint that
indirectly
* 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
causes
* 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.
Thanks,
Alexey Ermakov
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 |