Re: BUG #15579: Adding a column with default from configuration parameter fails on 11.1

On 1/7/19 9:57 AM, Tom Lane wrote:
> =?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)org> writes:
>> ... creating the table first and then adding the column does not
>> work on 11.1. It used to work at least from version 9.3 to 10.
>> create table t (x int);
>> alter table t add c varchar(50) default
>> current_setting('public.some_setting');
>> ERROR: unrecognized configuration parameter "public.some_setting"
> I think this is a brown-paper-bag bug in the fast-column-default feature.
> current_setting() is stable, and should certainly not be treated as a
> fast default, but behold the test looks like this:
>
> /* If the DEFAULT is volatile we cannot use a missing value */
> if (colDef->missingMode && contain_volatile_functions((Node *) expr))
> colDef->missingMode = false;
>
> Of course, it should be insisting that the expression be immutable,
> not just that it not be volatile.
>
> - /* If the DEFAULT is volatile we cannot use a missing value */
> - if (colDef->missingMode && contain_volatile_functions((Node *) expr))
> + /* missingMode can only be used for immutable defaults */
> + if (colDef->missingMode && contain_mutable_functions((Node *) expr))
> colDef->missingMode = false;
>
>

Not sure who should be wearing a paper bag here, but I doubt it's me.
The feature is working here as designed and documented:

andrew=# set foo.bar = baz;
SET
andrew=# create table foo( a text);
CREATE TABLE
andrew=# insert into foo values('a');
INSERT 0 1
andrew=# alter table foo add column b text default
current_setting('foo.bar');
ALTER TABLE
andrew=# select * from foo;
 a |  b 
---+-----
 a | baz
(1 row)

andrew=# select current_setting('foo.baz');
ERROR:  unrecognized configuration parameter "foo.baz"
andrew=# alter table foo add column c text default
current_setting('foo.baz', true);
ALTER TABLE
andrew=# select * from foo;
 a |  b  | c
---+-----+---
 a | baz |
(1 row)

Stable expressions are quite ok for fast defaults. The expression is
evaluated once when the ALTER TABLE is done and the result (not the
expression) is stored in the catalog. The reason we check for volatile
expressions is precisely because we don't want all the existing rows to
get a single value in that case. This was discussed during the Postgres
11 development cycle.

Note: regardless of fast default, if you're going to use current_setting
in a default expression, you probably should use the missing_ok = true
variant. Otherwise you'll get an error any time you insert using the
default if the setting is missing.

cheers

andrew