Re: slow SELECT expr INTO var in plpgsql

pá 20. 3. 2026 v 23:33 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> 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.
>

Thank you very much

Regards

Pavel

>
> regards, tom lane
>