Thread: plpgsql and logical expression evaluation
One of the annoying things in plpgsql is logical expression
evaluation.
In most (all??) languages I know, logical expression like:
if ( [A_true_expression] or [B_false_expression] ) then
will stop evaluating when the A expression will be evaluated as a
TRUE.
So the B will be not checked. In plpgsql it's different - all the
expressions are evaluated.
Why I don't like it? One of the samples is trigger procedure called
with body like this:
IF (TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND NEW.status <>
OLD.status)) THEN
-- DO SOMETHING
END IF;
It don't work for insert as the part designed for UPDATE will be
evaluated, while there is no OLD for an insert operation.
So the code looks like that:
IF (TG_OP = 'INSERT') THEN
-- DO SOMETHING
ELSIF (TG_OP = 'UPDATE' AND NEW.status <> OLD.status) THEN
-- DO THE SAME AS ABOVE
END IF;
Is there any reason for that like side effects (please give me any
example?) or it's just not yet done optimization?
On Tue, Apr 22, 2008 at 02:41:50AM -0700, wstrzalka wrote: > One of the annoying things in plpgsql is logical expression > evaluation. > > In most (all??) languages I know, logical expression like: > > if ( [A_true_expression] or [B_false_expression] ) then > > will stop evaluating when the A expression will be evaluated as a > TRUE. I guess you should complain to the SQL design comittee, SQL is a declarative language not a procedural language and the order of the expressions in AND/OR is not important to the evaluation. > It don't work for insert as the part designed for UPDATE will be > evaluated, while there is no OLD for an insert operation. > So the code looks like that: > > IF (TG_OP = 'INSERT') THEN > -- DO SOMETHING > ELSIF (TG_OP = 'UPDATE' AND NEW.status <> OLD.status) THEN > -- DO THE SAME AS ABOVE > END IF; You could use a flag variable. > Is there any reason for that like side effects (please give me any > example?) or it's just not yet done optimization? The spec explicitly says it doesn't matter and it removes the possibility of optimisation (in many queries it is important that the optimiser can execute conditions in any order it likes). Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Attachment
Martijn van Oosterhout escribió: > On Tue, Apr 22, 2008 at 02:41:50AM -0700, wstrzalka wrote: > > One of the annoying things in plpgsql is logical expression > > evaluation. > > > > In most (all??) languages I know, logical expression like: > > > > if ( [A_true_expression] or [B_false_expression] ) then > > > > will stop evaluating when the A expression will be evaluated as a > > TRUE. > > I guess you should complain to the SQL design comittee, SQL is a > declarative language not a procedural language and the order of the > expressions in AND/OR is not important to the evaluation. While I agree with that in principle, we should by now start getting used to the idea that PL/pgSQL is a different language from SQL. For example, SELECT INTO are totally different in both languages. So are BEGIN and END, and then there are loads of procedural constructs not found on SQL at all. I think this business of non-shortcircuiting boolean operators is just an artifact of the fact that PL/pgSQL hands off expression to the SQL engine for evaluation. Perhaps SQL/PSM is designed differently (I haven't checked), or PL/SQL words differently on Oracle. If either of these cases is true, we will need to attack the problem sooner or later. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> I think this business of non-shortcircuiting boolean operators is just
> an artifact of the fact that PL/pgSQL hands off expression to the SQL
> engine for evaluation.
The complainant is not actually complaining about non-shortcircuiting
boolean operators --- he thinks he is, but he's 100% mistaken. The
reason he's got a problem in the given example is that plpgsql has to
provide parameter values for every parameter in the whole expression
before it ships it off to the main engine. ExecEvalAnd actually *does*
know about short-circuiting, but it doesn't help because control never
gets that far.
Even if we rejiggered things so that supplying parameter values could be
done lazily, there's the little problem that we can't even parse the
expression without knowing the types of the parameters. The correct
analogy for what the OP tried to do is writing in C
if (x == 0 || no_such_var == 0)
and expecting the "undefined variable no_such_var" failure not to be
reported if x is zero. Since it's happening at compile time, that's
not going to happen.
regards, tom lane
On 23 Kwi, 16:32, t...@sss.pgh.pa.us (Tom Lane) wrote: > Alvaro Herrera <alvhe...@commandprompt.com> writes: > > I think this business of non-shortcircuiting boolean operators is just > > an artifact of the fact that PL/pgSQL hands off expression to the SQL > > engine for evaluation. > > The complainant is not actually complaining about non-shortcircuiting > boolean operators --- he thinks he is, but he's 100% mistaken. The > reason he's got a problem in the given example is that plpgsql has to > provide parameter values for every parameter in the whole expression > before it ships it off to the main engine. ExecEvalAnd actually *does* > know about short-circuiting, but it doesn't help because control never > gets that far. > > Even if we rejiggered things so that supplying parameter values could be > done lazily, there's the little problem that we can't even parse the > expression without knowing the types of the parameters. The correct > analogy for what the OP tried to do is writing in C > > if (x == 0 || no_such_var == 0) > > and expecting the "undefined variable no_such_var" failure not to be > reported if x is zero. Since it's happening at compile time, that's > not going to happen. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Yes. You are right. I didn't realized what my complain was about. But the 'no_such_var' example is great and explains what I was trying to do, and why it doesn't work. So - does it mean that the whole IF-ELSE-ENDIF is not parsed at once - but lazy-parsed when the control reaches it, while the IF condition is parsed as a single expression and therefore I get error in this case? Thanks a lot. regards wojtek
wstrzalka <wstrzalka@gmail.com> writes:
> So - does it mean that the whole IF-ELSE-ENDIF is not parsed at once -
> but lazy-parsed when the control reaches it, while the IF condition is
> parsed as a single expression and therefore I get error in this case?
Right, for a suitable definition of "parsed". There is some trivial
syntax checking that happens early (eg, you'll see complaints for
mismatched parentheses) but actually trying to determine the meaning
of an expression happens when control first gets there.
regards, tom lane