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

Started by Dorian Hoxhaover 9 years ago4 messagesgeneral

dorian.hoxha@gmail.com

over 9 years ago

Hello friends,

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.

Thank you!

laurenz.albe@cybertec.at

over 9 years ago

In reply to: Dorian Hoxha (#1)

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Achilleas Mantzios

achill@matrix.gatewaynet.com

over 9 years ago

In reply to: Laurenz Albe (#2)

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

tgl@sss.pgh.pa.us

over 9 years ago

In reply to: Laurenz Albe (#2)

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general