Re: Schema variables - new implementation for Postgres 15 (typo)

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