BUG #16242: convert_tuple_* not handling missing values correctly - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      16242
Logged by:          Andrew Gierth
Email address:      andrew@tao11.riddles.org.uk
PostgreSQL version: 12.1
Operating system:   any
Description:

The following example shows a tuple appearing with NULLs in a column
declared NOT NULL, because there is a missing default value not being filled
in. The cause is that convert_tuples_by_name, or more precisely
check_attrmap_match, thinks that it's enough for the tupdescs to match up as
regards attnums/names/types, without considering that the source tupdesc
might have missing values that the destination does not. So it incorrectly
concludes that no conversion is needed, and the missing values become
null.

This test case uses a trigger on a partitioned table to demonstrate, but the
actual problem was discovered in a different context (Vik Fearing's
"periods" extension). It is likely that other users of
convert_tuples_by_name (and I know there are other non-core modules that use
this) are affected likewise.

-- partitioned table (parent)
create table bar1 (a integer, b integer not null default 1)
  partition by range (a);

-- this will become a partition:
create table bar2 (a integer);
insert into bar2 values (1);
alter table bar2 add column b integer not null default 1;

-- (at this point bar2 contains tuple with natts=1)

alter table bar1 attach partition bar2 default;

-- this works:

select * from bar1;

-- this doesn't:

create function xtrig()
  returns trigger
  language plpgsql
as $$
  declare
    r record;
  begin
    for r in select * from old loop
      raise info 'a=%, b=%', r.a, r.b;
    end loop;
    return NULL;
  end;
$$;

create trigger xtrig
  after update on bar1
  referencing old table as old
  for each statement execute procedure xtrig();

update bar1 set a = a + 1;