Re: Fixing busted citext function declarations

On Thu, May 7, 2015 at 04:19:52PM -0400, Bruce Momjian wrote:
> On Tue, May 5, 2015 at 02:07:08PM -0300, Alvaro Herrera wrote:
> > Tom Lane wrote:
> >
> > > * We can't use CREATE OR REPLACE FUNCTION in the upgrade script because
> > > that intentionally doesn't let you change the result type of an existing
> > > function. I considered doing a manual UPDATE of the pg_proc entry, but
> > > then remembered why CREATE OR REPLACE FUNCTION is picky about this: the
> > > result type, including set-ness, is embedded in the parse tree of any view
> > > referencing the function. So AFAICS we need to actually drop and recreate
> > > the citext regexp_matches() functions in the upgrade script. That means
> > > "ALTER EXTENSION citext UPDATE" will fail if these functions are being
> > > used in any views. That's annoying but I see no way around it. (We
> > > could have the upgrade script do DROP CASCADE, but that seems way too
> > > destructive.)
> >
> > I think we do need to have the upgrade script drop/recreate without
> > cascade. Then, users can "alter extension upgrade", note the
> > problematic views (which should be part of the error message), drop
> > them, then retry the extension update and re-create their views. This
> > is necessarily a manual procedure -- I don't think we can re-create
> > views using the function automatically. CASCADE seems pretty dangerous.
>
> Just a reality check but this will break a pg_upgrade, and will not be
> detected by --check.

Actually, pg_upgrade might be OK because the views would be recreated
with the new functions already installed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +