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?
In this moment I propose only local (not shared variables). I hope so access can be safe with IMMUTABLE access function.
First time I would to implement basic set of features, that can be enhanced in future.
1. shared variables
2. variables initialized on session start, ...
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.
It should be solved by IMMUTABLE PARALLEL SAFE access functions. The content of variable should be processed in planning time, and then the access from more processes is not necessary.
Default access function should VOLATILE PARALLEL UNSAFE - but immutable sets can be defined and used (and I see a sense of these function, because with these function the variables are accessed in query planning time).
