On 14 October 2016 at 13:30, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hi, > > long time I working on this topic. Session server side variables are one > major missing feature in PLpgSQL. Now I hope, I can summarize requests for > implementation in Postgres:
+1 > 2. accessed with respecting access rights: > > GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role > REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role
This bit is important.
For those wondering "why the hell would you want these, just (ab)use GUCs"... this is why.
Think RLS. Especially when we eventually have session start / at login triggers, but even before then, you can initialise some expensive state once at the start of the session, transfer it from the app, or whatever. You initialise it via a SECURITY DEFINER procedure so the session user does not have the rights to write to the variable, and it can only be set via arbitration from the database security logic. From then on your RLS policies, your triggers, etc, can all simply inspect the session variable.
People use package variables in another major database with a feature called virtual private database for something similar. So this will interest anyone who wants to make porting those users easier, too. > 4. non transactional - the metadata are transactional, but the content is > not.
but only within the session, right? You're not proposing some kind of inter-backend IPC where one backend sets a session var and another backend accesses it and sees the value set by the first session?
Speaking of which: parallel query. How do you envision this working in parallel query, where the workers are different backends? Especially since things like RLS are where it'd be quite desirable.
In first stage the session variables should be marked as parallel unsafe - but in future - there can be used similar technique like shared hashjoin.
I am sending proof concept - it doesn't support access to fields of composite variables, but any other functionality is done.
Most important features:
1. the values are stored in native types
2. access to content is protected by ACL - like the content of tables
3. the content is not MVCC based - no any cost of UPDATE
4. simple API allows access to content of variables from any supported environment.