Feedback-ID: i0fe9450f:Fastmail
Date: Fri, 23 Jan 2026 16:03:59 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Subject: Some tests for TOAST, STORAGE MAIN/EXTENDED
Message-ID: <aXMdX1UTHnzYPkHk@paquier.xyz>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="bzaggNvFc9OJKhkO"
Content-Disposition: inline
Archived-At: <https://www.postgresql.org/message-id/aXMdX1UTHnzYPkHk%40paquier.xyz>
Precedence: bulk


--bzaggNvFc9OJKhkO
Content-Type: multipart/mixed; boundary="Bu4cwnhgs91FmvgC"
Content-Disposition: inline


--Bu4cwnhgs91FmvgC
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Hi all,

While playing with the TOAST code this week, I have managed to break
the handling of inline compressible entries, and noticed that the main
regression test suite did not complain following that.

Breaking that stuff is my issue, but I would like to add some
regression tests to cover all that, giving the attached.  This also
includes tests with EXTENDED in the same area, while on it, with
checks for the TOAST table itself.

Thoughts or comments?
--
Michael

--Bu4cwnhgs91FmvgC
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="0001-More-tests-for-TOAST-and-STORAGE.patch"
Content-Transfer-Encoding: quoted-printable

=46rom b00fa7379eece6e3da2d46c0fd19aa80531cd7a9 Mon Sep 17 00:00:00 2001
=46rom: Michael Paquier <michael@paquier.xyz>
Date: Fri, 23 Jan 2026 15:32:50 +0900
Subject: [PATCH] More tests for TOAST and STORAGE

---
 src/test/regress/expected/strings.out | 52 +++++++++++++++++++++++++++
 src/test/regress/sql/strings.sql      | 22 ++++++++++++
 2 files changed, 74 insertions(+)

diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expec=
ted/strings.out
index 5f2b5c391739..db03fd00d071 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2159,6 +2159,58 @@ SELECT pg_column_compression(f1) AS f1_comp, pg_colu=
mn_compression(f2) AS f2_com
          |=20
 (1 row)
=20
+TRUNCATE toasttest;
+-- test with inline compressible varlenas.
+ALTER TABLE toasttest ALTER COLUMN f1 SET STORAGE MAIN;
+ALTER TABLE toasttest ALTER COLUMN f2 SET STORAGE MAIN;
+INSERT INTO toasttest values(repeat('1234', 1024), repeat('5678', 1024));
+-- There should be no values in the toast relation.
+SELECT substr(f1, 5, 10) AS f1_data, substr(f2, 5, 10) AS f2_data
+  FROM toasttest;
+  f1_data   |  f2_data  =20
+------------+------------
+ 1234123412 | 5678567856
+(1 row)
+
+SELECT pg_column_compression(f1) AS f1_comp, pg_column_compression(f2) AS =
f2_comp
+  FROM toasttest;
+ f1_comp | f2_comp=20
+---------+---------
+ pglz    | pglz
+(1 row)
+
+SELECT count(*) FROM :reltoastname;
+ count=20
+-------
+     0
+(1 row)
+
+TRUNCATE toasttest;
+-- test with external compressed data (default)
+ALTER TABLE toasttest ALTER COLUMN f1 SET STORAGE EXTENDED;
+ALTER TABLE toasttest ALTER COLUMN f2 SET STORAGE EXTENDED;
+INSERT INTO toasttest values(repeat('1234', 10240), NULL);
+-- There should be one value in the toast relation.
+SELECT substr(f1, 5, 10) AS f1_data, substr(f2, 5, 10) AS f2_data
+  FROM toasttest;
+  f1_data   | f2_data=20
+------------+---------
+ 1234123412 |=20
+(1 row)
+
+SELECT pg_column_compression(f1) AS f1_comp, pg_column_compression(f2) AS =
f2_comp
+  FROM toasttest;
+ f1_comp | f2_comp=20
+---------+---------
+ pglz    |=20
+(1 row)
+
+SELECT count(*) FROM :reltoastname;
+ count=20
+-------
+     1
+(1 row)
+
 DROP TABLE toasttest;
 --
 -- test length
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/string=
s.sql
index 37c0893ae838..592599722b8b 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -678,6 +678,28 @@ SELECT substr(f1, 5, 10) AS f1_data, substr(f2, 5, 10)=
 AS f2_data
   FROM toasttest;
 SELECT pg_column_compression(f1) AS f1_comp, pg_column_compression(f2) AS =
f2_comp
   FROM toasttest;
+TRUNCATE toasttest;
+-- test with inline compressible varlenas.
+ALTER TABLE toasttest ALTER COLUMN f1 SET STORAGE MAIN;
+ALTER TABLE toasttest ALTER COLUMN f2 SET STORAGE MAIN;
+INSERT INTO toasttest values(repeat('1234', 1024), repeat('5678', 1024));
+-- There should be no values in the toast relation.
+SELECT substr(f1, 5, 10) AS f1_data, substr(f2, 5, 10) AS f2_data
+  FROM toasttest;
+SELECT pg_column_compression(f1) AS f1_comp, pg_column_compression(f2) AS =
f2_comp
+  FROM toasttest;
+SELECT count(*) FROM :reltoastname;
+TRUNCATE toasttest;
+-- test with external compressed data (default)
+ALTER TABLE toasttest ALTER COLUMN f1 SET STORAGE EXTENDED;
+ALTER TABLE toasttest ALTER COLUMN f2 SET STORAGE EXTENDED;
+INSERT INTO toasttest values(repeat('1234', 10240), NULL);
+-- There should be one value in the toast relation.
+SELECT substr(f1, 5, 10) AS f1_data, substr(f2, 5, 10) AS f2_data
+  FROM toasttest;
+SELECT pg_column_compression(f1) AS f1_comp, pg_column_compression(f2) AS =
f2_comp
+  FROM toasttest;
+SELECT count(*) FROM :reltoastname;
 DROP TABLE toasttest;
=20
 --
--=20
2.51.0


--Bu4cwnhgs91FmvgC--

--bzaggNvFc9OJKhkO
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmlzHV8ACgkQnvQgOdby
QH0TAw/5ATPHFIq50RHuyS4rOufJx2bv/11+V9q/daeAPsE3CAw83ED63E7IRkDg
Zo57GRG/hYVbR5oI8ayATAqXv0WjGBnuVOXcOQ09ZBh2aPmA8cyYd7efhC+METfq
n41s5JmUOUuICzVF6VQXezVjRZxApC+3ObF/+y2RZvrTw/xMQhXhEEC5kR0wTQFQ
NmQNQiTI88HhOmgNNVTmspalR3UmJ1VV2dP7E4+YmgCH38vf5zYNzUqz0uJaNoDV
BXU6PkeVCk4YFSxlSU+g1hjW8nvdid0nzqUIQt44iazDzu+eYGcn+tMfa/a8iR93
/UWbJw7Ci3lGoFWx9cRUojFFiqeV9T/0LA18g0dJA/IPN5GUu1LoeRVodH+hH9w7
ebcUuhgARv36zNdMp5KaTXr+K+BvPzVlBYu41SwWsxifAsUwZb9+TIn5SrAACL3y
gWgUnbbzckt5ek19HG7jlnzHsRFhhJ9Ry8S2GPqbMZVd9R+pK+FClppKsJZcX4IF
FdXL7ZHu2qGLcojt3KUURDM949agsjref+BETeKZIdasC/22Kyk6y5PWcgJo+cZQ
6RPL7i1/SdVNYKE+owxpWvL8gNv8s88B9KrsV1QPF5GNNplYESNzjVkbPd2xWClC
3pxXxkrYwIrQSaBZgR0utpYfV7MRzEvzRSUS9oL/SFaqFl8YFX4=
=tnJ6
-----END PGP SIGNATURE-----

--bzaggNvFc9OJKhkO--