My apologies, I think I still don't have enough coffee in my body.
I've made my tests right, with drop function, but used a script and
named it drop language. This made me mess up things in my head and my
last email.
No more questions about this. Thanks for your patience.
El 23/9/19 a las 10:18, Achilleas Mantzios escribió:
Dear Ekaterina,
On 23/9/19 10:45 π.μ., Ekaterina Amez wrote:
Hi Achilleas,
El 20/9/19 a las 11:41, Achilleas Mantzios escribió:
On 20/9/19 12:22 μ.μ., Ekaterina Amez wrote:
Hi Achilleas,
After restoring backup in v8.4:
newdb=# select proname,probin,pronamespace from pg_proc where
proname='plpgsql_call_handler';
proname | probin | pronamespace
----------------------+----------------------------------------+--------------
plpgsql_call_handler | $libdir/plpgsql | 11
plpgsql_call_handler | /usr/lib/postgresql/8.4/lib/plpgsql.so
| 2200
(2 filas)
As you said, 2200 is public namespace/schema and 11 is pg_catalog.
At the beginning of the backup file I can find these sentences:
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
LANGUAGE c
AS '/usr/lib/postgresql/8.4/lib/plpgsql.so',
'plpgsql_call_handler'; <-- I've changed this line to use the right
path to plpgsql.so library
CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
I guess these are the ones causing all of this. **What should be
the best way to handle this situation?** Remove these lines and
create the language explicitly when creating database?
Just drop the 2nd function (in the public schema) :
drop function public.plpgsql_call_handler ( ) ;
and see pg_upgrade goes from there.
Or replace them with a create language sentence? Maybe something
else? My final goal is migrate from 7.14 server to 8.4 server and
after that (if I have an OK from the boss) upgrade 8.4 to the
latest version that I can use. Server uses CentOS, and probably I
won't be able to upgrade to v10 but I hope at least 9.5/9.6 will be
available.
I've tested your suggestion and it went OK: restored db in 8.4, run
"drop language" sentence, stopped services and run pg_upgrade
I wrote "drop function" not language.
--check which ended up with "Clusters are compatible". GREAT!! But...
In one of my tests I forgot to change the path to plpgsql.so library,
and when restoring db it gave me an error saying it could not
Drop the wrong extra function, and then you don't have to change the
path. The correct function (in pg_catalog) will be correct.
access to the path given (of course, because that path only exists in
the server, in my machine is different). After restoring with this
error I found that:
newdb=# select fn_now();
fn_now
----------------
20190923085521
(1 fila)
newdb=# select proname,probin,pronamespace from pg_proc where
proname='plpgsql_call_handler';
proname | probin | pronamespace
----------------------+-----------------+--------------
plpgsql_call_handler | $libdir/plpgsql | 11
(1 fila)
Which means that despite the error, language has been created. So in
the end, not creating plpgsql_call_handler function and running "drop
language" both leads me to the same result. I understood this piece
of code [1] like: "create this language called plpgsql that will be
interpreted by this function called plpgsql_call_handler wich code is
in the library plpgsql.so". But with these results don't know the
meaning of this code, as one of the solutions is drop language after
creating, and the other one is create language without it's handler.
I would appreciate an explanation about this, why is this happening
or what am I misunderstanding.
[1] this piece of code:
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
LANGUAGE c
AS '/usr/lib/pgsql/plpgsql.so', 'plpgsql_call_handler';
CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
You're kinda messing up FUNCTION with LANGUAGE. Read the docs, and if
you find any questions then go ahead and ask again.
Please try to just DROP the extra public.plpgsql_call_handler (not the
language) and try again.
