Re: When updating row that has TOAST column, is the TOAST column also reinserted ? Or just the oid to the value?

Dorian Hoxha wrote:
> When updating row that has TOAST column, is the TOAST column also inserted ? Or just the oid?
>
> Say I have a 1MB value in the TOAST column, and I update the row by changing another column, and since
> every update is an insert, will it also reinsert the toast-column ? The column that I will update will
> have an index so I think hot-update won't work in this case ? The same question also when full-page-
> writes is enabled ?
>
>
> Using 9.6.

The TOAST table will remain unchanged by the UPDATE; you can see that with the
"pageinspect" contrib module:

CREATE TABLE longtext (
id integer primary key,
val text NOT NULL,
other integer NOT NULL
);

INSERT INTO longtext VALUES (
4,
(SELECT string_agg(chr((random()*25+65)::integer), '')
FROM generate_series(1, 2000)),
42
);

SELECT reltoastrelid, reltoastrelid::regclass FROM pg_class
WHERE oid = 'longtext'::regclass;

reltoastrelid | reltoastrelid
---------------+-------------------------
25206 | pg_toast.pg_toast_25203
(1 row)

Use "pageinspect" to see the tuples in the table and the TOAST table:

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), 25203);

t_xmin | t_xmax | t_ctid | id | val | other
--------+--------+--------+------------+----------------------------------------+------------
2076 | 0 | (0,1) | \x04000000 | \x0112d4070000d00700007b62000076620000 | \x2a000000
(1 row)

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_25203', 0), 25206);

t_xmin | t_xmax | t_ctid | chunk_id | chunk_seq
--------+--------+--------+------------+------------
2076 | 0 | (0,1) | \x7b620000 | \x00000000
2076 | 0 | (0,2) | \x7b620000 | \x01000000
(2 rows)

Now let's UPDATE:

UPDATE longtext SET other = -1 WHERE id = 4;

Let's look at the tuples again:

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), 25203);

t_xmin | t_xmax | t_ctid | id | val | other
--------+--------+--------+------------+----------------------------------------+------------
2076 | 2077 | (0,2) | \x04000000 | \x0112d4070000d00700007b62000076620000 | \x2a000000
2077 | 0 | (0,2) | \x04000000 | \x0112d4070000d00700007b62000076620000 | \xffffffff
(2 rows)

A new tuple has been entered, but "val" still points to chunk ID 0x0000627b
(this is a little-endian machine).

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_25203', 0), 25206);

t_xmin | t_xmax | t_ctid | chunk_id | chunk_seq
--------+--------+--------+------------+------------
2076 | 0 | (0,1) | \x7b620000 | \x00000000
2076 | 0 | (0,2) | \x7b620000 | \x01000000
(2 rows)

The TOAST table is unchanged!

This was a HOT update, but it works the same for a non-HOT update:

UPDATE longtext SET id = 1 WHERE id = 4;

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), 25203);

t_xmin | t_xmax | t_ctid | id | val | other
--------+--------+--------+------------+----------------------------------------+------------
2076 | 2077 | (0,2) | \x04000000 | \x0112d4070000d00700007b62000076620000 | \x2a000000
2077 | 2078 | (0,3) | \x04000000 | \x0112d4070000d00700007b62000076620000 | \xffffffff
2078 | 0 | (0,3) | \x01000000 | \x0112d4070000d00700007b62000076620000 | \xffffffff
(3 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_25203', 0), 25206);

t_xmin | t_xmax | t_ctid | chunk_id | chunk_seq
--------+--------+--------+------------+------------
2076 | 0 | (0,1) | \x7b620000 | \x00000000
2076 | 0 | (0,2) | \x7b620000 | \x01000000
(2 rows)

Yours,
Laurenz Albe