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:
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?
@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.
Looks very promising and it might help with mitrationm from Oracle. Thanks for the proposal!
What are think about constant variables?
@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