PostgreSQL存储过程中commit之后立即获取toast值报错

问题概述

PostgreSQL使用TOAST 机制来存储较大的行值，当表中行值较长时可能会把值切片并存储到pg_toast表中。

如果很不巧，我们在存储过程中使用到了TOAST 存储的行值，并且是在commit之后获取了TOAST 存储的行值，那么就会遇到一个报错“no known snapshots”

该问题在12.8,13.4,14,15及以后版本中已经修复，测试不存在该问题

12.8的发布日志中可以看到修复记录

Restore the Portal-level snapshot after COMMIT or ROLLBACK within a procedure (Tom Lane)

This change fixes cases where an attempt to fetch a toasted value immediately after COMMIT/ROLLBACK would fail with errors like “no known snapshots” or “missing chunk number 0 for toast value”.

问题验证

pg12.3中存在该问题

[postgres@mydb1a pg14_0]$ psql
psql (12.3)
Type "help" for help.

postgres=# CREATE TABLE toasted(id serial primary key, data text);
INSERT INTO toasted(data) VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 1000)));
INSERT INTO toasted(data) VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 1000)));
DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toasted LOOP INSERT INTO toasted(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;
CREATE TABLE
postgres=# INSERT INTO toasted(data) VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 1000)));
INSERT 0 1
postgres=# INSERT INTO toasted(data) VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 1000)));
INSERT 0 1
postgres=# DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toasted LOOP INSERT INTO toasted(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;
ERROR:  no known snapshots
CONTEXT:  PL/pgSQL function inline_code_block line 1 at FOR over SELECT rows
postgres=#

pg12.8中可以正常执行，无报错

[postgres@mydb1a pg14_0]$ psql -p 5438
psql (12.3, server 12.8)
Type "help" for help.

postgres=# CREATE TABLE toasted(id serial primary key, data text);
ERROR:  relation "toasted" already exists
postgres=# INSERT INTO toasted(data) VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 1000)));
INSERT 0 1
postgres=# INSERT INTO toasted(data) VALUES((SELECT string_agg(random()::text, ':') FROM generate_series(1, 1000)));
INSERT 0 1
postgres=# DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toasted LOOP INSERT INTO toasted(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;
DO
postgres=#

解决方案

1）升级到12.8,13.4,14,15及之后版本即可，测试示例用12.8版本加原data目录启动数据库，执行无报错

[postgres@mydb1a pg12_8]$ bin/pg_ctl -D /app/pg12/data/ start
waiting for server to start....2023-05-12 05:08:55.167 CST [633156] LOG:  00000: starting PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
2023-05-12 05:08:55.167 CST [633156] LOCATION:  PostmasterMain, postmaster.c:1019
2023-05-12 05:08:55.167 CST [633156] LOG:  00000: listening on IPv4 address "0.0.0.0", port 5432
2023-05-12 05:08:55.167 CST [633156] LOCATION:  StreamServerPort, pqcomm.c:590
2023-05-12 05:08:55.167 CST [633156] LOG:  00000: listening on IPv6 address "::", port 5432
2023-05-12 05:08:55.167 CST [633156] LOCATION:  StreamServerPort, pqcomm.c:590
2023-05-12 05:08:55.168 CST [633156] LOG:  00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-05-12 05:08:55.168 CST [633156] LOCATION:  StreamServerPort, pqcomm.c:584
2023-05-12 05:08:55.568 CST [633156] LOG:  00000: redirecting log output to logging collector process
2023-05-12 05:08:55.568 CST [633156] HINT:  Future log output will appear in directory "pg_log".
2023-05-12 05:08:55.568 CST [633156] LOCATION:  SysLogger_Start, syslogger.c:675
............... done
server started
[postgres@mydb1a pg12_8]$ psql
psql (12.3, server 12.8)
Type "help" for help.

postgres=# DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toasted LOOP INSERT INTO toasted(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;
DO

postgres=# show data_directory;
 data_directory
----------------
 /app/pg12/data
(1 row)

postgres=#

2）如果行值小于8k，可以设置字段的存储策略为plain，并重写数据，因为修改存储策略不会改变已有数据的存储情况

测试示例中data字段原存储策略为external，插入长度6000的值后，触发了toast(向表中存储超过TOAST_TUPLE_THRESHOLD字节（通常是2kB）的行值的时候就会触发toast），toast表中有记录，执行代码块报错；

修改存储策略为plain（需注意：此策略不允许存储大小超过8k的行值），已有数据存储结构不会改变，执行代码块报错，重写数据后，toast表无记录，执行代码块无报错

postgres=# INSERT INTO toast2(data) VALUES( lpad('x', 6000));
INSERT 0 1
postgres=# INSERT INTO toast2(data) VALUES( lpad('x', 6000));
INSERT 0 1
postgres=# DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toast2 LOOP INSERT INTO toast2(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;
ERROR:  no known snapshots
CONTEXT:  PL/pgSQL function inline_code_block line 1 at FOR over SELECT rows
postgres=# select oid from pg_class where relname='toast2';
  oid
--------
 187318
(1 row)
postgres=# select count(1) from pg_toast.pg_toast_187318;
 count
-------
    12
(1 row)

postgres=# alter table toast2 alter COLUMN data set storage plain;
ALTER TABLE
postgres=# DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toast2 LOOP INSERT INTO toast2(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;
ERROR:  no known snapshots
CONTEXT:  PL/pgSQL function inline_code_block line 1 at FOR over SELECT rows
postgres=# truncate toast2;
TRUNCATE TABLE
postgres=# INSERT INTO toast2(data) VALUES( lpad('x', 6000));
INSERT 0 1
postgres=# INSERT INTO toast2(data) VALUES( lpad('x', 6000));
INSERT 0 1
postgres=# DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toast2 LOOP INSERT INTO toast2(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;
DO
postgres=# select count(1) from pg_toast.pg_toast_187318;
 count
-------
     0
(1 row)
postgres=#

另附：4种TOAST存储策略的区别

• ​ PLAIN避免压缩或者线外存储；而且它禁用变长类型的单字节头部。这是不可TOAST数据类型列的唯一可能的策略。只是对那些不能TOAST的数据类型才有可能。
• ​ EXTENDED允许压缩和线外存储。这是大多数可TOAST数据类型的默认策略。 首先将尝试进行压缩，如果行仍然太大，那么则进行线外存储。
• ​ EXTERNAL允许线外存储，但是不许压缩。使用EXTERNAL将令那些在宽text和 bytea列上的子串操作更快（代价是增加了存储空间）， 因此这些操作被优化为只抓取未压缩线外数据中需要的部分。
• ​ MAIN允许压缩，但不允许线外存储（实际上，在这样的列上仍然会进行线外存储，但只是作为没有办法把行变得足以放入一页的情况下的最后手段）。

参考文档

http://postgres.cn/docs/12/storage-toast.html

https://www.postgresql.org/docs/release/12.8/