Обсуждение: BUG #16178: DROP LANGUAGE plpythonu; doesn't actually drop language.

The following bug has been logged on the website:

Bug reference:      16178
Logged by:          Indrek Kalluste
Email address:      indreek@gmail.com
PostgreSQL version: 11.6
Operating system:   Ubuntu 18
Description:

If you run script:

CREATE SCHEMA IF NOT EXISTS moninfo_2ndq;
CREATE OR REPLACE LANGUAGE plpythonu;


CREATE TYPE moninfo_2ndq.mondata_int AS (name text, value bigint);

CREATE OR REPLACE FUNCTION moninfo_2ndq.pg_wal_info()
RETURNS SETOF moninfo_2ndq.mondata_int AS
$$
  import os

  filelist = os.listdir('pg_wal')
  yield 'PGSERVER.pg_xlog_files', len(filelist)

  dirsize = 0
  for filename in filelist:
    dirsize += os.path.getsize('pg_wal/'+filename)
  
  yield 'PGSERVER.pg_xlog_size', dirsize

$$ language plpythonu security definer;

select * from moninfo_2ndq.pg_wal_info();

---------------------
Exit postgres
---------------------

DROP SCHEMA IF EXISTS moninfo_2ndq CASCADE;
CREATE SCHEMA IF NOT EXISTS moninfo_2ndq;
CREATE TYPE moninfo_2ndq.mondata_int AS (name text, value bigint);

DROP LANGUAGE plpythonu;
CREATE EXTENSION plpython3u;

DROP FUNCTION IF EXISTS moninfo_2ndq.pg_wal_info();
CREATE OR REPLACE FUNCTION moninfo_2ndq.pg_wal_info()
    RETURNS SETOF moninfo_2ndq.mondata_int AS
$$
  import os

  if(os.path.exists('pg_xlog')):
    wal_dir = 'pg_xlog/'
  else:
    wal_dir = 'pg_wal/'

  filelist = os.listdir(wal_dir)

  yield 'PGSERVER.pg_wal_files', len(filelist)

  dirsize = 0
  for filename in filelist:
    dirsize += os.path.getsize(os.path.join(wal_dir,filename))

  yield 'PGSERVER.pg_wal_size', dirsize

$$ language plpython3u security definer;

select * from moninfo_2ndq.pg_wal_info();

#######
And try to run pg_upgrade check, it fails

/usr/lib/postgresql/12/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/11/main \
--new-datadir=/var/lib/postgresql/12/main \
--old-bindir=/usr/lib/postgresql/11/bin \
--new-bindir=/usr/lib/postgresql/12/bin \
--old-options '-c config_file=/etc/postgresql/11/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
--check

I get error: 
could not load library "$libdir/plpython2": ERROR:  could not access file
"$libdir/plpython2": No such file or directory
Database: test

Doing the same thing with extensions works fine.

PG Bug reporting form <noreply@postgresql.org> writes:
> [ assorted manual manipulation of plpythonu language ]

> Doing the same thing with extensions works fine.

This is in the category of "if it breaks you get to keep both pieces".
We don't really support installing PLs via any other means except their
extensions anymore.

In the case at hand, I believe what's biting you is that CREATE LANGUAGE
auto-created the language's support functions (because the alternative
would be to fail entirely) but DROP LANGUAGE didn't drop them.  Nobody
is going to work on changing that.  The actual likely future direction
of development is for the auto-creation behavior to go away [1], so
there would hardly be any point in hacking up DROP LANGUAGE.

I'd suggest manually dropping the support functions
(plpython_call_handler, plpython_inline_handler, plpython_validator)
to get back to an upgradable state.  Or you could install plpython2
in the new installation.

            regards, tom lane

[1] https://www.postgresql.org/message-id/flat/5889.1566415762%40sss.pgh.pa.us