Re: [GENERAL] When updating row that has TOAST column, is the TOASTcolumn also reinserted ? Or just the oid to the value? - Mailing list pgsql-general

Great info Albe!

On 13/12/2016 16:20, Albe Laurenz wrote:
> 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
>


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt