Re: [HACKERS] proposal: session server side variables - Mailing list pgsql-hackers

On Thu, Jan 5, 2017 at 5:39 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
> Half-persistence (in definition, not in value) is not a key feature needed
> by the use-case.

Well, you don't get to decide that.  You've been told by at least
three or four people that they don't want variables to be
transactional, you've been pointed to documentation links showing that
in other database systems including Oracle variables are not
transactional, and you still insist that this proposal is senseless
unless variables are transactional.  You have every right to decide
what you think is useful, but you don't have a right to decide what
other people think is useful.  You don't get veto power over what
Pavel wants to implement, even if you personally would not choose to
implement it that way, and especially not when multiple people are
agreeing with Pavel and disagreeing with you.

On the substance of this issue, I would note that Pavel's idea of
entering variables in pg_class has a number of advantages:

1. The code can control permissions using the same system that we use
to control permissions on all other objects, instead of having to
create a completely new one.  Similarly for dependencies.  This
wouldn't matter if it were possible to get by with no system for
privileges on variables or with a very simple system such as you
proposed upthread, but I think that's somewhat unrealistic in the face
of security-definer functions and row-level security.  Execution in
multiple privilege contexts within the same session is a fact of life,
and whatever design gets chosen has to somehow cope with that; using
the existing infrastructure is one reasonable choice.  Trying to do
something excessively simple here will result in security bugs.

2. The user doesn't need to re-declare the variables you want to use
at the beginning of every session.  This is also the reason why many
people want global temporary tables.  They don't do anything that
can't be done with local temporary tables; they're just more
convenient to use.

3. If somebody goes to drop a type, they'll have to use CASCADE to get
rid of the dependent variable.  That might tip them off to conduct an
investigation into whether that variables is being used.  If the
variables are established entirely on a per-session basis, there will
be no such warning at DROP time.  You'll only find out about the
problem when the application starts failing.

4. As Pavel already said, it makes things easier for a static checker.
I'm not certain that anyone, including you, has correctly understood
Pavel's point here, which is perhaps because Pavel's first language is
not English.  But I'm pretty sure I understand it, so let me try to
restate it more straightforwardly.  Suppose a static checker
encounters the statement SET VARIABLE flumpf = 1 (assume for purposes
of this bullet point that this is the syntax for setting a variable).
If variables have to be catalogued, and flumpf is not in the catalog,
this is a probably a bug.  If flumpf is in the catalog but is of a
type that cannot contain the value 1, this is also probably a bug.
But if variables do not have to be catalogued, then the static checker
will have a hard time inferring anything about the correctness of this
statement.  It is not impossible; for example, the static checker
could have a requirement that the user running it set up the session
with all required variables before running the checker.  But that is
not particularly convenient.  IIUC, Pavel's point is that a user who
creates a function which uses a certain variable would probably also
put code in that same function to create the variable if it is not yet
present.  And a static checker probably can't see through that.
You've argued that there is no problem here, but it seems absolutely
unarguable to me that static checkers benefit from having more things
statically configured and less stuff figured out at runtime.  That's
why it's easier to statically check programming languages with
strongly typed variables than it is to statically check languages with
run-time typing.

Now, there are certainly advantages to NOT entering variables in
pg_class, too.  For example, it makes the operation much more
light-weight.  If the code uses basically the same variables over and
over again, entering them in the catalog doesn't cost anything
meaningful.  If it's constantly creating new variables, that's going
to create catalog bloat if those variables have to be added to the
catalogs, and that's going to stink.  I don't mean to pass judgement
here in favor of Pavel's proposal and against other proposals.  But it
seems clear to me that Pavel's proposal does have certain fairly
compelling advantages and a good deal of community support; and your
replies don't seem to be acknowledging any of that.  I think they
should.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company