Currently PostgreSQL has a feature which is called snapshot too old. While each release of PostgreSQL comes with a bunch of new features, sometimes features also get removed. Starting with PostgreSQL 17 there will be most likely no more “snapshot too old”. Before looking at why that got removed lets first have a look at what it is.
The main goal of this feature was to reduce table bloat and it is very well described in the documentation.
By default this feature is disabled:
1 2 3 4 5 6 7 8 9 10 11 12 | postgres=# select version(); version ----------------------------------------------------------------------------------------------------------------- PostgreSQL 16beta3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64- bit (1 row) postgres=# \dconfig *snapshot* List of configuration parameters Parameter | Value ------------------------+------- old_snapshot_threshold | -1 (1 row) |
To see this is in action we can do the following: Enable the feature by setting it to one minute:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | postgres=# select context from pg_settings where name = 'old_snapshot_threshold' ; context ------------ postmaster (1 row) postgres=# alter system set old_snapshot_threshold = 1; ALTER SYSTEM postgres=# \! pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2023-09-08 11:58:10.718 CEST - 1 - 15692 - - @ - 0LOG: redirecting log output to logging collector process 2023-09-08 11:58:10.718 CEST - 2 - 15692 - - @ - 0HINT: Future log output will appear in directory "pg_log" . done server started postgres=# select 1; FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. postgres=# select 1; ? column ? ---------- 1 (1 row) postgres=# show old_snapshot_threshold ; old_snapshot_threshold ------------------------ 1min (1 row) postgres=# |
We’ve set old_snapshot_threshold to 1 minute and now we can easily produce a snapshot too old error. The procedure is:
- Create a table in the first session
- Start a transaction in a second session and select from that table
- Insert, update and delete all data from the table in the first session. The transaction in the second session started before all that so it will never see any rows as everything is gone, but not committed
- In a third session do a vacuum short before 1min passed (you can use pg_sleep to add a delay)
- In the second session also add some delay and then select from the table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | -- session 1 postgres=# create table t ( a int ); CREATE TABLE -- session 2 postgres=# begin transaction isolation level repeatable read ; BEGIN postgres=*# select * from t; a --- (0 rows ) -- session 1 postgres=# begin ; BEGIN postgres=*# insert into t select * from generate_series(1,1000); INSERT 0 1000 postgres=*# update t set a = 3; UPDATE 1000 postgres=*# delete from t; DELETE 1000 postgres=*# commit ; COMMIT -- session 3 postgres=# select pg_sleep(50); vacuum t; -- session 2 postgres=*# select pg_sleep(50); pg_sleep ---------- (1 row) postgres=*# select * from t; ERROR: snapshot too old postgres=!# |
This means vacuum already did the cleanup of those rows and this triggers the “snapshot too old” error. This was mean as a last defense for long running transaction or reporting queries. From PostgreSQL 17 on, this is not anymore possible. The commit message has all the details and links to the discussions around this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Remove the "snapshot too old" feature. Remove the old_snapshot_threshold setting and mechanism for producing the error "snapshot too old", originally added by commit 848ef42b. Unfortunately it had a number of known problems in terms of correctness and performance, mostly reported by Andres in the course of his work on snapshot scalability. We agreed to remove it, after a long period without an active plan to fix it. This is certainly a desirable feature, and someone might propose a new or improved implementation in the future. Reported-by: Andres Freund <andres@anarazel.de> |
Franck Pachot
27.05.2024Hi Daniel. I guess the workaround is transaction timeout, I see it has been accepted: https://commitfest.postgresql.org/45/4040/