Обсуждение: BUG #15113: alter table .. add column .. default null leads to tablerewrite
BUG #15113: alter table .. add column .. default null leads to tablerewrite
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 15113 Logged by: Alexey Ermakov Email address: alexey.ermakov@dataegret.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