Thread: [GENERAL] When updating row that has TOAST column, is the TOAST column alsoreinserted ? 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

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

Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> Dorian Hoxha wrote:
>> When updating row that has TOAST column, is the TOAST column also inserted ? Or just the oid?

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

You can also read the documentation ;-).  About halfway down this page:

https://www.postgresql.org/docs/current/static/storage-toast.html

it says:

  During an UPDATE operation, values of unchanged fields are normally
  preserved as-is; so an UPDATE of a row with out-of-line values incurs no
  TOAST costs if none of the out-of-line values change.

I don't remember offhand what corner cases might exist to prompt the
weasel wording "normally".  Maybe that just reflects the possibility
that one of the newly updated values would need toasting.

            regards, tom lane