Re: proposal: session server side variables

2016-12-30 10:29 GMT+01:00 Craig Ringer <craig(at)2ndquadrant(dot)com>:

> On 30 December 2016 at 16:46, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:
> >
> >> Pavel's personal requirements include that it be well suited for
> >> static analysis of plpgsql using his plpgsql_check tool. So he wants
> >> persistent definitions.
> >
> >
> > I've been in static analysis for the last 25 years, and the logic of this
> > statement fails me.
>
> I have no opinion here, as I've not seen plpgsql_check nor do I
> understand the issues Pavel perceives with having dynamic definitions
> of variables.
>
> All I'm saying is that you two are talking around in circles by
> repeating different requirements to each other, and it's not going to
> get anywhere unless you both change your approach. It sounds like
> you're already trying to do that.
>
> > I do not think that a feature should be designed around the current
> > limitations of a particular external tool, esp. if said tool can be
> improved
> > at a reasonable cost.
>
> Not arguing there.
>
> I was initially inclined to favour Pavel's proposal because it fits a
> RLS use case I was somewhat interested in. But so would dynamic
> variables resolved at runtime so long as they were fast.
>
> Personally I don't much care what the result is, so long as it can
> satisfy some kind of reasonable security isolation, such that role A
> can set it, B can read it but not set it, and role C can do neither.
> Preferably without resorting to creating SECURITY DEFINER accessors,
> since they're messy and slow. Support for data typing would also be
> nice too.
>
> If it doesn't deliver security controls then IMO there's not much
> advantage over (ab)using GUCs with current_setting(...).
>
> Exploring the other areas discussed:
>
> Personally I think MVCC, persistent variables are a totally
> unnecessary idea that solves a problem we don't have. But maybe I
> don't understand your use cases. I expect anything like that would
> land up using a pg_catalog relation as a k/v-like store with different
> columns for different types or something of the like, which is really
> something the user can do well enough for themselves. I don't see the
> point at all.
>
> Non-MVCC persistent variables would probably be prohibitively
> expensive to make crash-safe, and seem even more pointless.
>
> Now, I can see shared variables whose state is visible across backends
> but is neither MVCC nor persistent being a fun toy, albeit not one I
> find likely to be particularly useful personally. But we can probably
> already do that in extensions, we've got most if not all of the needed
> infrastructure. Because we're a shared-nothing-by-default system, such
> variables will probably need shared memory segments that need to be
> allocated and, if new vars are added or their values grow too much,
> re-allocated. Plus locks to control access. All of which we can
> already do. Most of the uses I can think of for such things are met
> reasonably well by advisory locking already, and I expect most of the
> rest would be met by autonomous commit, so it feels a bit like a
> feature looking for a use-case.
>
> So .... lets take a step back or eight and ask "why?"
>
>
> Pavel:
>
> * Why is it so necessary for plpgsql variables to be implemented as
> persistent entities that are in the catalogs in order for you to
> achieve the static checking you want to? Is this due to limitations of
> your approach in plpgsql_check, or more fundamental issues? Explain.
>

There are few reasons:

1. plpgsql_check cannot to know a order of calls of functions. So any
dynamic created object and related operations are not checkable by
plpgsql_check (or any tool). If you create variable in one function, then
this information is not available in other function.

2. You can write some hints - like Fabien proposal - it is not vulnerable
against typo. It is much more safer to have one "created" variable, then
more "declared" variables and believe so all declarations are valid. The
created variable is only on one place - you can do simple check if
reference to variable is valid or not. With query to system catalogue, you
can get the list of all variables - you can see very simply if some
variables are redundant, obsolete, wrong.

3. The catalogue objects allow to create well granularity of access
rights. without it, you have to introduce only "PRIVATE" variables - and
then you have to GRANT rights via security definer functions. There is not
simple reply to question who can work with this variable, who has access,
... you have to check a rights to getter functions. When variables are
catalogue objects, then this reply is simple. Catalogue objects allows to
have "declared" security. Without catalogue objects we have to construct
the security. For me, a declared security is stronger.

Regards

Pavel

>
> Fabien:
>
> * What use do you have for persistent-data variables? Please set out
> some use cases where they solve problems that are currently hard to to
> solve or greatly improve on the existing solutions.can
>
> * On what basis do you _oppose_ persistently defining variables in the
> catalogs as their own entities? (My own objection is that "temporary
> variables" would make our existing catalog bloat issues for temp
> objects even worse).
>
> * Do you expect temporary/transient session variables to come into
> existence when first set, or to require some form of explicit
> definition?
>
>
> Everyone:
>
> * Does anyone care about or want variables whose value is shared
> across sessions? If so, why? Set out use cases.
>
> * Does anyone care about or want variables whose value becomes visible
> as soon as set, i.e. non-MVCC? If so, why? Set out use cases.
>
> * Does anyone care about or want variables whose value is persistent
> on-disk across restarts and/or crashes, maybe recorded in WAL for
> replication, etc? If so, justify how this is better than a relation in
> real-world practical terms.
>
>
Thank you for this summary

Pavel

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