BUG #16644: null value for defaults in OLD variable for trigger
От | PG Bug reporting form |
---|---|
Тема | BUG #16644: null value for defaults in OLD variable for trigger |
Дата | |
Msg-id | 16644-5da7ef98a7ac4545@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #16644: null value for defaults in OLD variable for trigger
|
Список | 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/
В списке pgsql-bugs по дате отправления: