BUG #16843: pg_upgrade from 12.5 to 13.1 with extension plperlu failed
The following bug has been logged on the website:
Bug reference: 16843
Logged by: Thomas Steffen
Email address: linreg@gmx.net
PostgreSQL version: 13.1
Operating system: OpenSuse 15.2
Description:
Hello
In the upgrade process with pg_upgrade ( as user postgres) following error
occured:
Translated from German!
---- SNIPPET ----
pg_restore: create EXTENSION »plperlu«
pg_restore: create COMMENT »EXTENSION "plperlu"«
pg_restore: create PROCEDURAL LANGUAGE »plperlu«
pg_restore: in Phase PROCESSING TOC:
pg_restore: in Table of contents 2151; 2612 16427 PROCEDURAL LANGUAGE
plperlu postgres
pg_restore: Error: could not execute query: ERROR: Language »plperlu« does
not exist
The statement was: CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";
-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "plperlu" ADD LANGUAGE "plperlu";
----- SNIPPET END -----
The last command can not be executed succesfully. ==> ALTER EXTENSION
"plperlu" ADD LANGUAGE "plperlu";
possible cause:
This command "CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";"
does not create a LANGUAGE object. It creates only the extension object
plperlu!
This means the language cannot be added to this extension.
i think the workaround with
CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";
ALTER EXTENSION "plperlu" ADD LANGUAGE "plperlu";
for
CREATE EXTENSION "plperlu";
is not necessary.
best regards
PG Bug reporting form <noreply@postgresql.org> writes:
In the upgrade process with pg_upgrade ( as user postgres) following error
occured:
pg_restore: Error: could not execute query: ERROR: Language »plperlu« does
not exist
The statement was: CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";
It looks like you neglected to install the plperlu package
(it's probably named something like postgresql-plperl).
regards, tom lane
On Thu, Jan 28, 2021 at 10:34:46AM -0500, Tom Lane wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
In the upgrade process with pg_upgrade ( as user postgres) following error
occured:pg_restore: Error: could not execute query: ERROR: Language �plperlu� does
not exist
The statement was: CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";It looks like you neglected to install the plperlu package
(it's probably named something like postgresql-plperl).
Yeah, I am confused why this was not reported clearly to the user.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Bruce Momjian <bruce@momjian.us> writes:
On Thu, Jan 28, 2021 at 10:34:46AM -0500, Tom Lane wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
pg_restore: Error: could not execute query: ERROR: Language »plperlu« does
not exist
The statement was: CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";
It looks like you neglected to install the plperlu package
(it's probably named something like postgresql-plperl).
Yeah, I am confused why this was not reported clearly to the user.
It is odd that pg_upgrade's checks didn't catch it earlier. But
we can't investigate without more info about exactly what Postgres
packages are present on the OP's system.
regards, tom lane
On Thu, Jan 28, 2021 at 10:43:45AM -0500, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
On Thu, Jan 28, 2021 at 10:34:46AM -0500, Tom Lane wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
pg_restore: Error: could not execute query: ERROR: Language �plperlu� does
not exist
The statement was: CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";It looks like you neglected to install the plperlu package
(it's probably named something like postgresql-plperl).Yeah, I am confused why this was not reported clearly to the user.
It is odd that pg_upgrade's checks didn't catch it earlier. But
we can't investigate without more info about exactly what Postgres
packages are present on the OP's system.
Yes, usually the error report is very clear, we would need to see
_exactly_ what they saw.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Am Donnerstag, 28. Januar 2021, 16:56:44 CET schrieben Sie:
On Thu, Jan 28, 2021 at 10:43:45AM -0500, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
On Thu, Jan 28, 2021 at 10:34:46AM -0500, Tom Lane wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
pg_restore: Error: could not execute query: ERROR: Language »plperlu«
does
not exist
The statement was: CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";It looks like you neglected to install the plperlu package
(it's probably named something like postgresql-plperl).Yeah, I am confused why this was not reported clearly to the user.
It is odd that pg_upgrade's checks didn't catch it earlier. But
we can't investigate without more info about exactly what Postgres
packages are present on the OP's system.Yes, usually the error report is very clear, we would need to see
_exactly_ what they saw.
Hello,
to clearify,
- plperl package is already installed (see below).
- command 'CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";' doesn't create a
language object, but just an extension object (see below)
Please note the attached upgrade log file from pg_upgrade "pg_upgrade_dump_16401.log".
OS Packages:
*postgresql*-llvmjit-13-lp152.3.1.noarch
*postgresql*12-pldebugger-1.0+git13.ddbce7b-lp152.1.4.x86_64
*postgresql*12-llvmjit-12.5-lp152.34.1.x86_64
*postgresql*-13-lp152.3.1.noarch
*postgresql*12-docs-12.5-lp152.34.1.noarch
*postgresql*12-contrib-12.5-lp152.34.1.x86_64
*postgresql*13-plperl-13.1-lp152.16.1.x86_64
*postgresql*12-server-12.5-lp152.34.1.x86_64
*postgresql*-docs-13-lp152.3.1.noarch
*postgresql*12-12.5-lp152.34.1.x86_64
*postgresql*12-pg_qualstats-2.0.2-lp152.2.3.x86_64
*postgresql*12-ip4r-2.4.1+git1.5f9ce88-lp152.8.2.x86_64
*postgresql*-server-13-lp152.3.1.noarch
*postgresql*12-pg_qualstats-llvmjit-2.0.2-lp152.2.3.x86_64
*postgresql*13-docs-13.1-lp152.16.1.noarch
*postgresql*13-server-13.1-lp152.16.1.x86_64
*postgresql*-contrib-13-lp152.3.1.noarch
*postgresql*12-ip4r-llvmjit-2.4.1+git1.5f9ce88-lp152.8.2.x86_64
*postgresql*13-13.1-lp152.16.1.x86_64
*postgresql*13-llvmjit-13.1-lp152.16.1.x86_64
*postgresql*-plperl-13-lp152.3.1.noarch
*postgresql*12-pldebugger-llvmjit-1.0+git13.ddbce7b-lp152.1.4.x86_64
*postgresql*13-contrib-13.1-lp152.16.1.x86_64
Output from failed database upgrade:
botdb=# \dL
Attachments:
pg_upgrade_dump_16401.logtext/x-log; charset=UTF-8; name=pg_upgrade_dump_16401.logDownload
Am Donnerstag, 28. Januar 2021, 16:56:44 CET schrieben Sie:
On Thu, Jan 28, 2021 at 10:43:45AM -0500, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
On Thu, Jan 28, 2021 at 10:34:46AM -0500, Tom Lane wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
pg_restore: Error: could not execute query: ERROR: Language »plperlu«
does
not exist
The statement was: CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";It looks like you neglected to install the plperlu package
(it's probably named something like postgresql-plperl).Yeah, I am confused why this was not reported clearly to the user.
It is odd that pg_upgrade's checks didn't catch it earlier. But
we can't investigate without more info about exactly what Postgres
packages are present on the OP's system.Yes, usually the error report is very clear, we would need to see
_exactly_ what they saw.
Hello,
to clearify,
- plperl package is already installed (see below).
- command 'CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";' doesn't create a
language object, but just an extension object (see below)
Please note the attached upgrade log file from pg_upgrade "pg_upgrade_dump_16401.log".
OS Packages:
*postgresql*-llvmjit-13-lp152.3.1.noarch
*postgresql*12-pldebugger-1.0+git13.ddbce7b-lp152.1.4.x86_64
*postgresql*12-llvmjit-12.5-lp152.34.1.x86_64
*postgresql*-13-lp152.3.1.noarch
*postgresql*12-docs-12.5-lp152.34.1.noarch
*postgresql*12-contrib-12.5-lp152.34.1.x86_64
*postgresql*13-plperl-13.1-lp152.16.1.x86_64
*postgresql*12-server-12.5-lp152.34.1.x86_64
*postgresql*-docs-13-lp152.3.1.noarch
*postgresql*12-12.5-lp152.34.1.x86_64
*postgresql*12-pg_qualstats-2.0.2-lp152.2.3.x86_64
*postgresql*12-ip4r-2.4.1+git1.5f9ce88-lp152.8.2.x86_64
*postgresql*-server-13-lp152.3.1.noarch
*postgresql*12-pg_qualstats-llvmjit-2.0.2-lp152.2.3.x86_64
*postgresql*13-docs-13.1-lp152.16.1.noarch
*postgresql*13-server-13.1-lp152.16.1.x86_64
*postgresql*-contrib-13-lp152.3.1.noarch
*postgresql*12-ip4r-llvmjit-2.4.1+git1.5f9ce88-lp152.8.2.x86_64 *postgresql*13-13.1-
lp152.16.1.x86_64
*postgresql*13-llvmjit-13.1-lp152.16.1.x86_64
*postgresql*-plperl-13-lp152.3.1.noarch
*postgresql*12-pldebugger-llvmjit-1.0+git13.ddbce7b-lp152.1.4.x86_64 *postgresql*13-
contrib-13.1-lp152.16.1.x86_64
Output from failed database upgrade:
botdb=# \dL
Attachments:
pg_upgrade_dump_16401.logtext/x-log; charset=utf-8; name=pg_upgrade_dump_16401.logDownload
Hello, (third trial)
to clearify,
- plperl package is already installed (see below).
- command 'CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";' doesn't create a
language object, but just an extension object (see below)
Output from failed database upgrade:
botdb=# \dL
Liste der Sprachen
Name | Eigentümer | Vertraut | Beschreibung
---------+------------+----------+------------------------------
plpgsql | postgres | t | PL/pgSQL procedural language
(1 Zeile)
botdb=# \dx
Liste der installierten Erweiterungen
Name | Version | Schema | Beschreibung
--------------+---------+------------
+--------------------------------------------------
hstore | 1.5 | pg_catalog | data type for storing sets of (key,
value) pairs
pg_cron | 4.0.4 | public | Cron-like Job Scheduler for PostgreSQL
pg_http | 1.4.1 | public | A web service for PostgreSQL
pg_mqtt | 1.1 | public | A mqtt client daemon for PostgreSQL
pg_reporting | 1.1 | public | A reporting extension for PostgreSQL
plperlu | 1.0 | pg_catalog | PL/PerlU untrusted procedural language
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural languagebest regards
Thomas Steffen
Attachments:
pg_upgrade_dump_16401.logtext/x-log; charset=utf-8; name=pg_upgrade_dump_16401.logDownload
GMX Steffen <steffen.ang@gmx.de> writes:
OS Packages:
*postgresql*-llvmjit-13-lp152.3.1.noarch
*postgresql*12-pldebugger-1.0+git13.ddbce7b-lp152.1.4.x86_64
*postgresql*12-llvmjit-12.5-lp152.34.1.x86_64
*postgresql*-13-lp152.3.1.noarch
*postgresql*12-docs-12.5-lp152.34.1.noarch
*postgresql*12-contrib-12.5-lp152.34.1.x86_64
*postgresql*13-plperl-13.1-lp152.16.1.x86_64
*postgresql*12-server-12.5-lp152.34.1.x86_64
*postgresql*-docs-13-lp152.3.1.noarch
*postgresql*12-12.5-lp152.34.1.x86_64
*postgresql*12-pg_qualstats-2.0.2-lp152.2.3.x86_64
*postgresql*12-ip4r-2.4.1+git1.5f9ce88-lp152.8.2.x86_64
*postgresql*-server-13-lp152.3.1.noarch
*postgresql*12-pg_qualstats-llvmjit-2.0.2-lp152.2.3.x86_64
*postgresql*13-docs-13.1-lp152.16.1.noarch
*postgresql*13-server-13.1-lp152.16.1.x86_64
*postgresql*-contrib-13-lp152.3.1.noarch
*postgresql*12-ip4r-llvmjit-2.4.1+git1.5f9ce88-lp152.8.2.x86_64
*postgresql*13-13.1-lp152.16.1.x86_64
*postgresql*13-llvmjit-13.1-lp152.16.1.x86_64
*postgresql*-plperl-13-lp152.3.1.noarch
*postgresql*12-pldebugger-llvmjit-1.0+git13.ddbce7b-lp152.1.4.x86_64
*postgresql*13-contrib-13.1-lp152.16.1.x86_64
This ... looks like a bit of a mess. You evidently have three independent
sets of Postgres packages, but surely there should only be two. And
why do they all have "lp152" in the version? (And why are there only
two versions of plperl? And why are some of these "noarch"? That
would make sense for the docs subpackage, but not much else.)
The pg_restore trace gives us no more info than we had before. But
given this package list, I am suspecting some confusion over which
version of plperl.so should get loaded. You might try looking into
the destination postmaster's log file (not pg_upgrade's user-visible
output) to see if there is any low-level message from the dynamic
loader emitted just before it complains about plperlu not existing.
regards, tom lane
Am Freitag, 29. Januar 2021, 16:04:54 CET schrieb Tom Lane:
GMX Steffen <steffen.ang@gmx.de> writes:
OS Packages:
*postgresql*-llvmjit-13-lp152.3.1.noarch
*postgresql*12-pldebugger-1.0+git13.ddbce7b-lp152.1.4.x86_64
*postgresql*12-llvmjit-12.5-lp152.34.1.x86_64
*postgresql*-13-lp152.3.1.noarch
*postgresql*12-docs-12.5-lp152.34.1.noarch
*postgresql*12-contrib-12.5-lp152.34.1.x86_64
*postgresql*13-plperl-13.1-lp152.16.1.x86_64
*postgresql*12-server-12.5-lp152.34.1.x86_64
*postgresql*-docs-13-lp152.3.1.noarch
*postgresql*12-12.5-lp152.34.1.x86_64
*postgresql*12-pg_qualstats-2.0.2-lp152.2.3.x86_64
*postgresql*12-ip4r-2.4.1+git1.5f9ce88-lp152.8.2.x86_64
*postgresql*-server-13-lp152.3.1.noarch
*postgresql*12-pg_qualstats-llvmjit-2.0.2-lp152.2.3.x86_64
*postgresql*13-docs-13.1-lp152.16.1.noarch
*postgresql*13-server-13.1-lp152.16.1.x86_64
*postgresql*-contrib-13-lp152.3.1.noarch
*postgresql*12-ip4r-llvmjit-2.4.1+git1.5f9ce88-lp152.8.2.x86_64
*postgresql*13-13.1-lp152.16.1.x86_64
*postgresql*13-llvmjit-13.1-lp152.16.1.x86_64
*postgresql*-plperl-13-lp152.3.1.noarch
*postgresql*12-pldebugger-llvmjit-1.0+git13.ddbce7b-lp152.1.4.x86_64
*postgresql*13-contrib-13.1-lp152.16.1.x86_64This ... looks like a bit of a mess. You evidently have three independent
sets of Postgres packages, but surely there should only be two. And
why do they all have "lp152" in the version? (And why are there only
two versions of plperl? And why are some of these "noarch"? That
would make sense for the docs subpackage, but not much else.)The pg_restore trace gives us no more info than we had before. But
given this package list, I am suspecting some confusion over which
version of plperl.so should get loaded. You might try looking into
the destination postmaster's log file (not pg_upgrade's user-visible
output) to see if there is any low-level message from the dynamic
loader emitted just before it complains about plperlu not existing.regards, tom lane
Hello,
- packages like "postgresql*-contrib-13-lp152.3.1.noarch" are meta packages (noarch) and depend to actual installed version. in this case version 13.
- lp152 is a label for opensuse leap 15.2
- when run version 12 it use /usr/lib/postgresql12/lib64/plperl.so
for version 13 it's use /usr/lib/postgresql13/lib64/plperl.so
- there is no low-level message from the dynamic loader. the extension can created everytime. And exist in the upgraded database
i hope you could read my last email: i resend three times :(
There you can see this:
psql (13.1)
Geben Sie »help« für Hilfe ein.
botdb=# \dL
Liste der Sprachen
Name | Eigentümer | Vertraut | Beschreibung
---------+------------+----------+------------------------------
plpgsql | postgres | t | PL/pgSQL procedural language
(1 Zeile)
==> NO LANGUAGE PLPERLU! "CREATE LANGUAGE plperlu" ==> create ONLY an extension!
botdb=# \dx
Liste der installierten Erweiterungen
Name | Version | Schema | Beschreibung
--------------+---------+------------+--------------------------------------------------
hstore | 1.5 | pg_catalog | data type for storing sets of (key, value) pairs
pg_cron | 4.0.4 | public | Cron-like Job Scheduler for PostgreSQL
pg_http | 1.4.1 | public | A web service for PostgreSQL
pg_mqtt | 1.1 | public | A mqtt client daemon for PostgreSQL
pg_reporting | 1.1 | public | A reporting extension for PostgreSQL
plperlu | 1.0 | pg_catalog | PL/PerlU untrusted procedural language <====
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(7 Zeilen)
==> EXTENSION plperlu ALREADY CREATED AND EXIST
The command "create language" create NO plperlu language object. ONLY a extension object. Correct?
Therefor it must fail to add a language to an extension! And i think the "create extension" run fine, so that the command alter extension / add language combination is only for older postgresql version okay. it is not needed any more.
The failed upgraded database has the extension plperlu installed! So this is fine too.
best regards thomas steffen
Looking at this more closely, it seems like there must be something broken
about the plperlu extension in the source database. We see
pg_restore: erstelle EXTENSION »plperlu«
pg_restore: erstelle COMMENT »EXTENSION "plperlu"«
pg_restore: erstelle PROCEDURAL LANGUAGE »plperlu«
pg_restore: in Phase PROCESSING TOC:
pg_restore: in Inhaltsverzeichniseintrag 2151; 2612 16427 PROCEDURAL LANGUAGE plperlu postgres
pg_restore: Fehler: could not execute query: FEHLER: Sprache »plperlu« existiert nicht
Die Anweisung war: CREATE OR REPLACE PROCEDURAL LANGUAGE "plperlu";
but a binary-upgrade dump should have dumped the plperlu support functions
before the procedural language object. And the CREATE PROCEDURAL LANGUAGE
command should have included explicit HANDLER etc clauses. Both things
could be explained by supposing that pg_dump didn't see the support
functions as part of the extension, but why not?
It might be interesting to check the results of "\dx+ plperlu" in the
source database.
regards, tom lane
Am Freitag, 29. Januar 2021, 18:29:34 CET schrieb Tom Lane:
Looking at this more closely, it seems like there must be something broken
about the plperlu extension in the source database. We seepg_restore: erstelle EXTENSION »plperlu«
pg_restore: erstelle COMMENT »EXTENSION "plperlu"«
pg_restore: erstelle PROCEDURAL LANGUAGE »plperlu«
pg_restore: in Phase PROCESSING TOC:
pg_restore: in Inhaltsverzeichniseintrag 2151; 2612 16427 PROCEDURAL
LANGUAGE plperlu postgres pg_restore: Fehler: could not execute query:
FEHLER: Sprache »plperlu« existiert nicht Die Anweisung war: CREATE OR
REPLACE PROCEDURAL LANGUAGE "plperlu";but a binary-upgrade dump should have dumped the plperlu support functions
before the procedural language object. And the CREATE PROCEDURAL LANGUAGE
command should have included explicit HANDLER etc clauses. Both things
could be explained by supposing that pg_dump didn't see the support
functions as part of the extension, but why not?It might be interesting to check the results of "\dx+ plperlu" in the
source database.regards, tom lane
Hello
output from source database:
botdb=# \dx+ plperlu
Objekte in Erweiterung »plperlu«
Objektbeschreibung
--------------------
language plperlu
thomas steffen
GMX LINREG <linreg@gmx.net> writes:
output from source database:
botdb=# \dx+ plperlu
Objekte in Erweiterung »plperlu«
Objektbeschreibung
--------------------
language plperlu
Yeah, that's definitely broken: it should look like
\dx+ plperlu
Objects in extension "plperlu"
Object description
-------------------------------------------
function plperlu_call_handler()
function plperlu_inline_handler(internal)
function plperlu_validator(oid)
language plperlu
(4 rows)
Offhand I think that completely explains pg_dump's failure: it relies on
the dependency-membership entries to decide that it ought to dump the
language support functions, so if they're not there, this is what you get.
It's possible that the entries *are* there but they're not being found due
to index corruption. You could try "REINDEX TABLE pg_depend" and see if
the \dx+ output changes. I don't have a lot of hope for that though.
The next easiest fix would be to see if you have any plperlu functions
to worry about, and if not, just drop the busted extension.
If you do have some such functions, the best bet might be to dump
them separately, go ahead and DROP CASCADE the busted extension,
then upgrade; and restore the plperlu functions separately afterwards.
It'd be kind of interesting to figure out why those pg_depend entries
are missing, but I suppose the evidence is long gone :-(
regards, tom lane
Am Freitag, 29. Januar 2021, 18:55:42 CET schrieb Tom Lane:
GMX LINREG <linreg@gmx.net> writes:
output from source database:
botdb=# \dx+ plperlu
Objekte in Erweiterung »plperlu«Objektbeschreibung
--------------------
language plperlu
Yeah, that's definitely broken: it should look like
\dx+ plperlu
Objects in extension "plperlu"
Object description
-------------------------------------------
function plperlu_call_handler()
function plperlu_inline_handler(internal)
function plperlu_validator(oid)
language plperlu
(4 rows)Offhand I think that completely explains pg_dump's failure: it relies on
the dependency-membership entries to decide that it ought to dump the
language support functions, so if they're not there, this is what you get.It's possible that the entries *are* there but they're not being found due
to index corruption. You could try "REINDEX TABLE pg_depend" and see if
the \dx+ output changes. I don't have a lot of hope for that though.The next easiest fix would be to see if you have any plperlu functions
to worry about, and if not, just drop the busted extension.If you do have some such functions, the best bet might be to dump
them separately, go ahead and DROP CASCADE the busted extension,
then upgrade; and restore the plperlu functions separately afterwards.It'd be kind of interesting to figure out why those pg_depend entries
are missing, but I suppose the evidence is long gone :-(regards, tom lane
Hello,
after "REINDEX TABLE pg_depend" the problem still persists.
So I agree "the evidence is long gone";
I will perfrom a pg_dump/psql combo to restore and upgrade the database to
main version 13. Since version 9.6 we have had a lot of pg_upgrades.
Thank you very much.
Best regards from Germany
Thomas Steffen