Fast default trigger and expand_tuple fixes

Ensure that triggers get properly filled in tuples for the OLD value.
Also fix the logic of detecting missing null values. The previous logic
failed to detect a missing null column before the first missing column
with a default. Fixing this has simplified the logic a bit.

Regression tests are added to test changes. This should ensure better
coverage of expand_tuple().

Original bug reports, and some code and test scripts from Tomas Vondra

Backpatch to release 11.

2 files changed, 303 insertions, 2 deletions

diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out
index f3d783c2813..48bd360a799 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -539,8 +539,197 @@ FROM t1;
  1 | 0
 (20 rows)
 
-DROP TABLE t1;
 DROP TABLE T;
+-- test that we account for missing columns without defaults correctly
+-- in expand_tuple, and that rows are correctly expanded for triggers
+CREATE FUNCTION test_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+
+begin
+    raise notice 'old tuple: %', to_json(OLD)::text;
+    if TG_OP = 'DELETE'
+    then
+       return OLD;
+    else
+       return NEW;
+    end if;
+end;
+
+$$;
+-- 2 new columns, both have defaults
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,3);
+ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
+ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 | 3 | 4 | 5
+(1 row)
+
+UPDATE t SET y = 2;
+NOTICE:  old tuple: {"id":1,"a":1,"b":2,"c":3,"x":4,"y":5}
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 | 3 | 4 | 2
+(1 row)
+
+DROP TABLE t;
+-- 2 new columns, first has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,3);
+ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
+ALTER TABLE t ADD COLUMN y int;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 | 3 | 4 |  
+(1 row)
+
+UPDATE t SET y = 2;
+NOTICE:  old tuple: {"id":1,"a":1,"b":2,"c":3,"x":4,"y":null}
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 | 3 | 4 | 2
+(1 row)
+
+DROP TABLE t;
+-- 2 new columns, second has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,3);
+ALTER TABLE t ADD COLUMN x int;
+ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 | 3 |   | 5
+(1 row)
+
+UPDATE t SET y = 2;
+NOTICE:  old tuple: {"id":1,"a":1,"b":2,"c":3,"x":null,"y":5}
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 | 3 |   | 2
+(1 row)
+
+DROP TABLE t;
+-- 2 new columns, neither has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,3);
+ALTER TABLE t ADD COLUMN x int;
+ALTER TABLE t ADD COLUMN y int;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 | 3 |   |  
+(1 row)
+
+UPDATE t SET y = 2;
+NOTICE:  old tuple: {"id":1,"a":1,"b":2,"c":3,"x":null,"y":null}
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 | 3 |   | 2
+(1 row)
+
+DROP TABLE t;
+-- same as last 4 tests but here the last original column has a NULL value
+-- 2 new columns, both have defaults
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,NULL);
+ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
+ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 |   | 4 | 5
+(1 row)
+
+UPDATE t SET y = 2;
+NOTICE:  old tuple: {"id":1,"a":1,"b":2,"c":null,"x":4,"y":5}
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 |   | 4 | 2
+(1 row)
+
+DROP TABLE t;
+-- 2 new columns, first has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,NULL);
+ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
+ALTER TABLE t ADD COLUMN y int;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 |   | 4 |  
+(1 row)
+
+UPDATE t SET y = 2;
+NOTICE:  old tuple: {"id":1,"a":1,"b":2,"c":null,"x":4,"y":null}
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 |   | 4 | 2
+(1 row)
+
+DROP TABLE t;
+-- 2 new columns, second has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,NULL);
+ALTER TABLE t ADD COLUMN x int;
+ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 |   |   | 5
+(1 row)
+
+UPDATE t SET y = 2;
+NOTICE:  old tuple: {"id":1,"a":1,"b":2,"c":null,"x":null,"y":5}
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 |   |   | 2
+(1 row)
+
+DROP TABLE t;
+-- 2 new columns, neither has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,NULL);
+ALTER TABLE t ADD COLUMN x int;
+ALTER TABLE t ADD COLUMN y int;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 |   |   |  
+(1 row)
+
+UPDATE t SET y = 2;
+NOTICE:  old tuple: {"id":1,"a":1,"b":2,"c":null,"x":null,"y":null}
+SELECT * FROM t;
+ id | a | b | c | x | y 
+----+---+---+---+---+---
+  1 | 1 | 2 |   |   | 2
+(1 row)
+
+DROP TABLE t;
+-- cleanup
+DROP FUNCTION test_trigger();
+DROP TABLE t1;
 DROP FUNCTION set(name);
 DROP FUNCTION comp();
 DROP TABLE m;
diff --git a/src/test/regress/sql/fast_default.sql b/src/test/regress/sql/fast_default.sql
index 7b9cc47cef5..06205cb39f0 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -360,8 +360,120 @@ SELECT a,
        AS z
 FROM t1;
 
-DROP TABLE t1;
 DROP TABLE T;
+
+-- test that we account for missing columns without defaults correctly
+-- in expand_tuple, and that rows are correctly expanded for triggers
+
+CREATE FUNCTION test_trigger()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+
+begin
+    raise notice 'old tuple: %', to_json(OLD)::text;
+    if TG_OP = 'DELETE'
+    then
+       return OLD;
+    else
+       return NEW;
+    end if;
+end;
+
+$$;
+
+-- 2 new columns, both have defaults
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,3);
+ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
+ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+UPDATE t SET y = 2;
+SELECT * FROM t;
+DROP TABLE t;
+
+-- 2 new columns, first has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,3);
+ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
+ALTER TABLE t ADD COLUMN y int;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+UPDATE t SET y = 2;
+SELECT * FROM t;
+DROP TABLE t;
+
+-- 2 new columns, second has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,3);
+ALTER TABLE t ADD COLUMN x int;
+ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+UPDATE t SET y = 2;
+SELECT * FROM t;
+DROP TABLE t;
+
+-- 2 new columns, neither has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,3);
+ALTER TABLE t ADD COLUMN x int;
+ALTER TABLE t ADD COLUMN y int;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+UPDATE t SET y = 2;
+SELECT * FROM t;
+DROP TABLE t;
+
+-- same as last 4 tests but here the last original column has a NULL value
+-- 2 new columns, both have defaults
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,NULL);
+ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
+ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+UPDATE t SET y = 2;
+SELECT * FROM t;
+DROP TABLE t;
+
+-- 2 new columns, first has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,NULL);
+ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
+ALTER TABLE t ADD COLUMN y int;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+UPDATE t SET y = 2;
+SELECT * FROM t;
+DROP TABLE t;
+
+-- 2 new columns, second has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,NULL);
+ALTER TABLE t ADD COLUMN x int;
+ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+UPDATE t SET y = 2;
+SELECT * FROM t;
+DROP TABLE t;
+
+-- 2 new columns, neither has default
+CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
+INSERT INTO t (a,b,c) VALUES (1,2,NULL);
+ALTER TABLE t ADD COLUMN x int;
+ALTER TABLE t ADD COLUMN y int;
+CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
+SELECT * FROM t;
+UPDATE t SET y = 2;
+SELECT * FROM t;
+DROP TABLE t;
+
+-- cleanup
+DROP FUNCTION test_trigger();
+DROP TABLE t1;
 DROP FUNCTION set(name);
 DROP FUNCTION comp();
 DROP TABLE m;