*** /home/ubuntu/build/postgres/src/test/regress/expected/rowsecurity.out 2015-12-19 11:20:48.888747200 -0800 --- /home/ubuntu/build/postgres/src/test/regress/results/rowsecurity.out 2015-12-19 11:47:22.560492778 -0800 *************** *** 186,204 **** (7 rows) EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); ! QUERY PLAN ! ---------------------------------------------------------------------- Hash Join ! Hash Cond: (category.cid = document.cid) ! -> Seq Scan on category -> Hash ! -> Subquery Scan on document ! Filter: f_leak(document.dtitle) ! -> Seq Scan on document document_1 ! Filter: (dlevel <= $0) ! InitPlan 1 (returns $0) ! -> Index Scan using uaccount_pkey on uaccount ! Index Cond: (pguser = "current_user"()) (11 rows) -- only owner can change policies --- 186,204 ---- (7 rows) EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); ! QUERY PLAN ! ---------------------------------------------------------------- Hash Join ! Hash Cond: (document.cid = category.cid) ! -> Subquery Scan on document ! Filter: f_leak(document.dtitle) ! -> Seq Scan on document document_1 ! Filter: (dlevel <= $0) ! InitPlan 1 (returns $0) ! -> Index Scan using uaccount_pkey on uaccount ! Index Cond: (pguser = "current_user"()) -> Hash ! -> Seq Scan on category (11 rows) -- only owner can change policies *************** *** 1681,1721 **** PREPARE plancache_test AS SELECT * FROM z1 WHERE f_leak(b); EXPLAIN EXECUTE plancache_test; ! QUERY PLAN ! --------------------------------------------------------------- ! Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 0) (4 rows) PREPARE plancache_test2 AS WITH q AS (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2; EXPLAIN EXECUTE plancache_test2; ! QUERY PLAN ! ----------------------------------------------------------------------- ! Nested Loop (cost=29.11..86.78 rows=2540 width=72) CTE q ! -> Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 0) ! -> CTE Scan on q (cost=0.00..0.04 rows=2 width=36) ! -> Materialize (cost=0.00..29.05 rows=1270 width=36) ! -> Seq Scan on z2 (cost=0.00..22.70 rows=1270 width=36) (9 rows) PREPARE plancache_test3 AS WITH q AS (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b); EXPLAIN EXECUTE plancache_test3; ! QUERY PLAN ! --------------------------------------------------------------------------- ! Nested Loop (cost=22.70..108.97 rows=2540 width=72) CTE q ! -> Seq Scan on z2 (cost=0.00..22.70 rows=1270 width=36) ! -> CTE Scan on q (cost=0.00..25.40 rows=1270 width=36) ! -> Materialize (cost=0.00..29.12 rows=2 width=36) ! -> Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 0) (9 rows) --- 1681,1721 ---- PREPARE plancache_test AS SELECT * FROM z1 WHERE f_leak(b); EXPLAIN EXECUTE plancache_test; ! QUERY PLAN ! ---------------------------------------------------------------- ! Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 0) (4 rows) PREPARE plancache_test2 AS WITH q AS (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2; EXPLAIN EXECUTE plancache_test2; ! QUERY PLAN ! ------------------------------------------------------------------------ ! Nested Loop (cost=48.38..217.84 rows=10200 width=72) CTE q ! -> Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 0) ! -> CTE Scan on q (cost=0.00..0.08 rows=4 width=36) ! -> Materialize (cost=0.00..48.25 rows=2550 width=36) ! -> Seq Scan on z2 (cost=0.00..35.50 rows=2550 width=36) (9 rows) PREPARE plancache_test3 AS WITH q AS (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b); EXPLAIN EXECUTE plancache_test3; ! QUERY PLAN ! ---------------------------------------------------------------------------- ! Nested Loop (cost=35.50..262.39 rows=10200 width=72) CTE q ! -> Seq Scan on z2 (cost=0.00..35.50 rows=2550 width=36) ! -> CTE Scan on q (cost=0.00..51.00 rows=2550 width=36) ! -> Materialize (cost=0.00..48.40 rows=4 width=36) ! -> Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 0) (9 rows) *************** *** 1739,1777 **** (4 rows) EXPLAIN EXECUTE plancache_test; ! QUERY PLAN ! --------------------------------------------------------------- ! Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 0) (4 rows) EXPLAIN EXECUTE plancache_test2; ! QUERY PLAN ! ----------------------------------------------------------------------- ! Nested Loop (cost=29.11..86.78 rows=2540 width=72) CTE q ! -> Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 0) ! -> CTE Scan on q (cost=0.00..0.04 rows=2 width=36) ! -> Materialize (cost=0.00..29.05 rows=1270 width=36) ! -> Seq Scan on z2 (cost=0.00..22.70 rows=1270 width=36) (9 rows) EXPLAIN EXECUTE plancache_test3; ! QUERY PLAN ! --------------------------------------------------------------------------- ! Nested Loop (cost=22.70..108.97 rows=2540 width=72) CTE q ! -> Seq Scan on z2 (cost=0.00..22.70 rows=1270 width=36) ! -> CTE Scan on q (cost=0.00..25.40 rows=1270 width=36) ! -> Materialize (cost=0.00..29.12 rows=2 width=36) ! -> Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 0) (9 rows) --- 1739,1777 ---- (4 rows) EXPLAIN EXECUTE plancache_test; ! QUERY PLAN ! ---------------------------------------------------------------- ! Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 0) (4 rows) EXPLAIN EXECUTE plancache_test2; ! QUERY PLAN ! ------------------------------------------------------------------------ ! Nested Loop (cost=48.38..217.84 rows=10200 width=72) CTE q ! -> Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 0) ! -> CTE Scan on q (cost=0.00..0.08 rows=4 width=36) ! -> Materialize (cost=0.00..48.25 rows=2550 width=36) ! -> Seq Scan on z2 (cost=0.00..35.50 rows=2550 width=36) (9 rows) EXPLAIN EXECUTE plancache_test3; ! QUERY PLAN ! ---------------------------------------------------------------------------- ! Nested Loop (cost=35.50..262.39 rows=10200 width=72) CTE q ! -> Seq Scan on z2 (cost=0.00..35.50 rows=2550 width=36) ! -> CTE Scan on q (cost=0.00..51.00 rows=2550 width=36) ! -> Materialize (cost=0.00..48.40 rows=4 width=36) ! -> Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 0) (9 rows) *************** *** 1795,1833 **** (4 rows) EXPLAIN EXECUTE plancache_test; ! QUERY PLAN ! --------------------------------------------------------------- ! Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 1) (4 rows) EXPLAIN EXECUTE plancache_test2; ! QUERY PLAN ! ----------------------------------------------------------------------- ! Nested Loop (cost=29.11..86.78 rows=2540 width=72) CTE q ! -> Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 1) ! -> CTE Scan on q (cost=0.00..0.04 rows=2 width=36) ! -> Materialize (cost=0.00..29.05 rows=1270 width=36) ! -> Seq Scan on z2 (cost=0.00..22.70 rows=1270 width=36) (9 rows) EXPLAIN EXECUTE plancache_test3; ! QUERY PLAN ! --------------------------------------------------------------------------- ! Nested Loop (cost=22.70..108.97 rows=2540 width=72) CTE q ! -> Seq Scan on z2 (cost=0.00..22.70 rows=1270 width=36) ! -> CTE Scan on q (cost=0.00..25.40 rows=1270 width=36) ! -> Materialize (cost=0.00..29.12 rows=2 width=36) ! -> Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 1) (9 rows) --- 1795,1833 ---- (4 rows) EXPLAIN EXECUTE plancache_test; ! QUERY PLAN ! ---------------------------------------------------------------- ! Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 1) (4 rows) EXPLAIN EXECUTE plancache_test2; ! QUERY PLAN ! ------------------------------------------------------------------------ ! Nested Loop (cost=48.38..217.84 rows=10200 width=72) CTE q ! -> Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 1) ! -> CTE Scan on q (cost=0.00..0.08 rows=4 width=36) ! -> Materialize (cost=0.00..48.25 rows=2550 width=36) ! -> Seq Scan on z2 (cost=0.00..35.50 rows=2550 width=36) (9 rows) EXPLAIN EXECUTE plancache_test3; ! QUERY PLAN ! ---------------------------------------------------------------------------- ! Nested Loop (cost=35.50..262.39 rows=10200 width=72) CTE q ! -> Seq Scan on z2 (cost=0.00..35.50 rows=2550 width=36) ! -> CTE Scan on q (cost=0.00..51.00 rows=2550 width=36) ! -> Materialize (cost=0.00..48.40 rows=4 width=36) ! -> Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 1) (9 rows) *************** *** 1851,1889 **** (4 rows) EXPLAIN EXECUTE plancache_test; ! QUERY PLAN ! --------------------------------------------------------------- ! Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 1) (4 rows) EXPLAIN EXECUTE plancache_test2; ! QUERY PLAN ! ----------------------------------------------------------------------- ! Nested Loop (cost=29.11..86.78 rows=2540 width=72) CTE q ! -> Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 1) ! -> CTE Scan on q (cost=0.00..0.04 rows=2 width=36) ! -> Materialize (cost=0.00..29.05 rows=1270 width=36) ! -> Seq Scan on z2 (cost=0.00..22.70 rows=1270 width=36) (9 rows) EXPLAIN EXECUTE plancache_test3; ! QUERY PLAN ! --------------------------------------------------------------------------- ! Nested Loop (cost=22.70..108.97 rows=2540 width=72) CTE q ! -> Seq Scan on z2 (cost=0.00..22.70 rows=1270 width=36) ! -> CTE Scan on q (cost=0.00..25.40 rows=1270 width=36) ! -> Materialize (cost=0.00..29.12 rows=2 width=36) ! -> Subquery Scan on z1 (cost=0.00..29.11 rows=2 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..29.05 rows=6 width=36) Filter: ((a % 2) = 1) (9 rows) --- 1851,1889 ---- (4 rows) EXPLAIN EXECUTE plancache_test; ! QUERY PLAN ! ---------------------------------------------------------------- ! Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 1) (4 rows) EXPLAIN EXECUTE plancache_test2; ! QUERY PLAN ! ------------------------------------------------------------------------ ! Nested Loop (cost=48.38..217.84 rows=10200 width=72) CTE q ! -> Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 1) ! -> CTE Scan on q (cost=0.00..0.08 rows=4 width=36) ! -> Materialize (cost=0.00..48.25 rows=2550 width=36) ! -> Seq Scan on z2 (cost=0.00..35.50 rows=2550 width=36) (9 rows) EXPLAIN EXECUTE plancache_test3; ! QUERY PLAN ! ---------------------------------------------------------------------------- ! Nested Loop (cost=35.50..262.39 rows=10200 width=72) CTE q ! -> Seq Scan on z2 (cost=0.00..35.50 rows=2550 width=36) ! -> CTE Scan on q (cost=0.00..51.00 rows=2550 width=36) ! -> Materialize (cost=0.00..48.40 rows=4 width=36) ! -> Subquery Scan on z1 (cost=0.00..48.38 rows=4 width=36) Filter: f_leak(z1.b) ! -> Seq Scan on z1 z1_1 (cost=0.00..48.25 rows=13 width=36) Filter: ((a % 2) = 1) (9 rows) ====================================================================== *** /home/ubuntu/build/postgres/src/test/regress/expected/tablesample.out 2015-10-09 09:52:10.031695641 -0700 --- /home/ubuntu/build/postgres/src/test/regress/results/tablesample.out 2015-12-19 11:47:22.160520948 -0800 *************** *** 5,17 **** SELECT t.id FROM test_tablesample AS t TABLESAMPLE SYSTEM (50) REPEATABLE (0); id ---- - 3 - 4 - 5 6 7 8 ! (6 rows) SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (100.0/11) REPEATABLE (0); id --- 5,15 ---- SELECT t.id FROM test_tablesample AS t TABLESAMPLE SYSTEM (50) REPEATABLE (0); id ---- 6 7 8 ! 9 ! (4 rows) SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (100.0/11) REPEATABLE (0); id *************** *** 21,49 **** SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); id ---- - 3 - 4 - 5 6 7 8 ! (6 rows) SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (50) REPEATABLE (0); id ---- 4 5 - 6 7 8 ! (5 rows) SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (5.5) REPEATABLE (0); id ---- ! 7 ! (1 row) -- 100% should give repeatable count results (ie, all rows) in any case SELECT count(*) FROM test_tablesample TABLESAMPLE SYSTEM (100); --- 19,45 ---- SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); id ---- 6 7 8 ! 9 ! (4 rows) SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (50) REPEATABLE (0); id ---- + 3 4 5 7 8 ! 9 ! (6 rows) SELECT id FROM test_tablesample TABLESAMPLE BERNOULLI (5.5) REPEATABLE (0); id ---- ! (0 rows) -- 100% should give repeatable count results (ie, all rows) in any case SELECT count(*) FROM test_tablesample TABLESAMPLE SYSTEM (100); *************** *** 93,177 **** FETCH FIRST FROM tablesample_cur; id ---- ! 3 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 4 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 5 (1 row) SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); id ---- - 3 - 4 - 5 6 7 8 ! (6 rows) FETCH NEXT FROM tablesample_cur; id ---- ! 6 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 7 ! (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 8 ! (1 row) FETCH FIRST FROM tablesample_cur; id ---- ! 3 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 4 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 5 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 6 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 7 ! (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 8 ! (1 row) CLOSE tablesample_cur; END; --- 89,167 ---- FETCH FIRST FROM tablesample_cur; id ---- ! 6 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 7 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 8 (1 row) SELECT id FROM test_tablesample TABLESAMPLE SYSTEM (50) REPEATABLE (0); id ---- 6 7 8 ! 9 ! (4 rows) FETCH NEXT FROM tablesample_cur; id ---- ! 9 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! (0 rows) FETCH NEXT FROM tablesample_cur; id ---- ! (0 rows) FETCH FIRST FROM tablesample_cur; id ---- ! 6 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 7 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 8 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! 9 (1 row) FETCH NEXT FROM tablesample_cur; id ---- ! (0 rows) FETCH NEXT FROM tablesample_cur; id ---- ! (0 rows) CLOSE tablesample_cur; END; ====================================================================== *** /home/ubuntu/build/postgres/src/test/regress/expected/select_views_1.out 2015-10-09 09:52:10.027695641 -0700 --- /home/ubuntu/build/postgres/src/test/regress/results/select_views.out 2015-12-19 11:47:24.020389959 -0800 *************** *** 1492,1502 **** ------------------------------------------------------------------------------------ Subquery Scan on my_credit_card_usage_secure Filter: f_leak(my_credit_card_usage_secure.cnum) ! -> Nested Loop ! Join Filter: (l.cid = r.cid) -> Seq Scan on credit_usage r Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) ! -> Materialize -> Hash Join Hash Cond: (r_1.cid = l.cid) -> Seq Scan on credit_card r_1 --- 1492,1502 ---- ------------------------------------------------------------------------------------ Subquery Scan on my_credit_card_usage_secure Filter: f_leak(my_credit_card_usage_secure.cnum) ! -> Hash Join ! Hash Cond: (r.cid = l.cid) -> Seq Scan on credit_usage r Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) ! -> Hash -> Hash Join Hash Cond: (r_1.cid = l.cid) -> Seq Scan on credit_card r_1 ====================================================================== *** /home/ubuntu/build/postgres/src/test/regress/expected/indirect_toast.out 2015-10-09 09:52:10.007695639 -0700 --- /home/ubuntu/build/postgres/src/test/regress/results/indirect_toast.out 2015-12-19 11:47:24.364365731 -0800 *************** *** 48,64 **** ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (two-compressed,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 - ("one-compressed,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 ("one-toasted,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) SELECT substring(toasttest::text, 1, 200) FROM toasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (two-compressed,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) -- check we didn't screw with main/toast tuple visiblity --- 48,64 ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (two-compressed,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-toasted,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 + ("one-compressed,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (4 rows) SELECT substring(toasttest::text, 1, 200) FROM toasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (4 rows) -- check we didn't screw with main/toast tuple visiblity *************** *** 66,75 **** SELECT substring(toasttest::text, 1, 200) FROM toasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (two-compressed,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) -- now create a trigger that forces all Datums to be indirect ones --- 66,75 ---- SELECT substring(toasttest::text, 1, 200) FROM toasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,4,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",4,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (4 rows) -- now create a trigger that forces all Datums to be indirect ones *************** *** 89,137 **** UPDATE toasttest SET cnt = cnt +1 RETURNING substring(toasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (two-compressed,5,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,5,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",5,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",5,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) -- modification without modifying asigned value UPDATE toasttest SET cnt = cnt +1, f1 = f1 RETURNING substring(toasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (two-compressed,6,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,6,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",6,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",6,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) -- modification modifying, but effectively not changing UPDATE toasttest SET cnt = cnt +1, f1 = f1||'' RETURNING substring(toasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (two-compressed,7,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,7,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",7,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",7,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) UPDATE toasttest SET cnt = cnt +1, f1 = '-'||f1||'-' RETURNING substring(toasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (two-compressed,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-compressed,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (4 rows) INSERT INTO toasttest(descr, f1, f2) VALUES('one-toasted,one-null, via indirect', repeat('1234567890',30000), NULL); SELECT substring(toasttest::text, 1, 200) FROM toasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (two-compressed,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-compressed,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-toasted,one-null, via indirect",0,1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (5 rows) --- 89,137 ---- UPDATE toasttest SET cnt = cnt +1 RETURNING substring(toasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",5,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,5,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,5,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",5,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (4 rows) -- modification without modifying asigned value UPDATE toasttest SET cnt = cnt +1, f1 = f1 RETURNING substring(toasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",6,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,6,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,6,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",6,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (4 rows) -- modification modifying, but effectively not changing UPDATE toasttest SET cnt = cnt +1, f1 = f1||'' RETURNING substring(toasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",7,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,7,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901 (two-toasted,7,-1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234 ("one-compressed,one-null",7,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (4 rows) UPDATE toasttest SET cnt = cnt +1, f1 = '-'||f1||'-' RETURNING substring(toasttest::text, 1, 200); substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-compressed,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (4 rows) INSERT INTO toasttest(descr, f1, f2) VALUES('one-toasted,one-null, via indirect', repeat('1234567890',30000), NULL); SELECT substring(toasttest::text, 1, 200) FROM toasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-compressed,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 ("one-toasted,one-null, via indirect",0,1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (5 rows) *************** *** 140,149 **** SELECT substring(toasttest::text, 1, 200) FROM toasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (two-compressed,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-compressed,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 - ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-toasted,one-null, via indirect",0,1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (5 rows) --- 140,149 ---- SELECT substring(toasttest::text, 1, 200) FROM toasttest; substring ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + ("one-toasted,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 (two-compressed,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (two-toasted,8,--123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123 ("one-compressed,one-null",8,,12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 ("one-toasted,one-null, via indirect",0,1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 (5 rows) ======================================================================