Pages

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:

  1. 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?

    ReplyDelete
  2. @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.

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

    ReplyDelete
  4. What are think about constant variables?

    ReplyDelete
  5. @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.

    ReplyDelete