The following bug has been logged on the website:
Bug reference: 16644
Logged by: Fedor Erastov
Email address: fedor_erastov@mail.ru
PostgreSQL version: 13.0
Operating system: CentOS, MacOS
Description:
Start history:
https://postgresteam.slack.com/archives/C0FS3UTAP/p1601206489174900
Found weird postgres behavior (seems to work for >11 versions):
1. There is a table with data, and trigger before update for each row
2. Add a new column with not null default value
3. When trying to update the value in the old column, raise `ERROR: null
value in column violates not-null constraint`
Most likely this is because the default values in >11 versions are not
really put into the table when adding a column. And an important feature is
that if the trigger returns NEW, then there are no problems, and if OLD,
then an error appears. Although if you check these two variables, they will
be absolutely equal.
Full PoC:
create table test(a integer);
create or replace function set_updated_at_column() returns trigger
language plpgsql
as
$$
BEGIN
RAISE NOTICE 'OLD: %, NEW: %, COMPARE: %', OLD, NEW, OLD = NEW;
RETURN OLD;
END;
$$;
create trigger update_test
before update
on test
for each row
execute procedure set_updated_at_column();
insert into test values(1);
-- adds new column
alter table test add column b integer not null default 1;
-- fails with a not null constraint violation, which is not the case, since
the tuple is (1,1) not (1,null)
update test set a=1 where a=1;
Interesting observation: if you reassign the value of old.b old.b := old.b;
the error is gone.
With the help of the slack user @easteregg, it turned out to be possible to
find the first bad commit in which this error occurs, that would be:
https://github.com/postgres/postgres/commit/ff11e7f4b9ae017585c3ba146db7ba39c31f209a
In addition, I have a suspicion that it has something to do with work "lazy"
defaults
https://dataegret.com/2018/03/waiting-for-postgresql-11-pain-free-add-column-with-non-null-defaults/