Thread: BUG #16843: pg_upgrade from 12.5 to 13.1 with extension plperlu failed
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
postgresql12-pldebugger-1.0+git13.ddbce7b-lp152.1.4.x86_64
postgresql12-llvmjit-12.5-lp152.34.1.x86_64
postgresql-13-lp152.3.1.noarch
postgresql12-docs-12.5-lp152.34.1.noarch
postgresql12-contrib-12.5-lp152.34.1.x86_64
postgresql13-plperl-13.1-lp152.16.1.x86_64
postgresql12-server-12.5-lp152.34.1.x86_64
postgresql-docs-13-lp152.3.1.noarch
postgresql12-12.5-lp152.34.1.x86_64
postgresql12-pg_qualstats-2.0.2-lp152.2.3.x86_64
postgresql12-ip4r-2.4.1+git1.5f9ce88-lp152.8.2.x86_64
postgresql-server-13-lp152.3.1.noarch
postgresql12-pg_qualstats-llvmjit-2.0.2-lp152.2.3.x86_64
postgresql13-docs-13.1-lp152.16.1.noarch
postgresql13-server-13.1-lp152.16.1.x86_64
postgresql-contrib-13-lp152.3.1.noarch
postgresql12-ip4r-llvmjit-2.4.1+git1.5f9ce88-lp152.8.2.x86_64
postgresql13-13.1-lp152.16.1.x86_64
postgresql13-llvmjit-13.1-lp152.16.1.x86_64
postgresql-plperl-13-lp152.3.1.noarch
postgresql12-pldebugger-llvmjit-1.0+git13.ddbce7b-lp152.1.4.x86_64
postgresql13-contrib-13.1-lp152.16.1.x86_64
Output from failed database upgrade:
botdb=# \dL
Attachment
Re: BUG #16843: pg_upgrade from 12.5 to 13.1 with extension plperlu failed
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
postgresql12-pldebugger-1.0+git13.ddbce7b-lp152.1.4.x86_64
postgresql12-llvmjit-12.5-lp152.34.1.x86_64
postgresql-13-lp152.3.1.noarch
postgresql12-docs-12.5-lp152.34.1.noarch
postgresql12-contrib-12.5-lp152.34.1.x86_64
postgresql13-plperl-13.1-lp152.16.1.x86_64
postgresql12-server-12.5-lp152.34.1.x86_64
postgresql-docs-13-lp152.3.1.noarch
postgresql12-12.5-lp152.34.1.x86_64
postgresql12-pg_qualstats-2.0.2-lp152.2.3.x86_64
postgresql12-ip4r-2.4.1+git1.5f9ce88-lp152.8.2.x86_64
postgresql-server-13-lp152.3.1.noarch
postgresql12-pg_qualstats-llvmjit-2.0.2-lp152.2.3.x86_64
postgresql13-docs-13.1-lp152.16.1.noarch
postgresql13-server-13.1-lp152.16.1.x86_64
postgresql-contrib-13-lp152.3.1.noarch
postgresql12-ip4r-llvmjit-2.4.1+git1.5f9ce88-lp152.8.2.x86_64 postgresql13-13.1-lp152.16.1.x86_64
postgresql13-llvmjit-13.1-lp152.16.1.x86_64
postgresql-plperl-13-lp152.3.1.noarch
postgresql12-pldebugger-llvmjit-1.0+git13.ddbce7b-lp152.1.4.x86_64 postgresql13-contrib-13.1-lp152.16.1.x86_64
Output from failed database upgrade:
botdb=# \dL
Attachment
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 language
best regards
Thomas Steffen
Attachment
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_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
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
commandalter 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 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 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