Re: dubious optimization of the function in SELECT INTO target list

On 10/08/2015 01:57 AM, Oleksii Kliukin wrote:
>
>> On 06 Oct 2015, at 23:31, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
>> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>>
>> Oleksii Kliukin <alexk(at)hintbits(dot)com <mailto:alexk(at)hintbits(dot)com>> writes:
>>> This should work, but I'm interested in finding out why the original
>>> statement behaves the way I’ve described.
>>
>> plpgsql's SELECT INTO is only capable of storing a single result row,
>> so it only executes the statement far enough to obtain one row, and
>> then stops (as though a LIMIT were present). There is no guarantee
>> about how much useless computation will get done underneath.
>
> Thank you, now it’s clear. I have to say there is no guarantee that the
> computation would be useless. Someone might be calling a function that
> updates/deletes rows in the SELECT INTO block, being forced to use
> SELECT INTO by inability of pl/pgSQL to just discard the result of a
> normal SELECT. I know one can use a loop or call PERFORM, but in some
> cases (a complex CTE computing the data for the function being called at
> the end, which updates the tables with this data) actually using SELECT
> INTO looks like the easiest path to achieve the desired result.

Well the best I can come up with at the moment is:

DO $$
DECLARE l_id integer;
BEGIN
WITH gs AS (select generate_series(1,10) as id)
SELECT test(id) FROM gs ORDER BY id INTO l_id;
END;
$$ LANGUAGE plpgsql;

>
> This is essentially the same catch as with LIMIT, but LIMIT is better
> documented :-)
>
>
>>
>> If this is not the behavior you want, you shouldn't be using SELECT INTO
>> (which, I'll note, is very clearly documented as meant only for single-row
>> results).
>
> This is true, but what if I don’t care about the result and cannot use
> PERFORM?
>
> I admit it is a rather corner case, but to me it’s not clear from the
> documentation that SELECT INTO will not try to compute more rows than
> necessary. The docs say "Any result rows after the first row are
> discarded”, it’s not clear from it whether those rows are supposed to be
> evaluated before they are discarded, hence, the question that started
> this thread.
>
>
>> A plausible alternative is a FOR IN SELECT loop, which would
>> have the benefit that you could actually do something with the row values.
>
> Agree on that.
>
> Kind regards,
> --
> Oleksii
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com