diff options
Diffstat (limited to 'src/test/regress/sql/indirect_toast.sql')
-rw-r--r-- | src/test/regress/sql/indirect_toast.sql | 82 |
1 files changed, 82 insertions, 0 deletions
diff --git a/src/test/regress/sql/indirect_toast.sql b/src/test/regress/sql/indirect_toast.sql new file mode 100644 index 0000000..3e2f6c0 --- /dev/null +++ b/src/test/regress/sql/indirect_toast.sql @@ -0,0 +1,82 @@ +-- +-- Tests for external toast datums +-- + +-- directory paths and dlsuffix are passed to us in environment variables +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX + +\set regresslib :libdir '/regress' :dlsuffix + +CREATE FUNCTION make_tuple_indirect (record) + RETURNS record + AS :'regresslib' + LANGUAGE C STRICT; + +-- Other compression algorithms may cause the compressed data to be stored +-- inline. pglz guarantees that the data is externalized, so stick to it. +SET default_toast_compression = 'pglz'; + +CREATE TABLE indtoasttest(descr text, cnt int DEFAULT 0, f1 text, f2 text); + +INSERT INTO indtoasttest(descr, f1, f2) VALUES('two-compressed', repeat('1234567890',1000), repeat('1234567890',1000)); +INSERT INTO indtoasttest(descr, f1, f2) VALUES('two-toasted', repeat('1234567890',30000), repeat('1234567890',50000)); +INSERT INTO indtoasttest(descr, f1, f2) VALUES('one-compressed,one-null', NULL, repeat('1234567890',1000)); +INSERT INTO indtoasttest(descr, f1, f2) VALUES('one-toasted,one-null', NULL, repeat('1234567890',50000)); + +-- check whether indirect tuples works on the most basic level +SELECT descr, substring(make_tuple_indirect(indtoasttest)::text, 1, 200) FROM indtoasttest; + +-- modification without changing varlenas +UPDATE indtoasttest SET cnt = cnt +1 RETURNING substring(indtoasttest::text, 1, 200); + +-- modification without modifying assigned value +UPDATE indtoasttest SET cnt = cnt +1, f1 = f1 RETURNING substring(indtoasttest::text, 1, 200); + +-- modification modifying, but effectively not changing +UPDATE indtoasttest SET cnt = cnt +1, f1 = f1||'' RETURNING substring(indtoasttest::text, 1, 200); + +UPDATE indtoasttest SET cnt = cnt +1, f1 = '-'||f1||'-' RETURNING substring(indtoasttest::text, 1, 200); + +SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; +-- check we didn't screw with main/toast tuple visibility +VACUUM FREEZE indtoasttest; +SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; + +-- now create a trigger that forces all Datums to be indirect ones +CREATE FUNCTION update_using_indirect() + RETURNS trigger + LANGUAGE plpgsql AS $$ +BEGIN + NEW := make_tuple_indirect(NEW); + RETURN NEW; +END$$; + +CREATE TRIGGER indtoasttest_update_indirect + BEFORE INSERT OR UPDATE + ON indtoasttest + FOR EACH ROW + EXECUTE PROCEDURE update_using_indirect(); + +-- modification without changing varlenas +UPDATE indtoasttest SET cnt = cnt +1 RETURNING substring(indtoasttest::text, 1, 200); + +-- modification without modifying assigned value +UPDATE indtoasttest SET cnt = cnt +1, f1 = f1 RETURNING substring(indtoasttest::text, 1, 200); + +-- modification modifying, but effectively not changing +UPDATE indtoasttest SET cnt = cnt +1, f1 = f1||'' RETURNING substring(indtoasttest::text, 1, 200); + +UPDATE indtoasttest SET cnt = cnt +1, f1 = '-'||f1||'-' RETURNING substring(indtoasttest::text, 1, 200); + +INSERT INTO indtoasttest(descr, f1, f2) VALUES('one-toasted,one-null, via indirect', repeat('1234567890',30000), NULL); + +SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; +-- check we didn't screw with main/toast tuple visibility +VACUUM FREEZE indtoasttest; +SELECT substring(indtoasttest::text, 1, 200) FROM indtoasttest; + +DROP TABLE indtoasttest; +DROP FUNCTION update_using_indirect(); + +RESET default_toast_compression; |