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
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