Tuesday, February 6, 2018

schema variables

Last year I worked on few successful projects - with Alvaro Herrera I finished XMLTABLE. I materialized my ideas about better user interface and I wrote pspg pager. Now, I am working on implementation of session variables. There are lot of concepts: MSSQL, Oracle, DB2 has own based on history, with advantages and disadvantages. I am working on schema variables, where session variables are joined to schema like tables, views, ... This concept is pretty strong. We can limit access by rights to schema, to object self. We can share variables across complete PostgreSQL environment - psql, PL languages, SQL. Maybe in next versions, the schema variables can holds constraints and triggers. I have workable prototype - now only scalar types are supported, but I hope so support of composite types will be early.
postgres=# CREATE VARIABLE foo AS numeric;
CREATE VARIABLE
postgres=# LET foo = 100;
LET 
postgres=# SELECT foo;
┌─────┐
│ foo │
╞═════╡
│ 100 │
└─────┘
(1 row)

postgres=# DO $$
postgres$# BEGIN
postgres$#   RAISE NOTICE '%', foo;
postgres$# END;
postgres$# $$;
NOTICE:  100
DO
postgres=# GRANT SELECT ON foo TO public;
GRANT
postgres=# LET foo = (SELECT count(*) FROM pg_class);
LET 
postgres=# SELECT foo;
┌─────┐
│ foo │
╞═════╡
│ 344 │
└─────┘
(1 row)

postgres=# SELECT * FROM generate_series(1,1000) g(i) WHERE i = foo;
┌─────┐
│  i  │
╞═════╡
│ 344 │
└─────┘
(1 row)

postgres=# SELECT * FROM generate_series(1,1000) g(i) WHERE i = public.foo;
┌─────┐
│  i  │
╞═════╡
│ 344 │
└─────┘
(1 row)
The schema variables are persistent database object, but the content self is temporal. The analogy can be Oracle's global temporary tables.

The goals of this project:
  • fast non transactional non persistent (session based) storage,
  • possibility to control access to stored data with PostgreSQL GRANT/REVOKE commands - schema variable can be filled by security definer function, and anywhere in session can be read, but cannot be changed,
  • possibility to share data between different PostgreSQL environments,
  • possibility to have a analogy of package variables for PLpgSQL,
  • is should not block a possibility to check PLpgSQL code by plpgsql_check.

4 Comments:

At February 8, 2018 at 5:37 AM , Blogger PL said...

Great! Any chances to see this in 11?

 
At February 8, 2018 at 5:54 AM , Blogger Pavel Stěhule said...

@PL - it is not too big probability. I have to write probably 2/3 more code - and development cycle of 11 is near to end (and lot of big patches in pipe). But merging in 12 is realistic.

 
At February 8, 2018 at 6:04 AM , Blogger PL said...

In any case we will wait it. Thanks for your work.

 
At February 10, 2018 at 8:18 AM , Blogger David said...

Awesome work! I have been missing this!

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home