Re: stored procedures vs pg_stat_statements

On Mon, Dec 23, 2024 at 11:01 PM Michael Paquier <michael(at)paquier(dot)xyz>
wrote:

> On Mon, Dec 23, 2024 at 10:06:58PM -0600, Merlin Moncure wrote:
> > I'm aware of that and will set it -- it's the only option if I'm
> following
> > you. The way I've been doing things lately for bulk processing is a lot
> > of orchestrated procedures that are organized for purposes of monitoring
> > and easy administration, and telemetry would tend to be at that level,
> but
> > more granular tracking will get the job done and ought to be perfectly
> fine
> > as long as overhead is reasonable.
> >
> > Mainly, I was curious if the behavior not to parse constants out of
> stored
> > procedure invocations was an unintentional artifact of the
> > utility statement approach. I guess it might be, but also that there is
> > nothing to solve here.
>
> Hmm. So you mean that a combination of track_utility = off and track
> = 'all' leads to the internals of CALL to not be normalized on first
> call, while if we have track_utility = on and track = 'all' then the
> internals of CALL are normalized. The behavior is different depending
> on if the procedure is cached or not, as well, the second call
> following the traces of the first call and we ignore the combinations
> of the two GUCs. This kind of inconsistency is what I would call a
> bug. I'm pretty sure that it is saner to say that we should apply
> normalization all the time if we can, not avoid normalization in some
> cases like the one you are pointing at.
>

Actually, I hadn't gotten that far yet; I was just noticing that:
CALL foo(1,2,3);
CALL foo(2,3,4);
...resolved to different queryids and if that was expected, and if not, if
some logic tune-ups in the extension improve behavior without deeper
changes.

With client side preparation, you can work around this (e.g. CALL foo($1,
$2)) but it's impossible from any non-paramaterizing client or the server
(for top level) since explicit prepared statements are not allowed for
stored procedures -- that's pretty limiting IMNSHO.

If there are issues with the non-top level approach -- that makes it
worse. For my part, I'm going to tweak the extension to see if there's any
relief there. Thanks for taking a look.

merlin