Re: Schema variables - new implementation for Postgres 15 (typo)
| От | Pavel Stehule |
|---|---|
| Тема | Re: Schema variables - new implementation for Postgres 15 (typo) |
| Дата | |
| Msg-id | CAFj8pRBy7eqXSARmFHJQWeaH-RYeRUdUywVEfFbBJ4MKr8xQXQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Schema variables - new implementation for Postgres 15 (typo) (Pavel Stehule <pavel.stehule@gmail.com>) |
| Список | pgsql-hackers |
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
begin
let xx = 1;
for i in 1..10000 loop
let xx = xx + 1;
end loop;
for i in 1..10000 loop
let xx = xx + 1;
end loop;
raise notice '%', xx;
end;
$$;
end;
$$;
NOTICE: 10001
DO
Time: 4,079 ms
DO
Time: 4,079 ms
create temp table xx01(a int);
delete from xx01; vacuum full xx01; vacuum;
delete from xx01; vacuum full xx01; vacuum;
do $$
begin
begin
insert into xx01 values(1);
for i in 1..10000 loop
update xx01 set a = a + 1;
end loop;
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)
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)
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
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;
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
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
В списке pgsql-hackers по дате отправления: