Parameter toast_tuple_target controls TOAST for new rows

Specifies the point at which we try to move long column values
into TOAST tables.

No effect on existing rows.

Discussion: https://postgr.es/m/CANP8+jKsVmw6CX6YP9z7zqkTzcKV1+Uzr3XjKcZW=2Ya00OyQQ@mail.gmail.com

Author: Simon Riggs <simon@2ndQudrant.com>
Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndQuadrant.com>

2 files changed, 66 insertions, 0 deletions

diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index 35cadb24aa1..3a42ef77be3 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -1166,6 +1166,53 @@ SELECT substr(f1, 99995, 10) from toasttest;
  567890
 (4 rows)
 
+TRUNCATE TABLE toasttest;
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+SELECT pg_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
+ blocks 
+--------
+      1
+(1 row)
+
+select pg_relation_size('pg_toast.pg_toast_'||(select oid from pg_class where relname = 'toasttest'))/current_setting('block_size')::integer as blocks;
+ blocks 
+--------
+      3
+(1 row)
+
+SELECT pg_total_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
+ blocks 
+--------
+      9
+(1 row)
+
+TRUNCATE TABLE toasttest;
+ALTER TABLE toasttest set (toast_tuple_target = 4080);
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+SELECT pg_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
+ blocks 
+--------
+      2
+(1 row)
+
+select pg_relation_size('pg_toast.pg_toast_'||(select oid from pg_class where relname = 'toasttest'))/current_setting('block_size')::integer as blocks;
+ blocks 
+--------
+      0
+(1 row)
+
+SELECT pg_total_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
+ blocks 
+--------
+      6
+(1 row)
+
 DROP TABLE toasttest;
 --
 -- test substr with toasted bytea values
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index f9cfaeb44ac..6396693f279 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -366,6 +366,25 @@ SELECT substr(f1, 99995) from toasttest;
 -- string length
 SELECT substr(f1, 99995, 10) from toasttest;
 
+TRUNCATE TABLE toasttest;
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+SELECT pg_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
+select pg_relation_size('pg_toast.pg_toast_'||(select oid from pg_class where relname = 'toasttest'))/current_setting('block_size')::integer as blocks;
+SELECT pg_total_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
+
+TRUNCATE TABLE toasttest;
+ALTER TABLE toasttest set (toast_tuple_target = 4080);
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+INSERT INTO toasttest values (repeat('1234567890',400));
+SELECT pg_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
+select pg_relation_size('pg_toast.pg_toast_'||(select oid from pg_class where relname = 'toasttest'))/current_setting('block_size')::integer as blocks;
+SELECT pg_total_relation_size('toasttest')/current_setting('block_size')::integer as blocks;
+
 DROP TABLE toasttest;
 
 --