[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL
From: Pavel Stehule <pavel.stehule () gmail ! com>
Date: 2021-12-24 5:35:00
Message-ID: CAFj8pRB2JwQ5PSdqoMbPQeGYu+wQUThzmfPN=ssa2x8+eOg0gQ () mail ! gmail ! com
[Download RAW message or body]
>
> Thank you very much indeed for this careful reply, Pavel—and for the links
> to the threads on the Hackers list. A great deal is now clarified for me.
> You said "I am not native speaker, and my English is very poor". You're
> far, far, too modest. I am a native English speaker. And I often see
> writing from other native English speakers that's pretty awful. Your
> writing is a lot better than theirs. Of course, I don't know a single word
> of Czech.
>
Thank you. Gmail auto correction (ai) does a lot of good work :-)
> I'm glad to see that the non-transactional behavior is still part of the
> plan—even if, later, a transactional variant is added. I'm glad to see,
> too, that READ/WRITE privileges and IMMUTABLE are now part of the plan.
>
> Just a detail, now. You sad this:
>
> variables have their own namespace, because they have their own catalog
> table. It is designed like any catalog object - so you can use (or you
> don't need) to use a qualified identifier. But there is not any schema
> scope
>
>
> I probably misused the phrase "schema scope". I meant only to say that, if
> two schemas, s1 and s2, exist in the same database, then it's OK to have
> two session variables called s1.x and s2.x. And you confirmed that this is
> the case. It seems that you can also have a session variable called s1.x
> and a table called s1.x. In a separate reply to my email,
> david.g.johnston@gmail.com thought that this would be disallowed because
> session variables would share the same relations namespace that tables and
> views already do. (I have no opinion here.) Thanks for the clarification.
>
There are more possible collisions than like the mentioned. You can have
composite variable x with field y, and you can have schema x with table y -
and then what is x.y in a query when you don't know context? Good news is
fact, so we know context - and we know so FROM clause can contain just
table identifiers (so some collisions can be solved automatically with
context knowledge) . Fortunately, implicit FROM clause functionality was
removed a few years ago.
Inside the patch is a routine that calculates possible applications of
identifiers or quoted identifiers. When there is more than one valid
interpretation, the exception about ambiguous identifiers is raised.
Unfortunately a new badly named session variable can break working queries,
but this is not a new situation in SQL. New badly named column of table can
do this break too.
postgres=# create variable x as int default 10;
CREATE VARIABLE
postgres=# create table x (a int);
CREATE TABLE
postgres=# select * from x;
┌───┐
│ a │
╞═══╡
└───┘
(0 rows)
postgres=# select x.a from x;
┌───┐
│ a │
╞═══╡
└───┘
(0 rows)
postgres=# select x, x.a from x;
ERROR: column reference "x" is ambiguous (note - there is collision with
hidden column x of table x)
LINE 1: select x, x.a from x;
^
DETAIL: The qualified identifier can be column reference or session
variable reference
postgres=# select public.x, x.a from x;
┌───┬───┐
│ x │ a │
╞═══╪═══╡
└───┴───┘
(0 rows)
postgres=# insert into x values(1000);
INSERT 0 1
postgres=# select public.x, x.a from x;
┌────┬──────┐
│ x │ a │
╞════╪══════╡
│ 10 │ 1000 │
└────┴──────┘
(1 row)
> All is clear about collisions and name qualification now. And, yes: I
> agree with your advice to leave those settings that you mentioned at their
> shipped defaults and to go with the native behavior (collisions simply
> cause a run-time error). Then to fix such errors by ordinary spelling
> changes (esp. schema qualification) in one's code.
>
> You said "I invite any help with code and documentation review". I'm not a
> C coder. But I'd be happy to help with documentation review when the time
> comes.
>
documentation check or just check in code comments can be more than good
enough (or testing), or just voice in discussion in the mailing list.
> Finally, I'm well advanced with my promised mini-project to prepare and
> explain a realistic use-case to show the benefits of a package construct in
> PL/pgSQL. I'll wait until after Xmas before I share it with the General
> list. Having said this, I already appreciate that the chances of bringing
> packages to some future PG release are vanishingly small.
>
I am sure packages have some advantages - this is an important feature of
ADA language. The possibility of private objects is important and
interesting. Possibility to sharing code is interesting too.
But Postgres already has schemas (a little bit different from Oracle) and
extensions. And internal implementation of PL/pgSQL disallow any sharing
across databases. So introduction of packages to Postgres is introducing
some not trivial and partially redundant concept. Currently, Postgres is
relatively small and very very consistent software - and I believe so is
one of the reasons why Postgres is popular. It is easy to learn, easy to
use. The internal complexity is well solved and hidden. This is a long goal
for community Postgres. The compatibility with Oracle should not be
important after 20 years (although it is very important for a lot of
current users and for users who can leave Oracle). If we miss some feature
in Postgres, we should to implement it, but with respect to current
features.
Regards
Pavel
[Attachment #3 (text/html)]
<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div style="overflow-wrap: \
break-word;"><br><div><br></div><div>Thank you very much indeed for this careful \
reply, Pavel—and for the links to the threads on the Hackers list. A great deal is \
now clarified for me. You said "I am not native speaker, and my English is very \
poor". You're far, far, too modest. I am a native English speaker. And I often see \
writing from other native English speakers that's pretty awful. Your writing is a lot \
better than theirs. Of course, I don't know a single word of \
Czech.</div></div></blockquote><div><br></div><div>Thank you. Gmail auto correction \
(ai) does a lot of good work :-)</div><div><br></div><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div style="overflow-wrap: \
break-word;"><div><br></div><div>I'm glad to see that the non-transactional behavior \
is still part of the plan—even if, later, a transactional variant is added. I'm \
glad to see, too, that READ/WRITE privileges and IMMUTABLE are now part of the \
plan.</div><div><br></div><div>Just a detail, now. You sad \
this:</div><div><br></div><div><blockquote type="cite">variables have their own \
namespace, because they have their own catalog table. It is designed like any catalog \
object - so you can use (or you don't need) to use a qualified identifier. But \
there is not any schema scope </blockquote></div><div><br></div><div>I probably \
misused the phrase "schema scope". I meant only to say that, if two schemas, s1 and \
s2, exist in the same database, then it's OK to have two session variables called \
s1.x and s2.x. And you confirmed that this is the case. It seems that you can also \
have a session variable called s1.x and a table called s1.x. In a separate reply to \
my email, <a href="mailto:david.g.johnston@gmail.com" \
target="_blank">david.g.johnston@gmail.com</a> thought that this would be disallowed \
because session variables would share the same relations namespace that tables and \
views already do. (I have no opinion here.) Thanks for the \
clarification.</div></div></blockquote><div><br></div><div>There are more possible \
collisions than like the mentioned. You can have composite variable x with field y, \
and you can have schema x with table y - and then what is x.y in a query when you \
don't know context? Good news is fact, so we know context - and we know so FROM \
clause can contain just table identifiers (so some collisions can be solved \
automatically with context knowledge) . Fortunately, implicit FROM clause \
functionality was removed a few years ago.<br></div><div><br></div><div>Inside the \
patch is a routine that calculates possible applications of identifiers or quoted \
identifiers. When there is more than one valid interpretation, the exception about \
ambiguous identifiers is raised.</div><div><br></div><div>Unfortunately a new badly \
named session variable can break working queries, but this is not a new situation in \
SQL. New badly named column of table can do this break \
too.</div><div><br></div><div><span style="font-family:monospace">postgres=# create \
variable x as int default 10;<br>CREATE VARIABLE<br>postgres=# create table x (a \
int);<br>CREATE TABLE<br>postgres=# select * from x;<br>┌───┐<br>│ a \
│<br>╞═══╡<br>└───┘<br>(0 rows)</span></div><div><span \
style="font-family:monospace"><br></span></div><div><span \
style="font-family:monospace">postgres=# select x.a from x;<br>┌───┐<br>│ \
a │<br>╞═══╡<br>└───┘<br>(0 rows)</span></div><div><span \
style="font-family:monospace"><br></span></div><div><span \
style="font-family:monospace">postgres=# select x, x.a from x;<br>ERROR: column \
reference "x" is ambiguous (note - there is collision with hidden column x \
of table x)<br>LINE 1: select x, x.a from x;<br> ^<br>DETAIL: \
The qualified identifier can be column reference or session variable \
reference<br>postgres=# select public.x, x.a from \
x;<br>┌───┬───┐<br>│ x │ a \
│<br>╞═══╪═══╡<br>└───┴───┘<br>(0 \
rows)<br><br>postgres=# insert into x values(1000);<br>INSERT 0 1<br>postgres=# \
select public.x, x.a from x;<br>┌────┬──────┐<br>│ x \
│ a │<br>╞════╪══════╡<br>│ 10 │ 1000 \
│<br>└────┴──────┘<br>(1 \
row)</span></div><div><br></div><div><br></div><div><br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div style="overflow-wrap: \
break-word;"><div><br></div><div>All is clear about collisions and name qualification \
now. And, yes: I agree with your advice to leave those settings that you mentioned at \
their shipped defaults and to go with the native behavior (collisions simply cause a \
run-time error). Then to fix such errors by ordinary spelling changes (esp. schema \
qualification) in one's code.</div><div><br></div><div>You said "I invite any help \
with code and documentation review". I'm not a C coder. But I'd be happy to help with \
documentation review when the time \
comes.</div></div></blockquote><div><br></div><div>documentation check or just check \
in code comments can be more than good enough (or testing), or just voice in \
discussion in the mailing list.<br></div><div> <br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div style="overflow-wrap: \
break-word;"><div><br></div><div>Finally, I'm well advanced with my promised \
mini-project to prepare and explain a realistic use-case to show the benefits of a \
package construct in PL/pgSQL. I'll wait until after Xmas before I share it with the \
General list. Having said this, I already appreciate that the chances of bringing \
packages to some future PG release are vanishingly \
small.</div></div></blockquote><div><br></div><div>I am sure packages have some \
advantages - this is an important feature of ADA language. The possibility of private \
objects is important and interesting. Possibility to sharing code is interesting \
too.</div><div><br></div><div>But Postgres already has schemas (a little bit \
different from Oracle) and extensions. And internal implementation of PL/pgSQL \
disallow any sharing across databases. So introduction of packages to Postgres is \
introducing some not trivial and partially redundant concept. Currently, Postgres is \
relatively small and very very consistent software - and I believe so is one of the \
reasons why Postgres is popular. It is easy to learn, easy to use. The internal \
complexity is well solved and hidden. This is a long goal for community Postgres. The \
compatibility with Oracle should not be important after 20 years (although it is very \
important for a lot of current users and for users who can leave Oracle). If we miss \
some feature in Postgres, we should to implement it, but with respect to current \
features.</div><div><br></div><div>Regards</div><div><br></div><div>Pavel<br></div><div><br></div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic