Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL - Mailing list pgsql-general
| From | Pavel Stehule |
|---|---|
| Subject | Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL |
| Date | |
| Msg-id | CAFj8pRB2JwQ5PSdqoMbPQeGYu+wQUThzmfPN=ssa2x8+eOg0gQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL (Bryn Llewellyn <bryn@yugabyte.com>) |
| Responses |
Re: Packages and inner subprograms for PL/pgSQL
(Bryn Llewellyn <bryn@yugabyte.com>)
|
| List | pgsql-general |
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 scopeI 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)
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)
┌───┐
│ 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)
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
pgsql-general by date: