Re: proposal: session server side variables

Hi

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

Regards

Pavel

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