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

[Oleksii Kliukin <alexk@xxxxxxxxxxxx>]

On 06 Oct 2015, at 23:31, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Oleksii Kliukin <alexk@xxxxxxxxxxxx> 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.

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