Postgres: Rewriting Existing Values Generate Dead TOAST Tuples

dead_toast_tuples.sql

// adapted from https://www.postgresql.org/message-id/A737B7A37273E048B164557ADEF4A58B539A6ACE@ntex2010i.host.magwien.gv.at

// tested on PG 9.6.12

// create extension for viewing physical page data
CREATE EXTENSION pageinspect;
=>
CREATE EXTENSION

// create test table
CREATE TABLE longtext (
 id integer primary key,
 val text NOT NULL,
 other integer NOT NULL
);
=> 
CREATE TABLE

// insert new row into table. second value will be stored in toast table
INSERT INTO longtext VALUES (
 4,
 (SELECT string_agg(chr((random()*25+65)::integer), '')
 FROM generate_series(1, 2000)),
 42
);
=>
INSERT 0 1

// fetch toast table metadata
SELECT reltoastrelid, reltoastrelid::regclass FROM pg_class WHERE oid = 'longtext'::regclass;
=>
reltoastrelid |       reltoastrelid
---------------+---------------------------
       5739831 | pg_toast.pg_toast_5739828

// fetch a raw page of data from the longtext table
// the longtext table shows a single row that is visible (t_xmax == 0) and the val column value is a reference into a toast table
// 5739828 is the reltoastrelid second colun suffix from above query, which is used in this query
SELECT t_xmin, t_xmax, t_ctid, t_attrs[1] AS id, t_attrs[2] AS val, t_attrs[3] AS other FROM heap_page_item_attrs(get_raw_page('longtext', 0), 5739828);
=>
t_xmin  | t_xmax | t_ctid |     id     |                  val                   |   other
---------+--------+--------+------------+----------------------------------------+------------
 2001937 |      0 | (0,1)  | \x04000000 | \x0112d4070000d00700003c95570037955700 | \x2a000000

// fetch a raw page of data from the longtext's toast storage table
// the toast table has 2 rows since the val had to be broken into 2 chunks
// 5739831 is the reltoastrelid first column and pg_toast_5739828 is reltoastrelid second column from the toast metadata query above 
SELECT t_xmin, t_xmax, t_ctid, t_attrs[1] AS chunk_id, t_attrs[2] AS chunk_seq FROM heap_page_item_attrs(get_raw_page('pg_toast.pg_toast_5739828', 0), 5739831);
=>
t_xmin  | t_xmax | t_ctid |  chunk_id  | chunk_seq
---------+--------+--------+------------+------------
 2001937 |      0 | (0,1)  | \x3c955700 | \x00000000
 2001937 |      0 | (0,2)  | \x3c955700 | \x01000000
 
 
// update the row to generate a dead tuple in the longtext table
UPDATE longtext SET other = -1 WHERE id = 4;
=>
UPDATE 1

// fetch a raw page of data from the longtext table
// see the dead tuple (t_max != 0) and the live tuple
// the reference in the `val` column remains the same
SELECT t_xmin, t_xmax, t_ctid, t_attrs[1] AS id, t_attrs[2] AS val, t_attrs[3] AS other FROM heap_page_item_attrs(get_raw_page('longtext', 0), 5739828);
=> 
t_xmin  | t_xmax  | t_ctid |     id     |                  val                   |   other
---------+---------+--------+------------+----------------------------------------+------------
 2001937 | 2001938 | (0,2)  | \x04000000 | \x0112d4070000d00700003c95570037955700 | \x2a000000
 2001938 |       0 | (0,2)  | \x04000000 | \x0112d4070000d00700003c95570037955700 | \xffffffff

// fetch a raw page of data from the longtext's toast storage table
// still see the same 2 chunks in toast
SELECT t_xmin, t_xmax, t_ctid, t_attrs[1] AS chunk_id, t_attrs[2] AS chunk_seq FROM heap_page_item_attrs(get_raw_page('pg_toast.pg_toast_5739828', 0), 5739831);
=>
 t_xmin  | t_xmax | t_ctid |  chunk_id  | chunk_seq
---------+--------+--------+------------+------------
 2001937 |      0 | (0,1)  | \x3c955700 | \x00000000
 2001937 |      0 | (0,2)  | \x3c955700 | \x01000000

// pull out the existing value
select val from longtext;
=>
XFRPLTHKEXLEHBOAPWMRWGRRWWWUHTMFZEULWBVBYHFGITGXQSPNYGGVDCQKVDPVHKHEMDFLKKSSEYPURFIPLOLOQDYNGPJMAQRMTWYEHQVLPMFHROWDCIRTLRGRGPEHGVTZRRDYIZJXLPFDECGGKXZVPGNVVRDCMWBFOEDXENVQDBTGDZMNXMJNSWKNONPBKQGYVJWAWSQYTKGWKSKIFTVWQGLFTBGDRLCNUYNRRERMNWJXPTGUNDRFJDJCDPGUCIIWHVOZZGLNDULSOSNCVFHEIQHLHMGIUPGDLUCKANYDIKWWDJZXOHDWXKIFWPNRETUPOWAOLYRTJORMXQKNXNKVWSBTHPMMJHCWECLPAEJJRAVQRGDPSNLQGMKOCVALDCIHETWEXFNPGJGWOJMIXXXEKISMESWHUFOYYLDWQQMWZSTODGWBEUFODWBHPXPKDEJDPMZGDMDDGWRJDNJHIOVLLWSCUIMXLVABIAHLMKOSHGCKTLRCZNNLJGMEOYCZUCBECIPPSEHYJJJDUBFTOSFXYRCMQEMMGNQIVFXOJFNSOWWJXCDMUIKSANGRRSEXGTHDZFQJKECZBYJYAMLUUVNVJTNBMQYTKFVKKNSURUUSTDRTPDOLYCHIVTIJKHCVMYGWLYRDUMVOPNIGQWQPYXWURFEDMGYZFEWQEOTXBPMQDTWTQNIPLGKELOGXVFXAKURNJLLKBXBERXXILGYWMJBXXHVTNTTWOLKXWWIXTKCMIZUTGTQSDRQBZMUMGOJUAUSWQBULLWXTWSNDLEVOVMPVXKIEYSYYMRVESPPEMMXJFLMRPHGLTVHRGPVFHUEUMYYFPNJCBHLGRWXHFESYZYQFONKVINQUNPZCDIEEPPKHNIOSMHQLGIRUUBQDPHXDWWFZGKEVZODNWRFIYWTFELZYMQCCXZFUWKUDTYYTNCHJTMRSILXNWXMJOOMMOQHLBDOVBNPOPWXIJPBRCYFYWRJKGUWULFHNHVIIJXXXUVHELIVNHAMDRVOXQLSCPYPWUXGEWDCRYJULQQYXQLCIGPGWBYZRXPOSNUWKXYBXHWIYNHWESYMYOSWPQVHOLVHYQEJPDKMKHVJUCGYUELTTEQJUMQJXMRWDVFSYQFIXBRRDWQXCCRUGIEBUUKSHCPLXUDVLIFJJWBMTSKVUDRALVBGQMYYOOJMKNIVVNFFKGREYCATERTPNUWEHVDVKMHUZQRVEWBODSSCUTUYLOOXJLDQHFMRRTNRKENOBODEHVGCOBBZPPXYBBQIGDAXWNQHREVSSYWATDCJEDITSHSUHJDNMDMJQDRIHNCALYBGCDOFGXYYERTMBVANYMWPPNYVBAVMYWSBZIGGGFFKWYWXUWKUJHKXUJUVJQIINCKNJQTPVYZSXWPSSBNCHXADHUYQLHYZJJMSZGIUFINCFDVXEJALHBONVNEHUDHEMTWLAFHFNUHSXDQCMQOSRDGNPKUKOCOAVKMVPSBENJVLMMOXECRUFXIUIDFWFTWBEJWTDXXQHTCTHQRKSJGXHOSPRXMVQKWUTTOVSMMYGPSNFKXXTEVBSORJMEGDODXIWNEPZQOGGITLSSKMWGNOTFYGJFJXIHGFUKUUBJBHRUSKNDWJJKXDPWJYCSWKBDPVMKPOTQVLLOVYRTIBEGETDNTFHQPITFDGPTUJKPUVERUWKDWPJBJMPDSVTHEMMHSCBNLLDGHIXCEIFCXODHBSKSPDBTQNBJPCWAOBGVIEXNNDPKQSRRLCKBFLUVYVFOXCOMDVHLZFYMIOXYHPQSRBTWMOSMJXBHAPTDLBOKHNXPCVOJKECCFVYSKRETPFCPUVSFWIQEVNSXJHHTLJVRGVKQMOKCTLSNHLTETJIPXBNGIVBUFWMLSVCFKLIEXARFLKJFURVRSJXBFYVKVIVOEWTOJCRGCJLOTUTOMPGFYFGEECOAKJOOHICQKUWMEIBXDUMOKSTJXAMCCBCMKQBRYEIKXEWCNXAPSMFDGYMEYYFCZHO

// update the val to the current val, a noop change
UPDATE longtext SET val = 'XFRPLTHKEXLEHBOAPWMRWGRRWWWUHTMFZEULWBVBYHFGITGXQSPNYGGVDCQKVDPVHKHEMDFLKKSSEYPURFIPLOLOQDYNGPJMAQRMTWYEHQVLPMFHROWDCIRTLRGRGPEHGVTZRRDYIZJXLPFDECGGKXZVPGNVVRDCMWBFOEDXENVQDBTGDZMNXMJNSWKNONPBKQGYVJWAWSQYTKGWKSKIFTVWQGLFTBGDRLCNUYNRRERMNWJXPTGUNDRFJDJCDPGUCIIWHVOZZGLNDULSOSNCVFHEIQHLHMGIUPGDLUCKANYDIKWWDJZXOHDWXKIFWPNRETUPOWAOLYRTJORMXQKNXNKVWSBTHPMMJHCWECLPAEJJRAVQRGDPSNLQGMKOCVALDCIHETWEXFNPGJGWOJMIXXXEKISMESWHUFOYYLDWQQMWZSTODGWBEUFODWBHPXPKDEJDPMZGDMDDGWRJDNJHIOVLLWSCUIMXLVABIAHLMKOSHGCKTLRCZNNLJGMEOYCZUCBECIPPSEHYJJJDUBFTOSFXYRCMQEMMGNQIVFXOJFNSOWWJXCDMUIKSANGRRSEXGTHDZFQJKECZBYJYAMLUUVNVJTNBMQYTKFVKKNSURUUSTDRTPDOLYCHIVTIJKHCVMYGWLYRDUMVOPNIGQWQPYXWURFEDMGYZFEWQEOTXBPMQDTWTQNIPLGKELOGXVFXAKURNJLLKBXBERXXILGYWMJBXXHVTNTTWOLKXWWIXTKCMIZUTGTQSDRQBZMUMGOJUAUSWQBULLWXTWSNDLEVOVMPVXKIEYSYYMRVESPPEMMXJFLMRPHGLTVHRGPVFHUEUMYYFPNJCBHLGRWXHFESYZYQFONKVINQUNPZCDIEEPPKHNIOSMHQLGIRUUBQDPHXDWWFZGKEVZODNWRFIYWTFELZYMQCCXZFUWKUDTYYTNCHJTMRSILXNWXMJOOMMOQHLBDOVBNPOPWXIJPBRCYFYWRJKGUWULFHNHVIIJXXXUVHELIVNHAMDRVOXQLSCPYPWUXGEWDCRYJULQQYXQLCIGPGWBYZRXPOSNUWKXYBXHWIYNHWESYMYOSWPQVHOLVHYQEJPDKMKHVJUCGYUELTTEQJUMQJXMRWDVFSYQFIXBRRDWQXCCRUGIEBUUKSHCPLXUDVLIFJJWBMTSKVUDRALVBGQMYYOOJMKNIVVNFFKGREYCATERTPNUWEHVDVKMHUZQRVEWBODSSCUTUYLOOXJLDQHFMRRTNRKENOBODEHVGCOBBZPPXYBBQIGDAXWNQHREVSSYWATDCJEDITSHSUHJDNMDMJQDRIHNCALYBGCDOFGXYYERTMBVANYMWPPNYVBAVMYWSBZIGGGFFKWYWXUWKUJHKXUJUVJQIINCKNJQTPVYZSXWPSSBNCHXADHUYQLHYZJJMSZGIUFINCFDVXEJALHBONVNEHUDHEMTWLAFHFNUHSXDQCMQOSRDGNPKUKOCOAVKMVPSBENJVLMMOXECRUFXIUIDFWFTWBEJWTDXXQHTCTHQRKSJGXHOSPRXMVQKWUTTOVSMMYGPSNFKXXTEVBSORJMEGDODXIWNEPZQOGGITLSSKMWGNOTFYGJFJXIHGFUKUUBJBHRUSKNDWJJKXDPWJYCSWKBDPVMKPOTQVLLOVYRTIBEGETDNTFHQPITFDGPTUJKPUVERUWKDWPJBJMPDSVTHEMMHSCBNLLDGHIXCEIFCXODHBSKSPDBTQNBJPCWAOBGVIEXNNDPKQSRRLCKBFLUVYVFOXCOMDVHLZFYMIOXYHPQSRBTWMOSMJXBHAPTDLBOKHNXPCVOJKECCFVYSKRETPFCPUVSFWIQEVNSXJHHTLJVRGVKQMOKCTLSNHLTETJIPXBNGIVBUFWMLSVCFKLIEXARFLKJFURVRSJXBFYVKVIVOEWTOJCRGCJLOTUTOMPGFYFGEECOAKJOOHICQKUWMEIBXDUMOKSTJXAMCCBCMKQBRYEIKXEWCNXAPSMFDGYMEYYFCZHO' WHERE id = 4;
=>
UPDATE 1

// fetch a raw page of data from the longtext's toast storage table
// see a dead tuple in toast (t_max != 0) and new rows
SELECT t_xmin, t_xmax, t_ctid, t_attrs[1] AS chunk_id, t_attrs[2] AS chunk_seq FROM heap_page_item_attrs(get_raw_page('pg_toast.pg_toast_5739828', 0), 5739831);
=> 
 t_xmin  | t_xmax  | t_ctid |  chunk_id  | chunk_seq
---------+---------+--------+------------+------------
 2001937 | 2001939 | (0,1)  | \x3c955700 | \x00000000
 2001937 | 2001939 | (0,2)  | \x3c955700 | \x01000000
 2001939 |       0 | (0,3)  | \x3d955700 | \x00000000
 2001939 |       0 | (0,4)  | \x3d955700 | \x01000000

// fetch a raw page of data from the longtext table
// the update also causes dead tuple in the longtext table since the toast pointer had to be updated
SELECT t_xmin, t_xmax, t_ctid, t_attrs[1] AS id, t_attrs[2] AS val, t_attrs[3] AS other FROM heap_page_item_attrs(get_raw_page('longtext', 0), 5739828);
=> 
t_xmin  | t_xmax  | t_ctid |     id     |                  val                   |   other
---------+---------+--------+------------+----------------------------------------+------------
 2001937 | 2001938 | (0,2)  | \x04000000 | \x0112d4070000d00700003c95570037955700 | \x2a000000
 2001938 | 2001939 | (0,3)  | \x04000000 | \x0112d4070000d00700003c95570037955700 | \xffffffff
 2001939 |       0 | (0,3)  | \x04000000 | \x0112d4070000d00700003d95570037955700 | \xffffffff