Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Postgres: Rewriting Existing Values Generate Dead TOAST Tuples
// 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment