Thread: pg_upgrade Python version issue on openSUSE

pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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




Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Tom Lane
Date:
=?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



Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Tom Lane
Date:
=?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



Re: pg_upgrade Python version issue on openSUSE

From
Adrian Klaver
Date:
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



Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Adrian Klaver
Date:
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



Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Adrian Klaver
Date:
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



Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Adrian Klaver
Date:
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



Re: pg_upgrade Python version issue on openSUSE

From
Adrian Klaver
Date:
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



Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Adrian Klaver
Date:
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



Re: pg_upgrade Python version issue on openSUSE

From
Adrian Klaver
Date:
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



Re: pg_upgrade Python version issue on openSUSE

From
Adrian Klaver
Date:
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



Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Adrian Klaver
Date:
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



Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Adrian Klaver
Date:
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



Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
Adrian Klaver
Date:
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



Re: pg_upgrade Python version issue on openSUSE

From
Paul Förster
Date:
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


Re: pg_upgrade Python version issue on openSUSE

From
"Peter J. Holzer"
Date:
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!"

Attachment