Thread: pg_upgrade Python version issue on openSUSE
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
ofthem uses plpython, so I definitely need it. To better be safe than sorry, I want the issue resolved before
migrationsstart, even for database clusters which don't need it.
Any help would be appreciated. Thanks very much in advance.
Cheers,
Paul
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
ofthem uses plpython, so I definitely need it. To better be safe than sorry, I want the issue resolved before
migrationsstart, 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.9yet, 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.9yet, 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 yearsnow 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 softwarewas compiled exactly the same way, only without ICU support. The other configure options were the same. Other thanthat, 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
databasesfrom 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
databasesfrom 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
databasesfrom 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 databasesfrom 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, eachdrop/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 extensiondidn'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 reallycurious 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 tomake plpythonu be equivalent to plpython3u, keeping in mind that this would make their installation incompatible with mostof 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: "keepingin 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 notin the company. So I will run into trouble if I install something here to make something else work, and then take it tothe company where it does not work. So, to maintain the highest level of compatibility to the machines at work, I don'tinstall extra packages. But yes, that would be the second most proper solution. The first and utmost proper solution would be for Python to finallystop 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 butnot in the company. So I will run into trouble if I install something here to make something else work, and then takeit 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 finallystop 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 incrementalupgrade. 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 theyjust 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 incrementalupgrade. > > 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 theyjust 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 databasesfrom 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 pressingtab. So PostgreSQL knew about them. Still, as you can see, I could drop pypythonu again though it did not appearin 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 databasesfrom within a database > plpython3u | 1.0 | t | t | f | pg_catalog | | PL/Python3U untrusted procedurallanguage > 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 dealingwith the vestiges of that? I do know for sure that that never happened because the database clusters with this effect are my personal test databasesand 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 justcopied them to new PGDATAs back then and changed PGPORT of course. I know, I could have done initdb and pg_dumpall butjust copying the whole database cluster was the fast way to go, even more so as the PostgreSQL software was exactly thesame. Just PGDATA and PGPORT changed for the clone. From what I know this is a perfectly legal way to do it as long asthe 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 anythingowned 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 reinstallit. So going back into the past with backups is not possible for me. The only thing that I kept running a long timenow is the Patroni cluster because I have some data stored in it. But this is the only "history" there is. However, \dxand \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
dealingwith the vestiges of that?
>
> I do know for sure that that never happened because the database clusters with this effect are my personal test
databasesand 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
justcopied them to new PGDATAs back then and changed PGPORT of course. I know, I could have done initdb and pg_dumpall
butjust copying the whole database cluster was the fast way to go, even more so as the PostgreSQL software was exactly
thesame. Just PGDATA and PGPORT changed for the clone. From what I know this is a perfectly legal way to do it as long
asthe 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
anythingowned 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
justreinstall it. So going back into the past with backups is not possible for me. The only thing that I kept running a
longtime 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
Hi Adrian, > On 27. Sep, 2020, at 19:30, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > 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. nope, nothing on any database, not even on template1. template0 does not allow connections. Cheers, Paul
On 9/28/20 12:46 AM, Paul Förster wrote: > Hi Adrian, > >> On 27. Sep, 2020, at 19:30, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> >> 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. > > nope, nothing on any database, not even on template1. template0 does not allow connections. Well I'm out of ideas. That means circling back to having Python 2 installed, should the powers that be agree. > > Cheers, > Paul > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian, > On 28. Sep, 2020, at 15:34, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > Well I'm out of ideas. That means circling back to having Python 2 installed, should the powers that be agree. they don't. But fortunately, it seems that the number of applications which use Python code inside a database, seems rathersmall. One of them is pgwatch2. I will check if there's an update which then uses Python 3. Thanks for helping. Cheers, Paul
On 9/28/20 6:37 AM, Paul Förster wrote: > Hi Adrian, > >> On 28. Sep, 2020, at 15:34, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> >> Well I'm out of ideas. That means circling back to having Python 2 installed, should the powers that be agree. > > they don't. But fortunately, it seems that the number of applications which use Python code inside a database, seems rathersmall. > > One of them is pgwatch2. I will check if there's an update which then uses Python 3. So pgwatch2 installs functions that use plpythonu? How does that work if there is no plpython language installed? > > Thanks for helping. > > Cheers, > Paul > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian, > On 28. Sep, 2020, at 16:03, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > So pgwatch2 installs functions that use plpythonu? > How does that work if there is no plpython language installed? at work, the extension is installed everywhere. But it seems we have an old version. The current version seems to use plpython3u. https://github.com/cybertec-postgresql/pgwatch2 Or more specifically: https://github.com/cybertec-postgresql/pgwatch2#integration-of-os-level-metrics At least, at a very quick first look, this one is updated and hence off the list to check. Ok, but this is going to be off-topic. Cheers, Paul
On 9/28/20 7:22 AM, Paul Förster wrote: > Hi Adrian, > >> On 28. Sep, 2020, at 16:03, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> >> So pgwatch2 installs functions that use plpythonu? >> How does that work if there is no plpython language installed? > > at work, the extension is installed everywhere. But it seems we have an old version. The current version seems to use plpython3u. > > https://github.com/cybertec-postgresql/pgwatch2 > > Or more specifically: > https://github.com/cybertec-postgresql/pgwatch2#integration-of-os-level-metrics > > At least, at a very quick first look, this one is updated and hence off the list to check. > > Ok, but this is going to be off-topic. Not necessarily, if it is installing plpythonu functions. > > Cheers, > Paul > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian, > On 28. Sep, 2020, at 16:30, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > Not necessarily, if it is installing plpythonu functions. I'll have to check that anyway. I'm already logged out of work, so I won't do that now. 😇 Cheers, Paul
On 2020-09-26 17:54:31 +0200, 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?
Python 2 is officially unsupported by the Python team since January 1st
2020. There has been one final release after that, but that contained
only bug fixes which were already pending before that date.
So as far as the Python team is concerned, there is only one Python and
that is Python 3.
But there is still a lot of legacy software out there and there are OSs
with very long (like 10 years) maintenance periods. So in practical
terms, Python 2 isn't dead, it just smells funny.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"