1CREATE TABLE toasttest(descr text, cnt int DEFAULT 0, f1 text, f2 text); 2 3INSERT INTO toasttest(descr, f1, f2) VALUES('two-compressed', repeat('1234567890',1000), repeat('1234567890',1000)); 4INSERT INTO toasttest(descr, f1, f2) VALUES('two-toasted', repeat('1234567890',30000), repeat('1234567890',50000)); 5INSERT INTO toasttest(descr, f1, f2) VALUES('one-compressed,one-null', NULL, repeat('1234567890',1000)); 6INSERT INTO toasttest(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(toasttest)::text, 1, 200) FROM toasttest; 10 11-- modification without changing varlenas 12UPDATE toasttest SET cnt = cnt +1 RETURNING substring(toasttest::text, 1, 200); 13 14-- modification without modifying assigned value 15UPDATE toasttest SET cnt = cnt +1, f1 = f1 RETURNING substring(toasttest::text, 1, 200); 16 17-- modification modifying, but effectively not changing 18UPDATE toasttest SET cnt = cnt +1, f1 = f1||'' RETURNING substring(toasttest::text, 1, 200); 19 20UPDATE toasttest SET cnt = cnt +1, f1 = '-'||f1||'-' RETURNING substring(toasttest::text, 1, 200); 21 22SELECT substring(toasttest::text, 1, 200) FROM toasttest; 23-- check we didn't screw with main/toast tuple visibility 24VACUUM FREEZE toasttest; 25SELECT substring(toasttest::text, 1, 200) FROM toasttest; 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 toasttest_update_indirect 37 BEFORE INSERT OR UPDATE 38 ON toasttest 39 FOR EACH ROW 40 EXECUTE PROCEDURE update_using_indirect(); 41 42-- modification without changing varlenas 43UPDATE toasttest SET cnt = cnt +1 RETURNING substring(toasttest::text, 1, 200); 44 45-- modification without modifying assigned value 46UPDATE toasttest SET cnt = cnt +1, f1 = f1 RETURNING substring(toasttest::text, 1, 200); 47 48-- modification modifying, but effectively not changing 49UPDATE toasttest SET cnt = cnt +1, f1 = f1||'' RETURNING substring(toasttest::text, 1, 200); 50 51UPDATE toasttest SET cnt = cnt +1, f1 = '-'||f1||'-' RETURNING substring(toasttest::text, 1, 200); 52 53INSERT INTO toasttest(descr, f1, f2) VALUES('one-toasted,one-null, via indirect', repeat('1234567890',30000), NULL); 54 55SELECT substring(toasttest::text, 1, 200) FROM toasttest; 56-- check we didn't screw with main/toast tuple visibility 57VACUUM FREEZE toasttest; 58SELECT substring(toasttest::text, 1, 200) FROM toasttest; 59 60DROP TABLE toasttest; 61DROP FUNCTION update_using_indirect(); 62