On Wed, Dec 22, 2021 at 1:54 PM Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote:
Is this model not used for the PostgreSQL system?
It is not. Basically we have an "Proposed Patches" tracker and they either get committed, or they do not. Once committed, new features become available at the next annual release.
1. I see that a schema variable will be another kind of object in the class that has tables, views, sequences, indexes, UDTs, procedures, functions, and so on. So it’s persisted within a database; it’s owned by a user; and it’s localized in a schema. Does it have its own namespace too? For example, can a table x, a function x(), and a schema variable x all live in the same schema s in some database. And can I use the normal qualified name syntax to access a schema variable?
I haven't read the patch in great detail (or recently...) but: The first four items you listed (relations) share a namespace. Procedures and functions share a different namespace. I'm presuming schema variables will share the relations namespace. These namespaces are subdivisions of schemas.
2. It seems, then, that all the familiar notions, and especially the ability for a non-owner to access it apply. I see that the SELECT privilege governs who can read the value of a schema variable. But there seems to be no mechanism that governs who can change the value of a schema variable. It looks like only the owner can change the value—using the new LET SQL statement. Does this mean both that a top-level call from a client session that’s authorized as the owner can change it and a “security definer” subprogram with the same owner can change it—but that there is no explicit (say, WRITE) privilege for this. Is this what you intend? If so, why not have a WRITE privilege?
We'd probably call it INSERT and/or UPDATE privileges to avoid creating a new enumeration value.
4. You said “security definer function”. Is this what you mean? Or do you mean ANY function or procedure as long as the current user (i.e. the owner of the most tightly enclosing security definer unit) is the schema variable’s owner?
If the invoker (current user) and the function owner are the same user the specified mode is immaterial for purposes of that query. Though in the presence of views and triggers it can still get a bit confusing.
5. Could you please consider allowing a CONSTANT schema variable (with the usual syntax and requirement for an initialization _expression_)? One very popular use of a spec-level package variable is for a universal constant that has to be accessed in several places—like, for example, the conversion factor between some metric unit and some imperial unit.
Interesting. Specifically, though, constant even for a superuser and the owner? Usually constant is enforced by just not allowing people to update.
Another way to think of the question, are these immutable or stable?
3. What is the precedence scheme? For example, if a SQL statement in a PL/pgSQL unit has a restriction like this:…where col = x…and x is both the name of an in-scope variable (or formal parameter) in the unit and the name of a schema variable?
When the table has a column called x, then there’s (at least sometimes) no way round a run-time collision error except to rename one of the X’s. (Qualifying the names doesn’t fix it.)
Usually qualifying fixes it just fine - just some syntax elements presently do not allow for a qualifier to be added preventing the option from being used.
Will it be the same with schema variables? The question extends to ordinary assignment statements that become SQL statements under the covers:v := a + b;where b happens to be both an in-scope variable and a schema variable.
I'm light on specifics at the moment but this is already a solved problem and schema variables should stick with the existing convention. Which I think is telling the user there is ambiguity and to add an appropriate qualifier.
IIUC, a schema variable is slightly augmented shorthand for what today can be done by writing a scalar subquery (i.e., schema.col == (SELECT tbl.vars FROM schema.vars)) with the presumption that the table vars has exactly one row.
David J.
