Re: BUG #13798: Unexpected multiple exection of user defined function with out parameters

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Kevin Grittner <kgrittn(at)gmail(dot)com>
Cc: mike(dot)lang1010(at)gmail(dot)com, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #13798: Unexpected multiple exection of user defined function with out parameters
Date: 2015-12-08 16:24:39
Message-ID: CAKFQuwYYef+kCX_ixs+gsKMmLNSC18qZso9hjLa5N9f4qSYw7g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Tue, Dec 8, 2015 at 9:08 AM, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:

> On Sat, Dec 5, 2015 at 12:30 AM, <mike(dot)lang1010(at)gmail(dot)com> wrote:
>
> > I've found that when a user defined function has
> > out parameters, it is invoked once per out parameter if invoked with the
> > syntax:
> >
> > `SELECT (udf()).*`
> >
> > Is this the expected behavior? It seems like it shouldn't.
>
> You can see why this happens if you use EXPLAIN this way:
>

​You can see "what" happens. The why is still a mystery...

> test=# explain (analyze, buffers, verbose) SELECT (reproduceBehavior()).*;
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------------------------------------
> Result (cost=0.00..1.01 rows=1 width=0) (actual time=1.931..1.932
> rows=1 loops=1)
> Output: (reproducebehavior()).message1,
> (reproducebehavior()).message2, (reproducebehavior()).message3,
> (reproducebehavior()).message4
> Buffers: shared hit=17
> Planning time: 0.038 ms
> Execution time: 1.968 ms
> (5 rows)
>
> That shows that the * causes expansion to the following query:
>
> SELECT
> (reproducebehavior()).message1,
> (reproducebehavior()).message2,
> (reproducebehavior()).message3,
> (reproducebehavior()).message4;
>
> From that you can see why it is not surprising that the function is
> executed once per OUT parameter, especially if it is VOLATILE.
>
>
​While you've explained how to see what is happening it doesn't remove the
POLA violation that has occurred here.​

You seem to be expecting it to behave like this:
>
> SELECT * FROM (SELECT * FROM reproduceBehavior()) x;
>
> Which is interpreted as:
>
> SELECT x.message1, x.message2, x.message3, x.message4
> FROM (SELECT * FROM reproduceBehavior()) x;
>
> To avoid surprises, avoid using *
>

​OK - but how is one supposed to do that? There is no good way to explode
a composite type, especially one created using a function, without using *.

I've responded to the original thread with two possible alternative query
forms. The CTE one is a hack while the implementation of LATERAL finally
provided a non-hackey means to accomplish the goal. The behavior of
(SELECT (func_call()).*) will likely never change but I'd still argue that
not repeatedly invoking the function would be the better implementation and
the least astonishing one.

David J.

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2015-12-08 16:25:21 Re: BUG #13803: too many clients exception
Previous Message David G. Johnston 2015-12-08 16:16:04 Re: BUG #13798: Unexpected multiple exection of user defined function with out parameters