Forcing a stored procedure recompile

[Mike McGavin]

Hello everyone.

Can anyone suggest if there's a way to force a plpgsql stored procedure
to be recompiled every time that it's called, based on the values of the
parameters that it's given? I assumed it would be possible, but
unfortunately haven't been able to find any documentation on how to
force a recompile at all, let alone automatically.

I've encountered a situation where the standard precompiled generic
query plan isn't working at all well with the unknown variables that the
stored procedure receives. It seems to do nicely if I replace them with
constants, though. (Compilation time isn't really an issue in this
situation, but I'd like to leave everything in the stored procedure for
other reasons.)

I could probably re-write the procedure to concatenate a string
containing the unknowns as constants and then EXECUTE it. Doing it that
way seems a bit ugly, though, and it'd make maintenance a bigger problem.
Thanks in advance for any help or suggestions.
Mike.

[Gaetano Mendola]

Mike McGavin wrote:

Hello everyone.

Can anyone suggest if there's a way to force a plpgsql stored procedure
to be recompiled every time that it's called, based on the values of the
parameters that it's given? I assumed it would be possible, but
unfortunately haven't been able to find any documentation on how to
force a recompile at all, let alone automatically.

I've encountered a situation where the standard precompiled generic
query plan isn't working at all well with the unknown variables that the
stored procedure receives. It seems to do nicely if I replace them with
constants, though. (Compilation time isn't really an issue in this
situation, but I'd like to leave everything in the stored procedure for
other reasons.)

I could probably re-write the procedure to concatenate a string
containing the unknowns as constants and then EXECUTE it. Doing it that
way seems a bit ugly, though, and it'd make maintenance a bigger problem.

Right now unfortunately this is the only one solution.

See the post on performance (8/28/2004) with the title:

ill-planned queries inside a stored procedure"


Regards
Gaetano Mendola