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.stehule@gmail.com> wrote:
Hello
try version 8.3. There lot of dependencies are solved.
Regards
Pavel Stehule
2008/8/5 Martin Pihlak <martin.pihlak@gmail.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@postgresql.org)
> To make changes to your subscription:
>
http://www.postgresql.org/mailpref/pgsql-hackers >
--
Sent via pgsql-hackers mailing list (
pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
