Hi I read notes from the FOSDEM developer meeting, and I would like to repeat notice about motivation for introduction of session variables, and one reason why session_variables are not transactional, and why they should not be replaced by temp tables is performance.
There are more use cases where session variables can be used. One scenario for session variables is to use them like static variables. They can be used from some rows triggers, .. where local variable is not enough (like https://www.cybertec-postgresql.com/en/why-are-my-postgresql-updates-getting-slower/ ) create variable xx as int; do $$ begin let xx = 1; for i in 1..10000 loop let xx = xx + 1; end loop; raise notice '%', xx; end; $$; NOTICE: 10001 DO Time: 4,079 ms create temp table xx01(a int); delete from xx01; vacuum full xx01; vacuum; do $$ begin insert into xx01 values(1); for i in 1..10000 loop update xx01 set a = a + 1; end loop; raise notice '%', (select a from xx01); end; $$; NOTICE: 10001 DO Time: 1678,949 ms (00:01,679) postgres=# \dt+ xx01 List of relations ┌───────────┬──────┬───────┬───────┬─────────────┬───────────────┬────────┬─────────────┐ │ Schema │ Name │ Type │ Owner │ Persistence │ Access method │ Size │ Description │ ╞═══════════╪══════╪═══════╪═══════╪═════════════╪═══════════════╪════════╪═════════════╡ │ pg_temp_3 │ xx01 │ table │ pavel │ temporary │ heap │ 384 kB │ │ └───────────┴──────┴───────┴───────┴─────────────┴───────────────┴────────┴─────────────┘ (1 row) Originally, I tested 100K iterations, but it was too slow, and I cancelled it after 5 minutes. Vacuum can be done after the end of transaction. And there can be another negative impact related to bloating of pg_attribute, pg_class, pg_depend tables. Workaround based on custom GUC is not too bad, but there is not any possibility of security protection (and there is not any possibility of static check in plpgsql_check) - and still it is 20x slower than session variables do $$ begin perform set_config('cust.xx', '1', false); for i in 1..10000 loop perform set_config('cust.xx', (current_setting('cust.xx')::int + 1)::text, true); end loop; raise notice '%', current_setting('cust.xx'); end; $$; NOTICE: 10001 DO Time: 80,201 ms Session variables don't try to replace temp tables, and temp tables can be a very bad replacement of session's variables. Regards Pavel