Thread: BUG #1644: control structeres perfomance in pgsql

The following bug has been logged online:

Bug reference:      1644
Logged by:          tirny
Email address:      tirny@mail.ru
PostgreSQL version: 7.4.7
Operating system:   FreeBSD 5.3
Description:        control structeres perfomance in pgsql
Details:

for example, look at this code:

any code instructions
***
IF FOUND AND TG_OP = 'UPDATE' THEN
***
END IF;
***

if not found plsql does't break this structure after IF FOUND and proceed
TG_OP = \'UPDATE\', what bring control structures to:
IF FOUND THEN
  IF TG_OP = 'UPDATE' THEN
   ***
  END IF;
END IF;

inconveniently, isn't it?

tirny wrote:
> any code instructions
> ***
> IF FOUND AND TG_OP = 'UPDATE' THEN
> ***
> END IF;
> ***
>
> if not found plsql does't break this structure after IF FOUND and proceed
> TG_OP = \'UPDATE\', what bring control structures to:
> IF FOUND THEN
>   IF TG_OP = 'UPDATE' THEN
>    ***
>   END IF;
> END IF;
>
> inconveniently, isn't it?

I don't understand. Can you explain what the problem is?

-Neil

On Wed, 04 May 2005 11:52:32 +1000, Neil Conway <neilc@samurai.com> wrote:
> tirny wrote:
> > any code instructions
> > ***
> > IF FOUND AND TG_OP = 'UPDATE' THEN
> > ***
> > END IF;
> > ***
> >
> > if not found plsql does't break this structure after IF FOUND and proceed
> > TG_OP = \'UPDATE\', what bring control structures to:
> > IF FOUND THEN
> >   IF TG_OP = 'UPDATE' THEN
> >    ***
> >   END IF;
> > END IF;
> >
> > inconveniently, isn't it?
>
> I don't understand. Can you explain what the problem is?

I think they mean that it doesn't do short-circuit evaluation.

[to tirny]
If so, see explanation in the documentation at the bottom of
http://www.postgresql.org/docs/8.0/static/sql-expressions.html

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

Klint Gore <kg@kgb.une.edu.au> writes:
> On Wed, 04 May 2005 11:52:32 +1000, Neil Conway <neilc@samurai.com> wrote:
>> I don't understand. Can you explain what the problem is?

> I think they mean that it doesn't do short-circuit evaluation.

AFAICS the claimed example cannot exhibit any different behavior whether
it's short-circuit or not.  I have seen some related issues though, like

    IF tg_op = 'UPDATE' AND NEW.foo = 42 THEN ...

In a trigger that is also used for DELETE operations this will fail
--- not because the AND itself isn't short-circuit, but because plpgsql
has to pass down the parameters for the whole IF-expression before the
AND gets to run at all, and so it's forced to evaluate NEW.* which
fails in a DELETE trigger.

We could fix some variants of this problem by modifying plpgsql to
handle top-level AND and OR by itself (ie, split the above into two
separate SQL-expression evaluations with the AND being processed by
plpgsql itself).  However the added overhead of multiple executor
calls would be large, and it still wouldn't fix every such case.
Given that we don't promise AND/OR to always behave in short-circuit
fashion anyway, my reaction to the bug report is basically "tough beans,
this is SQL not C" ...

            regards, tom lane