Re: proposal: session server side variables

On 14 October 2016 at 13:30, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services