Re: Patch: plan invalidation vs stored procedures

> you have functions
>
> A) caller1() to callerN() which includes call to called1()
>
> B) one of these functions, say callerM() needs one more field returned
> from called1(), so you either write a completely new function
> called1_v2() with one more field and then update callerM() to call
> called1_v2()
>
> C) now, to get rid of called1() you have to replace called1 with
> called1_v2 also in all other functions caller1() to callerN()
>
> D) then you can drop called1()

True. I complained about this same problem in the context of views -
you can add a column to a table in place but not to a view, or even a
type created via CREATE TYPE. I even went so far as to develop a
patch[1] to improve the situation, which to my sadness was not met
with wild enthusiasm.

[1] http://archives.postgresql.org/pgsql-hackers/2008-08/msg00272.php

Does it help to do CREATE OR REPLACE FUNCTION on callerX() after
dropping and recreating called1()? If so, you might want to just
recreate all of your functions every time you do a release. I wrote a
perl script that does this and it's worked pretty well for me.
Besides possibly avoiding this problem, it means that I don't really
need to worry about which functions I've modified in this release
quite as much, since I'm just going to push out the most-current
definition for all of them.

> Nope, but the amount of release engineering (and deployment-time work)
> you need to do depends a lot on fragility of the system.

Also true, but I think comparing plan invalidation to transactional
semantics is quite unfair. There's basically no amount of user code
which will compensate for the lack of an ACID-compliant database. On
the other hand, working around the lack of plan invalidation (or the
inability to add columns to views without recreating them) just
requires being careful to catch all of the stray references in your
DDL and testing thoroughly before you roll out to production, which
are good things to do anyway. That's not to say that we shouldn't
have plan invalidation, just that I don't think it's anywhere close to
the same level of broken.

...Robert