Upgrade instructions ->7.4.8/8.0.3 incomplete regarding tsearch2 - Mailing list pgsql-general

Hello!

I am currently trying to import a 8.0.1 database dump into a 8.0.3
installation. I, too, have at first stumbled over the tsearch2-issue
which is explained here:
http://www.postgresql.org/docs/8.0/static/release-7-4-8.html (should
make a rule to thoroughly read the upgrade notes first :) )

So I followed the instructions and executed the recommended procedure in
every database of my 8.0.1 cluster:

BEGIN;
UPDATE pg_proc SET proargtypes[0] = 'internal'::regtype
WHERE oid IN (
    'dex_init(text)'::regprocedure,
    'snb_en_init(text)'::regprocedure,
    'snb_ru_init(text)'::regprocedure,
    'spell_init(text)'::regprocedure,
    'syn_init(text)'::regprocedure
);
-- The command should report having updated 5 rows;
-- if not, rollback and investigate instead of committing!
COMMIT;

Unfortunately, this is indeed not sufficient, if one has configured
support for one or more additional languages, which are not configured
per default (i.e. anything but English and Russian, e.g. German).

In my case, I have got a function dinit_de which is declared thus:

-- Name: dinit_de(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION dinit_de(text) RETURNS internal
    AS '$libdir/dict_de', 'dinit_de'
    LANGUAGE c;

ALTER FUNCTION public.dinit_de(text) OWNER TO postgres;

So when restoring the dump from 8.0.1 to 8.0.3, I receive this error:

ERROR:  unsafe use of INTERNAL pseudo-type
DETAIL:  A function returning "internal" must have at least one
"internal" argument.

In order to be able to restore the dump, the function declaration must
be altered according to the new declaration of the other
tsearch2-functions thus:

CREATE FUNCTION dinit_de(internal) RETURNS internal
    AS '$libdir/tsearch2', 'dinit_de'
    LANGUAGE c;

So the recommended procedure for upgrading the databases in my
particular case should be

BEGIN;
UPDATE pg_proc SET proargtypes[0] = 'internal'::regtype
WHERE oid IN (
    'dex_init(text)'::regprocedure,
    'snb_en_init(text)'::regprocedure,
    'snb_ru_init(text)'::regprocedure,
    'dinit_de(text)'::regprocedure,
    'spell_init(text)'::regprocedure,
    'syn_init(text)'::regprocedure
);
-- The command should report having updated 6 rows;
-- if not, rollback and investigate instead of committing!
COMMIT;

I recommend that anyone who wishes to upgrade their tsearch2-databases
examine their function declarations for any declaration which is of the
same type (returning "internal" without having at least one "internal"
argument); this certainly applies to the function-declarations for the
initialization of any tsearch2-dictionaries, which originally expect
text as input and are supposed to return "internal". These declarations
must be included in the pre-upgrade-procedure, or else there will be
errors on restoring the dump. I don't know how this will affect the
restored database or if the issue can be somehow resolved after the dump
has already been restored without previous correction of the matter,
maybe someone else can shed some light on that. I think that it's
probably more on the safe side to assume, that it's better to have a
restore process that doesn't throw any such errors.

Kind regards

   Markus