BUG #16644: null value for defaults in OLD variable for trigger
The Post Office <noreply@postgresql.org>
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: fedor_erastov@mail.ru
Date: 2020-09-29T21:50:15Z
Lists: pgsql-bugs
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/
Commits
-
In INSERT/UPDATE, use the table's real tuple descriptor as target.
- 94ec005f334e 12.5 landed
- 8b39345a9d9a 14.0 landed
- 7aeb6404f0aa 13.1 landed
- 20d3fe9009dd 14.0 landed
-
Fix corner case for a BEFORE ROW UPDATE trigger returning OLD.
- de78c10072b3 12.5 landed
- d88d8ad28484 13.1 landed
- ba9f18abd365 14.0 landed
-
Use slots in trigger infrastructure, except for the actual invocation.
- ff11e7f4b9ae 12.0 cited