Re: proposal: session server side variables

Hi

2016-11-28 10:39 GMT+01:00 Artur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru>:

> On 28.11.2016 10:42, Pavel Stehule wrote:
>
>>
>> next update - setattr, getattr functions are working now
>>
>> notes, comments?
>>
>> Regards
>>
>> Pavel
>>
>>
> It is interesting!
>
> Do you have plans to support also table variables? For example, like this:
>
> create type composite_type_2 as (a int, b text);
> create variable var7 composite_type_2;
> select insertvar('var7','(10,Hello world\, Hello world\, Hello world)');
> select insertvar('var7','(1000,Hola, hola!)');
> select * from getvar('var7');
> a | b
> ------+---------------------------------------
> 10 | Hello world, Hello world, Hello world
> 1000 | Hola, hola!
>
> Or it is a bad idea? Or it is not related to this patch?
>

Minimally in first stage I have not plan to support tables. It opens lot of
questions - lot of code to implement - how to implement indexes,
statistics, MVCC?

But some workaround is not hard - you can store a array of composite types.

postgres=# select setvar('a', array(select row(10,'ahoj')::test from
generate_series(1,10)));
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│
a
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
│
{"(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,aho
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
(1 row)

Time: 0,992 ms
postgres=# select * from unnest(getvar('a'));
┌────┬──────┐
│ a │ b │
╞════╪══════╡
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
└────┴──────┘
(10 rows)

For fast append it needs another significant work (and can be done in next
step), but almost all work did Tom already.

> We have the extension (https://github.com/postgrespro/pg_variables). And
> it supports table like variables. It shows better performance against
> temporary tables.
>
> --
> Artur Zakirov
> Postgres Professional: http://www.postgrespro.com
> Russian Postgres Company
>