Re: slow SELECT expr INTO var in plpgsql

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> so 31. 1. 2026 v 21:58 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
>>> I experimented a little bit with converting simple-expression
>>> SELECT INTO into an assignment, as attached. It does reclaim
>>> nearly all of the performance difference: for me, these two
>>> test cases now take about 276 vs 337 ms. However, I'm concerned
>>> about the side-effects of substituting this other code path;
>>> there's a lot of potential minor differences in behavior.
>>> Two that you can see in the regression test changes are:
>>>
>>> * SELECT INTO is tracked by pg_stat_statements, assignments aren't.
>>>
>>> * The context report for an error can be different, because
>>> _SPI_error_callback() doesn't get used.
>>>
>>> We could probably eliminate the context-report difference by setting
>>> up a custom error context callback in this new code path, but the
>>> difference in pg_stat_statements output would be hard to mask.
>>> There may be other discrepancies as well, such as variations in
>>> error message wording.

> Do you plan to push this patch? Unfortunately there is not any discussion
> about side effects.

Yeah, general radio silence out there. After thinking about it for
awhile, I've decided to go ahead with the patch. It'll be easy enough
to revert if people are unhappy.

> I wrote a version with dedicated error context callback,

Thanks for doing that. I found though that it wasn't quite enough,
because the existing code path applies _SPI_error_callback() during
evaluation of the expression but not during assignment to the target
variable. So for example, errors during type conversion to match
the target variable don't get a context line claiming they happened
during evaluation of the expression, which seems correct to me.
I was able to fix it by not using exec_assign_expr() but instead
copying that code in-line, so that we can pop the error context stack
at the right point. (See added tests in the committed patch,
ce8d5fe0e2802158b65699aeae1551d489948167.)

> ... so there will be
> differences only in pg_stat_statements. It is true, so this should be hard
> to mask. Maybe this difference can be just documented - like "`SELECT expr
> INTO variable` can be optimized and executed by a direct expression
> executor, and then this query will not be visible in pg_stat_statement."

We don't document that "var := expression" isn't captured, so I don't
think this needs to be either. Possibly Bruce will pick up the change
as a release-note item.

regards, tom lane