Re: Fixing busted citext function declarations

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.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +