Re: [HACKERS] proposal: session server side variables

Hello Craig,

A long mail with many questions, that I tried to answered clearly, the 
result is too long...


> [...] 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.

I understand that Pavel assumes that a static analysis tool cannot take 
into account a dynamic variable, hence is reserve.


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

Yep, that is why I have created the wiki page, so at least a argument 
should not be repeated cyclically, it should be written once.


> [...] 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.

Fitting the need of use cases is the key point, obviously.

> [...] Preferably without resorting to creating SECURITY DEFINER 
> accessors, since they're messy and slow.

I'm not sure what you mean about "messy", but if you can objectivate this 
it can be an argument. Please feel free to explain it on the wiki.

As for "slow", I have just tested overheads with pgbench, comparing a 
direct arithmetic operation (as a proxy to a fast session variable 
consultation) to constant returning plpgsql functions with security 
definer and security invoker, on a direct socket connection, with prepared 
statements:
  select 1 + 0    : 0.020 ms  select one_sd() : 0.024 ms  select one_si() : 0.024 ms

I do not think that there is a significant "slowness" issue with using 
function calls.


> Exploring the other areas discussed:
>
> Personally I think MVCC, persistent variables are a totally unnecessary 
> [...] But maybe I don't understand your use cases.

I've done a survey about the schema of projects based on databases, mysql 
or pgsql. A significant number of them use a common pattern based on a 
one-row table, essentially to hold some scalar information about the 
application version and facilitate upgrades.

However the one-row property is just hoped for, and on principle a 
database is about declaring constraints that are enforced afterwards.

I see two clean solutions to this use case: declaring tables as one row, 
or having scalar objects.


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

Yep, I'm skeptical as well. I would like to see a convincing use case.


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

Note about this question not addressed to me: currently "plpgsql_check" 
cannot analyze any session variables as no such concept exists, whether 
with or without persistent declarations.


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

It is about the often seen one-row pattern, that I think should be 
enforced either with some singleton table declaration, or scalar objects.


> * On what basis do you _oppose_ persistently defining variables in the
> catalogs as their own entities?

In understand that you are speaking of "persistent session variables".

For me a database is about persistence (metadata & data) with safety 
(transactions) and security (permissions)... and maybe performance:-)

Pavel's proposal creates a new object with 2 (secure metadata-persistence) 
out of 4 properties... I'm not a ease with introducting a new 
half-database concept in a database. I could accept it for a convincing 
use case that absolutely require that for deep reasons.

On the other hand there are dynamic session variables (mysql, mssql, 
oracle have some variants) which are useful on their own without 
pretending to be database objects (no CREATE/ALTER/DROP, GRANT/REVOKE). If 
I can make these to handle the special case and avoid a new special 
half-database concept, I would prefer it.

The key point about all that is to discuss, understand and evaluate the 
involved use cases.


> (My own objection is that "temporary variables" would make our existing 
> catalog bloat issues for temp objects even worse).

I do agree that inefficient temporary variables are worthless, but ISTM 
that Pavel's proposal is not exactly about temporary variables, it is 
about temporary-valued permanent-variables. So there is no temporary (on 
the fly) variable as such, and if it is extended for this purpose then 
indeed the catalog costs look expensive.

> * Do you expect temporary/transient session variables to come into
> existence when first set, or to require some form of explicit
> definition?

It is still an open question in the proposal I have written in the wiki. 
Opinions are welcome.

A declaration is required for typing if a variable it set, but it may not 
be necessary for consulting the variable if the default value of non 
existing variables is NULL, but then typos are not errors, although they 
could be warnings...

MySQL does on-assignment limited typing, eg "SET @foo = (...)::UNSIGNED". 
I prefer a declaration, but I could live with that kind of thing, although 
I do not like it because what happens on a subsequent "SET @foo = 
'17'::TEXT" is unclear... is the type changed or is the value cast to 
unsigned anyway? MySQL does the later.


> * Does anyone care about or want variables whose value is shared
> across sessions? If so, why? Set out use cases.

Rather add them to the wiki, please!

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

Idem, in the wiki!

(1) Having some kind of variable, especially in interactive mode, allows 
to manipulate previous results and reuse them later, without having to 
resort to repeated sub-queries or to retype non trivial values.

Client side psql :-variables are untyped and unescaped, thus not very 
convenient for this purpose.

(2) Writing a simple psql script which can be parameterized to some degree 
by setting some variables and then include the script.

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

IMO it is *not* better, but I wish that I could declare a table as a 
singleton. See the wiki.

-- 
Fabien.