1CREATE TABLE indtoasttest(descr text, cnt int DEFAULT 0, f1 text, f2 text); 2 3INSERT INTO indtoasttest(descr, f1, f2) VALUES('two-compressed', repeat('1234567890',1000), repeat('1234567890',1000)); 4INSERT INTO indtoasttest(descr, f1, f2) VALUES('two-toasted', repeat('1234567890',30000), repeat('1234567890',50000)); 5INSERT INTO indtoasttest(descr, f1, f2) VALUES('one-compressed,one-null', NULL, repeat('1234567890',1000)); 6INSERT INTO indtoasttest(descr, f1, f2) VALUES('one-toasted,one-null', NULL, repeat('1234567890',50000)); 7 8-- check whether indirect tuples works on the most basic level 9SELECT descr, substring(make_tuple_indirect(indtoasttest)::text, 1, 200) FROM indtoasttest; 10 11-- modification without changing varlenas 12UPDATE indtoasttest SET cnt = cnt +1 RETURNING substring(indtoasttest::text, 1, 200); 13 14-- modification without modifying assigned value 15UPDATE indtoasttest SET cnt = cnt +1, f1 = f1 RETURNING substring(indtoasttest::text, 1, 200); 16 17-- modification modifying, but effectively not changing 18UPDATE indtoasttest SET cnt = cnt +1, f1 = f1||'' RETURNING substring(indtoasttest::text, 1, 200); 19 20UPDATE indtoasttest SET cnt = cnt +1, f1 = '-'||f1||'-' RETURNING substring(indtoasttest::text, 1, 200); 21 22SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; 23-- check we didn't screw with main/toast tuple visibility 24VACUUM FREEZE indtoasttest; 25SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; 26 27-- now create a trigger that forces all Datums to be indirect ones 28CREATE FUNCTION update_using_indirect() 29 RETURNS trigger 30 LANGUAGE plpgsql AS $$ 31BEGIN 32 NEW := make_tuple_indirect(NEW); 33 RETURN NEW; 34END$$; 35 36CREATE TRIGGER indtoasttest_update_indirect 37 BEFORE INSERT OR UPDATE 38 ON indtoasttest 39 FOR EACH ROW 40 EXECUTE PROCEDURE update_using_indirect(); 41 42-- modification without changing varlenas 43UPDATE indtoasttest SET cnt = cnt +1 RETURNING substring(indtoasttest::text, 1, 200); 44 45-- modification without modifying assigned value 46UPDATE indtoasttest SET cnt = cnt +1, f1 = f1 RETURNING substring(indtoasttest::text, 1, 200); 47 48-- modification modifying, but effectively not changing 49UPDATE indtoasttest SET cnt = cnt +1, f1 = f1||'' RETURNING substring(indtoasttest::text, 1, 200); 50 51UPDATE indtoasttest SET cnt = cnt +1, f1 = '-'||f1||'-' RETURNING substring(indtoasttest::text, 1, 200); 52 53INSERT INTO indtoasttest(descr, f1, f2) VALUES('one-toasted,one-null, via indirect', repeat('1234567890',30000), NULL); 54 55SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; 56-- check we didn't screw with main/toast tuple visibility 57VACUUM FREEZE indtoasttest; 58SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; 59 60DROP TABLE indtoasttest; 61DROP FUNCTION update_using_indirect(); 62