From: | "Asko Oja" <ascoja(at)gmail(dot)com> |
---|---|
To: | "Martin Pihlak" <martin(dot)pihlak(at)gmail(dot)com> |
Cc: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>, "Pg Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: plan invalidation vs stored procedures |
Date: | 2008-08-05 12:59:39 |
Message-ID: | ecd779860808050559q6803feddrede8069d17e84e53@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi
Sadly PostgreSQL inability to invalidate plan cache when function is dropped
causes us downtime and costs money.
ERROR: cache lookup failed for function 24865)
This time our developers just rewrote function to use OUT parameters instead
of return type.
Currently i had to forbid dropping functions in our most critical databases
but that makes developers unhappy.
And as i understand it is not fixed in 8.3:
Comment from code
* Currently, we use only relcache invalidation events to invalidate plans.
* This means that changes such as modification of a function definition do
* not invalidate plans using the function. This is not 100% OK --- for
* example, changing a SQL function that's been inlined really ought to
* cause invalidation of the plan that it's been inlined into --- but the
* cost of tracking additional types of object seems much higher than the
* gain, so we're just ignoring them for now.
So we will have to get it fixed and better would be to do it so that
solution suits everybody.
Our current workaround include updating pg_proc after release or letting
pgBouncer to reconnect all connections but neither solution is good and
cause us to lose valuable minutes in error flood when we miss some crucial
drop function.
Asko
On Tue, Aug 5, 2008 at 1:40 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:
> Hello
>
> try version 8.3. There lot of dependencies are solved.
>
> Regards
> Pavel Stehule
>
> 2008/8/5 Martin Pihlak <martin(dot)pihlak(at)gmail(dot)com>:
> > Howdy,
> >
> > What is the status of plan invalidation vs stored procedures? From
> > the initial design discussion I understand that function change handling
> > was postponed to "some time in the future". Is anybody already working
> > on that or maybe some ideas of how to implement this?
> >
> > The business case for the feature is that most of our db logic is inside
> > stored procedures and hence use cached plans. Every time a function is
> > dropped and recreated we get a storm of "cache lookup failed" errors.
> > If we are lucky, the DBA will detect it and apply appropriate
> workarounds.
> > If not ... things get messy.
> >
> > We are considering of hacking up a proprietary solution to address our
> > specific problems (e.g. invalidate every plan on pg_proc changes). But I
> > think that this is something that would be useful to a wider audience and
> > deserves a more general solution. How about it?
> >
> > regards,
> > Martin
> >
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
> >
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2008-08-05 13:00:55 | Re: plan invalidation vs stored procedures |
Previous Message | Martin Pihlak | 2008-08-05 12:55:00 | Re: plan invalidation vs stored procedures |