Re: Patch: plan invalidation vs stored procedures

On Tue, 2008-08-19 at 21:26 +0200, Dimitri Fontaine wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi,
>
> Le 19 août 08 à 19:06, Tom Lane a écrit :
> > Dimitri Fontaine <dfontaine(at)hi-media(dot)com> writes:
> >> Another thing I do not understand well is how people are expected
> >> to work in
> >> 8.3 with a function based API, without hitting Skype problems.
> >
> > What we've got at this point is a submitted patch for a new feature
> > that hasn't even been accepted into HEAD yet. Lobbying to get it
> > back-patched is entirely inappropriate IMHO.
>
> Well, there's a misunderstanding here. I certainly were lobbying for
> considering a backpatch as I saw it as a bugfix. You told me it's a
> new feature, I say ok for not backpatching, obviously.
>
> This mail was a real attempt at learning some tips to be able to push
> the functions usage as far as Skype is doing, in 8.3 release, and
> avoiding the trap which has always existed in released PostgreSQL
> version. This certainly was a bad attempt at it.
>
> Now, my understanding is that rolling out new versions of functions
> requires forcing dropping all current opened sessions as soon as
> PostgreSQL considers you need to drop any function. I'll think about
> it in next project design meetings.

I think that another option is to manipulate pg_proc - just do a no-op
update to advance xmin for all functions that may have cached plans.

UPDATE pg_proc SET proname = proname;

then make sure that pg_proc is vacuumed often enough.

It's a bit wasteful, as it forces re-planning of all functions, but
should have similar effect than the patch.

It's also possible that updating pg_proc in bulk introduces some race
conditions which lock up the database.

------------------
Hannu