Обсуждение: « The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »
The subject line is copied from "PL/pgSQL under the Hood" (https://www.postgresql.org/docs/current/plpgsql-implementation.html). It implies the question:
« What does the term "parse" mean? »
I couldn't find more than what I quoted. Have I missed something?
Anyway, I tried some tests. Here's an example that aims to make a reasonable compromise between brevity and its capacity to illustrate. First, I create a domain and then leave it unchanged:
create domain tt as text[];
Then I do this:
create or replace function f()
returns text
language plpgsql
as $body$
declare
n int;
arr tt := array['dog', 'cat'];
begin
n := (select count(*) from (select unnest(art)) as a);
return n::text;
end;
$body$;
\sf+ f()
select f();
returns text
language plpgsql
as $body$
declare
n int;
arr tt := array['dog', 'cat'];
begin
n := (select count(*) from (select unnest(art)) as a);
return n::text;
end;
$body$;
\sf+ f()
select f();
The "create or replace" completes without error and the "select" runs to produce the result, 2, that I expect.
If I simulate a typo by changing "n" on the LHS of the assignments to "m", then I get this error at "create or replace" time:
"m" is not a known variable
Moreover, "\sf+" shows that the former definition has remained intact—as I've come to expect.
If I fix the "n" typo and simulate a second typo by changing "tt" in the declaration of "arr" to "tz", then I get this error at "create or replace" time:
type "tz" does not exist
If I fix the "tz" typo and simulate a third typo by changing "arr" in the scalar subquery expression to "art", then "create or replace" completes without error and "\sf+" confirms that the new source is in place. Then, at "select" time, I get this error:
type "tz" does not exist
If I fix the "tz" typo and simulate a third typo by changing "arr" in the scalar subquery expression to "art", then "create or replace" completes without error and "\sf+" confirms that the new source is in place. Then, at "select" time, I get this error:
column "art" does not exist
So far, I'm tempted to think that "parse" covers everything about "regular" (i.e. not embedded SQL) PL/pgSQL statements, including syntactic analysis *and* the resolution of identifiers—both within the scope of the to-be-created subprogram and within schema scopes.
But, as it seems, embedded SQL statements receive only syntactic analysis—leaving the resolution of identifiers (even when this can be done in the scope of the to-be-created subprogram) to runtime. (I tried changing "from" to "frim" and that caused a syntax error.)
Then I dropped "f()" and extended the test, thus:
create or replace function f()
returns table(z text)
language plpgsql
as $body$
declare
v_sqlstate text not null := '';
v_message text not null := '';
n int;
arr tt := array['dog', 'cat'];
begin
z := (select count(*) from (select unnest(arr)) as a)::text; return next;
exception when others then
get stacked diagnostics
v_sqlstate = returned_sqlstate,
v_message = message_text;
z := ''; return next;
z := v_sqlstate; return next;
z := v_message; return next;
end;
$body$;
\sf+ f()
select f();
returns table(z text)
language plpgsql
as $body$
declare
v_sqlstate text not null := '';
v_message text not null := '';
n int;
arr tt := array['dog', 'cat'];
begin
z := (select count(*) from (select unnest(arr)) as a)::text; return next;
exception when others then
get stacked diagnostics
v_sqlstate = returned_sqlstate,
v_message = message_text;
z := ''; return next;
z := v_sqlstate; return next;
z := v_message; return next;
end;
$body$;
\sf+ f()
select f();
"create or replace" succeeds and "select" reports what I expect: 2. Now if I change "arr" to "art", I get the error report from my "others" handler that I expect:
42703
column "art" does not exist
column "art" does not exist
If I fix "art" back to "arr" and change "v_message" in "z := v_message; return next;" to "q_message", then "create or replace" succeeds—very much to my surprise. Moreover; "select" succeeds too—presumably because the point of execution never enters the "others" handler. Only if (with the "q_message"" typo still in place) I change "arr" to "art" again, do I get this error on "select":
column "q_message" does not exist
Is this expected? In other words, is there a careful explanation of what "parse" means in the context of "create or replace" for a subprogram that predicts all of the outcomes that I reported here?
Or might my final observation be considered to be a bug—and if so, might it be fixed?
> On Jul 28, 2022, at 18:04, Bryn Llewellyn <bryn@yugabyte.com> wrote: > Is this expected? Yes. This isn't a bug. > In other words, is there a careful explanation of what "parse" means in the context of "create or replace" for a subprogramthat predicts all of the outcomes that I reported here? Database objects (such as tables and columns) are left as identifiers until they are executed, because that is the pointat which a plan for those statements is created. The other components of PL/pgSQL are translated to internal form (andthus checked for existence) as compile time.
xof@thebuild.com wrote:bryn@yugabyte.com wrote:Is this expected?
Yes. This isn't a bug… Database objects (such as tables and columns) are left as identifiers until they are executed, because that is the point at which a plan for those statements is created. The other components of PL/pgSQL are translated to internal form (and thus checked for existence) at compile time.
My example was carefully contrived to test what you said—which is what I had earlier understood. My deliberate typo thus:
change "v_message" in "z := v_message; return next;" to "q_message"
(surely) has nothing to do with possible database objects. The context is a straight PL/pgSQL assignment statement (with no scalar subquery in sight).
It's this that surprises me. And it's this, and only this, that I'm asking about: might _just_ this be a fixable bug?
Christophe Pettus <xof@thebuild.com> writes:
>> On Jul 28, 2022, at 18:04, Bryn Llewellyn <bryn@yugabyte.com> wrote:
>> Is this expected?
> Yes. This isn't a bug.
It's actually a feature: there are cases where it's useful that we
don't do semantics checking on statements until they are reached.
A trivial example is
begin
create table foo(...);
insert into foo values(...);
end;
which would never work at all if we insisted on semantic validity
of the INSERT before the CREATE is executed.
Having said that, there are certainly aspects of what happens when
in plpgsql that don't have a lot of justification other than being
implementation artifacts. For instance, things that are certainly
plpgsql variable names (e.g. the lefthand side of an assignment)
are checked sooner than things that might not be (e.g. the righthand
side). That's defensible on a couple of grounds but it undoubtedly
leads to surprising results if you expect error conditions to be
recognized left-to-right or anything like that.
Of course, SQL commands themselves have to be analyzed in not
particularly left-to-right order, and we don't get that many
complaints about that.
regards, tom lane
> On Jul 28, 2022, at 18:49, Bryn Llewellyn <bryn@yugabyte.com> wrote:
> It's this that surprises me. And it's this, and only this, that I'm asking about: might _just_ this be a fixable bug?
It might be surprising, but it's not a bug. You can demonstrate it with a very small test case:
CREATE FUNCTION f() RETURNS VOID AS $$
DECLARE
x int not null := 0;
BEGIN
x := y;
END;
$$ language plpgsql;
But gets an error on execution:
xof=# SELECT f();
ERROR: column "y" does not exist
LINE 1: x := y
^
QUERY: x := y
CONTEXT: PL/pgSQL function f() line 5 at assignment
The clue is that it is complaining about a missing "column." Assignment in PL/pgSQL is essentially syntactic sugar
arounda SELECT ... INTO. The assignment there is processed pretty much as if it were written:
SELECT y INTO x;
Note, however, that this does *not* compile:
CREATE OR REPLACE FUNCTION f() RETURNS VOID AS $$
DECLARE
x int not null := 0;
BEGIN
y := x;
END;
$$ language plpgsql;
ERROR: "y" is not a known variable
LINE 5: y := x;
Unquestionably, this is surprising! The reasons, such as they are, are based in how PL/pgSQL processes SQL statements.
(For example, if you look at the grammar, it literally takes "SELECT x INTO y;" turns it into "SELECT x ;", and
passesthat to the SPI. This has the virtue that it doesn't have to have a complete PostgreSQL SQL grammar replicated
init (what a nightmare), but it does result in some of the implementation poking through.
xof@thebuild.com wrote:This isn't a bug.
It's actually a feature…
Having said that, there are certainly aspects of what happens when in plpgsql that don't have a lot of justification other than being implementation artifacts…
Thanks, Tom. I'll take your « aspects of… plpgsql [are simply] implementation artifacts » to mean that my hope to understand what is checked at "create or replace <my subprogram>" time and what is checked first at runtime is futile.
There does seem to be a general rule. But, as my example shows, there are exceptions to the rule. And it's impossible to make a simple user-facing statement of what determines "exceptional" status.
I suppose that the conclusion is clear: you can't be sure that a subprogram is good until every single code path (in the basic block coverage sense of this) has been tested. But, anyway, it was ever thus. (Error-free compilation never did guarantee error-free runtime outcomes.)
I'll call this "case closed" then.
pá 29. 7. 2022 v 4:57 odesílatel Bryn Llewellyn <bryn@yugabyte.com> napsal:
xof@thebuild.com wrote:This isn't a bug.
It's actually a feature…
Having said that, there are certainly aspects of what happens when in plpgsql that don't have a lot of justification other than being implementation artifacts…Thanks, Tom. I'll take your « aspects of… plpgsql [are simply] implementation artifacts » to mean that my hope to understand what is checked at "create or replace <my subprogram>" time and what is checked first at runtime is futile.There does seem to be a general rule. But, as my example shows, there are exceptions to the rule. And it's impossible to make a simple user-facing statement of what determines "exceptional" status.I suppose that the conclusion is clear: you can't be sure that a subprogram is good until every single code path (in the basic block coverage sense of this) has been tested. But, anyway, it was ever thus. (Error-free compilation never did guarantee error-free runtime outcomes.)
plpgsql_check https://github.com/okbob/plpgsql_check can help with it. It does full static (without execution) analyze
Regards
Pavel
I'll call this "case closed" then.
xof@thebuild.com wrote:bryn@yugabyte.com wrote:
It's this that surprises me. And it's this, and only this, that I'm asking about: might _just_ this be a fixable bug?
It might be surprising, but it's not a bug. You can demonstrate it with a very small test case… ["create" succeeds but it]gets an error on execution… Assignment in PL/pgSQL is essentially syntactic sugar around a SELECT ... INTO. Note, however, that this does *not* compile… The reasons, such as they are, are based in how PL/pgSQL processes SQL statements."SELECT x INTO y;"turns it into "SELECT x ;"This has the virtue that… but it does result in some of the implementation poking through.
Thanks for those two maximally terse examples, Christophe. They illustrate the same point that my larger examples aimed at. (Forgive me for not working more to distill mine down to what you showed.)
Unquestionably, this is surprising!
Well, surprise is in the eye of the beholder. I was surprised at first because I hadn't joined the dots from:
« how PL/pgSQL evaluates expressions »
to
«the moments at which the different flavors of "identifier could not be resolved" error surface:% is not a known variableorcolumn % does not exist»
But now I've changed the way that I see this—thanks to your replies and to Tom's. See my reply to Tom here:
I'll now adopt a very simple model for when "identifier could not be resolved" errors surface:
« Some surface at "create or replace" time. But many don't surface until runtime. It doesn't help me to look for a reliable specific predictive model here. »
This is what matters:
— The fact that the semantics of (embedded) SQL and expression evaluation are down to a single implementation, and are therefore identical in both top-level SQL and in PL/pgSQL, are enormous. (This stands in stark contrast to Oracle's PL/SQL where there are two implementations that bring inevitable divergences in semantics are limitations.)
— Self-evidently, runtime testing is all that ultimately matters. The more of this I do, and the sooner I do it, the better will be my outcomes.
— The practical advantages of later semantic checking that you've both pointed out are huge. For example, create a temporary table and use it *in the same block statement*.
And now (for the second time) "case closed".