public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Stehule <[email protected]>
To: Tom Lane <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: slow SELECT expr INTO var in plpgsql
Date: Sat, 21 Mar 2026 06:10:52 +0100
Message-ID: <CAFj8pRAbfvHA7kduB=yyhyjPdQsLunLU_2_9T+xBpUvgY2go7A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAFj8pRDieSQOPDHD_svvR75875uRejS9cN87FoAC3iXMXS1saQ@mail.gmail.com>
<[email protected]>
<CAFj8pRDhF0ZwOgK=hMoALZkuyOgjtqHgR6CkVyq86ByERpUYYA@mail.gmail.com>
<CAFj8pRDDtcErg0HFtDJ-9YutnqdkofBfO45sdjc_VoNAzsU-dw@mail.gmail.com>
<[email protected]>
pá 20. 3. 2026 v 23:33 odesílatel Tom Lane <[email protected]> napsal:
> Pavel Stehule <[email protected]> writes:
> >> so 31. 1. 2026 v 21:58 odesílatel Tom Lane <[email protected]> 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
>
view thread (4+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: slow SELECT expr INTO var in plpgsql
In-Reply-To: <CAFj8pRAbfvHA7kduB=yyhyjPdQsLunLU_2_9T+xBpUvgY2go7A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox