Re: proposal: session server side variables

2016-10-14 9:56 GMT+02:00 Craig Ringer <craig(at)2ndquadrant(dot)com>:

> 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?
>

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).

Regards

Pavel

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