Re: pg_upgrade Python version issue on openSUSE

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