postgres.git / summary / log / commit / refs
commit c68cba09dd7fb2cca1fdddfcf8e0fe206e97a8a4
Author: Michael Paquier <michael@paquier.xyz>
Date: Tue Sep 15 02:30:45 2026 +0000
Add battery of tests related TOAST tables with oid8
The tests added by this commit cover a large ground in terms of the
introduction of oid8 in TOAST tables, with the following areas covered,
with all the gaps found during the development of the feature:
- Tables with oid8 TOAST tables in general, under strings.sql.
- ALTER TABLE with attribute manipulations in transactions and different
toast_table_value settings.
- Relation rewrites, not affecting the TOAST relation after initial
creation with VACUUM FULL, CLUSTER, REPACK.
- pg_column_compression().
- test_decoding, for external and extended TOAST data.
- amcheck, external and extended storage with heap checked.
- UPDATE with out-of-line datums that belongs to another TOAST table.
This offers coverage for toast_tuple_init(), where external vartags
could be mixed once multiple on-disk external TOAST pointers can
co-exist. This pattern can be triggered with asan/ubsan, with an
out-of-bound memcpy when unpatched.
These tests are added before the new vartag_external, mostly as a matter
of avoiding dead code in the tree.
Author: Michael Paquier <michael@paquier.xyz>
Reviewed-by: Greg Burd <greg@burd.me>
Reviewed-by: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Reviewed-by: Yugo Nagata <nagata@sraoss.co.jp>
Discussion: https://postgr.es/m/af19kUjwjhaoUTLn@paquier.xy
contrib/amcheck/expected/check_heap.out | 40 +++
contrib/amcheck/sql/check_heap.sql | 27 ++
contrib/test_decoding/expected/toast.out | 39 +++
contrib/test_decoding/sql/toast.sql | 20 ++
src/test/regress/expected/compression.out | 41 ++++
src/test/regress/expected/strings.out | 394 +++++++++++++++++++++++++++---
src/test/regress/sql/compression.sql | 22 ++
src/test/regress/sql/strings.sql | 241 ++++++++++++++----
8 files changed, 746 insertions(+), 78 deletions(-)
diff --git a/contrib/amcheck/expected/check_heap.out b/contrib/amcheck/expected/check_heap.out
index 979e5e84e72..8686fba712d 100644
--- a/contrib/amcheck/expected/check_heap.out
+++ b/contrib/amcheck/expected/check_heap.out
@@ -199,6 +199,44 @@ SELECT * FROM verify_heapam('test_partition',
-------+--------+--------+-----
(0 rows)
+-- Check TOAST relations of both chunk_id: oid and oid8
+CREATE TABLE test_toast_oid (a int, b text) WITH (toast_value_type = 'oid');
+CREATE TABLE test_toast_oid8 (a int, b text) WITH (toast_value_type = 'oid8');
+-- Uncompressed out-of-line values
+ALTER TABLE test_toast_oid ALTER COLUMN b SET STORAGE EXTERNAL;
+ALTER TABLE test_toast_oid8 ALTER COLUMN b SET STORAGE EXTERNAL;
+INSERT INTO test_toast_oid (a, b)
+ (SELECT gs, repeat('xyzzy', 20000) FROM generate_series(1,5) gs);
+INSERT INTO test_toast_oid8 (a, b)
+ (SELECT gs, repeat('xyzzy', 20000) FROM generate_series(1,5) gs);
+-- Compressed out-of-line values.
+ALTER TABLE test_toast_oid ALTER COLUMN b SET STORAGE EXTENDED;
+ALTER TABLE test_toast_oid8 ALTER COLUMN b SET STORAGE EXTENDED;
+INSERT INTO test_toast_oid (a, b)
+ (SELECT gs, repeat('xyzzy', 20000) FROM generate_series(6,10) gs);
+INSERT INTO test_toast_oid8 (a, b)
+ (SELECT gs, repeat('xyzzy', 20000) FROM generate_series(6,10) gs);
+SELECT c.relname, a.atttypid::regtype AS chunk_id_type
+ FROM pg_class AS c, pg_attribute AS a
+ WHERE c.relname IN ('test_toast_oid', 'test_toast_oid8') AND
+ a.attrelid = c.reltoastrelid AND a.attname = 'chunk_id'
+ ORDER BY c.relname COLLATE "C";
+ relname | chunk_id_type
+-----------------+---------------
+ test_toast_oid | oid
+ test_toast_oid8 | oid8
+(2 rows)
+
+SELECT * FROM verify_heapam('test_toast_oid', check_toast := true);
+ blkno | offnum | attnum | msg
+-------+--------+--------+-----
+(0 rows)
+
+SELECT * FROM verify_heapam('test_toast_oid8', check_toast := true);
+ blkno | offnum | attnum | msg
+-------+--------+--------+-----
+(0 rows)
+
-- Check that indexes are rejected
CREATE INDEX test_index ON test_partition (a);
SELECT * FROM verify_heapam('test_index',
@@ -232,6 +270,8 @@ SELECT * FROM verify_heapam('test_foreign_table',
ERROR: cannot check relation "test_foreign_table"
DETAIL: This operation is not supported for foreign tables.
-- cleanup
+DROP TABLE test_toast_oid;
+DROP TABLE test_toast_oid8;
DROP TABLE heaptest;
DROP TABLESPACE regress_test_stats_tblspc;
DROP TABLE test_partition;
diff --git a/contrib/amcheck/sql/check_heap.sql b/contrib/amcheck/sql/check_heap.sql
index 1745bae634e..eb46fe71035 100644
--- a/contrib/amcheck/sql/check_heap.sql
+++ b/contrib/amcheck/sql/check_heap.sql
@@ -112,6 +112,31 @@ SELECT * FROM verify_heapam('test_partition',
startblock := NULL,
endblock := NULL);
+-- Check TOAST relations of both chunk_id: oid and oid8
+CREATE TABLE test_toast_oid (a int, b text) WITH (toast_value_type = 'oid');
+CREATE TABLE test_toast_oid8 (a int, b text) WITH (toast_value_type = 'oid8');
+-- Uncompressed out-of-line values
+ALTER TABLE test_toast_oid ALTER COLUMN b SET STORAGE EXTERNAL;
+ALTER TABLE test_toast_oid8 ALTER COLUMN b SET STORAGE EXTERNAL;
+INSERT INTO test_toast_oid (a, b)
+ (SELECT gs, repeat('xyzzy', 20000) FROM generate_series(1,5) gs);
+INSERT INTO test_toast_oid8 (a, b)
+ (SELECT gs, repeat('xyzzy', 20000) FROM generate_series(1,5) gs);
+-- Compressed out-of-line values.
+ALTER TABLE test_toast_oid ALTER COLUMN b SET STORAGE EXTENDED;
+ALTER TABLE test_toast_oid8 ALTER COLUMN b SET STORAGE EXTENDED;
+INSERT INTO test_toast_oid (a, b)
+ (SELECT gs, repeat('xyzzy', 20000) FROM generate_series(6,10) gs);
+INSERT INTO test_toast_oid8 (a, b)
+ (SELECT gs, repeat('xyzzy', 20000) FROM generate_series(6,10) gs);
+SELECT c.relname, a.atttypid::regtype AS chunk_id_type
+ FROM pg_class AS c, pg_attribute AS a
+ WHERE c.relname IN ('test_toast_oid', 'test_toast_oid8') AND
+ a.attrelid = c.reltoastrelid AND a.attname = 'chunk_id'
+ ORDER BY c.relname COLLATE "C";
+SELECT * FROM verify_heapam('test_toast_oid', check_toast := true);
+SELECT * FROM verify_heapam('test_toast_oid8', check_toast := true);
+
-- Check that indexes are rejected
CREATE INDEX test_index ON test_partition (a);
SELECT * FROM verify_heapam('test_index',
@@ -139,6 +164,8 @@ SELECT * FROM verify_heapam('test_foreign_table',
endblock := NULL);
-- cleanup
+DROP TABLE test_toast_oid;
+DROP TABLE test_toast_oid8;
DROP TABLE heaptest;
DROP TABLESPACE regress_test_stats_tblspc;
DROP TABLE test_partition;
diff --git a/contrib/test_decoding/expected/toast.out b/contrib/test_decoding/expected/toast.out
index a757e7dc8d5..8bef6cc32af 100644
--- a/contrib/test_decoding/expected/toast.out
+++ b/contrib/test_decoding/expected/toast.out
@@ -382,6 +382,45 @@ SELECT substr(data, 1, 200) FROM pg_logical_slot_get_changes('regression_slot',
COMMIT
(4 rows)
+-- Test decoding of TOAST values with oid8
+CREATE TABLE toasted_oid8 (id serial primary key, data text)
+ WITH (toast_value_type = 'oid8');
+-- uncompressed external toast data
+ALTER TABLE toasted_oid8 ALTER COLUMN data SET STORAGE EXTERNAL;
+INSERT INTO toasted_oid8(data) VALUES (repeat('1234567890', 20000));
+-- compressed external toast data
+ALTER TABLE toasted_oid8 ALTER COLUMN data SET STORAGE EXTENDED;
+INSERT INTO toasted_oid8(data) VALUES (repeat('1234567890', 20000));
+-- update without changing the toasted column, reported as unchanged
+UPDATE toasted_oid8 SET id = id + 10 WHERE id = 1;
+-- update changing the toasted column
+UPDATE toasted_oid8 SET data = repeat('abcdefghij', 20000) WHERE id = 2;
+DELETE FROM toasted_oid8;
+-- Check that the values are reassembled in full.
+SELECT regexp_replace(data, '^(.{60}).*(.{20})$', '\1..\2') AS shortened,
+ length(data) AS len
+ FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+ shortened | len
+------------------------------------------------------------------------------------+--------
+ BEGIN | 5
+ table public.toasted_oid8: INSERT: id[integer]:1 data[text]:..2345678901234567890' | 200062
+ COMMIT | 6
+ BEGIN | 5
+ table public.toasted_oid8: INSERT: id[integer]:2 data[text]:..2345678901234567890' | 200062
+ COMMIT | 6
+ BEGIN | 5
+ table public.toasted_oid8: UPDATE: old-key: id[integer]:1 ne..nchanged-toast-datum | 116
+ COMMIT | 6
+ BEGIN | 5
+ table public.toasted_oid8: UPDATE: id[integer]:2 data[text]:..bcdefghijabcdefghij' | 200062
+ COMMIT | 6
+ BEGIN | 5
+ table public.toasted_oid8: DELETE: id[integer]:11 | 49
+ table public.toasted_oid8: DELETE: id[integer]:2 | 48
+ COMMIT | 6
+(16 rows)
+
+DROP TABLE toasted_oid8;
SELECT pg_drop_replication_slot('regression_slot');
pg_drop_replication_slot
--------------------------
diff --git a/contrib/test_decoding/sql/toast.sql b/contrib/test_decoding/sql/toast.sql
index d1c560a174d..8a2c49a5c9a 100644
--- a/contrib/test_decoding/sql/toast.sql
+++ b/contrib/test_decoding/sql/toast.sql
@@ -324,4 +324,24 @@ INSERT INTO tbl2 VALUES(1);
commit;
SELECT substr(data, 1, 200) FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+-- Test decoding of TOAST values with oid8
+CREATE TABLE toasted_oid8 (id serial primary key, data text)
+ WITH (toast_value_type = 'oid8');
+-- uncompressed external toast data
+ALTER TABLE toasted_oid8 ALTER COLUMN data SET STORAGE EXTERNAL;
+INSERT INTO toasted_oid8(data) VALUES (repeat('1234567890', 20000));
+-- compressed external toast data
+ALTER TABLE toasted_oid8 ALTER COLUMN data SET STORAGE EXTENDED;
+INSERT INTO toasted_oid8(data) VALUES (repeat('1234567890', 20000));
+-- update without changing the toasted column, reported as unchanged
+UPDATE toasted_oid8 SET id = id + 10 WHERE id = 1;
+-- update changing the toasted column
+UPDATE toasted_oid8 SET data = repeat('abcdefghij', 20000) WHERE id = 2;
+DELETE FROM toasted_oid8;
+-- Check that the values are reassembled in full.
+SELECT regexp_replace(data, '^(.{60}).*(.{20})$', '\1..\2') AS shortened,
+ length(data) AS len
+ FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
+DROP TABLE toasted_oid8;
+
SELECT pg_drop_replication_slot('regression_slot');
diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out
index 09f198149aa..e9128193515 100644
--- a/src/test/regress/expected/compression.out
+++ b/src/test/regress/expected/compression.out
@@ -66,6 +66,47 @@ SELECT SUBSTR(f1, 200, 5) FROM cmdata2;
(1 row)
DROP TABLE cmdata2;
+-- pg_column_compression() with oid and oid8
+CREATE TABLE toastcomp_oid(f1 text) WITH (toast_value_type = 'oid');
+CREATE TABLE toastcomp_oid8(f1 text) WITH (toast_value_type = 'oid8');
+ALTER TABLE toastcomp_oid ALTER COLUMN f1 SET STORAGE EXTERNAL;
+ALTER TABLE toastcomp_oid8 ALTER COLUMN f1 SET STORAGE EXTERNAL;
+INSERT INTO toastcomp_oid VALUES (repeat('1234567890', 10000));
+INSERT INTO toastcomp_oid8 VALUES (repeat('1234567890', 10000));
+SELECT pg_column_compression(f1) IS NULL AS uncompressed FROM toastcomp_oid;
+ uncompressed
+--------------
+ t
+(1 row)
+
+SELECT pg_column_compression(f1) IS NULL AS uncompressed FROM toastcomp_oid8;
+ uncompressed
+--------------
+ t
+(1 row)
+
+-- out-of-line and compressed.
+TRUNCATE toastcomp_oid;
+TRUNCATE toastcomp_oid8;
+ALTER TABLE toastcomp_oid ALTER COLUMN f1 SET STORAGE EXTENDED;
+ALTER TABLE toastcomp_oid8 ALTER COLUMN f1 SET STORAGE EXTENDED;
+ALTER TABLE toastcomp_oid SET (toast_tuple_target = 128);
+ALTER TABLE toastcomp_oid8 SET (toast_tuple_target = 128);
+INSERT INTO toastcomp_oid VALUES (repeat('1234567890', 10000));
+INSERT INTO toastcomp_oid8 VALUES (repeat('1234567890', 10000));
+SELECT pg_column_compression(f1) FROM toastcomp_oid;
+ pg_column_compression
+-----------------------
+ pglz
+(1 row)
+
+SELECT pg_column_compression(f1) FROM toastcomp_oid8;
+ pg_column_compression
+-----------------------
+ pglz
+(1 row)
+
+DROP TABLE toastcomp_oid, toastcomp_oid8;
--test column type update varlena/non-varlena
CREATE TABLE cmdata2 (f1 int);
\d+ cmdata2
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index 313c5039465..fa29abfd829 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -1980,21 +1980,37 @@ SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar";
(1 row)
--
--- test substr with toasted text values
+-- Test substr with toasted text values, for all types of TOAST relations
+-- supported.
--
-CREATE TABLE toasttest(f1 text);
-insert into toasttest values(repeat('1234567890',10000));
-insert into toasttest values(repeat('1234567890',10000));
+CREATE TABLE toasttest_oid(f1 text) with (toast_value_type = 'oid');
+CREATE TABLE toasttest_oid8(f1 text) with (toast_value_type = 'oid8');
+insert into toasttest_oid values(repeat('1234567890',10000));
+insert into toasttest_oid values(repeat('1234567890',10000));
+insert into toasttest_oid8 values(repeat('1234567890',10000));
+insert into toasttest_oid8 values(repeat('1234567890',10000));
--
-- Ensure that some values are uncompressed, to test the faster substring
-- operation used in that case
--
-alter table toasttest alter column f1 set storage external;
-insert into toasttest values(repeat('1234567890',10000));
-insert into toasttest values(repeat('1234567890',10000));
+alter table toasttest_oid alter column f1 set storage external;
+insert into toasttest_oid values(repeat('1234567890',10000));
+insert into toasttest_oid values(repeat('1234567890',10000));
+alter table toasttest_oid8 alter column f1 set storage external;
+insert into toasttest_oid8 values(repeat('1234567890',10000));
+insert into toasttest_oid8 values(repeat('1234567890',10000));
-- If the starting position is zero or less, then return from the start of the string
-- adjusting the length to be consistent with the "negative start" per SQL.
-SELECT substr(f1, -1, 5) from toasttest;
+SELECT substr(f1, -1, 5) from toasttest_oid;
+ substr
+--------
+ 123
+ 123
+ 123
+ 123
+(4 rows)
+
+SELECT substr(f1, -1, 5) from toasttest_oid8;
substr
--------
123
@@ -2004,11 +2020,22 @@ SELECT substr(f1, -1, 5) from toasttest;
(4 rows)
-- If the length is less than zero, an ERROR is thrown.
-SELECT substr(f1, 5, -1) from toasttest;
+SELECT substr(f1, 5, -1) from toasttest_oid;
+ERROR: negative substring length not allowed
+SELECT substr(f1, 5, -1) from toasttest_oid8;
ERROR: negative substring length not allowed
-- If no third argument (length) is provided, the length to the end of the
-- string is assumed.
-SELECT substr(f1, 99995) from toasttest;
+SELECT substr(f1, 99995) from toasttest_oid;
+ substr
+--------
+ 567890
+ 567890
+ 567890
+ 567890
+(4 rows)
+
+SELECT substr(f1, 99995) from toasttest_oid8;
substr
--------
567890
@@ -2019,7 +2046,7 @@ SELECT substr(f1, 99995) from toasttest;
-- If start plus length is > string length, the result is truncated to
-- string length
-SELECT substr(f1, 99995, 10) from toasttest;
+SELECT substr(f1, 99995, 10) from toasttest_oid;
substr
--------
567890
@@ -2028,50 +2055,105 @@ SELECT substr(f1, 99995, 10) from toasttest;
567890
(4 rows)
-TRUNCATE TABLE toasttest;
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
+SELECT substr(f1, 99995, 10) from toasttest_oid8;
+ substr
+--------
+ 567890
+ 567890
+ 567890
+ 567890
+(4 rows)
+
+-- TRUNCATE cases for TOAST relations with OID values.
+TRUNCATE TABLE toasttest_oid;
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
-- expect >0 blocks
SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty
- FROM pg_class where relname = 'toasttest';
+ FROM pg_class where relname = 'toasttest_oid';
is_empty
----------
f
(1 row)
-TRUNCATE TABLE toasttest;
-ALTER TABLE toasttest set (toast_tuple_target = 4080);
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
+TRUNCATE TABLE toasttest_oid;
+ALTER TABLE toasttest_oid set (toast_tuple_target = 4080);
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
-- expect 0 blocks
SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty
- FROM pg_class where relname = 'toasttest';
+ FROM pg_class where relname = 'toasttest_oid';
is_empty
----------
t
(1 row)
-DROP TABLE toasttest;
+DROP TABLE toasttest_oid;
+-- TRUNCATE cases for TOAST relation with int8 values.
+TRUNCATE TABLE toasttest_oid8;
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+-- expect >0 blocks
+SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty
+ FROM pg_class where relname = 'toasttest_oid8';
+ is_empty
+----------
+ f
+(1 row)
+
+TRUNCATE TABLE toasttest_oid8;
+ALTER TABLE toasttest_oid8 set (toast_tuple_target = 4080);
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+-- expect 0 blocks
+SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty
+ FROM pg_class where relname = 'toasttest_oid8';
+ is_empty
+----------
+ t
+(1 row)
+
+DROP TABLE toasttest_oid8;
--
--- test substr with toasted bytea values
+-- test substr with toasted bytea values, for all types of TOAST relations
+-- supported. Do not drop these two relations, for pg_upgrade.
--
-CREATE TABLE toasttest(f1 bytea);
-insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
-insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
+CREATE TABLE toasttest_oid(f1 bytea) WITH (toast_value_type = 'oid');
+CREATE TABLE toasttest_oid8(f1 bytea) WITH (toast_value_type = 'oid8');
+insert into toasttest_oid values(decode(repeat('1234567890',10000),'escape'));
+insert into toasttest_oid values(decode(repeat('1234567890',10000),'escape'));
+insert into toasttest_oid8 values(decode(repeat('1234567890',10000),'escape'));
+insert into toasttest_oid8 values(decode(repeat('1234567890',10000),'escape'));
--
-- Ensure that some values are uncompressed, to test the faster substring
-- operation used in that case
--
-alter table toasttest alter column f1 set storage external;
-insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
-insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
+alter table toasttest_oid alter column f1 set storage external;
+insert into toasttest_oid values(decode(repeat('1234567890',10000),'escape'));
+insert into toasttest_oid values(decode(repeat('1234567890',10000),'escape'));
+alter table toasttest_oid8 alter column f1 set storage external;
+insert into toasttest_oid8 values(decode(repeat('1234567890',10000),'escape'));
+insert into toasttest_oid8 values(decode(repeat('1234567890',10000),'escape'));
-- If the starting position is zero or less, then return from the start of the string
-- adjusting the length to be consistent with the "negative start" per SQL.
-SELECT substr(f1, -1, 5) from toasttest;
+SELECT substr(f1, -1, 5) from toasttest_oid;
+ substr
+--------
+ 123
+ 123
+ 123
+ 123
+(4 rows)
+
+SELECT substr(f1, -1, 5) from toasttest_oid8;
substr
--------
123
@@ -2081,11 +2163,22 @@ SELECT substr(f1, -1, 5) from toasttest;
(4 rows)
-- If the length is less than zero, an ERROR is thrown.
-SELECT substr(f1, 5, -1) from toasttest;
+SELECT substr(f1, 5, -1) from toasttest_oid;
+ERROR: negative substring length not allowed
+SELECT substr(f1, 5, -1) from toasttest_oid8;
ERROR: negative substring length not allowed
-- If no third argument (length) is provided, the length to the end of the
-- string is assumed.
-SELECT substr(f1, 99995) from toasttest;
+SELECT substr(f1, 99995) from toasttest_oid;
+ substr
+--------
+ 567890
+ 567890
+ 567890
+ 567890
+(4 rows)
+
+SELECT substr(f1, 99995) from toasttest_oid8;
substr
--------
567890
@@ -2096,7 +2189,7 @@ SELECT substr(f1, 99995) from toasttest;
-- If start plus length is > string length, the result is truncated to
-- string length
-SELECT substr(f1, 99995, 10) from toasttest;
+SELECT substr(f1, 99995, 10) from toasttest_oid;
substr
--------
567890
@@ -2105,7 +2198,234 @@ SELECT substr(f1, 99995, 10) from toasttest;
567890
(4 rows)
-DROP TABLE toasttest;
+SELECT substr(f1, 99995, 10) from toasttest_oid8;
+ substr
+--------
+ 567890
+ 567890
+ 567890
+ 567890
+(4 rows)
+
+-- A relation rewrite leaves the TOAST value attributes unchanged.
+VACUUM FULL toasttest_oid;
+VACUUM FULL toasttest_oid8;
+SELECT c1.relname, a.atttypid::regtype
+ FROM pg_attribute AS a,
+ pg_class AS c1,
+ pg_class AS c2
+ WHERE
+ c1.relname IN ('toasttest_oid', 'toasttest_oid8') AND
+ c1.reltoastrelid = c2.oid AND
+ a.attrelid = c2.oid AND
+ a.attname = 'chunk_id'
+ ORDER BY c1.relname COLLATE "C";
+ relname | atttypid
+----------------+----------
+ toasttest_oid | oid
+ toasttest_oid8 | oid8
+(2 rows)
+
+-- Check that data slices are still accessible.
+SELECT substr(f1, 99995) from toasttest_oid;
+ substr
+--------
+ 567890
+ 567890
+ 567890
+ 567890
+(4 rows)
+
+SELECT substr(f1, 99995) from toasttest_oid8;
+ substr
+--------
+ 567890
+ 567890
+ 567890
+ 567890
+(4 rows)
+
+SELECT substr(f1, 99995, 10) from toasttest_oid;
+ substr
+--------
+ 567890
+ 567890
+ 567890
+ 567890
+(4 rows)
+
+SELECT substr(f1, 99995, 10) from toasttest_oid8;
+ substr
+--------
+ 567890
+ 567890
+ 567890
+ 567890
+(4 rows)
+
+-- ALTER TABLE after TOAST table creation does not affect relation rewrite.
+ALTER TABLE toasttest_oid SET (toast_value_type = oid8);
+ALTER TABLE toasttest_oid8 SET (toast_value_type = oid);
+VACUUM FULL toasttest_oid;
+VACUUM FULL toasttest_oid8;
+SELECT c1.relname, a.atttypid::regtype
+ FROM pg_attribute AS a,
+ pg_class AS c1,
+ pg_class AS c2
+ WHERE
+ c1.relname IN ('toasttest_oid', 'toasttest_oid8') AND
+ c1.reltoastrelid = c2.oid AND
+ a.attrelid = c2.oid AND
+ a.attname = 'chunk_id'
+ ORDER BY c1.relname COLLATE "C";
+ relname | atttypid
+----------------+----------
+ toasttest_oid | oid
+ toasttest_oid8 | oid8
+(2 rows)
+
+REPACK toasttest_oid;
+REPACK toasttest_oid8;
+SELECT c1.relname, a.atttypid::regtype
+ FROM pg_attribute AS a,
+ pg_class AS c1,
+ pg_class AS c2
+ WHERE
+ c1.relname IN ('toasttest_oid', 'toasttest_oid8') AND
+ c1.reltoastrelid = c2.oid AND
+ a.attrelid = c2.oid AND
+ a.attname = 'chunk_id'
+ ORDER BY c1.relname COLLATE "C";
+ relname | atttypid
+----------------+----------
+ toasttest_oid | oid
+ toasttest_oid8 | oid8
+(2 rows)
+
+ALTER TABLE toasttest_oid RESET (toast_value_type);
+ALTER TABLE toasttest_oid8 RESET (toast_value_type);
+-- Reset column storage to its default
+ALTER TABLE toasttest_oid ALTER COLUMN f1 SET STORAGE EXTENDED;
+ALTER TABLE toasttest_oid8 ALTER COLUMN f1 SET STORAGE EXTENDED;
+-- UPDATE with out-of-line datum that belongs to another TOAST table.
+CREATE TABLE toastupd_oid(f1 text) WITH (toast_value_type = 'oid');
+CREATE TABLE toastupd_oid8(f1 text) WITH (toast_value_type = 'oid8');
+ALTER TABLE toastupd_oid ALTER COLUMN f1 SET STORAGE EXTERNAL;
+ALTER TABLE toastupd_oid8 ALTER COLUMN f1 SET STORAGE EXTERNAL;
+SELECT reltoastrelid::regclass AS upd_oid_toast FROM pg_class
+ WHERE oid = 'toastupd_oid'::regclass \gset
+SELECT reltoastrelid::regclass AS upd_oid8_toast FROM pg_class
+ WHERE oid = 'toastupd_oid8'::regclass \gset
+INSERT INTO toastupd_oid VALUES (repeat('a', 100000));
+INSERT INTO toastupd_oid8 VALUES (repeat('b', 100000));
+-- old value is an oid8 pointer, new value an oid pointer.
+UPDATE toastupd_oid8 SET f1 = toastupd_oid.f1 FROM toastupd_oid;
+SELECT length(f1), substr(f1, 1, 3) FROM toastupd_oid8;
+ length | substr
+--------+--------
+ 100000 | aaa
+(1 row)
+
+-- replaced value must be gone, leaving a single value behind.
+SELECT count(*) FROM :upd_oid8_toast WHERE chunk_seq = 0;
+ count
+-------
+ 1
+(1 row)
+
+-- reverse: old value is an oid pointer, new value an oid8 pointer.
+TRUNCATE toastupd_oid;
+INSERT INTO toastupd_oid VALUES (repeat('c', 100000));
+UPDATE toastupd_oid SET f1 = toastupd_oid8.f1 FROM toastupd_oid8;
+SELECT length(f1), substr(f1, 1, 3) FROM toastupd_oid;
+ length | substr
+--------+--------
+ 100000 | aaa
+(1 row)
+
+SELECT count(*) FROM :upd_oid_toast WHERE chunk_seq = 0;
+ count
+-------
+ 1
+(1 row)
+
+DROP TABLE toastupd_oid, toastupd_oid8;
+-- TOAST table manipulations with ALTER TABLE and attributes
+CREATE TABLE toast_alter_oid (f1 int, f2 text STORAGE EXTERNAL)
+ WITH (toast_value_type = 'oid');
+INSERT INTO toast_alter_oid VALUES (1, repeat('1234567890',10000));
+INSERT INTO toast_alter_oid VALUES (2, repeat('2234567890',10000));
+BEGIN;
+ALTER TABLE toast_alter_oid DROP COLUMN f2;
+SELECT reltoastrelid::regclass > 0 AS has_toast FROM pg_class
+ WHERE oid = 'toast_alter_oid'::regclass;
+ has_toast
+-----------
+ t
+(1 row)
+
+-- New toast_value_type has no effect.
+ALTER TABLE toast_alter_oid SET (toast_value_type = 'oid8');
+ALTER TABLE toast_alter_oid ADD COLUMN f3 text
+ DEFAULT (repeat('5234567890',10000));
+ALTER TABLE toast_alter_oid ALTER COLUMN f3 SET STORAGE EXTERNAL;
+INSERT INTO toast_alter_oid VALUES (3, repeat('3234567890',10000));
+INSERT INTO toast_alter_oid VALUES (4, repeat('4234567890',10000));
+SELECT reltoastrelid::regclass > 0 AS has_toast FROM pg_class
+ WHERE oid = 'toast_alter_oid'::regclass;
+ has_toast
+-----------
+ t
+(1 row)
+
+SELECT c1.relname, a.atttypid::regtype
+ FROM pg_attribute AS a,
+ pg_class AS c1,
+ pg_class AS c2
+ WHERE
+ c1.relname = 'toast_alter_oid' AND
+ c1.reltoastrelid = c2.oid AND
+ a.attrelid = c2.oid AND
+ a.attname = 'chunk_id'
+ ORDER BY c1.relname COLLATE "C";
+ relname | atttypid
+-----------------+----------
+ toast_alter_oid | oid
+(1 row)
+
+SELECT f1, substr(f3, 99991) FROM toast_alter_oid;
+ f1 | substr
+----+------------
+ 1 | 5234567890
+ 2 | 5234567890
+ 3 | 3234567890
+ 4 | 4234567890
+(4 rows)
+
+ROLLBACK;
+SELECT f1, substr(f2, 99991) FROM toast_alter_oid;
+ f1 | substr
+----+------------
+ 1 | 1234567890
+ 2 | 2234567890
+(2 rows)
+
+SELECT c1.relname, a.atttypid::regtype
+ FROM pg_attribute AS a,
+ pg_class AS c1,
+ pg_class AS c2
+ WHERE
+ c1.relname = 'toast_alter_oid' AND
+ c1.reltoastrelid = c2.oid AND
+ a.attrelid = c2.oid AND
+ a.attname = 'chunk_id'
+ ORDER BY c1.relname COLLATE "C";
+ relname | atttypid
+-----------------+----------
+ toast_alter_oid | oid
+(1 row)
+
+DROP TABLE toast_alter_oid;
-- test internally compressing datums
-- this tests compressing a datum to a very small size which exercises a
-- corner case in packed-varlena handling: even though small, the compressed
diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression.sql
index ce5ea37a660..229143f06dd 100644
--- a/src/test/regress/sql/compression.sql
+++ b/src/test/regress/sql/compression.sql
@@ -37,6 +37,28 @@ SELECT pg_column_compression(f1) FROM cmdata2;
SELECT SUBSTR(f1, 200, 5) FROM cmdata2;
DROP TABLE cmdata2;
+-- pg_column_compression() with oid and oid8
+CREATE TABLE toastcomp_oid(f1 text) WITH (toast_value_type = 'oid');
+CREATE TABLE toastcomp_oid8(f1 text) WITH (toast_value_type = 'oid8');
+ALTER TABLE toastcomp_oid ALTER COLUMN f1 SET STORAGE EXTERNAL;
+ALTER TABLE toastcomp_oid8 ALTER COLUMN f1 SET STORAGE EXTERNAL;
+INSERT INTO toastcomp_oid VALUES (repeat('1234567890', 10000));
+INSERT INTO toastcomp_oid8 VALUES (repeat('1234567890', 10000));
+SELECT pg_column_compression(f1) IS NULL AS uncompressed FROM toastcomp_oid;
+SELECT pg_column_compression(f1) IS NULL AS uncompressed FROM toastcomp_oid8;
+-- out-of-line and compressed.
+TRUNCATE toastcomp_oid;
+TRUNCATE toastcomp_oid8;
+ALTER TABLE toastcomp_oid ALTER COLUMN f1 SET STORAGE EXTENDED;
+ALTER TABLE toastcomp_oid8 ALTER COLUMN f1 SET STORAGE EXTENDED;
+ALTER TABLE toastcomp_oid SET (toast_tuple_target = 128);
+ALTER TABLE toastcomp_oid8 SET (toast_tuple_target = 128);
+INSERT INTO toastcomp_oid VALUES (repeat('1234567890', 10000));
+INSERT INTO toastcomp_oid8 VALUES (repeat('1234567890', 10000));
+SELECT pg_column_compression(f1) FROM toastcomp_oid;
+SELECT pg_column_compression(f1) FROM toastcomp_oid8;
+DROP TABLE toastcomp_oid, toastcomp_oid8;
+
--test column type update varlena/non-varlena
CREATE TABLE cmdata2 (f1 int);
\d+ cmdata2
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 38946e8954d..7d9c7275a02 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -563,89 +563,248 @@ SELECT text 'text' || char(20) ' and characters' AS "Concat text to char";
SELECT text 'text' || varchar ' and varchar' AS "Concat text to varchar";
--
--- test substr with toasted text values
+-- Test substr with toasted text values, for all types of TOAST relations
+-- supported.
--
-CREATE TABLE toasttest(f1 text);
+CREATE TABLE toasttest_oid(f1 text) with (toast_value_type = 'oid');
+CREATE TABLE toasttest_oid8(f1 text) with (toast_value_type = 'oid8');
-insert into toasttest values(repeat('1234567890',10000));
-insert into toasttest values(repeat('1234567890',10000));
+insert into toasttest_oid values(repeat('1234567890',10000));
+insert into toasttest_oid values(repeat('1234567890',10000));
+insert into toasttest_oid8 values(repeat('1234567890',10000));
+insert into toasttest_oid8 values(repeat('1234567890',10000));
--
-- Ensure that some values are uncompressed, to test the faster substring
-- operation used in that case
--
-alter table toasttest alter column f1 set storage external;
-insert into toasttest values(repeat('1234567890',10000));
-insert into toasttest values(repeat('1234567890',10000));
+alter table toasttest_oid alter column f1 set storage external;
+insert into toasttest_oid values(repeat('1234567890',10000));
+insert into toasttest_oid values(repeat('1234567890',10000));
+alter table toasttest_oid8 alter column f1 set storage external;
+insert into toasttest_oid8 values(repeat('1234567890',10000));
+insert into toasttest_oid8 values(repeat('1234567890',10000));
-- If the starting position is zero or less, then return from the start of the string
-- adjusting the length to be consistent with the "negative start" per SQL.
-SELECT substr(f1, -1, 5) from toasttest;
+SELECT substr(f1, -1, 5) from toasttest_oid;
+SELECT substr(f1, -1, 5) from toasttest_oid8;
-- If the length is less than zero, an ERROR is thrown.
-SELECT substr(f1, 5, -1) from toasttest;
+SELECT substr(f1, 5, -1) from toasttest_oid;
+SELECT substr(f1, 5, -1) from toasttest_oid8;
-- If no third argument (length) is provided, the length to the end of the
-- string is assumed.
-SELECT substr(f1, 99995) from toasttest;
+SELECT substr(f1, 99995) from toasttest_oid;
+SELECT substr(f1, 99995) from toasttest_oid8;
-- If start plus length is > string length, the result is truncated to
-- string length
-SELECT substr(f1, 99995, 10) from toasttest;
-
-TRUNCATE TABLE toasttest;
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
+SELECT substr(f1, 99995, 10) from toasttest_oid;
+SELECT substr(f1, 99995, 10) from toasttest_oid8;
+
+-- TRUNCATE cases for TOAST relations with OID values.
+TRUNCATE TABLE toasttest_oid;
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
-- expect >0 blocks
SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty
- FROM pg_class where relname = 'toasttest';
-
-TRUNCATE TABLE toasttest;
-ALTER TABLE toasttest set (toast_tuple_target = 4080);
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
-INSERT INTO toasttest values (repeat('1234567890',300));
+ FROM pg_class where relname = 'toasttest_oid';
+TRUNCATE TABLE toasttest_oid;
+ALTER TABLE toasttest_oid set (toast_tuple_target = 4080);
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
+INSERT INTO toasttest_oid values (repeat('1234567890',300));
-- expect 0 blocks
SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty
- FROM pg_class where relname = 'toasttest';
-
-DROP TABLE toasttest;
+ FROM pg_class where relname = 'toasttest_oid';
+DROP TABLE toasttest_oid;
+
+-- TRUNCATE cases for TOAST relation with int8 values.
+TRUNCATE TABLE toasttest_oid8;
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+-- expect >0 blocks
+SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty
+ FROM pg_class where relname = 'toasttest_oid8';
+TRUNCATE TABLE toasttest_oid8;
+ALTER TABLE toasttest_oid8 set (toast_tuple_target = 4080);
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+INSERT INTO toasttest_oid8 values (repeat('1234567890',300));
+-- expect 0 blocks
+SELECT pg_relation_size(reltoastrelid) = 0 AS is_empty
+ FROM pg_class where relname = 'toasttest_oid8';
+DROP TABLE toasttest_oid8;
--
--- test substr with toasted bytea values
+-- test substr with toasted bytea values, for all types of TOAST relations
+-- supported. Do not drop these two relations, for pg_upgrade.
--
-CREATE TABLE toasttest(f1 bytea);
+CREATE TABLE toasttest_oid(f1 bytea) WITH (toast_value_type = 'oid');
+CREATE TABLE toasttest_oid8(f1 bytea) WITH (toast_value_type = 'oid8');
-insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
-insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
+insert into toasttest_oid values(decode(repeat('1234567890',10000),'escape'));
+insert into toasttest_oid values(decode(repeat('1234567890',10000),'escape'));
+insert into toasttest_oid8 values(decode(repeat('1234567890',10000),'escape'));
+insert into toasttest_oid8 values(decode(repeat('1234567890',10000),'escape'));
--
-- Ensure that some values are uncompressed, to test the faster substring
-- operation used in that case
--
-alter table toasttest alter column f1 set storage external;
-insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
-insert into toasttest values(decode(repeat('1234567890',10000),'escape'));
+alter table toasttest_oid alter column f1 set storage external;
+insert into toasttest_oid values(decode(repeat('1234567890',10000),'escape'));
+insert into toasttest_oid values(decode(repeat('1234567890',10000),'escape'));
+alter table toasttest_oid8 alter column f1 set storage external;
+insert into toasttest_oid8 values(decode(repeat('1234567890',10000),'escape'));
+insert into toasttest_oid8 values(decode(repeat('1234567890',10000),'escape'));
-- If the starting position is zero or less, then return from the start of the string
-- adjusting the length to be consistent with the "negative start" per SQL.
-SELECT substr(f1, -1, 5) from toasttest;
+SELECT substr(f1, -1, 5) from toasttest_oid;
+SELECT substr(f1, -1, 5) from toasttest_oid8;
-- If the length is less than zero, an ERROR is thrown.
-SELECT substr(f1, 5, -1) from toasttest;
+SELECT substr(f1, 5, -1) from toasttest_oid;
+SELECT substr(f1, 5, -1) from toasttest_oid8;
-- If no third argument (length) is provided, the length to the end of the
-- string is assumed.
-SELECT substr(f1, 99995) from toasttest;
+SELECT substr(f1, 99995) from toasttest_oid;
+SELECT substr(f1, 99995) from toasttest_oid8;
-- If start plus length is > string length, the result is truncated to
-- string length
-SELECT substr(f1, 99995, 10) from toasttest;
-
-DROP TABLE toasttest;
+SELECT substr(f1, 99995, 10) from toasttest_oid;
+SELECT substr(f1, 99995, 10) from toasttest_oid8;
+
+-- A relation rewrite leaves the TOAST value attributes unchanged.
+VACUUM FULL toasttest_oid;
+VACUUM FULL toasttest_oid8;
+SELECT c1.relname, a.atttypid::regtype
+ FROM pg_attribute AS a,
+ pg_class AS c1,
+ pg_class AS c2
+ WHERE
+ c1.relname IN ('toasttest_oid', 'toasttest_oid8') AND
+ c1.reltoastrelid = c2.oid AND
+ a.attrelid = c2.oid AND
+ a.attname = 'chunk_id'
+ ORDER BY c1.relname COLLATE "C";
+-- Check that data slices are still accessible.
+SELECT substr(f1, 99995) from toasttest_oid;
+SELECT substr(f1, 99995) from toasttest_oid8;
+SELECT substr(f1, 99995, 10) from toasttest_oid;
+SELECT substr(f1, 99995, 10) from toasttest_oid8;
+
+-- ALTER TABLE after TOAST table creation does not affect relation rewrite.
+ALTER TABLE toasttest_oid SET (toast_value_type = oid8);
+ALTER TABLE toasttest_oid8 SET (toast_value_type = oid);
+VACUUM FULL toasttest_oid;
+VACUUM FULL toasttest_oid8;
+SELECT c1.relname, a.atttypid::regtype
+ FROM pg_attribute AS a,
+ pg_class AS c1,
+ pg_class AS c2
+ WHERE
+ c1.relname IN ('toasttest_oid', 'toasttest_oid8') AND
+ c1.reltoastrelid = c2.oid AND
+ a.attrelid = c2.oid AND
+ a.attname = 'chunk_id'
+ ORDER BY c1.relname COLLATE "C";
+REPACK toasttest_oid;
+REPACK toasttest_oid8;
+SELECT c1.relname, a.atttypid::regtype
+ FROM pg_attribute AS a,
+ pg_class AS c1,
+ pg_class AS c2
+ WHERE
+ c1.relname IN ('toasttest_oid', 'toasttest_oid8') AND
+ c1.reltoastrelid = c2.oid AND
+ a.attrelid = c2.oid AND
+ a.attname = 'chunk_id'
+ ORDER BY c1.relname COLLATE "C";
+ALTER TABLE toasttest_oid RESET (toast_value_type);
+ALTER TABLE toasttest_oid8 RESET (toast_value_type);
+
+-- Reset column storage to its default
+ALTER TABLE toasttest_oid ALTER COLUMN f1 SET STORAGE EXTENDED;
+ALTER TABLE toasttest_oid8 ALTER COLUMN f1 SET STORAGE EXTENDED;
+
+-- UPDATE with out-of-line datum that belongs to another TOAST table.
+CREATE TABLE toastupd_oid(f1 text) WITH (toast_value_type = 'oid');
+CREATE TABLE toastupd_oid8(f1 text) WITH (toast_value_type = 'oid8');
+ALTER TABLE toastupd_oid ALTER COLUMN f1 SET STORAGE EXTERNAL;
+ALTER TABLE toastupd_oid8 ALTER COLUMN f1 SET STORAGE EXTERNAL;
+SELECT reltoastrelid::regclass AS upd_oid_toast FROM pg_class
+ WHERE oid = 'toastupd_oid'::regclass \gset
+SELECT reltoastrelid::regclass AS upd_oid8_toast FROM pg_class
+ WHERE oid = 'toastupd_oid8'::regclass \gset
+INSERT INTO toastupd_oid VALUES (repeat('a', 100000));
+INSERT INTO toastupd_oid8 VALUES (repeat('b', 100000));
+-- old value is an oid8 pointer, new value an oid pointer.
+UPDATE toastupd_oid8 SET f1 = toastupd_oid.f1 FROM toastupd_oid;
+SELECT length(f1), substr(f1, 1, 3) FROM toastupd_oid8;
+-- replaced value must be gone, leaving a single value behind.
+SELECT count(*) FROM :upd_oid8_toast WHERE chunk_seq = 0;
+-- reverse: old value is an oid pointer, new value an oid8 pointer.
+TRUNCATE toastupd_oid;
+INSERT INTO toastupd_oid VALUES (repeat('c', 100000));
+UPDATE toastupd_oid SET f1 = toastupd_oid8.f1 FROM toastupd_oid8;
+SELECT length(f1), substr(f1, 1, 3) FROM toastupd_oid;
+SELECT count(*) FROM :upd_oid_toast WHERE chunk_seq = 0;
+DROP TABLE toastupd_oid, toastupd_oid8;
+
+-- TOAST table manipulations with ALTER TABLE and attributes
+CREATE TABLE toast_alter_oid (f1 int, f2 text STORAGE EXTERNAL)
+ WITH (toast_value_type = 'oid');
+INSERT INTO toast_alter_oid VALUES (1, repeat('1234567890',10000));
+INSERT INTO toast_alter_oid VALUES (2, repeat('2234567890',10000));
+BEGIN;
+ALTER TABLE toast_alter_oid DROP COLUMN f2;
+SELECT reltoastrelid::regclass > 0 AS has_toast FROM pg_class
+ WHERE oid = 'toast_alter_oid'::regclass;
+-- New toast_value_type has no effect.
+ALTER TABLE toast_alter_oid SET (toast_value_type = 'oid8');
+ALTER TABLE toast_alter_oid ADD COLUMN f3 text
+ DEFAULT (repeat('5234567890',10000));
+ALTER TABLE toast_alter_oid ALTER COLUMN f3 SET STORAGE EXTERNAL;
+INSERT INTO toast_alter_oid VALUES (3, repeat('3234567890',10000));
+INSERT INTO toast_alter_oid VALUES (4, repeat('4234567890',10000));
+SELECT reltoastrelid::regclass > 0 AS has_toast FROM pg_class
+ WHERE oid = 'toast_alter_oid'::regclass;
+SELECT c1.relname, a.atttypid::regtype
+ FROM pg_attribute AS a,
+ pg_class AS c1,
+ pg_class AS c2
+ WHERE
+ c1.relname = 'toast_alter_oid' AND
+ c1.reltoastrelid = c2.oid AND
+ a.attrelid = c2.oid AND
+ a.attname = 'chunk_id'
+ ORDER BY c1.relname COLLATE "C";
+SELECT f1, substr(f3, 99991) FROM toast_alter_oid;
+ROLLBACK;
+SELECT f1, substr(f2, 99991) FROM toast_alter_oid;
+SELECT c1.relname, a.atttypid::regtype
+ FROM pg_attribute AS a,
+ pg_class AS c1,
+ pg_class AS c2
+ WHERE
+ c1.relname = 'toast_alter_oid' AND
+ c1.reltoastrelid = c2.oid AND
+ a.attrelid = c2.oid AND
+ a.attname = 'chunk_id'
+ ORDER BY c1.relname COLLATE "C";
+DROP TABLE toast_alter_oid;
-- test internally compressing datums