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.


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


* 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

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.


Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to