Tuesday, March 20, 2018

some update about schema variables

I sent new patch to mailing list - can be composite, scalar or array - and it is working like plpgsql variables (but are placed to schema, and content is session limited) - that was my target:
postgres=# create variable foo as numeric default 0;
CREATE VARIABLE
postgres=# select foo;
┌─────┐
│ foo │
╞═════╡
│   0 │
└─────┘
(1 row)

postgres=# let foo = pi();
LET 
postgres=# select foo;
┌──────────────────┐
│       foo        │
╞══════════════════╡
│ 3.14159265358979 │
└──────────────────┘
(1 row)

postgres=# create variable boo as (x numeric default 0, y numeric default 0);
CREATE VARIABLE
postgres=# let boo.x = 100;
LET 
postgres=# select boo;
┌─────────┐
│   boo   │
╞═════════╡
│ (100,0) │
└─────────┘
(1 row)

postgres=# select boo.x;
┌─────┐
│  x  │
╞═════╡
│ 100 │
└─────┘
(1 row)
In new session, the variables are available - but the content was initialized:
[pavel@nemesis src]$ psql
psql (11devel)
Type "help" for help.

postgres=# \dV
             List of relations
┌────────┬──────┬─────────────────┬───────┐
│ Schema │ Name │      Type       │ Owner │
╞════════╪══════╪═════════════════╪═══════╡
│ public │ boo  │ schema variable │ pavel │
│ public │ foo  │ schema variable │ pavel │
└────────┴──────┴─────────────────┴───────┘
(2 rows)

postgres=# select boo;
┌───────┐
│  boo  │
╞═══════╡
│ (0,0) │
└───────┘
(1 row)
These variables can be used like package variables in PL/SQL:
postgres=# create schema mypackage;
CREATE SCHEMA
postgres=# create variable mypackage.state as (name text, last_access timestamp, debug boolean default false);
CREATE VARIABLE
postgres=# let mypackage.state.name = 'Pavel';
LET 
postgres=# let mypackage.state.last_access = current_timestamp;
LET 
postgres=# select mypackage.state;
┌───────────────────────────────────────┐
│                 state                 │
╞═══════════════════════════════════════╡
│ (Pavel,"2018-03-20 19:12:18.29888",f) │
└───────────────────────────────────────┘
(1 row)

postgres=# select mypackage.state.name;
┌───────┐
│ name  │
╞═══════╡
│ Pavel │
└───────┘
(1 row)

5 Comments:

At March 20, 2018 at 11:51 AM , Blogger depesz said...

How fast is it? Is it comparable with GUCs?

Also, if I'd do:

select myvariable.*

will it have the same performance problem as doing things like:

select (some_function()).*

or will it behave more sanely?

 
At March 20, 2018 at 12:18 PM , Blogger Pavel Stěhule said...

@depesz Should be faster than GUC - data are stored in binary form, not in text like GUC

boo.* is not allowed is this moment -(boo).* is working as expected. It works exactly like functions - there is same transformation, but the overhead of variable reading is +/- zero (and can be reduced, if it will be requested). But in this case, the variable evaluation is just copy of some binary value - nothing is evaluated.

I am looking to code, and the expression x.* is directly interpreted like RTE.star - so it is out of my scope. The expression ().* is evaluated out of my scope too. So - variables has same behave like function, but against function, the cost of evaluation should be +/- 0 in normal cases. If somebody do some optimization with (fx()).*, then he fix variables too.

 
At March 21, 2018 at 12:00 AM , Blogger Aleš Zelený said...

Looks very promising and it might help with mitrationm from Oracle. Thanks for the proposal!

 
At March 21, 2018 at 12:30 AM , Blogger Wojciech Kolo said...

What are think about constant variables?

 
At March 21, 2018 at 12:37 AM , Blogger Pavel Stěhule said...

@Wojcieach Kolo - I have not a case for this - but the implementation is trivial - current implementation supports it is partially. For any user without UPDATE right the variable is constant.

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home