From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
Cc: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL |
Date: | 2021-12-22 21:24:34 |
Message-ID: | CAKFQuwYSzQJ1_2C3rjyLf+xxvy2_9JPN3pTRw-4bX5XXfpFnzw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Dec 22, 2021 at 1:54 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> 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?
>
https://www.postgresql.org/docs/current/sql-createfunction.html
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2021-12-22 21:38:27 | Re: When Update balloons memory |
Previous Message | Theodore M Rolle, Jr. | 2021-12-22 21:15:32 | Tab-completion error...? |