Re: proposal: session server side variables

2016-12-28 4:40 GMT+01:00 Craig Ringer <craig(at)2ndquadrant(dot)com>:

> Fabien, I don't really see the point of "persistent variables". What
> benefit do they add over relations?
>
> You can add a simple function to fetch a tuple if you want it not to
> look like a subquery. Do it with heap access in C if you like, save
> the (trivial) planning costs.
>
>
> I do see value to two different things discussed here:
>
> * Pavel's proposal for persistent-declaration, non-persistent-value
> session variables with access control. These will be very beneficial
> with RLS, where we need very fast lookups. Their purpose is that you
> can set up expensive state with SECURITY DEFINER functions, C-level
> functions, etc, then test it very cheaply later from RLS and from
> other functions. Their advantage over relations is very cheap, fast
> access.
>
> I can maybe see global temporary relations being an alternative to
> these, if we optimise by using a tuplestore to back them and only
> switch to a relfilenode if the relation grows. The pg_catalog entries
> would be persistent so you could GRANT or REVOKE access to them, etc.
> Especially if we optimised the 1-row case this could work. It'd be
> less like what Oracle does, but might let us re-use more functionality
> and have fewer overlapping features. Pavel?
>

This is hard question - I have different expectation from variables and
from tables. Sure, there can be a optimization for one row global temporary
tables, but it fails on first update. And with this optimization we should
not to use current heap access functionality - or we increase a complexity
of currently lot of complex code. From success global temp tables
implementation I am expecting unbloating catalogue and all relation related
functionality - indexes, column statistics, usage statistics, MVCC. I can't
to imagine a one row optimization there. More it has limit - it is pretty
hard implement there expandable types.

I see very useful to define variable as memory based NOT ACID, not MVCC
storage (always). Tables are ACI(D) MVCC. The minimalism is great, but have
to has practical limit - we have variables inside/outside plpgsql -
although we can use temporary tables.

There are secondary question if we can introduce some better interface for
writing getter/setter function, where current relations can be used.

>
>
> * Fabien's earlier mention of transient session / query variables,
> a-la MySQL or MS-SQL. They're obviously handy for more general purpose
> programming, but our strict division between SQL and plpgsql makes
> them a bit less useful than in MS-SQL with T-SQL. I think it's a very
> separate topic to this and should be dealt with in a separate thread
> if/when someone wants to work on them.
>
> --
> Craig Ringer http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>