pg_upgrade Python version issue on openSUSE

Hi,

sorry, forgot to mention two things (see below)

On 26. Sep, 2020, at 11:33, Paul Förster <paul.foerster@gmail.com> wrote:

Hi,

the OS here is openSUSE Leap 15.2. I downloaded the PostgreSQL 13.0 source and built it without issues like so:

(pgTargetDir is /data/postgres/13.0)

$ ./configure \
--prefix=${pgTargetDir} \
--enable-nls \
--with-icu \
--with-perl \
--with-python \
--with-openssl \
--with-ldap \
--with-libxml \
--with-tclconfig=/usr/lib64
$ make install-world

As I said, no issues at all so far. Now, I set the new environment for version 13.0 and do:

$ initdb -D "${PGDATANEW}" \
-k \
--encoding=utf8 \
--lc-collate=en_US.UTF-8 \
--lc-ctype=en_US.UTF-8 \
--wal-segsize=32

Still, no issues and all is well.

Now comes the problem: I run pg_upgrade and it spits out problems with plpython2:

$ pg_upgrade --check -k
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for presence of required libraries fatal

Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
loadable_libraries.txt

Failure, exiting

$ cat loadable_libraries.txt
could not load library "$libdir/plpython2": ERROR: could not access file "$libdir/plpython2": No such file or directory
In database: postgres

openSUSE has no python2 anymore, only python3. The source database does not even have plpython installed:

postgres=# select name, version from pg_available_extension_versions
postgres-# where installed;
name | version
---------+---------
plperlu | 1.0
dblink | 1.2
plpgsql | 1.0
plperl | 1.0
(4 rows)

Can you please tell me what I am doing wrong here? Yes, I could compile the source without Python in this particular case, because it is not needed on this particular database cluster. But we have many more database clusters and a bunch of them uses plpython, so I definitely need it. To better be safe than sorry, I want the issue resolved before migrations start, even for database clusters which don't need it.

the two things I forgot to mention are:

a) the versions I will be upgrading from are 11.9 and 12.4. The above thing happens with 12.4. Since I didn't check 11.9 yet, I cannot say if it appears there too.

b) the 12.4 software also does not have plpython2 files in its lib64 directory. Both only have plpython3.so:

$ ll /data/postgres/*/lib64/plpython*
-rwxr-xr-x 1 postgres dba 151672 Aug 13 16:28 /data/postgres/12.4/lib64/plpython3.so
-rwxr-xr-x 1 postgres dba 151544 Sep 26 10:38 /data/postgres/13.0/lib64/plpython3.so

Any help would be appreciated. Thanks very much in advance.

Cheers,
Paul

Hi,

On 26. Sep, 2020, at 11:42, Paul Förster <paul.foerster@gmail.com> wrote:

the two things I forgot to mention are:

a) the versions I will be upgrading from are 11.9 and 12.4. The above thing happens with 12.4. Since I didn't check 11.9 yet, I cannot say if it appears there too.

b) the 12.4 software also does not have plpython2 files in its lib64 directory. Both only have plpython3.so:

$ ll /data/postgres/*/lib64/plpython*
-rwxr-xr-x 1 postgres dba 151672 Aug 13 16:28 /data/postgres/12.4/lib64/plpython3.so
-rwxr-xr-x 1 postgres dba 151544 Sep 26 10:38 /data/postgres/13.0/lib64/plpython3.so

Any help would be appreciated. Thanks very much in advance.

seems, I found some kind of solution:

- before running "pg_upgrade --check -k":
drop extension plpythonu;
- run pg_upgrade
- after the upgrade:
create extension plpython3u;

Is this the correct way?

Cheers,
Paul

=?utf-8?Q?Paul_F=C3=B6rster?= <paul.foerster@gmail.com> writes:

seems, I found some kind of solution:

- before running "pg_upgrade --check -k":
drop extension plpythonu;
- run pg_upgrade
- after the upgrade:
create extension plpython3u;

Is this the correct way?

If you had plpythonu installed before, that's a plausible thing
to do. (There was discussion some time ago about making the
python-2-to-3 transition less painful for users, but we failed
to come to any consensus about how; so manual fixes like this
are going to be needed for a lot of people.)

However, I don't understand how "drop extension plpythonu"
worked for you, given your previous query showing that
that extension wasn't installed.

regards, tom lane

Hi Tom,

On 26. Sep, 2020, at 16:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

If you had plpythonu installed before, that's a plausible thing
to do. (There was discussion some time ago about making the
python-2-to-3 transition less painful for users, but we failed
to come to any consensus about how; so manual fixes like this
are going to be needed for a lot of people.)

that's one of the things I very much don't like about Python. This version 2 and 3 gibberish has been going on for years now wherever it's used, be it packaging with operating systems or integrated into applications.

However, I don't understand how "drop extension plpythonu"
worked for you, given your previous query showing that
that extension wasn't installed.

that is exactly what I don't understand too. It should have shown up in the query, but it didn't. The PostgreSQL 12.4 software was compiled exactly the same way, only without ICU support. The other configure options were the same. Other than that, I didn't change anything in my build script.

For your reference, below are the two configs:

PostgreSQL 12.4:

$ pg_config
BINDIR = /data/postgres/12.4/bin
DOCDIR = /data/postgres/12.4/share/doc
HTMLDIR = /data/postgres/12.4/share/doc
INCLUDEDIR = /data/postgres/12.4/include
PKGINCLUDEDIR = /data/postgres/12.4/include
INCLUDEDIR-SERVER = /data/postgres/12.4/include/server
LIBDIR = /data/postgres/12.4/lib64
PKGLIBDIR = /data/postgres/12.4/lib64
LOCALEDIR = /data/postgres/12.4/share/locale
MANDIR = /data/postgres/12.4/share/man
SHAREDIR = /data/postgres/12.4/share
SYSCONFDIR = /data/postgres/12.4/etc
PGXS = /data/postgres/12.4/lib64/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/data/postgres/12.4' '--enable-nls' '--with-perl' '--with-python' '--with-openssl' '--with-ldap' '--with-libxml' '--with-tclconfig=/usr/lib64'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/data/postgres/12.4/lib64',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lpthread -lxml2 -lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 12.4

PostgreSQL 13.0:

$ pg_config
BINDIR = /data/postgres/13.0/bin
DOCDIR = /data/postgres/13.0/share/doc
HTMLDIR = /data/postgres/13.0/share/doc
INCLUDEDIR = /data/postgres/13.0/include
PKGINCLUDEDIR = /data/postgres/13.0/include
INCLUDEDIR-SERVER = /data/postgres/13.0/include/server
LIBDIR = /data/postgres/13.0/lib64
PKGLIBDIR = /data/postgres/13.0/lib64
LOCALEDIR = /data/postgres/13.0/share/locale
MANDIR = /data/postgres/13.0/share/man
SHAREDIR = /data/postgres/13.0/share
SYSCONFDIR = /data/postgres/13.0/etc
PGXS = /data/postgres/13.0/lib64/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/data/postgres/13.0' '--enable-nls' '--with-icu' '--with-perl' '--with-python' '--with-openssl' '--with-ldap' '--with-libxml' '--with-tclconfig=/usr/lib64'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/data/postgres/13.0/lib64',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lpthread -lxml2 -lssl -lcrypto -lz -lreadline -lrt -ldl -lm
VERSION = PostgreSQL 13.0

Cheers,
Paul

Hi Tom,

On 26. Sep, 2020, at 16:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

=?utf-8?Q?Paul_F=C3=B6rster?= <paul.foerster@gmail.com> writes:

seems, I found some kind of solution:

- before running "pg_upgrade --check -k":
drop extension plpythonu;
- run pg_upgrade
- after the upgrade:
create extension plpython3u;

Is this the correct way?

If you had plpythonu installed before, that's a plausible thing
to do. (There was discussion some time ago about making the
python-2-to-3 transition less painful for users, but we failed
to come to any consensus about how; so manual fixes like this
are going to be needed for a lot of people.)

However, I don't understand how "drop extension plpythonu"
worked for you, given your previous query showing that
that extension wasn't installed.

just checked with another 12.4. It's the same:

postgres=# select * from pg_available_extension_versions where installed;
name | version | installed | superuser | relocatable | schema | requires | comment
---------+---------+-----------+-----------+-------------+------------+----------+--------------------------------------------------------------
plperlu | 1.0 | t | t | f | pg_catalog | | PL/PerlU untrusted procedural language
dblink | 1.2 | t | t | t | | | connect to other PostgreSQL databases from within a database
plpgsql | 1.0 | t | f | f | pg_catalog | | PL/pgSQL procedural language
plperl | 1.0 | t | f | f | pg_catalog | | PL/Perl procedural language
(4 rows)

postgres=# drop extension plpythonu ;
DROP EXTENSION
postgres=# create extension plpython3u ;
CREATE EXTENSION

The "plpython" and "plpython3u" for the drop and create extension statements came by entering "plpy" and then pressing tab. So PostgreSQL knew about them. Still, as you can see, I could drop pypythonu again though it did not appear in the query. After the create extension, it appears as it should:

postgres=# select * from pg_available_extension_versions where installed;
name | version | installed | superuser | relocatable | schema | requires | comment
------------+---------+-----------+-----------+-------------+------------+----------+--------------------------------------------------------------
plperlu | 1.0 | t | t | f | pg_catalog | | PL/PerlU untrusted procedural language
dblink | 1.2 | t | t | t | | | connect to other PostgreSQL databases from within a database
plpython3u | 1.0 | t | t | f | pg_catalog | | PL/Python3U untrusted procedural language
plpgsql | 1.0 | t | f | f | pg_catalog | | PL/pgSQL procedural language
plperl | 1.0 | t | f | f | pg_catalog | | PL/Perl procedural language
(5 rows)

Is this a bug in 12.4 not showing the extension?

Cheers,
Paul

=?utf-8?Q?Paul_F=C3=B6rster?= <paul.foerster@gmail.com> writes:

On 26. Sep, 2020, at 16:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

However, I don't understand how "drop extension plpythonu"
worked for you, given your previous query showing that
that extension wasn't installed.

just checked with another 12.4. It's the same:

postgres=# select * from pg_available_extension_versions where installed;
name | version | installed | superuser | relocatable | schema | requires | comment
---------+---------+-----------+-----------+-------------+------------+----------+--------------------------------------------------------------
plperlu | 1.0 | t | t | f | pg_catalog | | PL/PerlU untrusted procedural language
dblink | 1.2 | t | t | t | | | connect to other PostgreSQL databases from within a database
plpgsql | 1.0 | t | f | f | pg_catalog | | PL/pgSQL procedural language
plperl | 1.0 | t | f | f | pg_catalog | | PL/Perl procedural language
(4 rows)

postgres=# drop extension plpythonu ;
DROP EXTENSION
postgres=# create extension plpython3u ;
CREATE EXTENSION

Actually, now that I think about it, you're querying the wrong view.
I'm too lazy to check the source code right now, but I'm pretty sure
that pg_available_extension_versions is mostly driven off what control
files exist in the on-disk libdir. But that may have little to do with
what's in the system catalogs. You should have checked pg_extension,
or just "\dx" in psql.

regards, tom lane

On 9/26/20 7:49 AM, Tom Lane wrote:

=?utf-8?Q?Paul_F=C3=B6rster?= <paul.foerster@gmail.com> writes:

On 26. Sep, 2020, at 16:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

However, I don't understand how "drop extension plpythonu"
worked for you, given your previous query showing that
that extension wasn't installed.

just checked with another 12.4. It's the same:

postgres=# select * from pg_available_extension_versions where installed;
name | version | installed | superuser | relocatable | schema | requires | comment
---------+---------+-----------+-----------+-------------+------------+----------+--------------------------------------------------------------
plperlu | 1.0 | t | t | f | pg_catalog | | PL/PerlU untrusted procedural language
dblink | 1.2 | t | t | t | | | connect to other PostgreSQL databases from within a database
plpgsql | 1.0 | t | f | f | pg_catalog | | PL/pgSQL procedural language
plperl | 1.0 | t | f | f | pg_catalog | | PL/Perl procedural language
(4 rows)

postgres=# drop extension plpythonu ;
DROP EXTENSION
postgres=# create extension plpython3u ;
CREATE EXTENSION

Actually, now that I think about it, you're querying the wrong view.
I'm too lazy to check the source code right now, but I'm pretty sure
that pg_available_extension_versions is mostly driven off what control
files exist in the on-disk libdir. But that may have little to do with
what's in the system catalogs. You should have checked pg_extension,
or just "\dx" in psql.

I believe the issue is here:

select * from pg_pltemplate ;

plpythonu | f | f | plpython_call_handler |
plpython_inline_handler | plpython_validator | $libdir/plpython2 | NULL
plpython2u | f | f | plpython2_call_handler |
plpython2_inline_handler | plpython2_validator | $libdir/plpython2 | NULL
plpython3u | f | f | plpython3_call_handler |
plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | NULL

The default plpython is plpythonu and that points at $libdir/plpython2.

The instructions here:

https://www.postgresql.org/docs/12/plpython-python23.html

offer a work around:

"Daredevils, who want to build a Python-3-only operating system
environment, can change the contents of pg_pltemplate to make plpythonu
be equivalent to plpython3u, keeping in mind that this would make their
installation incompatible with most of the rest of the world."

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Tom,

On 26. Sep, 2020, at 16:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Actually, now that I think about it, you're querying the wrong view.
I'm too lazy to check the source code right now, but I'm pretty sure
that pg_available_extension_versions is mostly driven off what control
files exist in the on-disk libdir. But that may have little to do with
what's in the system catalogs. You should have checked pg_extension,
or just "\dx" in psql.

just created another new empty database cluster because I run out of them on my test box here at home. :-) After all, each drop/create extension seems to resolve the issue, so the cluster is unusable for repetition, unless I would restore it. Ok, I'm too lazy now... :-D

Did the usual initdb -k on the new database cluster. Then the select plus your suggested \dx. Nothing there and drop extension didn't work, all as I would have expected. This is strange.

I will check further next week on company databases. The ones I did it up to now are my private ones at home. I'm really curious about that next week.

Thanks for the tips.

Cheers,
Paul

On 9/26/20 2:33 AM, Paul Förster wrote:

Hi,

the OS here is openSUSE Leap 15.2. I downloaded the PostgreSQL 13.0 source and built it without issues like so:

openSUSE has no python2 anymore, only python3. The source database does not even have plpython installed:

Actually it does:

https://software.opensuse.org/package/python?search_term=%22python%22

Cheers,
Paul

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

On 26. Sep, 2020, at 17:07, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

I believe the issue is here:

select * from pg_pltemplate ;

plpythonu | f | f | plpython_call_handler | plpython_inline_handler | plpython_validator | $libdir/plpython2 | NULL
plpython2u | f | f | plpython2_call_handler | plpython2_inline_handler | plpython2_validator | $libdir/plpython2 | NULL
plpython3u | f | f | plpython3_call_handler | plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | NULL

The default plpython is plpythonu and that points at $libdir/plpython2.

The instructions here:

https://www.postgresql.org/docs/12/plpython-python23.html

offer a work around:

"Daredevils, who want to build a Python-3-only operating system environment, can change the contents of pg_pltemplate to make plpythonu be equivalent to plpython3u, keeping in mind that this would make their installation incompatible with most of the rest of the world."

sounds like:

update pg_pltemplate
set
tmplhandler='plpython3_call_handler',
tmplinline='plpython3_inline_handler',
tmplvalidator='plpython3_validator',
tmpllibrary='$libdir/plpython3'
where
tmplname='plpythonu';

And that sounds somewhat dangerous to me, especially if I take the comment on the plpython-python23 page into account: "keeping in mind that this would make their installation incompatible with most of the rest of the world."

I'd rather not...

Cheers,
Paul

Hi Adrian,

On 26. Sep, 2020, at 17:17, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 9/26/20 2:33 AM, Paul Förster wrote:

Hi,
the OS here is openSUSE Leap 15.2. I downloaded the PostgreSQL 13.0 source and built it without issues like so:

openSUSE has no python2 anymore, only python3. The source database does not even have plpython installed:

Actually it does:

https://software.opensuse.org/package/python?search_term=%22python%22

well, actually, it does not. There are still Python2 packages in the repo which I can happily install here at home but not in the company. So I will run into trouble if I install something here to make something else work, and then take it to the company where it does not work. So, to maintain the highest level of compatibility to the machines at work, I don't install extra packages.

But yes, that would be the second most proper solution. The first and utmost proper solution would be for Python to finally stop that versioning crap after years have gone by now.

Cheers,
Paul

On 9/26/20 8:26 AM, Paul Förster wrote:

Hi Adrian,

On 26. Sep, 2020, at 17:17, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 9/26/20 2:33 AM, Paul Förster wrote:

Hi,
the OS here is openSUSE Leap 15.2. I downloaded the PostgreSQL 13.0 source and built it without issues like so:

openSUSE has no python2 anymore, only python3. The source database does not even have plpython installed:

Actually it does:

https://software.opensuse.org/package/python?search_term=%22python%22

well, actually, it does not. There are still Python2 packages in the repo which I can happily install here at home but not in the company. So I will run into trouble if I install something here to make something else work, and then take it to the company where it does not work. So, to maintain the highest level of compatibility to the machines at work, I don't install extra packages.

I suppose getting them to install Python 2 is out of the question? It is
an official package.

But yes, that would be the second most proper solution. The first and utmost proper solution would be for Python to finally stop that versioning crap after years have gone by now.

Well there is always going to be versioning. If you mean the
incompatibility split, then for 2/3 that is not going away. There will
be a Python 4, but the core developers have said they learned their
lesson and it will just be an incremental upgrade.

Cheers,
Paul

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

On 26. Sep, 2020, at 17:43, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

I suppose getting them to install Python 2 is out of the question? It is an official package.

I can try, but chances are at 99% that they refuse.

Well there is always going to be versioning. If you mean the incompatibility split, then for 2/3 that is not going away. There will be a Python 4, but the core developers have said they learned their lesson and it will just be an incremental upgrade.

so you're saying there will always be two Pythons? One Python 2 and one Python x (with x>=3)? Oh my god... Why don't they just make Python 3 backward compatible?

Cheers,
Paul

On 9/26/20 8:54 AM, Paul Förster wrote:

Hi Adrian,

On 26. Sep, 2020, at 17:43, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

I suppose getting them to install Python 2 is out of the question? It is an official package.

I can try, but chances are at 99% that they refuse.

Well there is always going to be versioning. If you mean the incompatibility split, then for 2/3 that is not going away. There will be a Python 4, but the core developers have said they learned their lesson and it will just be an incremental upgrade.

so you're saying there will always be two Pythons? One Python 2 and one Python x (with x>=3)? Oh my god... Why don't they just make Python 3 backward compatible?

Well one would hope folks eventually finish migrating off Python 2, but
there is a lot of that code out there. There have been tweaks to make
them more compatible. The sticking point for full compatibility is the
Unicode transition. That would cause the same breakage as exists now in
Python 2 --> Python 3, so there is no real point and the developers
don't want to relive that experience. The goal going forward is for
everybody to move to Python 3 and have changes in the future be
incremental. Anyway that is enough for an off-topic discussion.

Cheers,
Paul

--
Adrian Klaver
adrian.klaver@aklaver.com

On 9/26/20 7:35 AM, Paul Förster wrote:

Hi Tom,

just checked with another 12.4. It's the same:

postgres=# select * from pg_available_extension_versions where installed;
name | version | installed | superuser | relocatable | schema | requires | comment
---------+---------+-----------+-----------+-------------+------------+----------+--------------------------------------------------------------
plperlu | 1.0 | t | t | f | pg_catalog | | PL/PerlU untrusted procedural language
dblink | 1.2 | t | t | t | | | connect to other PostgreSQL databases from within a database
plpgsql | 1.0 | t | f | f | pg_catalog | | PL/pgSQL procedural language
plperl | 1.0 | t | f | f | pg_catalog | | PL/Perl procedural language
(4 rows)

postgres=# drop extension plpythonu ;
DROP EXTENSION
postgres=# create extension plpython3u ;
CREATE EXTENSION

The "plpython" and "plpython3u" for the drop and create extension statements came by entering "plpy" and then pressing tab. So PostgreSQL knew about them. Still, as you can see, I could drop pypythonu again though it did not appear in the query. After the create extension, it appears as it should:

postgres=# select * from pg_available_extension_versions where installed;
name | version | installed | superuser | relocatable | schema | requires | comment
------------+---------+-----------+-----------+-------------+------------+----------+--------------------------------------------------------------
plperlu | 1.0 | t | t | f | pg_catalog | | PL/PerlU untrusted procedural language
dblink | 1.2 | t | t | t | | | connect to other PostgreSQL databases from within a database
plpython3u | 1.0 | t | t | f | pg_catalog | | PL/Python3U untrusted procedural language
plpgsql | 1.0 | t | f | f | pg_catalog | | PL/pgSQL procedural language
plperl | 1.0 | t | f | f | pg_catalog | | PL/Perl procedural language
(5 rows)

Is this a bug in 12.4 not showing the extension?

Could it be that at some point in these instances history plpython*
where installed as CREATE LANGUAGE and you are dealing with the vestiges
of that?

Are you able to go back and reconstruct them and then do \dL (languages)
and \dx (extensions)?

Cheers,
Paul

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

On 27. Sep, 2020, at 00:09, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Could it be that at some point in these instances history plpython* where installed as CREATE LANGUAGE and you are dealing with the vestiges of that?

I do know for sure that that never happened because the database clusters with this effect are my personal test databases and I never used Python. I did some tests with plperl and plperlu, though, but never python, because I don't "speak" python.

What is possible is, those databases are clones of a Patroni cluster database (primary) I used to experiment with. I just copied them to new PGDATAs back then and changed PGPORT of course. I know, I could have done initdb and pg_dumpall but just copying the whole database cluster was the fast way to go, even more so as the PostgreSQL software was exactly the same. Just PGDATA and PGPORT changed for the clone. From what I know this is a perfectly legal way to do it as long as the source database cluster is properly shut down during the copy process.

Maybe Patroni did it then implicitly? I'm not sure how Patroni works internally but I know that it is written in Python. Maybe it does install something in the database which I don't know and can't find? I tried searching for anything owned by "replicator" but can't find anything.

Are you able to go back and reconstruct them and then do \dL (languages) and \dx (extensions)?

The machine in question is my personal test box at home. I don't do regular backups there. If I break something I just reinstall it. So going back into the past with backups is not possible for me. The only thing that I kept running a long time now is the Patroni cluster because I have some data stored in it. But this is the only "history" there is. However, \dx and \dL do not show any Python extension or language on the Patroni cluster too, which is still 12.4.

Still, thanks for helping.

Cheers,
Paul

On 9/26/20 8:07 AM, Adrian Klaver wrote:

On 9/26/20 7:49 AM, Tom Lane wrote:

=?utf-8?Q?Paul_F=C3=B6rster?= <paul.foerster@gmail.com> writes:

On 26. Sep, 2020, at 16:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

However, I don't understand how "drop extension plpythonu"
worked for you, given your previous query showing that
that extension wasn't installed.

just checked with another 12.4. It's the same:

postgres=# select * from pg_available_extension_versions where
installed;
   name   | version | installed | superuser | relocatable |
schema   | requires |                           comment
---------+---------+-----------+-----------+-------------+------------+----------+--------------------------------------------------------------

  plperlu | 1.0     | t         | t         | f           |
pg_catalog |          | PL/PerlU untrusted procedural language
  dblink  | 1.2     | t         | t         | t
|            |          | connect to other PostgreSQL databases from
within a database
  plpgsql | 1.0     | t         | f         | f           |
pg_catalog |          | PL/pgSQL procedural language
  plperl  | 1.0     | t         | f         | f           |
pg_catalog |          | PL/Perl procedural language
(4 rows)

postgres=# drop extension plpythonu ;
DROP EXTENSION
postgres=# create extension plpython3u ;
CREATE EXTENSION

Actually, now that I think about it, you're querying the wrong view.
I'm too lazy to check the source code right now, but I'm pretty sure
that pg_available_extension_versions is mostly driven off what control
files exist in the on-disk libdir.  But that may have little to do with
what's in the system catalogs.  You should have checked pg_extension,
or just "\dx" in psql.

I believe the issue is here:

select * from pg_pltemplate ;

 plpythonu  | f           | f             | plpython_call_handler  |
plpython_inline_handler  | plpython_validator  | $libdir/plpython2 | NULL
 plpython2u | f           | f             | plpython2_call_handler |
plpython2_inline_handler | plpython2_validator | $libdir/plpython2 | NULL
 plpython3u | f           | f             | plpython3_call_handler |
plpython3_inline_handler | plpython3_validator | $libdir/plpython3 | NULL

Some digging in the pg_upgrade code(function.c) proved the above wrong.
Turns out pg_upgrade uses information from pg_proc.

The default plpython is plpythonu and that points at $libdir/plpython2.

The instructions here:

https://www.postgresql.org/docs/12/plpython-python23.html

offer a work around:

"Daredevils, who want to build a Python-3-only operating system
environment, can change the contents of pg_pltemplate to make plpythonu
be equivalent to plpython3u, keeping in mind that this would make their
installation incompatible with most of the rest of the world."

            regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

On 9/27/20 2:00 AM, Paul Förster wrote:

Hi Adrian,

On 27. Sep, 2020, at 00:09, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Could it be that at some point in these instances history plpython* where installed as CREATE LANGUAGE and you are dealing with the vestiges of that?

I do know for sure that that never happened because the database clusters with this effect are my personal test databases and I never used Python. I did some tests with plperl and plperlu, though, but never python, because I don't "speak" python.

What is possible is, those databases are clones of a Patroni cluster database (primary) I used to experiment with. I just copied them to new PGDATAs back then and changed PGPORT of course. I know, I could have done initdb and pg_dumpall but just copying the whole database cluster was the fast way to go, even more so as the PostgreSQL software was exactly the same. Just PGDATA and PGPORT changed for the clone. From what I know this is a perfectly legal way to do it as long as the source database cluster is properly shut down during the copy process.

Maybe Patroni did it then implicitly? I'm not sure how Patroni works internally but I know that it is written in Python. Maybe it does install something in the database which I don't know and can't find? I tried searching for anything owned by "replicator" but can't find anything.

Are you able to go back and reconstruct them and then do \dL (languages) and \dx (extensions)?

The machine in question is my personal test box at home. I don't do regular backups there. If I break something I just reinstall it. So going back into the past with backups is not possible for me. The only thing that I kept running a long time now is the Patroni cluster because I have some data stored in it. But this is the only "history" there is. However, \dx and \dL do not show any Python extension or language on the Patroni cluster too, which is still 12.4.

Does:

SELECT
lanname, proname, probin
FROM
pg_proc
JOIN
pg_language
ON
pg_language.oid = pg_proc.prolang
WHERE
pg_language.lanname='plpythonu'
AND
probin IS NOT NULL;

show anything? This would need to be repeated for each cluster in database.

Still, thanks for helping.

Cheers,
Paul

--
Adrian Klaver
adrian.klaver@aklaver.com

On 9/27/20 10:30 AM, Adrian Klaver wrote:

On 9/27/20 2:00 AM, Paul Förster wrote:

Hi Adrian,

On 27. Sep, 2020, at 00:09, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
Could it be that at some point in these instances history plpython*
where installed as CREATE LANGUAGE and you are dealing with the
vestiges of that?

I do know for sure that that never happened because the database
clusters with this effect are my personal test databases and I never
used Python. I did some tests with plperl and plperlu, though, but
never python, because I don't "speak" python.

What is possible is, those databases are clones of a Patroni cluster
database (primary) I used to experiment with. I just copied them to
new PGDATAs back then and changed PGPORT of course. I know, I could
have done initdb and pg_dumpall but just copying the whole database
cluster was the fast way to go, even more so as the PostgreSQL
software was exactly the same. Just PGDATA and PGPORT changed for the
clone. From what I know this is a perfectly legal way to do it as long
as the source database cluster is properly shut down during the copy
process.

Maybe Patroni did it then implicitly? I'm not sure how Patroni works
internally but I know that it is written in Python. Maybe it does
install something in the database which I don't know and can't find? I
tried searching for anything owned by "replicator" but can't find
anything.

Are you able to go back and reconstruct them and then do \dL
(languages) and \dx (extensions)?

The machine in question is my personal test box at home. I don't do
regular backups there. If I break something I just reinstall it. So
going back into the past with backups is not possible for me. The only
thing that I kept running a long time now is the Patroni cluster
because I have some data stored in it. But this is the only "history"
there is. However, \dx and \dL do not show any Python extension or
language on the Patroni cluster too, which is still 12.4.

Does:

SELECT
    lanname, proname, probin
FROM
    pg_proc
JOIN
    pg_language
ON
    pg_language.oid = pg_proc.prolang
WHERE
    pg_language.lanname='plpythonu'
AND
   probin IS NOT NULL;

show anything? This would need to be repeated for each cluster in database.

Really? Make that for each database in cluster!

Still, thanks for helping.

Cheers,
Paul

--
Adrian Klaver
adrian.klaver@aklaver.com