Thread: Resolving the python 2 -> python 3 mess

Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

17 February 2020, 19:49:38

We've had multiple previous discussions of $SUBJECT (eg [1][2]),
without any resolution of what to do exactly. Thinking about this
some more, I had an idea that I don't think has been discussed.
To wit:

1. On platforms where Python 2.x is still supported, recommend that
packagers continue to build both plpython2 and plpython3, same as now.

2. On platforms where Python 2.x is no longer supported, transparently
map plpythonu and plpython2u to plpython3u. "Transparent" meaning that
dump/reload or pg_upgrade of existing plpythonu/plpython2u functions
will work, but when you run them, what you actually get is Python 3.x.

For existing functions that don't use any obsolete Python syntax
(which one would hope is a pretty large percentage), this is a
zero-effort conversion for users. If a function does use obsolete
constructs, it will get a parse failure when executed, and the user
will have to update it to Python 3 syntax. I propose that we make
that case reasonably painless by providing the conversion script
I posted in [3] (or another one if somebody's got a better one),
bundled as a separately-installable extension.

A possible gotcha in this approach is if there are any python 2/3
incompatibilities that would not manifest as syntax errors or
obvious runtime errors, but would allow old code to execute and
silently do the wrong thing. One would hope that the Python crowd
weren't dumb enough to do that, but I don't know whether it's true.
If there are nasty cases like that, maybe what we have to do is allow
plpythonu/plpython2u functions to be dumped and reloaded into a
python-3-only install, but refuse to execute them until they've
been converted.

In either case, to allow dump/reload or pg_upgrade to work without
ugly hacks, what we need to do is provide a stub version of
plpython2.so. (The extension definitions that sit on top of it
then don't need to change.) The stub would either redirect calls
to plpython3.so if we prefer that approach, or throw errors if we
prefer that approach. I envision adding a configure option that
enables build and install of this stub library while doing a
plpython3 build; packagers not planning to build a "real" plpython2
should ask for the stub instead.

The end result given the first approach is that "plpythonu" and
"plpython2u" and "plpython3u" all work and mean the same thing.
Over some long time period we might want to deprecate and remove
the "plpython2u" alias, but there would be no hurry about it.

The work involved in making this happen seems fairly minimal, and
practical to get done in time for PG 13. Perhaps there'd even be
a case for back-patching it, though I'm not going to advocate for
that here.

Thoughts?

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/5351890.TdMePpdHBD%40nb.usersys.redhat.com
[2] https://www.postgresql.org/message-id/flat/CAKmB1PGDAy9mXxSTqUchYEi4iJAA6NKVj4P5BtAzvQ9wSDUwJw%40mail.gmail.com
[3] https://www.postgresql.org/message-id/11546.1566584867%40sss.pgh.pa.us

Re: Resolving the python 2 -> python 3 mess

From

Jesse Zhang

Date:

18 February 2020, 01:57:59

Hi Tom,

I really like the "stub .so" idea, but feel pretty uncomfortable for the
"transparent" upgrade. Response inlined.

On Mon, Feb 17, 2020 at 8:49 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 2. On platforms where Python 2.x is no longer supported, transparently
> map plpythonu and plpython2u to plpython3u.  "Transparent" meaning that
> dump/reload or pg_upgrade of existing plpythonu/plpython2u functions
> will work, but when you run them, what you actually get is Python 3.x.

It's fair enough that plpythonu changes its meaning, people who really
want the stability should explicitly use plpython2u.

>
> For existing functions that don't use any obsolete Python syntax
> (which one would hope is a pretty large percentage), this is a
> zero-effort conversion for users.  If a function does use obsolete
> constructs, it will get a parse failure when executed, and the user
> will have to update it to Python 3 syntax.  I propose that we make
> that case reasonably painless by providing the conversion script
> I posted in [3] (or another one if somebody's got a better one),
> bundled as a separately-installable extension.
>
> A possible gotcha in this approach is if there are any python 2/3
> incompatibilities that would not manifest as syntax errors or
> obvious runtime errors, but would allow old code to execute and
> silently do the wrong thing.  One would hope that the Python crowd
> weren't dumb enough to do that, but I don't know whether it's true.
> If there are nasty cases like that, maybe what we have to do is allow
> plpythonu/plpython2u functions to be dumped and reloaded into a
> python-3-only install, but refuse to execute them until they've
> been converted.

"True division", one of the very first (2011, awww) few breaking changes
introduced in Python 3 [1], comes to mind. While it's not the worst
incompatibilities between Python 2 and 3, it's bad enough to give pause
to the notion that a successful parsing implies successful conversion.

[1] https://www.python.org/dev/peps/pep-0238/

Cheers,
Jesse

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

18 February 2020, 02:57:49

Jesse Zhang <sbjesse@gmail.com> writes:
> I really like the "stub .so" idea, but feel pretty uncomfortable for the
> "transparent" upgrade. Response inlined.

Fair enough, but ...

>> 2. On platforms where Python 2.x is no longer supported, transparently
>> map plpythonu and plpython2u to plpython3u.  "Transparent" meaning that
>> dump/reload or pg_upgrade of existing plpythonu/plpython2u functions
>> will work, but when you run them, what you actually get is Python 3.x.

> It's fair enough that plpythonu changes its meaning, people who really
> want the stability should explicitly use plpython2u.

Yeah, but then what do you want to do with functions declared plpython2u?
Have them fail even if they'd work fine under Python 3?  Doesn't really
seem like that's helping anyone.

>> A possible gotcha in this approach is if there are any python 2/3
>> incompatibilities that would not manifest as syntax errors or
>> obvious runtime errors, but would allow old code to execute and
>> silently do the wrong thing.

> "True division", one of the very first (2011, awww) few breaking changes
> introduced in Python 3 [1], comes to mind. While it's not the worst
> incompatibilities between Python 2 and 3, it's bad enough to give pause
> to the notion that a successful parsing implies successful conversion.

Hm.  I agree that's kind of nasty, because 2to3 doesn't fix it AFAICT
(and, likely, there is no way to do so that doesn't include solving
the halting problem).  However, it's not clear to me why forcing users
to do a conversion is going to help them any with that, precisely
because the automated conversion won't fix it.  They're going to have
to find such issues the hard way whenever they move to Python 3, no
matter what we do.

            regards, tom lane

Re: Resolving the python 2 -> python 3 mess

From

Corey Huinker

Date:

18 February 2020, 04:25:41

A possible gotcha in this approach is if there are any python 2/3
incompatibilities that would not manifest as syntax errors or
obvious runtime errors, but would allow old code to execute and
silently do the wrong thing. One would hope that the Python crowd
weren't dumb enough to do that, but I don't know whether it's true.
If there are nasty cases like that, maybe what we have to do is allow
plpythonu/plpython2u functions to be dumped and reloaded into a
python-3-only install, but refuse to execute them until they've
been converted.

Unfortunately, I think there are cases like that. The shift to Unicode as the default string means that some functions that used to return a `str` now return a `bytes` (I know of this in the hashlib and base64 modules, but probably also in URL request data and others), and to use a `bytes` in string manipulation you have to first explicitly convert it to some string encoding. So things like a function that wraps around a python crypto library would be the exact places where those was-str-now-bytes functions would be used.

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

18 February 2020, 18:59:11

Corey Huinker <corey.huinker@gmail.com> writes:
>> A possible gotcha in this approach is if there are any python 2/3
>> incompatibilities that would not manifest as syntax errors or
>> obvious runtime errors, but would allow old code to execute and
>> silently do the wrong thing.

> Unfortunately, I think there are cases like that. The shift to Unicode as
> the default string means that some functions that used to return a `str`
> now return a `bytes` (I know of this in the hashlib and base64 modules, but
> probably also in URL request data and others), and to use a `bytes` in
> string manipulation you have to first explicitly convert it to some string
> encoding. So things like a function that wraps around a python crypto
> library would be the exact places where those was-str-now-bytes functions
> would be used.

So, as with Jesse's example, what I'm wondering is whether or not 2to3
will fix that for you (or even flag it).  The basic difference between
the two alternatives I suggested is whether we force people to put their
python function through that converter before we'll even try to run it.
Subtleties that 2to3 doesn't catch seem like non-reasons to insist on
applying it.

            regards, tom lane

Re: Resolving the python 2 -> python 3 mess

From

Corey Huinker

Date:

18 February 2020, 22:37:10

So, as with Jesse's example, what I'm wondering is whether or not 2to3
will fix that for you (or even flag it). The basic difference between
the two alternatives I suggested is whether we force people to put their
python function through that converter before we'll even try to run it.
Subtleties that 2to3 doesn't catch seem like non-reasons to insist on
applying it.

The 2018 vintage of 2to3 didn't catch it.

It's not firsthand knowledge, but I just watched a nearby team have some production issues where one library couldn't fetch b'http://foo.org' so I'm guessing 2to3 still doesn't catch those things, or they stopped using it.

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

19 February 2020, 07:39:28

After thinking about this awhile longer, I'm starting to believe
we should do some of each.  That is, the stub replacement for
plpython2.so should redirect "plpythonu" functions to plpython3.so,
but throw errors for "plpython2u" functions.  This is not because
of any technical difference between plpythonu and plpython2u ---
up to now, there wasn't any --- but because it seems like users
would be expecting that if they've read what we have said in

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

Admittedly, what it says there is that plpythonu might become
Python 3 in some "distant" future release, not next year.
But at least there's a direct line between that documentation
and this behavior.

So attached is a pair of draft patches that do it like that.
0001 creates an extension with two conversion functions, based
on the script I showed in the other thread.  Almost independently
of that, 0002 provides code to generate a stub version of
plpython2.so that behaves as stated above.  0002 is incomplete,
because I haven't looked into what is needed in the MSVC build
scripts.  Maybe we could create some regression tests, too.
But I think these are potentially committable with those additions,
if people approve of this approach.

            regards, tom lane

diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index 1921915..ac989a3 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -164,13 +164,6 @@
   </para>

   <para>
-   See also the
-   document <ulink url="https://docs.python.org/3/whatsnew/3.0.html">What's
-   New In Python 3.0</ulink> for more information about porting to
-   Python 3.
-  </para>
-
-  <para>
    It is not allowed to use PL/Python based on Python 2 and PL/Python
    based on Python 3 in the same session, because the symbols in the
    dynamic modules would clash, which could result in crashes of the
@@ -179,6 +172,90 @@
    a mismatch is detected.  It is possible, however, to use both
    PL/Python variants in the same database, from separate sessions.
   </para>
+
+  <sect2 id="plpython-python3-conversion">
+   <title>Converting from Python 2 to Python 3</title>
+
+   <para>
+    See the
+    document <ulink url="https://docs.python.org/3/whatsnew/3.0.html">What's
+    New In Python 3.0</ulink> for the Python community's information and
+    recommendations about porting to Python 3.
+   </para>
+
+   <para>
+    <productname>PostgreSQL</productname> provides some support for helping
+    you to convert existing Python 2 routines to Python 3.  In an
+    installation built with Python 3, there is an
+    extension <filename>convert_python3</filename> that changes functions
+    and procedures from the <literal>plpythonu</literal>
+    and <literal>plpython2u</literal> languages to
+    the <literal>plpython3u</literal> language.  While doing so, it applies
+    the <filename>2to3</filename> tool described in the above document to
+    the body of each such routine.
+   </para>
+
+   <para>
+    Using <filename>convert_python3</filename> can be as simple as:
+<programlisting>
+CREATE EXTENSION convert_python3;
+CALL convert_python3_all();
+</programlisting>
+    This must be done as database superuser.  If you wish, you can drop the
+    extension once you're done converting everything.
+   </para>
+
+   <para>
+    Since <filename>convert_python3</filename> is Python 3 code, be careful
+    not to install or run it in a session that has previously executed any
+    Python 2 code.  As explained above, that won't work.
+   </para>
+
+   <para>
+    <function>convert_python3_all</function> has two optional arguments: the
+    name of the conversion tool to use (by default <literal>2to3</literal>,
+    but you might for instance need to provide a full path name) and any
+    special command-line options to provide to it.  You might for example
+    want to adjust the set of <quote>fixer</quote> rules
+    that <literal>2to3</literal> applies:
+<programlisting>
+CALL convert_python3_all(options => '-f idioms -x apply');
+</programlisting>
+    See <literal>2to3</literal>'s
+    <ulink url="https://docs.python.org/3/library/2to3.html">documentation</ulink>
+    for more information.
+   </para>
+
+   <para>
+    The <filename>convert_python3</filename> extension also provides a
+    procedure that converts just one Python 2 function at a time:
+<programlisting>
+CALL convert_python3_one('myfunc(int)');
+</programlisting>
+    The argument is the target function's OID, which can be written as
+    a <type>regprocedure</type> constant (see
+    <xref linkend="datatype-oid"/>).  The main reason to use this would be
+    if you need to use different options for different functions.  It has
+    the same optional arguments as <function>convert_python3_all</function>:
+<programlisting>
+CALL convert_python3_one('otherfunc(text)', tool => '/usr/bin/2to3',
+                         options => '-f idioms');
+</programlisting>
+   </para>
+
+   <para>
+    If you have needs that go beyond this, consult the source code for
+    the <filename>convert_python3</filename> extension (it's just a
+    couple of <literal>plpython3u</literal> procedures) and adapt those
+    procedures as necessary.
+   </para>
+
+   <para>
+    Keep in mind that if you've constructed any <command>DO</command> blocks
+    that use Python 2 code, those will have to be fixed up manually,
+    wherever the source code for them exists.
+   </para>
+  </sect2>
  </sect1>

  <sect1 id="plpython-funcs">
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 0d53d3d..f0de8ff 100644
--- a/src/pl/plpython/Makefile
+++ b/src/pl/plpython/Makefile
@@ -38,6 +38,9 @@ DATA = $(NAME)u.control $(NAME)u--1.0.sql $(NAME)u--unpackaged--1.0.sql
 ifeq ($(python_majorversion),2)
 DATA += plpythonu.control plpythonu--1.0.sql plpythonu--unpackaged--1.0.sql
 endif
+ifeq ($(python_majorversion),3)
+DATA += convert_python3.control convert_python3--1.0.sql
+endif

 # header files to install - it's not clear which of these might be needed
 # so install them all.
diff --git a/src/pl/plpython/convert_python3--1.0.sql b/src/pl/plpython/convert_python3--1.0.sql
new file mode 100644
index 0000000..ad5d86e
--- /dev/null
+++ b/src/pl/plpython/convert_python3--1.0.sql
@@ -0,0 +1,148 @@
+/* convert_python3--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION convert_python3" to load this file. \quit
+
+-- This module provides two procedures, one to convert all python2
+-- functions and one to do just one.  They're nearly identical, and
+-- in principle convert_python3_all() could be written as a loop
+-- around convert_python3_one().  It's not done that way since
+-- creating a temp directory for each function in a bulk conversion
+-- could get expensive.
+
+-- For some benighted reason, lib2to3 has exactly no documented API,
+-- so we must use the command-line API "2to3" instead.  User may pass
+-- in the name of that program (in case it's not in the server's PATH)
+-- as well as any desired options for it (perhaps some -f switches).
+
+create procedure convert_python3_all(tool text default '2to3',
+                                     options text default '')
+language plpython3u as $$
+import re, subprocess, tempfile
+
+# pattern to extract just the function header from pg_get_functiondef result
+aspat = re.compile("^(.*?\nAS )", re.DOTALL)
+# pattern for replacing LANGUAGE portion
+langpat = re.compile("\n LANGUAGE plpython2?u\n")
+
+# collect info about functions to update
+rv = plpy.execute("""
+select p.oid::pg_catalog.regprocedure as funcid,
+       pg_catalog.pg_get_functiondef(p.oid) as fd,
+       prosrc as body
+from pg_catalog.pg_proc p join pg_catalog.pg_language l on p.prolang = l.oid
+where lanname in ('plpythonu', 'plpython2u')
+""")
+
+# Make a temp directory to hold the file for 2to3 to work on.
+with tempfile.TemporaryDirectory() as tmpdirname:
+
+    # process each function
+    for r in rv:
+        # emit notices so user can tell which function failed, if one does
+        plpy.notice("converting function " + r["funcid"])
+
+        # extract everything but the body from pg_get_functiondef result
+        m = aspat.match(r["fd"])
+        if not m:
+            raise ValueError('unexpected match failure')
+        fheader = m.group(1)
+
+        # replace the language clause
+        fheader = langpat.sub("\n LANGUAGE plpython3u\n", fheader, 1)
+
+        # put body in a temp file so we can apply 2to3
+        f = open(tmpdirname + "/temp.py", mode = 'w')
+        f.write(r["body"])
+        f.close()
+
+        # apply 2to3 to body
+        subprocess.check_call(tool + " " + options + " --no-diffs -w " + tmpdirname + "/temp.py", shell=True)
+        f = open(tmpdirname + "/temp.py", mode = 'r')
+        fbody = f.read()
+        f.close()
+
+        # ensure check_function_bodies is enabled
+        plpy.execute("set local check_function_bodies = true")
+
+        # construct and execute SQL command to replace the function
+        newstmt = fheader + plpy.quote_literal(fbody)
+        # uncomment this for debugging purposes:
+        # plpy.info(newstmt)
+        plpy.execute(newstmt)
+
+        # commit after each successful replacement, in case a later one fails
+        plpy.commit()
+$$;
+
+-- The above procedure has to be superuser-only since it trivially allows
+-- executing random programs.  But you'd have to be superuser anyway
+-- to replace the definitions of plpython functions.
+
+revoke all on procedure convert_python3_all(text, text) from public;
+
+
+-- Here's the one-function version.
+
+create procedure convert_python3_one(funcid regprocedure,
+                                     tool text default '2to3',
+                                     options text default '')
+language plpython3u as $$
+import re, subprocess, tempfile
+
+# pattern to extract just the function header from pg_get_functiondef result
+aspat = re.compile("^(.*?\nAS )", re.DOTALL)
+# pattern for replacing LANGUAGE portion
+langpat = re.compile("\n LANGUAGE plpython2?u\n")
+
+# collect info about function to update, making sure it's the right language
+plan = plpy.prepare("""
+select p.oid::pg_catalog.regprocedure as funcid,
+       pg_catalog.pg_get_functiondef(p.oid) as fd,
+       prosrc as body
+from pg_catalog.pg_proc p join pg_catalog.pg_language l on p.prolang = l.oid
+where p.oid = $1 and lanname in ('plpythonu', 'plpython2u')
+""", ["pg_catalog.regprocedure"])
+
+rv = plpy.execute(plan, [funcid])
+
+# Make a temp directory to hold the file for 2to3 to work on.
+with tempfile.TemporaryDirectory() as tmpdirname:
+
+    # process each function (we only expect one, but it's easy to loop)
+    for r in rv:
+        # extract everything but the body from pg_get_functiondef result
+        m = aspat.match(r["fd"])
+        if not m:
+            raise ValueError('unexpected match failure')
+        fheader = m.group(1)
+
+        # replace the language clause
+        fheader = langpat.sub("\n LANGUAGE plpython3u\n", fheader, 1)
+
+        # put body in a temp file so we can apply 2to3
+        f = open(tmpdirname + "/temp.py", mode = 'w')
+        f.write(r["body"])
+        f.close()
+
+        # apply 2to3 to body
+        subprocess.check_call(tool + " " + options + " --no-diffs -w " + tmpdirname + "/temp.py", shell=True)
+        f = open(tmpdirname + "/temp.py", mode = 'r')
+        fbody = f.read()
+        f.close()
+
+        # ensure check_function_bodies is enabled
+        plpy.execute("set local check_function_bodies = true")
+
+        # construct and execute SQL command to replace the function
+        newstmt = fheader + plpy.quote_literal(fbody)
+        # uncomment this for debugging purposes:
+        # plpy.info(newstmt)
+        plpy.execute(newstmt)
+$$;
+
+-- The above procedure has to be superuser-only since it trivially allows
+-- executing random programs.  But you'd have to be superuser anyway
+-- to replace the definitions of plpython functions.
+
+revoke all on procedure convert_python3_one(regprocedure, text, text) from public;
diff --git a/src/pl/plpython/convert_python3.control b/src/pl/plpython/convert_python3.control
new file mode 100644
index 0000000..8debb3b
--- /dev/null
+++ b/src/pl/plpython/convert_python3.control
@@ -0,0 +1,5 @@
+# convert_python3 extension
+comment = 'convert plpython[2]u functions to plpython3u'
+default_version = '1.0'
+relocatable = true
+requires = 'plpython3u'
diff --git a/configure b/configure
index 37aa82d..4887dee 100755
--- a/configure
+++ b/configure
@@ -714,6 +714,7 @@ with_ldap
 with_krb_srvnam
 krb_srvtab
 with_gssapi
+with_python_stub
 with_python
 with_perl
 with_tcl
@@ -847,6 +848,7 @@ with_tcl
 with_tclconfig
 with_perl
 with_python
+with_python_stub
 with_gssapi
 with_krb_srvnam
 with_pam
@@ -1546,6 +1548,7 @@ Optional Packages:
   --with-tclconfig=DIR    tclConfig.sh is in DIR
   --with-perl             build Perl modules (PL/Perl)
   --with-python           build Python modules (PL/Python)
+  --with-python-stub      build Python 2 compatibility stub
   --with-gssapi           build with GSSAPI support
   --with-krb-srvnam=NAME  default service principal name in Kerberos (GSSAPI)
                           [postgres]
@@ -7644,6 +7647,32 @@ fi
 $as_echo "$with_python" >&6; }


+
+
+
+# Check whether --with-python-stub was given.
+if test "${with_python_stub+set}" = set; then :
+  withval=$with_python_stub;
+  case $withval in
+    yes)
+      :
+      ;;
+    no)
+      :
+      ;;
+    *)
+      as_fn_error $? "no argument expected for --with-python-stub option" "$LINENO" 5
+      ;;
+  esac
+
+else
+  with_python_stub=no
+
+fi
+
+
+
+
 #
 # GSSAPI
 #
@@ -9745,6 +9774,12 @@ $as_echo "${python_libspec} ${python_additional_libs}" >&6; }



+  # Disable building Python 2 stub if primary version isn't Python 3
+  if test "$python_majorversion" -lt 3; then
+    with_python_stub=no
+  fi
+else
+  with_python_stub=no
 fi

 if test "$cross_compiling" = yes && test -z "$with_system_tzdata"; then
diff --git a/configure.in b/configure.in
index 8adb409..ba2ff2d 100644
--- a/configure.in
+++ b/configure.in
@@ -766,6 +766,9 @@ PGAC_ARG_BOOL(with, python, no, [build Python modules (PL/Python)])
 AC_MSG_RESULT([$with_python])
 AC_SUBST(with_python)

+PGAC_ARG_BOOL(with, python-stub, no, [build Python 2 compatibility stub])
+AC_SUBST(with_python_stub)
+
 #
 # GSSAPI
 #
@@ -1042,6 +1045,12 @@ fi
 if test "$with_python" = yes; then
   PGAC_PATH_PYTHON
   PGAC_CHECK_PYTHON_EMBED_SETUP
+  # Disable building Python 2 stub if primary version isn't Python 3
+  if test "$python_majorversion" -lt 3; then
+    with_python_stub=no
+  fi
+else
+  with_python_stub=no
 fi

 if test "$cross_compiling" = yes && test -z "$with_system_tzdata"; then
diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml
index f1adcc3..2cf34d2 100644
--- a/doc/src/sgml/installation.sgml
+++ b/doc/src/sgml/installation.sgml
@@ -877,6 +877,23 @@ build-postgresql:
       </varlistentry>

       <varlistentry>
+       <term><option>--with-python-stub</option></term>
+       <listitem>
+        <para>
+         Build a stub version of the Python
+         2 <application>PL/Python</application> language, to aid in
+         transitioning old <application>PL/Python</application> code.
+         This option is recommended if you are
+         building <application>PL/Python</application> for Python 3
+         and do not intend to also build a version for Python 2.
+         This option is ignored if you do not also
+         specify <option>--with-python</option>, or if the selected Python
+         implementation is Python 2.
+        </para>
+       </listitem>
+      </varlistentry>
+
+      <varlistentry>
        <term><option>--with-tcl</option></term>
        <listitem>
         <para>
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index 1921915..14b9a4b 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -77,13 +77,7 @@
      <para>
       The language named <literal>plpythonu</literal> implements
       PL/Python based on the default Python language variant, which is
-      currently Python 2.  (This default is independent of what any
-      local Python installations might consider to be
-      their <quote>default</quote>, for example,
-      what <filename>/usr/bin/python</filename> might be.)  The
-      default will probably be changed to Python 3 in a distant future
-      release of PostgreSQL, depending on the progress of the
-      migration to Python 3 in the Python community.
+      determined by the person who builds and installs PostgreSQL.
      </para>
     </listitem>
    </itemizedlist>
@@ -103,9 +97,22 @@
     The built variant depends on which Python version was found during
     the installation or which version was explicitly set using
     the <envar>PYTHON</envar> environment variable;
-    see <xref linkend="install-procedure"/>.  To make both variants of
-    PL/Python available in one installation, the source tree has to be
-    configured and built twice.
+    see <xref linkend="install-procedure"/>.  To make working versions of
+    both variants of PL/Python available in one installation, the source
+    tree has to be configured and built twice.
+   </para>
+
+   <para>
+    However, if the builder specifies both <option>--with-python</option>
+    and <option>--with-python-stub</option> while building with Python 3,
+    then in addition to the working Python-3-based PL/Python,
+    a <quote>stub</quote> version of <literal>plpython2u</literal> is
+    produced.  This stub is non-functional and will simply throw errors if
+    any <literal>plpython2u</literal> function is executed, but its
+    presence allows such functions to be loaded into the database in
+    preparation for conversion to <literal>plpython3u</literal>.  This
+    configuration also causes <literal>plpythonu</literal> to become
+    Python 3 rather than Python 2.
    </para>
   </tip>

@@ -115,49 +122,38 @@
    <itemizedlist>
     <listitem>
      <para>
-      Existing users and users who are currently not interested in
-      Python 3 use the language name <literal>plpythonu</literal> and
-      don't have to change anything for the foreseeable future.  It is
-      recommended to gradually <quote>future-proof</quote> the code
-      via migration to Python 2.6/2.7 to simplify the eventual
-      migration to Python 3.
+      Use the language name <literal>plpythonu</literal> if you have
+      simple Python code that works for either Python 2 or Python 3;
+      or if you are prepared to migrate your code whenever your packager
+      decides to change the default Python version.
      </para>

      <para>
       In practice, many PL/Python functions will migrate to Python 3
-      with few or no changes.
+      with few or no changes.  However, there are some cases that
+      will require more work.
      </para>
     </listitem>

     <listitem>
      <para>
-      Users who know that they have heavily Python 2 dependent code
-      and don't plan to ever change it can make use of
+      If you know that you have heavily Python 2 dependent code
+      and don't plan to ever change it, you can make use of
       the <literal>plpython2u</literal> language name.  This will
-      continue to work into the very distant future, until Python 2
-      support might be completely dropped by PostgreSQL.
-     </para>
-    </listitem>
-
-    <listitem>
-     <para>
-      Users who want to dive into Python 3 can use
-      the <literal>plpython3u</literal> language name, which will keep
-      working forever by today's standards.  In the distant future,
-      when Python 3 might become the default, they might like to
-      remove the <quote>3</quote> for aesthetic reasons.
+      continue to work for as long as your operating system platform
+      continues to provide Python 2.  PostgreSQL itself may eventually
+      drop support for this option, but that is unlikely to happen
+      as long as any platform support remains in the wild.
      </para>
     </listitem>

     <listitem>
      <para>
-      Daredevils, who want to build a Python-3-only operating system
-      environment, can change the contents of
-      <literal>plpythonu</literal>'s extension control and script files
-      to make <literal>plpythonu</literal> be equivalent
-      to <literal>plpython3u</literal>, keeping in mind that this
-      would make their installation incompatible with most of the rest
-      of the world.
+      If you know your code requires Python 3, use
+      the <literal>plpython3u</literal> language name.  You can also
+      use this language name to tag functions that have been successfully
+      converted from Python 2, so as to keep track of which functions have
+      been converted and which have not.
      </para>
     </listitem>
    </itemizedlist>
@@ -171,6 +167,8 @@
    Python major versions in a session, which will abort the session if
    a mismatch is detected.  It is possible, however, to use both
    PL/Python variants in the same database, from separate sessions.
+   (Also, the <quote>stub</quote> version of <literal>plpython2u</literal>
+   does not present any conflicts.)
   </para>

   <sect2 id="plpython-python3-conversion">
diff --git a/src/Makefile.global.in b/src/Makefile.global.in
index e4db3e8..f039fb4 100644
--- a/src/Makefile.global.in
+++ b/src/Makefile.global.in
@@ -180,6 +180,7 @@ bitcodedir = $(pkglibdir)/bitcode
 with_icu    = @with_icu@
 with_perl    = @with_perl@
 with_python    = @with_python@
+with_python_stub = @with_python_stub@
 with_tcl    = @with_tcl@
 with_openssl    = @with_openssl@
 with_readline    = @with_readline@
diff --git a/src/pl/Makefile b/src/pl/Makefile
index c4a0d1c..c011607 100644
--- a/src/pl/Makefile
+++ b/src/pl/Makefile
@@ -26,6 +26,12 @@ else
 ALWAYS_SUBDIRS += plpython
 endif

+ifeq ($(with_python_stub), yes)
+SUBDIRS += stub_plpython2
+else
+ALWAYS_SUBDIRS += stub_plpython2
+endif
+
 ifeq ($(with_tcl), yes)
 SUBDIRS += tcl
 else
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 0d53d3d..e40844b 100644
--- a/src/pl/plpython/Makefile
+++ b/src/pl/plpython/Makefile
@@ -37,6 +37,9 @@ OBJS = \
 DATA = $(NAME)u.control $(NAME)u--1.0.sql $(NAME)u--unpackaged--1.0.sql
 ifeq ($(python_majorversion),2)
 DATA += plpythonu.control plpythonu--1.0.sql plpythonu--unpackaged--1.0.sql
+else ifeq ($(with_python_stub), yes)
+# install extension files for the stub module (see ../stub_plpython2)
+DATA += plpythonu.control plpythonu--1.0.sql plpython2u.control plpython2u--1.0.sql
 endif
 ifeq ($(python_majorversion),3)
 DATA += convert_python3.control convert_python3--1.0.sql
diff --git a/src/pl/stub_plpython2/Makefile b/src/pl/stub_plpython2/Makefile
new file mode 100644
index 0000000..67e7559
--- /dev/null
+++ b/src/pl/stub_plpython2/Makefile
@@ -0,0 +1,38 @@
+# src/pl/stub_plpython2/Makefile
+
+# Note that this Makefile only builds and installs a quasi-dummy
+# version of plpython2.so.  The control and script files for the
+# plpythonu and plpython2u extensions are installed by ../plpython;
+# they are the same whether we're using real or stub plpython2.
+# (We'd probably not have this separate subdirectory at all, except
+# that Makefile.shlib can only build one shlib per directory.)
+
+subdir = src/pl/stub_plpython2
+top_builddir = ../../..
+include $(top_builddir)/src/Makefile.global
+
+PGFILEDESC = "PL/Python - procedural language stub for Python 2"
+
+NAME = plpython2
+
+OBJS = \
+    $(WIN32RES) \
+    stub_plpython2.o
+
+include $(top_srcdir)/src/Makefile.shlib
+
+all: all-lib
+
+# Ensure parallel safety if a build is started in this directory
+$(OBJS): | submake-generated-headers
+
+install: all install-lib
+
+installdirs: installdirs-lib
+
+uninstall: uninstall-lib
+
+clean distclean: clean-lib
+    rm -f $(OBJS)
+
+maintainer-clean: distclean
diff --git a/src/pl/stub_plpython2/stub_plpython2.c b/src/pl/stub_plpython2/stub_plpython2.c
new file mode 100644
index 0000000..4ade88b
--- /dev/null
+++ b/src/pl/stub_plpython2/stub_plpython2.c
@@ -0,0 +1,105 @@
+/*
+ * PL/Python stub for Python 2, in an environment that has only Python 3
+ *
+ * Our strategy is to pass through "plpythonu" functions to Python 3,
+ * but throw a not-implemented error for "plpython2u".
+ *
+ * Pass-through is implemented by using dfmgr.c to look up the appropriate
+ * function in plpython3.so, rather than trying to resolve the reference
+ * directly.  This greatly simplifies building this as an independent
+ * shared library, and it ensures that we can't somehow pull in a different
+ * version of plpython3 (and thence libpython) than would get loaded for
+ * a plpython3u function.
+ *
+ * src/pl/stub_plpython2/stub_plpython2.c
+ */
+
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#define PLPYTHON_LIBNAME "$libdir/plpython3"
+
+/*
+ * exported functions
+ */
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(plpython_validator);
+PG_FUNCTION_INFO_V1(plpython_call_handler);
+PG_FUNCTION_INFO_V1(plpython_inline_handler);
+
+PG_FUNCTION_INFO_V1(plpython2_validator);
+PG_FUNCTION_INFO_V1(plpython2_call_handler);
+PG_FUNCTION_INFO_V1(plpython2_inline_handler);
+
+
+Datum
+plpython_validator(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_validator = NULL;
+
+    if (plpython3_validator == NULL)
+        plpython3_validator =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_validator",
+                                   true, NULL);
+
+    return (*plpython3_validator) (fcinfo);
+}
+
+Datum
+plpython_call_handler(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_call_handler = NULL;
+
+    if (plpython3_call_handler == NULL)
+        plpython3_call_handler =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_call_handler",
+                                   true, NULL);
+
+    return (*plpython3_call_handler) (fcinfo);
+}
+
+Datum
+plpython_inline_handler(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_inline_handler = NULL;
+
+    if (plpython3_inline_handler == NULL)
+        plpython3_inline_handler =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_inline_handler",
+                                   true, NULL);
+
+    return (*plpython3_inline_handler) (fcinfo);
+}
+
+Datum
+plpython2_validator(PG_FUNCTION_ARGS)
+{
+    /* It seems more convenient to do nothing here than throw an error. */
+    PG_RETURN_VOID();
+}
+
+Datum
+plpython2_call_handler(PG_FUNCTION_ARGS)
+{
+    ereport(ERROR,
+            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+             errmsg("Python 2 is no longer supported"),
+             errhint("Convert the function to use plpython3u.")));
+    PG_RETURN_NULL();            /* keep compiler quiet */
+}
+
+Datum
+plpython2_inline_handler(PG_FUNCTION_ARGS)
+{
+    ereport(ERROR,
+            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+             errmsg("Python 2 is no longer supported"),
+             errhint("Convert the DO block to use plpython3u.")));
+    PG_RETURN_NULL();            /* keep compiler quiet */
+}

Re: Resolving the python 2 -> python 3 mess

From

Peter Eisentraut

Date:

19 February 2020, 22:42:36

On 2020-02-19 05:39, Tom Lane wrote:
> After thinking about this awhile longer, I'm starting to believe
> we should do some of each.  That is, the stub replacement for
> plpython2.so should redirect "plpythonu" functions to plpython3.so,
> but throw errors for "plpython2u" functions.

I'm not sure these complications are worth it.  They don't match 
anything that is done in other Python 2/3 porting schemes.  I think 
there should just be an option "plpython is: {2|3|don't build it at 
all}".  Then packagers can match this to what their plan for 
/usr/bin/python* is -- which appears to be different everywhere.

Your scheme appears to center around the assumption that people will 
want to port their functions at the same time as not building plpython2u 
anymore.  This would defeat testing functions before and after in the 
same installation.  I think the decisions of what plpythonu points to 
and which variants are built at all should be separate.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

19 February 2020, 23:00:40

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> Your scheme appears to center around the assumption that people will 
> want to port their functions at the same time as not building plpython2u 
> anymore.

Not really; use of the proposed porting infrastructure is the same whether
plpython2u still works or not.  You end up with functions that are labeled
plpython3u, so what bare "plpythonu" means is not a factor.

It is true that as this patch is written, switching of plpythonu to
point at Python 3 rather than 2 is coupled to disabling plpython2u.
If we'd have gotten this done a year or two ago, I'd have made it more
complex to allow more separation there.  But events have passed us by:
the info we are getting from packagers is that Python 2 is getting
dropped *this year*, not in some distant future.  So I think that allowing
the plpythonu redefinition to be separate is no longer of any great value,
and not worth extra complication for.  People are just going to be
shipping v13 with both things changed in any case.

If we wanted to do something to help people port their functions in
advance of the big changeover, the thing to do would be to back-patch
the proposed convert_python3 extension into existing branches.

            regards, tom lane

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

25 February 2020, 22:57:53

Here's an updated pair of patches that attempt to fix the MSVC
scripts (pretty blindly) and provide a very simple regression test.
I'm not too sure whether the regression test will really prove
workable or not: for starters, it'll fail if "2to3" isn't available
in the PATH.  Perhaps there's reason to object to even trying to
test that, on security grounds.

I set up the MSVC scripts to default to building the stub extension.
I don't know if we really want to commit it that way, but the idea
for the moment is to try to get the cfbot to test it on Windows.

            regards, tom lane

diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index 1921915..ac989a3 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -164,13 +164,6 @@
   </para>

   <para>
-   See also the
-   document <ulink url="https://docs.python.org/3/whatsnew/3.0.html">What's
-   New In Python 3.0</ulink> for more information about porting to
-   Python 3.
-  </para>
-
-  <para>
    It is not allowed to use PL/Python based on Python 2 and PL/Python
    based on Python 3 in the same session, because the symbols in the
    dynamic modules would clash, which could result in crashes of the
@@ -179,6 +172,90 @@
    a mismatch is detected.  It is possible, however, to use both
    PL/Python variants in the same database, from separate sessions.
   </para>
+
+  <sect2 id="plpython-python3-conversion">
+   <title>Converting from Python 2 to Python 3</title>
+
+   <para>
+    See the
+    document <ulink url="https://docs.python.org/3/whatsnew/3.0.html">What's
+    New In Python 3.0</ulink> for the Python community's information and
+    recommendations about porting to Python 3.
+   </para>
+
+   <para>
+    <productname>PostgreSQL</productname> provides some support for helping
+    you to convert existing Python 2 routines to Python 3.  In an
+    installation built with Python 3, there is an
+    extension <filename>convert_python3</filename> that changes functions
+    and procedures from the <literal>plpythonu</literal>
+    and <literal>plpython2u</literal> languages to
+    the <literal>plpython3u</literal> language.  While doing so, it applies
+    the <filename>2to3</filename> tool described in the above document to
+    the body of each such routine.
+   </para>
+
+   <para>
+    Using <filename>convert_python3</filename> can be as simple as:
+<programlisting>
+CREATE EXTENSION convert_python3;
+CALL convert_python3_all();
+</programlisting>
+    This must be done as database superuser.  If you wish, you can drop the
+    extension once you're done converting everything.
+   </para>
+
+   <para>
+    Since <filename>convert_python3</filename> is Python 3 code, be careful
+    not to install or run it in a session that has previously executed any
+    Python 2 code.  As explained above, that won't work.
+   </para>
+
+   <para>
+    <function>convert_python3_all</function> has two optional arguments: the
+    name of the conversion tool to use (by default <literal>2to3</literal>,
+    but you might for instance need to provide a full path name) and any
+    special command-line options to provide to it.  You might for example
+    want to adjust the set of <quote>fixer</quote> rules
+    that <literal>2to3</literal> applies:
+<programlisting>
+CALL convert_python3_all(options => '-f idioms -x apply');
+</programlisting>
+    See <literal>2to3</literal>'s
+    <ulink url="https://docs.python.org/3/library/2to3.html">documentation</ulink>
+    for more information.
+   </para>
+
+   <para>
+    The <filename>convert_python3</filename> extension also provides a
+    procedure that converts just one Python 2 function at a time:
+<programlisting>
+CALL convert_python3_one('myfunc(int)');
+</programlisting>
+    The argument is the target function's OID, which can be written as
+    a <type>regprocedure</type> constant (see
+    <xref linkend="datatype-oid"/>).  The main reason to use this would be
+    if you need to use different options for different functions.  It has
+    the same optional arguments as <function>convert_python3_all</function>:
+<programlisting>
+CALL convert_python3_one('otherfunc(text)', tool => '/usr/bin/2to3',
+                         options => '-f idioms');
+</programlisting>
+   </para>
+
+   <para>
+    If you have needs that go beyond this, consult the source code for
+    the <filename>convert_python3</filename> extension (it's just a
+    couple of <literal>plpython3u</literal> procedures) and adapt those
+    procedures as necessary.
+   </para>
+
+   <para>
+    Keep in mind that if you've constructed any <command>DO</command> blocks
+    that use Python 2 code, those will have to be fixed up manually,
+    wherever the source code for them exists.
+   </para>
+  </sect2>
  </sect1>

  <sect1 id="plpython-funcs">
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 9e95285..03f858b 100644
--- a/src/pl/plpython/Makefile
+++ b/src/pl/plpython/Makefile
@@ -38,6 +38,9 @@ DATA = $(NAME)u.control $(NAME)u--1.0.sql
 ifeq ($(python_majorversion),2)
 DATA += plpythonu.control plpythonu--1.0.sql
 endif
+ifeq ($(python_majorversion),3)
+DATA += convert_python3.control convert_python3--1.0.sql
+endif

 # header files to install - it's not clear which of these might be needed
 # so install them all.
diff --git a/src/pl/plpython/convert_python3--1.0.sql b/src/pl/plpython/convert_python3--1.0.sql
new file mode 100644
index 0000000..3444ac0
--- /dev/null
+++ b/src/pl/plpython/convert_python3--1.0.sql
@@ -0,0 +1,149 @@
+/* convert_python3--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION convert_python3" to load this file. \quit
+
+-- This module provides two procedures, one to convert all python2
+-- functions and one to do just one.  They're nearly identical, and
+-- in principle convert_python3_all() could be written as a loop
+-- around convert_python3_one().  It's not done that way since
+-- creating a temp directory for each function in a bulk conversion
+-- could get expensive.
+
+-- For some benighted reason, lib2to3 has exactly no documented API,
+-- so we must use the command-line API "2to3" instead.  User may pass
+-- in the name of that program (in case it's not in the server's PATH)
+-- as well as any desired options for it (perhaps some -f switches).
+
+create procedure convert_python3_all(tool text default '2to3',
+                                     options text default '')
+language plpython3u as $$
+import re, subprocess, tempfile
+
+# pattern to extract just the function header from pg_get_functiondef result
+aspat = re.compile("^(.*?\nAS )", re.DOTALL)
+# pattern for replacing LANGUAGE portion
+langpat = re.compile("\n LANGUAGE plpython2?u\n")
+
+# collect info about functions to update
+rv = plpy.execute("""
+select p.oid::pg_catalog.regprocedure as funcid,
+       pg_catalog.pg_get_functiondef(p.oid) as fd,
+       prosrc as body
+from pg_catalog.pg_proc p join pg_catalog.pg_language l on p.prolang = l.oid
+where lanname in ('plpythonu', 'plpython2u')
+order by proname
+""")
+
+# Make a temp directory to hold the file for 2to3 to work on.
+with tempfile.TemporaryDirectory() as tmpdirname:
+
+    # process each function
+    for r in rv:
+        # emit notices so user can tell which function failed, if one does
+        plpy.notice("converting function " + r["funcid"])
+
+        # extract everything but the body from pg_get_functiondef result
+        m = aspat.match(r["fd"])
+        if not m:
+            raise ValueError('unexpected match failure')
+        fheader = m.group(1)
+
+        # replace the language clause
+        fheader = langpat.sub("\n LANGUAGE plpython3u\n", fheader, 1)
+
+        # put body in a temp file so we can apply 2to3
+        f = open(tmpdirname + "/temp.py", mode = 'w')
+        f.write(r["body"])
+        f.close()
+
+        # apply 2to3 to body
+        subprocess.check_call(tool + " " + options + " --no-diffs -w " + tmpdirname + "/temp.py", shell=True)
+        f = open(tmpdirname + "/temp.py", mode = 'r')
+        fbody = f.read()
+        f.close()
+
+        # ensure check_function_bodies is enabled
+        plpy.execute("set local check_function_bodies = true")
+
+        # construct and execute SQL command to replace the function
+        newstmt = fheader + plpy.quote_literal(fbody)
+        # uncomment this for debugging purposes:
+        # plpy.info(newstmt)
+        plpy.execute(newstmt)
+
+        # commit after each successful replacement, in case a later one fails
+        plpy.commit()
+$$;
+
+-- The above procedure has to be superuser-only since it trivially allows
+-- executing random programs.  But you'd have to be superuser anyway
+-- to replace the definitions of plpython functions.
+
+revoke all on procedure convert_python3_all(text, text) from public;
+
+
+-- Here's the one-function version.
+
+create procedure convert_python3_one(funcid regprocedure,
+                                     tool text default '2to3',
+                                     options text default '')
+language plpython3u as $$
+import re, subprocess, tempfile
+
+# pattern to extract just the function header from pg_get_functiondef result
+aspat = re.compile("^(.*?\nAS )", re.DOTALL)
+# pattern for replacing LANGUAGE portion
+langpat = re.compile("\n LANGUAGE plpython2?u\n")
+
+# collect info about function to update, making sure it's the right language
+plan = plpy.prepare("""
+select p.oid::pg_catalog.regprocedure as funcid,
+       pg_catalog.pg_get_functiondef(p.oid) as fd,
+       prosrc as body
+from pg_catalog.pg_proc p join pg_catalog.pg_language l on p.prolang = l.oid
+where p.oid = $1 and lanname in ('plpythonu', 'plpython2u')
+""", ["pg_catalog.regprocedure"])
+
+rv = plpy.execute(plan, [funcid])
+
+# Make a temp directory to hold the file for 2to3 to work on.
+with tempfile.TemporaryDirectory() as tmpdirname:
+
+    # process each function (we only expect one, but it's easy to loop)
+    for r in rv:
+        # extract everything but the body from pg_get_functiondef result
+        m = aspat.match(r["fd"])
+        if not m:
+            raise ValueError('unexpected match failure')
+        fheader = m.group(1)
+
+        # replace the language clause
+        fheader = langpat.sub("\n LANGUAGE plpython3u\n", fheader, 1)
+
+        # put body in a temp file so we can apply 2to3
+        f = open(tmpdirname + "/temp.py", mode = 'w')
+        f.write(r["body"])
+        f.close()
+
+        # apply 2to3 to body
+        subprocess.check_call(tool + " " + options + " --no-diffs -w " + tmpdirname + "/temp.py", shell=True)
+        f = open(tmpdirname + "/temp.py", mode = 'r')
+        fbody = f.read()
+        f.close()
+
+        # ensure check_function_bodies is enabled
+        plpy.execute("set local check_function_bodies = true")
+
+        # construct and execute SQL command to replace the function
+        newstmt = fheader + plpy.quote_literal(fbody)
+        # uncomment this for debugging purposes:
+        # plpy.info(newstmt)
+        plpy.execute(newstmt)
+$$;
+
+-- The above procedure has to be superuser-only since it trivially allows
+-- executing random programs.  But you'd have to be superuser anyway
+-- to replace the definitions of plpython functions.
+
+revoke all on procedure convert_python3_one(regprocedure, text, text) from public;
diff --git a/src/pl/plpython/convert_python3.control b/src/pl/plpython/convert_python3.control
new file mode 100644
index 0000000..8debb3b
--- /dev/null
+++ b/src/pl/plpython/convert_python3.control
@@ -0,0 +1,5 @@
+# convert_python3 extension
+comment = 'convert plpython[2]u functions to plpython3u'
+default_version = '1.0'
+relocatable = true
+requires = 'plpython3u'
diff --git a/configure b/configure
index d2c74e5..3feebf6 100755
--- a/configure
+++ b/configure
@@ -714,6 +714,7 @@ with_ldap
 with_krb_srvnam
 krb_srvtab
 with_gssapi
+with_python2_stub
 with_python
 with_perl
 with_tcl
@@ -847,6 +848,7 @@ with_tcl
 with_tclconfig
 with_perl
 with_python
+with_python2_stub
 with_gssapi
 with_krb_srvnam
 with_pam
@@ -1546,6 +1548,7 @@ Optional Packages:
   --with-tclconfig=DIR    tclConfig.sh is in DIR
   --with-perl             build Perl modules (PL/Perl)
   --with-python           build Python modules (PL/Python)
+  --with-python2-stub     build Python 2 compatibility stub
   --with-gssapi           build with GSSAPI support
   --with-krb-srvnam=NAME  default service principal name in Kerberos (GSSAPI)
                           [postgres]
@@ -7644,6 +7647,32 @@ fi
 $as_echo "$with_python" >&6; }


+
+
+
+# Check whether --with-python2-stub was given.
+if test "${with_python2_stub+set}" = set; then :
+  withval=$with_python2_stub;
+  case $withval in
+    yes)
+      :
+      ;;
+    no)
+      :
+      ;;
+    *)
+      as_fn_error $? "no argument expected for --with-python2-stub option" "$LINENO" 5
+      ;;
+  esac
+
+else
+  with_python2_stub=no
+
+fi
+
+
+
+
 #
 # GSSAPI
 #
@@ -9745,6 +9774,12 @@ $as_echo "${python_libspec} ${python_additional_libs}" >&6; }



+  # Disable building Python 2 stub if primary version isn't Python 3
+  if test "$python_majorversion" -lt 3; then
+    with_python2_stub=no
+  fi
+else
+  with_python2_stub=no
 fi

 if test "$cross_compiling" = yes && test -z "$with_system_tzdata"; then
diff --git a/configure.in b/configure.in
index 0b0a871..ffa49c9 100644
--- a/configure.in
+++ b/configure.in
@@ -766,6 +766,9 @@ PGAC_ARG_BOOL(with, python, no, [build Python modules (PL/Python)])
 AC_MSG_RESULT([$with_python])
 AC_SUBST(with_python)

+PGAC_ARG_BOOL(with, python2-stub, no, [build Python 2 compatibility stub])
+AC_SUBST(with_python2_stub)
+
 #
 # GSSAPI
 #
@@ -1042,6 +1045,12 @@ fi
 if test "$with_python" = yes; then
   PGAC_PATH_PYTHON
   PGAC_CHECK_PYTHON_EMBED_SETUP
+  # Disable building Python 2 stub if primary version isn't Python 3
+  if test "$python_majorversion" -lt 3; then
+    with_python2_stub=no
+  fi
+else
+  with_python2_stub=no
 fi

 if test "$cross_compiling" = yes && test -z "$with_system_tzdata"; then
diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml
index cc242dc..ff81ea8 100644
--- a/doc/src/sgml/installation.sgml
+++ b/doc/src/sgml/installation.sgml
@@ -877,6 +877,23 @@ build-postgresql:
       </varlistentry>

       <varlistentry>
+       <term><option>--with-python2-stub</option></term>
+       <listitem>
+        <para>
+         Build a stub version of the Python
+         2 <application>PL/Python</application> language, to aid in
+         transitioning old <application>PL/Python</application> code.
+         This option is recommended if you are
+         building <application>PL/Python</application> for Python 3
+         and do not intend to also build a version for Python 2.
+         This option is ignored if you do not also
+         specify <option>--with-python</option>, or if the selected Python
+         implementation is Python 2.
+        </para>
+       </listitem>
+      </varlistentry>
+
+      <varlistentry>
        <term><option>--with-tcl</option></term>
        <listitem>
         <para>
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index ac989a3..9014750 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -77,13 +77,7 @@
      <para>
       The language named <literal>plpythonu</literal> implements
       PL/Python based on the default Python language variant, which is
-      currently Python 2.  (This default is independent of what any
-      local Python installations might consider to be
-      their <quote>default</quote>, for example,
-      what <filename>/usr/bin/python</filename> might be.)  The
-      default will probably be changed to Python 3 in a distant future
-      release of PostgreSQL, depending on the progress of the
-      migration to Python 3 in the Python community.
+      determined by the person who builds and installs PostgreSQL.
      </para>
     </listitem>
    </itemizedlist>
@@ -103,9 +97,22 @@
     The built variant depends on which Python version was found during
     the installation or which version was explicitly set using
     the <envar>PYTHON</envar> environment variable;
-    see <xref linkend="install-procedure"/>.  To make both variants of
-    PL/Python available in one installation, the source tree has to be
-    configured and built twice.
+    see <xref linkend="install-procedure"/>.  To make working versions of
+    both variants of PL/Python available in one installation, the source
+    tree has to be configured and built twice.
+   </para>
+
+   <para>
+    However, if the builder specifies both <option>--with-python</option>
+    and <option>--with-python2-stub</option> while building with Python 3,
+    then in addition to the working Python-3-based PL/Python,
+    a <quote>stub</quote> version of <literal>plpython2u</literal> is
+    produced.  This stub is non-functional and will simply throw errors if
+    any <literal>plpython2u</literal> function is executed, but its
+    presence allows such functions to be loaded into the database in
+    preparation for conversion to <literal>plpython3u</literal>.  This
+    configuration also causes <literal>plpythonu</literal> to become
+    Python 3 rather than Python 2.
    </para>
   </tip>

@@ -115,49 +122,38 @@
    <itemizedlist>
     <listitem>
      <para>
-      Existing users and users who are currently not interested in
-      Python 3 use the language name <literal>plpythonu</literal> and
-      don't have to change anything for the foreseeable future.  It is
-      recommended to gradually <quote>future-proof</quote> the code
-      via migration to Python 2.6/2.7 to simplify the eventual
-      migration to Python 3.
+      Use the language name <literal>plpythonu</literal> if you have
+      simple Python code that works for either Python 2 or Python 3;
+      or if you are prepared to migrate your code whenever your packager
+      decides to change the default Python version.
      </para>

      <para>
       In practice, many PL/Python functions will migrate to Python 3
-      with few or no changes.
+      with few or no changes.  However, there are some cases that
+      will require more work.
      </para>
     </listitem>

     <listitem>
      <para>
-      Users who know that they have heavily Python 2 dependent code
-      and don't plan to ever change it can make use of
+      If you know that you have heavily Python 2 dependent code
+      and don't plan to ever change it, you can make use of
       the <literal>plpython2u</literal> language name.  This will
-      continue to work into the very distant future, until Python 2
-      support might be completely dropped by PostgreSQL.
-     </para>
-    </listitem>
-
-    <listitem>
-     <para>
-      Users who want to dive into Python 3 can use
-      the <literal>plpython3u</literal> language name, which will keep
-      working forever by today's standards.  In the distant future,
-      when Python 3 might become the default, they might like to
-      remove the <quote>3</quote> for aesthetic reasons.
+      continue to work for as long as your operating system platform
+      continues to provide Python 2.  PostgreSQL itself may eventually
+      drop support for this option, but that is unlikely to happen
+      as long as any platform support remains in the wild.
      </para>
     </listitem>

     <listitem>
      <para>
-      Daredevils, who want to build a Python-3-only operating system
-      environment, can change the contents of
-      <literal>plpythonu</literal>'s extension control and script files
-      to make <literal>plpythonu</literal> be equivalent
-      to <literal>plpython3u</literal>, keeping in mind that this
-      would make their installation incompatible with most of the rest
-      of the world.
+      If you know your code requires Python 3, use
+      the <literal>plpython3u</literal> language name.  You can also
+      use this language name to tag functions that have been successfully
+      converted from Python 2, so as to keep track of which functions have
+      been converted and which have not.
      </para>
     </listitem>
    </itemizedlist>
@@ -171,6 +167,8 @@
    Python major versions in a session, which will abort the session if
    a mismatch is detected.  It is possible, however, to use both
    PL/Python variants in the same database, from separate sessions.
+   (Also, the <quote>stub</quote> version of <literal>plpython2u</literal>
+   does not present any conflicts.)
   </para>

   <sect2 id="plpython-python3-conversion">
diff --git a/src/Makefile.global.in b/src/Makefile.global.in
index e4db3e8..1f880b6 100644
--- a/src/Makefile.global.in
+++ b/src/Makefile.global.in
@@ -180,6 +180,7 @@ bitcodedir = $(pkglibdir)/bitcode
 with_icu    = @with_icu@
 with_perl    = @with_perl@
 with_python    = @with_python@
+with_python2_stub = @with_python2_stub@
 with_tcl    = @with_tcl@
 with_openssl    = @with_openssl@
 with_readline    = @with_readline@
diff --git a/src/pl/Makefile b/src/pl/Makefile
index c4a0d1c..fe9cd0f 100644
--- a/src/pl/Makefile
+++ b/src/pl/Makefile
@@ -26,6 +26,12 @@ else
 ALWAYS_SUBDIRS += plpython
 endif

+ifeq ($(with_python2_stub), yes)
+SUBDIRS += stub_plpython2
+else
+ALWAYS_SUBDIRS += stub_plpython2
+endif
+
 ifeq ($(with_tcl), yes)
 SUBDIRS += tcl
 else
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 03f858b..ac262fa 100644
--- a/src/pl/plpython/Makefile
+++ b/src/pl/plpython/Makefile
@@ -37,6 +37,9 @@ OBJS = \
 DATA = $(NAME)u.control $(NAME)u--1.0.sql
 ifeq ($(python_majorversion),2)
 DATA += plpythonu.control plpythonu--1.0.sql
+else ifeq ($(with_python2_stub), yes)
+# install extension files for the stub module (see ../stub_plpython2)
+DATA += plpythonu.control plpythonu--1.0.sql plpython2u.control plpython2u--1.0.sql
 endif
 ifeq ($(python_majorversion),3)
 DATA += convert_python3.control convert_python3--1.0.sql
@@ -109,6 +112,7 @@ REGRESS = \
     plpython_composite \
     plpython_subtransaction \
     plpython_transaction \
+    plpython_stub \
     plpython_drop

 REGRESS_PLPYTHON3_MANGLE := $(REGRESS)
diff --git a/src/pl/plpython/expected/plpython_stub.out b/src/pl/plpython/expected/plpython_stub.out
new file mode 100644
index 0000000..2a565b4
--- /dev/null
+++ b/src/pl/plpython/expected/plpython_stub.out
@@ -0,0 +1,71 @@
+--
+-- Test stub plpython2 and convert_python3 extension
+--
+-- skip test unless we have python3 installed and the others are available
+SELECT (SELECT count(*) FROM pg_extension WHERE extname = 'plpython3u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpython2u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpythonu') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'convert_python3') = 0
+       AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+-- Funny formatting of some commands below is to prevent
+-- regress-python3-mangle.mk from being "helpful".
+-- install the additional extensions
+CREATE EXTENSION
+plpython2u;
+CREATE EXTENSION
+plpythonu;
+CREATE EXTENSION convert_python3;
+-- create some functions that need conversion
+create function convert1() returns int
+as 'return 123l'
+language
+plpython2u
+immutable;
+-- disable syntax checking since if plpythonu is python 3, this'll fail
+set check_function_bodies = false;
+create function convert2() returns int
+as 'return 123l'
+language
+plpythonu
+immutable;
+-- can't use \sf here because mangling would affect the LANGUAGE clauses
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert1';
+ proname  |  lanname   |   prosrc
+----------+------------+-------------
+ convert1 | plpython2u | return 123l
+(1 row)
+
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert2';
+ proname  |  lanname  |   prosrc
+----------+-----------+-------------
+ convert2 | plpythonu | return 123l
+(1 row)
+
+call convert_python3_all();
+NOTICE:  converting function convert1()
+NOTICE:  converting function convert2()
+\sf convert1()
+CREATE OR REPLACE FUNCTION public.convert1()
+ RETURNS integer
+ LANGUAGE plpython3u
+ IMMUTABLE
+AS $function$return 123$function$
+\sf convert2()
+CREATE OR REPLACE FUNCTION public.convert2()
+ RETURNS integer
+ LANGUAGE plpython3u
+ IMMUTABLE
+AS $function$return 123$function$
+-- clean up
+DROP EXTENSION
+plpython2u;
+DROP EXTENSION
+plpythonu;
+DROP EXTENSION convert_python3;
diff --git a/src/pl/plpython/expected/plpython_stub_1.out b/src/pl/plpython/expected/plpython_stub_1.out
new file mode 100644
index 0000000..bb625b9
--- /dev/null
+++ b/src/pl/plpython/expected/plpython_stub_1.out
@@ -0,0 +1,11 @@
+--
+-- Test stub plpython2 and convert_python3 extension
+--
+-- skip test unless we have python3 installed and the others are available
+SELECT (SELECT count(*) FROM pg_extension WHERE extname = 'plpython3u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpython2u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpythonu') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'convert_python3') = 0
+       AS skip_test \gset
+\if :skip_test
+\quit
diff --git a/src/pl/plpython/sql/plpython_stub.sql b/src/pl/plpython/sql/plpython_stub.sql
new file mode 100644
index 0000000..8c587fd
--- /dev/null
+++ b/src/pl/plpython/sql/plpython_stub.sql
@@ -0,0 +1,60 @@
+--
+-- Test stub plpython2 and convert_python3 extension
+--
+
+-- skip test unless we have python3 installed and the others are available
+SELECT (SELECT count(*) FROM pg_extension WHERE extname = 'plpython3u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpython2u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpythonu') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'convert_python3') = 0
+       AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+
+-- Funny formatting of some commands below is to prevent
+-- regress-python3-mangle.mk from being "helpful".
+
+-- install the additional extensions
+CREATE EXTENSION
+plpython2u;
+CREATE EXTENSION
+plpythonu;
+CREATE EXTENSION convert_python3;
+
+-- create some functions that need conversion
+create function convert1() returns int
+as 'return 123l'
+language
+plpython2u
+immutable;
+
+-- disable syntax checking since if plpythonu is python 3, this'll fail
+set check_function_bodies = false;
+
+create function convert2() returns int
+as 'return 123l'
+language
+plpythonu
+immutable;
+
+-- can't use \sf here because mangling would affect the LANGUAGE clauses
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert1';
+
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert2';
+
+call convert_python3_all();
+
+\sf convert1()
+\sf convert2()
+
+-- clean up
+DROP EXTENSION
+plpython2u;
+DROP EXTENSION
+plpythonu;
+DROP EXTENSION convert_python3;
diff --git a/src/pl/stub_plpython2/Makefile b/src/pl/stub_plpython2/Makefile
new file mode 100644
index 0000000..67e7559
--- /dev/null
+++ b/src/pl/stub_plpython2/Makefile
@@ -0,0 +1,38 @@
+# src/pl/stub_plpython2/Makefile
+
+# Note that this Makefile only builds and installs a quasi-dummy
+# version of plpython2.so.  The control and script files for the
+# plpythonu and plpython2u extensions are installed by ../plpython;
+# they are the same whether we're using real or stub plpython2.
+# (We'd probably not have this separate subdirectory at all, except
+# that Makefile.shlib can only build one shlib per directory.)
+
+subdir = src/pl/stub_plpython2
+top_builddir = ../../..
+include $(top_builddir)/src/Makefile.global
+
+PGFILEDESC = "PL/Python - procedural language stub for Python 2"
+
+NAME = plpython2
+
+OBJS = \
+    $(WIN32RES) \
+    stub_plpython2.o
+
+include $(top_srcdir)/src/Makefile.shlib
+
+all: all-lib
+
+# Ensure parallel safety if a build is started in this directory
+$(OBJS): | submake-generated-headers
+
+install: all install-lib
+
+installdirs: installdirs-lib
+
+uninstall: uninstall-lib
+
+clean distclean: clean-lib
+    rm -f $(OBJS)
+
+maintainer-clean: distclean
diff --git a/src/pl/stub_plpython2/stub_plpython2.c b/src/pl/stub_plpython2/stub_plpython2.c
new file mode 100644
index 0000000..4ade88b
--- /dev/null
+++ b/src/pl/stub_plpython2/stub_plpython2.c
@@ -0,0 +1,105 @@
+/*
+ * PL/Python stub for Python 2, in an environment that has only Python 3
+ *
+ * Our strategy is to pass through "plpythonu" functions to Python 3,
+ * but throw a not-implemented error for "plpython2u".
+ *
+ * Pass-through is implemented by using dfmgr.c to look up the appropriate
+ * function in plpython3.so, rather than trying to resolve the reference
+ * directly.  This greatly simplifies building this as an independent
+ * shared library, and it ensures that we can't somehow pull in a different
+ * version of plpython3 (and thence libpython) than would get loaded for
+ * a plpython3u function.
+ *
+ * src/pl/stub_plpython2/stub_plpython2.c
+ */
+
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#define PLPYTHON_LIBNAME "$libdir/plpython3"
+
+/*
+ * exported functions
+ */
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(plpython_validator);
+PG_FUNCTION_INFO_V1(plpython_call_handler);
+PG_FUNCTION_INFO_V1(plpython_inline_handler);
+
+PG_FUNCTION_INFO_V1(plpython2_validator);
+PG_FUNCTION_INFO_V1(plpython2_call_handler);
+PG_FUNCTION_INFO_V1(plpython2_inline_handler);
+
+
+Datum
+plpython_validator(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_validator = NULL;
+
+    if (plpython3_validator == NULL)
+        plpython3_validator =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_validator",
+                                   true, NULL);
+
+    return (*plpython3_validator) (fcinfo);
+}
+
+Datum
+plpython_call_handler(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_call_handler = NULL;
+
+    if (plpython3_call_handler == NULL)
+        plpython3_call_handler =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_call_handler",
+                                   true, NULL);
+
+    return (*plpython3_call_handler) (fcinfo);
+}
+
+Datum
+plpython_inline_handler(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_inline_handler = NULL;
+
+    if (plpython3_inline_handler == NULL)
+        plpython3_inline_handler =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_inline_handler",
+                                   true, NULL);
+
+    return (*plpython3_inline_handler) (fcinfo);
+}
+
+Datum
+plpython2_validator(PG_FUNCTION_ARGS)
+{
+    /* It seems more convenient to do nothing here than throw an error. */
+    PG_RETURN_VOID();
+}
+
+Datum
+plpython2_call_handler(PG_FUNCTION_ARGS)
+{
+    ereport(ERROR,
+            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+             errmsg("Python 2 is no longer supported"),
+             errhint("Convert the function to use plpython3u.")));
+    PG_RETURN_NULL();            /* keep compiler quiet */
+}
+
+Datum
+plpython2_inline_handler(PG_FUNCTION_ARGS)
+{
+    ereport(ERROR,
+            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+             errmsg("Python 2 is no longer supported"),
+             errhint("Convert the DO block to use plpython3u.")));
+    PG_RETURN_NULL();            /* keep compiler quiet */
+}
diff --git a/src/tools/msvc/Install.pm b/src/tools/msvc/Install.pm
index 1a92ed2..cf8b1e2 100644
--- a/src/tools/msvc/Install.pm
+++ b/src/tools/msvc/Install.pm
@@ -154,6 +154,7 @@ sub Install
         my @pldirs             = ('src/pl/plpgsql/src');
         push @pldirs, "src/pl/plperl"   if $config->{perl};
         push @pldirs, "src/pl/plpython" if $config->{python};
+        push @pldirs, "src/pl/stub_plpython2" if $config->{python2_stub};
         push @pldirs, "src/pl/tcl"      if $config->{tcl};
         File::Find::find(
             {
diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm
index 727a8fd..1d28607 100644
--- a/src/tools/msvc/Mkvcbuild.pm
+++ b/src/tools/msvc/Mkvcbuild.pm
@@ -531,7 +531,12 @@ sub mkvcbuild
             'ltree',                        'contrib');
         $ltree_plpython->AddDefine(
             'PLPYTHON_LIBNAME="plpython' . $pymajorver . '"');
+
+        # Ignore --with-python2-stub if building real plpython2
+        $solution->{options}->{python2_stub} = 0 if $pymajorver < 3;
     }
+    else
+        $solution->{options}->{python2_stub} = 0;

     if ($solution->{options}->{perl})
     {
diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm
index 6b4a6ee..848e118 100644
--- a/src/tools/msvc/Solution.pm
+++ b/src/tools/msvc/Solution.pm
@@ -1145,6 +1145,7 @@ sub GetFakeConfigure
     $cfg .= ' --with-tcl'           if ($self->{options}->{tcl});
     $cfg .= ' --with-perl'          if ($self->{options}->{perl});
     $cfg .= ' --with-python'        if ($self->{options}->{python});
+    $cfg .= ' --with-python2-stub'  if ($self->{options}->{python2_stub});

     return $cfg;
 }
diff --git a/src/tools/msvc/clean.bat b/src/tools/msvc/clean.bat
index 672bb2d..4ad5f07 100755
--- a/src/tools/msvc/clean.bat
+++ b/src/tools/msvc/clean.bat
@@ -26,6 +26,7 @@ if exist src\interfaces\ecpg\test\win32ver.rc del /q src\interfaces\ecpg\test\wi
 if exist src\pl\plperl\win32ver.rc del /q src\pl\plperl\win32ver.rc
 if exist src\pl\plpgsql\src\win32ver.rc del /q src\pl\plpgsql\src\win32ver.rc
 if exist src\pl\plpython\win32ver.rc del /q src\pl\plpython\win32ver.rc
+if exist src\pl\stub_plpython2\win32ver.rc del /q src\pl\stub_plpython2\win32ver.rc
 if exist src\pl\tcl\win32ver.rc del /q src\pl\tcl\win32ver.rc
 if exist src\test\isolation\win32ver.rc del /q src\test\isolation\win32ver.rc
 if exist src\test\regress\win32ver.rc del /q src\test\regress\win32ver.rc
diff --git a/src/tools/msvc/config_default.pl b/src/tools/msvc/config_default.pl
index 2ef2cfc..d7f9b60 100644
--- a/src/tools/msvc/config_default.pl
+++ b/src/tools/msvc/config_default.pl
@@ -16,6 +16,7 @@ our $config = {
     tcl       => undef,    # --with-tcl=<path>
     perl      => undef,    # --with-perl=<path>
     python    => undef,    # --with-python=<path>
+    python2_stub => 1,     # --with-python2-stub (ignored unless Python is v3)
     openssl   => undef,    # --with-openssl=<path>
     uuid      => undef,    # --with-uuid=<path>
     xml       => undef,    # --with-libxml=<path>

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

25 February 2020, 23:48:12

I wrote:
> Here's an updated pair of patches that attempt to fix the MSVC
> scripts (pretty blindly) and provide a very simple regression test.

A little *too* blindly, evidently.  Try again ...

            regards, tom lane

diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index 1921915..ac989a3 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -164,13 +164,6 @@
   </para>

   <para>
-   See also the
-   document <ulink url="https://docs.python.org/3/whatsnew/3.0.html">What's
-   New In Python 3.0</ulink> for more information about porting to
-   Python 3.
-  </para>
-
-  <para>
    It is not allowed to use PL/Python based on Python 2 and PL/Python
    based on Python 3 in the same session, because the symbols in the
    dynamic modules would clash, which could result in crashes of the
@@ -179,6 +172,90 @@
    a mismatch is detected.  It is possible, however, to use both
    PL/Python variants in the same database, from separate sessions.
   </para>
+
+  <sect2 id="plpython-python3-conversion">
+   <title>Converting from Python 2 to Python 3</title>
+
+   <para>
+    See the
+    document <ulink url="https://docs.python.org/3/whatsnew/3.0.html">What's
+    New In Python 3.0</ulink> for the Python community's information and
+    recommendations about porting to Python 3.
+   </para>
+
+   <para>
+    <productname>PostgreSQL</productname> provides some support for helping
+    you to convert existing Python 2 routines to Python 3.  In an
+    installation built with Python 3, there is an
+    extension <filename>convert_python3</filename> that changes functions
+    and procedures from the <literal>plpythonu</literal>
+    and <literal>plpython2u</literal> languages to
+    the <literal>plpython3u</literal> language.  While doing so, it applies
+    the <filename>2to3</filename> tool described in the above document to
+    the body of each such routine.
+   </para>
+
+   <para>
+    Using <filename>convert_python3</filename> can be as simple as:
+<programlisting>
+CREATE EXTENSION convert_python3;
+CALL convert_python3_all();
+</programlisting>
+    This must be done as database superuser.  If you wish, you can drop the
+    extension once you're done converting everything.
+   </para>
+
+   <para>
+    Since <filename>convert_python3</filename> is Python 3 code, be careful
+    not to install or run it in a session that has previously executed any
+    Python 2 code.  As explained above, that won't work.
+   </para>
+
+   <para>
+    <function>convert_python3_all</function> has two optional arguments: the
+    name of the conversion tool to use (by default <literal>2to3</literal>,
+    but you might for instance need to provide a full path name) and any
+    special command-line options to provide to it.  You might for example
+    want to adjust the set of <quote>fixer</quote> rules
+    that <literal>2to3</literal> applies:
+<programlisting>
+CALL convert_python3_all(options => '-f idioms -x apply');
+</programlisting>
+    See <literal>2to3</literal>'s
+    <ulink url="https://docs.python.org/3/library/2to3.html">documentation</ulink>
+    for more information.
+   </para>
+
+   <para>
+    The <filename>convert_python3</filename> extension also provides a
+    procedure that converts just one Python 2 function at a time:
+<programlisting>
+CALL convert_python3_one('myfunc(int)');
+</programlisting>
+    The argument is the target function's OID, which can be written as
+    a <type>regprocedure</type> constant (see
+    <xref linkend="datatype-oid"/>).  The main reason to use this would be
+    if you need to use different options for different functions.  It has
+    the same optional arguments as <function>convert_python3_all</function>:
+<programlisting>
+CALL convert_python3_one('otherfunc(text)', tool => '/usr/bin/2to3',
+                         options => '-f idioms');
+</programlisting>
+   </para>
+
+   <para>
+    If you have needs that go beyond this, consult the source code for
+    the <filename>convert_python3</filename> extension (it's just a
+    couple of <literal>plpython3u</literal> procedures) and adapt those
+    procedures as necessary.
+   </para>
+
+   <para>
+    Keep in mind that if you've constructed any <command>DO</command> blocks
+    that use Python 2 code, those will have to be fixed up manually,
+    wherever the source code for them exists.
+   </para>
+  </sect2>
  </sect1>

  <sect1 id="plpython-funcs">
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 9e95285..03f858b 100644
--- a/src/pl/plpython/Makefile
+++ b/src/pl/plpython/Makefile
@@ -38,6 +38,9 @@ DATA = $(NAME)u.control $(NAME)u--1.0.sql
 ifeq ($(python_majorversion),2)
 DATA += plpythonu.control plpythonu--1.0.sql
 endif
+ifeq ($(python_majorversion),3)
+DATA += convert_python3.control convert_python3--1.0.sql
+endif

 # header files to install - it's not clear which of these might be needed
 # so install them all.
diff --git a/src/pl/plpython/convert_python3--1.0.sql b/src/pl/plpython/convert_python3--1.0.sql
new file mode 100644
index 0000000..3444ac0
--- /dev/null
+++ b/src/pl/plpython/convert_python3--1.0.sql
@@ -0,0 +1,149 @@
+/* convert_python3--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION convert_python3" to load this file. \quit
+
+-- This module provides two procedures, one to convert all python2
+-- functions and one to do just one.  They're nearly identical, and
+-- in principle convert_python3_all() could be written as a loop
+-- around convert_python3_one().  It's not done that way since
+-- creating a temp directory for each function in a bulk conversion
+-- could get expensive.
+
+-- For some benighted reason, lib2to3 has exactly no documented API,
+-- so we must use the command-line API "2to3" instead.  User may pass
+-- in the name of that program (in case it's not in the server's PATH)
+-- as well as any desired options for it (perhaps some -f switches).
+
+create procedure convert_python3_all(tool text default '2to3',
+                                     options text default '')
+language plpython3u as $$
+import re, subprocess, tempfile
+
+# pattern to extract just the function header from pg_get_functiondef result
+aspat = re.compile("^(.*?\nAS )", re.DOTALL)
+# pattern for replacing LANGUAGE portion
+langpat = re.compile("\n LANGUAGE plpython2?u\n")
+
+# collect info about functions to update
+rv = plpy.execute("""
+select p.oid::pg_catalog.regprocedure as funcid,
+       pg_catalog.pg_get_functiondef(p.oid) as fd,
+       prosrc as body
+from pg_catalog.pg_proc p join pg_catalog.pg_language l on p.prolang = l.oid
+where lanname in ('plpythonu', 'plpython2u')
+order by proname
+""")
+
+# Make a temp directory to hold the file for 2to3 to work on.
+with tempfile.TemporaryDirectory() as tmpdirname:
+
+    # process each function
+    for r in rv:
+        # emit notices so user can tell which function failed, if one does
+        plpy.notice("converting function " + r["funcid"])
+
+        # extract everything but the body from pg_get_functiondef result
+        m = aspat.match(r["fd"])
+        if not m:
+            raise ValueError('unexpected match failure')
+        fheader = m.group(1)
+
+        # replace the language clause
+        fheader = langpat.sub("\n LANGUAGE plpython3u\n", fheader, 1)
+
+        # put body in a temp file so we can apply 2to3
+        f = open(tmpdirname + "/temp.py", mode = 'w')
+        f.write(r["body"])
+        f.close()
+
+        # apply 2to3 to body
+        subprocess.check_call(tool + " " + options + " --no-diffs -w " + tmpdirname + "/temp.py", shell=True)
+        f = open(tmpdirname + "/temp.py", mode = 'r')
+        fbody = f.read()
+        f.close()
+
+        # ensure check_function_bodies is enabled
+        plpy.execute("set local check_function_bodies = true")
+
+        # construct and execute SQL command to replace the function
+        newstmt = fheader + plpy.quote_literal(fbody)
+        # uncomment this for debugging purposes:
+        # plpy.info(newstmt)
+        plpy.execute(newstmt)
+
+        # commit after each successful replacement, in case a later one fails
+        plpy.commit()
+$$;
+
+-- The above procedure has to be superuser-only since it trivially allows
+-- executing random programs.  But you'd have to be superuser anyway
+-- to replace the definitions of plpython functions.
+
+revoke all on procedure convert_python3_all(text, text) from public;
+
+
+-- Here's the one-function version.
+
+create procedure convert_python3_one(funcid regprocedure,
+                                     tool text default '2to3',
+                                     options text default '')
+language plpython3u as $$
+import re, subprocess, tempfile
+
+# pattern to extract just the function header from pg_get_functiondef result
+aspat = re.compile("^(.*?\nAS )", re.DOTALL)
+# pattern for replacing LANGUAGE portion
+langpat = re.compile("\n LANGUAGE plpython2?u\n")
+
+# collect info about function to update, making sure it's the right language
+plan = plpy.prepare("""
+select p.oid::pg_catalog.regprocedure as funcid,
+       pg_catalog.pg_get_functiondef(p.oid) as fd,
+       prosrc as body
+from pg_catalog.pg_proc p join pg_catalog.pg_language l on p.prolang = l.oid
+where p.oid = $1 and lanname in ('plpythonu', 'plpython2u')
+""", ["pg_catalog.regprocedure"])
+
+rv = plpy.execute(plan, [funcid])
+
+# Make a temp directory to hold the file for 2to3 to work on.
+with tempfile.TemporaryDirectory() as tmpdirname:
+
+    # process each function (we only expect one, but it's easy to loop)
+    for r in rv:
+        # extract everything but the body from pg_get_functiondef result
+        m = aspat.match(r["fd"])
+        if not m:
+            raise ValueError('unexpected match failure')
+        fheader = m.group(1)
+
+        # replace the language clause
+        fheader = langpat.sub("\n LANGUAGE plpython3u\n", fheader, 1)
+
+        # put body in a temp file so we can apply 2to3
+        f = open(tmpdirname + "/temp.py", mode = 'w')
+        f.write(r["body"])
+        f.close()
+
+        # apply 2to3 to body
+        subprocess.check_call(tool + " " + options + " --no-diffs -w " + tmpdirname + "/temp.py", shell=True)
+        f = open(tmpdirname + "/temp.py", mode = 'r')
+        fbody = f.read()
+        f.close()
+
+        # ensure check_function_bodies is enabled
+        plpy.execute("set local check_function_bodies = true")
+
+        # construct and execute SQL command to replace the function
+        newstmt = fheader + plpy.quote_literal(fbody)
+        # uncomment this for debugging purposes:
+        # plpy.info(newstmt)
+        plpy.execute(newstmt)
+$$;
+
+-- The above procedure has to be superuser-only since it trivially allows
+-- executing random programs.  But you'd have to be superuser anyway
+-- to replace the definitions of plpython functions.
+
+revoke all on procedure convert_python3_one(regprocedure, text, text) from public;
diff --git a/src/pl/plpython/convert_python3.control b/src/pl/plpython/convert_python3.control
new file mode 100644
index 0000000..8debb3b
--- /dev/null
+++ b/src/pl/plpython/convert_python3.control
@@ -0,0 +1,5 @@
+# convert_python3 extension
+comment = 'convert plpython[2]u functions to plpython3u'
+default_version = '1.0'
+relocatable = true
+requires = 'plpython3u'
diff --git a/configure b/configure
index d2c74e5..3feebf6 100755
--- a/configure
+++ b/configure
@@ -714,6 +714,7 @@ with_ldap
 with_krb_srvnam
 krb_srvtab
 with_gssapi
+with_python2_stub
 with_python
 with_perl
 with_tcl
@@ -847,6 +848,7 @@ with_tcl
 with_tclconfig
 with_perl
 with_python
+with_python2_stub
 with_gssapi
 with_krb_srvnam
 with_pam
@@ -1546,6 +1548,7 @@ Optional Packages:
   --with-tclconfig=DIR    tclConfig.sh is in DIR
   --with-perl             build Perl modules (PL/Perl)
   --with-python           build Python modules (PL/Python)
+  --with-python2-stub     build Python 2 compatibility stub
   --with-gssapi           build with GSSAPI support
   --with-krb-srvnam=NAME  default service principal name in Kerberos (GSSAPI)
                           [postgres]
@@ -7644,6 +7647,32 @@ fi
 $as_echo "$with_python" >&6; }


+
+
+
+# Check whether --with-python2-stub was given.
+if test "${with_python2_stub+set}" = set; then :
+  withval=$with_python2_stub;
+  case $withval in
+    yes)
+      :
+      ;;
+    no)
+      :
+      ;;
+    *)
+      as_fn_error $? "no argument expected for --with-python2-stub option" "$LINENO" 5
+      ;;
+  esac
+
+else
+  with_python2_stub=no
+
+fi
+
+
+
+
 #
 # GSSAPI
 #
@@ -9745,6 +9774,12 @@ $as_echo "${python_libspec} ${python_additional_libs}" >&6; }



+  # Disable building Python 2 stub if primary version isn't Python 3
+  if test "$python_majorversion" -lt 3; then
+    with_python2_stub=no
+  fi
+else
+  with_python2_stub=no
 fi

 if test "$cross_compiling" = yes && test -z "$with_system_tzdata"; then
diff --git a/configure.in b/configure.in
index 0b0a871..ffa49c9 100644
--- a/configure.in
+++ b/configure.in
@@ -766,6 +766,9 @@ PGAC_ARG_BOOL(with, python, no, [build Python modules (PL/Python)])
 AC_MSG_RESULT([$with_python])
 AC_SUBST(with_python)

+PGAC_ARG_BOOL(with, python2-stub, no, [build Python 2 compatibility stub])
+AC_SUBST(with_python2_stub)
+
 #
 # GSSAPI
 #
@@ -1042,6 +1045,12 @@ fi
 if test "$with_python" = yes; then
   PGAC_PATH_PYTHON
   PGAC_CHECK_PYTHON_EMBED_SETUP
+  # Disable building Python 2 stub if primary version isn't Python 3
+  if test "$python_majorversion" -lt 3; then
+    with_python2_stub=no
+  fi
+else
+  with_python2_stub=no
 fi

 if test "$cross_compiling" = yes && test -z "$with_system_tzdata"; then
diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml
index cc242dc..ff81ea8 100644
--- a/doc/src/sgml/installation.sgml
+++ b/doc/src/sgml/installation.sgml
@@ -877,6 +877,23 @@ build-postgresql:
       </varlistentry>

       <varlistentry>
+       <term><option>--with-python2-stub</option></term>
+       <listitem>
+        <para>
+         Build a stub version of the Python
+         2 <application>PL/Python</application> language, to aid in
+         transitioning old <application>PL/Python</application> code.
+         This option is recommended if you are
+         building <application>PL/Python</application> for Python 3
+         and do not intend to also build a version for Python 2.
+         This option is ignored if you do not also
+         specify <option>--with-python</option>, or if the selected Python
+         implementation is Python 2.
+        </para>
+       </listitem>
+      </varlistentry>
+
+      <varlistentry>
        <term><option>--with-tcl</option></term>
        <listitem>
         <para>
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index ac989a3..9014750 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -77,13 +77,7 @@
      <para>
       The language named <literal>plpythonu</literal> implements
       PL/Python based on the default Python language variant, which is
-      currently Python 2.  (This default is independent of what any
-      local Python installations might consider to be
-      their <quote>default</quote>, for example,
-      what <filename>/usr/bin/python</filename> might be.)  The
-      default will probably be changed to Python 3 in a distant future
-      release of PostgreSQL, depending on the progress of the
-      migration to Python 3 in the Python community.
+      determined by the person who builds and installs PostgreSQL.
      </para>
     </listitem>
    </itemizedlist>
@@ -103,9 +97,22 @@
     The built variant depends on which Python version was found during
     the installation or which version was explicitly set using
     the <envar>PYTHON</envar> environment variable;
-    see <xref linkend="install-procedure"/>.  To make both variants of
-    PL/Python available in one installation, the source tree has to be
-    configured and built twice.
+    see <xref linkend="install-procedure"/>.  To make working versions of
+    both variants of PL/Python available in one installation, the source
+    tree has to be configured and built twice.
+   </para>
+
+   <para>
+    However, if the builder specifies both <option>--with-python</option>
+    and <option>--with-python2-stub</option> while building with Python 3,
+    then in addition to the working Python-3-based PL/Python,
+    a <quote>stub</quote> version of <literal>plpython2u</literal> is
+    produced.  This stub is non-functional and will simply throw errors if
+    any <literal>plpython2u</literal> function is executed, but its
+    presence allows such functions to be loaded into the database in
+    preparation for conversion to <literal>plpython3u</literal>.  This
+    configuration also causes <literal>plpythonu</literal> to become
+    Python 3 rather than Python 2.
    </para>
   </tip>

@@ -115,49 +122,38 @@
    <itemizedlist>
     <listitem>
      <para>
-      Existing users and users who are currently not interested in
-      Python 3 use the language name <literal>plpythonu</literal> and
-      don't have to change anything for the foreseeable future.  It is
-      recommended to gradually <quote>future-proof</quote> the code
-      via migration to Python 2.6/2.7 to simplify the eventual
-      migration to Python 3.
+      Use the language name <literal>plpythonu</literal> if you have
+      simple Python code that works for either Python 2 or Python 3;
+      or if you are prepared to migrate your code whenever your packager
+      decides to change the default Python version.
      </para>

      <para>
       In practice, many PL/Python functions will migrate to Python 3
-      with few or no changes.
+      with few or no changes.  However, there are some cases that
+      will require more work.
      </para>
     </listitem>

     <listitem>
      <para>
-      Users who know that they have heavily Python 2 dependent code
-      and don't plan to ever change it can make use of
+      If you know that you have heavily Python 2 dependent code
+      and don't plan to ever change it, you can make use of
       the <literal>plpython2u</literal> language name.  This will
-      continue to work into the very distant future, until Python 2
-      support might be completely dropped by PostgreSQL.
-     </para>
-    </listitem>
-
-    <listitem>
-     <para>
-      Users who want to dive into Python 3 can use
-      the <literal>plpython3u</literal> language name, which will keep
-      working forever by today's standards.  In the distant future,
-      when Python 3 might become the default, they might like to
-      remove the <quote>3</quote> for aesthetic reasons.
+      continue to work for as long as your operating system platform
+      continues to provide Python 2.  PostgreSQL itself may eventually
+      drop support for this option, but that is unlikely to happen
+      as long as any platform support remains in the wild.
      </para>
     </listitem>

     <listitem>
      <para>
-      Daredevils, who want to build a Python-3-only operating system
-      environment, can change the contents of
-      <literal>plpythonu</literal>'s extension control and script files
-      to make <literal>plpythonu</literal> be equivalent
-      to <literal>plpython3u</literal>, keeping in mind that this
-      would make their installation incompatible with most of the rest
-      of the world.
+      If you know your code requires Python 3, use
+      the <literal>plpython3u</literal> language name.  You can also
+      use this language name to tag functions that have been successfully
+      converted from Python 2, so as to keep track of which functions have
+      been converted and which have not.
      </para>
     </listitem>
    </itemizedlist>
@@ -171,6 +167,8 @@
    Python major versions in a session, which will abort the session if
    a mismatch is detected.  It is possible, however, to use both
    PL/Python variants in the same database, from separate sessions.
+   (Also, the <quote>stub</quote> version of <literal>plpython2u</literal>
+   does not present any conflicts.)
   </para>

   <sect2 id="plpython-python3-conversion">
diff --git a/src/Makefile.global.in b/src/Makefile.global.in
index e4db3e8..1f880b6 100644
--- a/src/Makefile.global.in
+++ b/src/Makefile.global.in
@@ -180,6 +180,7 @@ bitcodedir = $(pkglibdir)/bitcode
 with_icu    = @with_icu@
 with_perl    = @with_perl@
 with_python    = @with_python@
+with_python2_stub = @with_python2_stub@
 with_tcl    = @with_tcl@
 with_openssl    = @with_openssl@
 with_readline    = @with_readline@
diff --git a/src/pl/Makefile b/src/pl/Makefile
index c4a0d1c..fe9cd0f 100644
--- a/src/pl/Makefile
+++ b/src/pl/Makefile
@@ -26,6 +26,12 @@ else
 ALWAYS_SUBDIRS += plpython
 endif

+ifeq ($(with_python2_stub), yes)
+SUBDIRS += stub_plpython2
+else
+ALWAYS_SUBDIRS += stub_plpython2
+endif
+
 ifeq ($(with_tcl), yes)
 SUBDIRS += tcl
 else
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 03f858b..ac262fa 100644
--- a/src/pl/plpython/Makefile
+++ b/src/pl/plpython/Makefile
@@ -37,6 +37,9 @@ OBJS = \
 DATA = $(NAME)u.control $(NAME)u--1.0.sql
 ifeq ($(python_majorversion),2)
 DATA += plpythonu.control plpythonu--1.0.sql
+else ifeq ($(with_python2_stub), yes)
+# install extension files for the stub module (see ../stub_plpython2)
+DATA += plpythonu.control plpythonu--1.0.sql plpython2u.control plpython2u--1.0.sql
 endif
 ifeq ($(python_majorversion),3)
 DATA += convert_python3.control convert_python3--1.0.sql
@@ -109,6 +112,7 @@ REGRESS = \
     plpython_composite \
     plpython_subtransaction \
     plpython_transaction \
+    plpython_stub \
     plpython_drop

 REGRESS_PLPYTHON3_MANGLE := $(REGRESS)
diff --git a/src/pl/plpython/expected/plpython_stub.out b/src/pl/plpython/expected/plpython_stub.out
new file mode 100644
index 0000000..2a565b4
--- /dev/null
+++ b/src/pl/plpython/expected/plpython_stub.out
@@ -0,0 +1,71 @@
+--
+-- Test stub plpython2 and convert_python3 extension
+--
+-- skip test unless we have python3 installed and the others are available
+SELECT (SELECT count(*) FROM pg_extension WHERE extname = 'plpython3u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpython2u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpythonu') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'convert_python3') = 0
+       AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+-- Funny formatting of some commands below is to prevent
+-- regress-python3-mangle.mk from being "helpful".
+-- install the additional extensions
+CREATE EXTENSION
+plpython2u;
+CREATE EXTENSION
+plpythonu;
+CREATE EXTENSION convert_python3;
+-- create some functions that need conversion
+create function convert1() returns int
+as 'return 123l'
+language
+plpython2u
+immutable;
+-- disable syntax checking since if plpythonu is python 3, this'll fail
+set check_function_bodies = false;
+create function convert2() returns int
+as 'return 123l'
+language
+plpythonu
+immutable;
+-- can't use \sf here because mangling would affect the LANGUAGE clauses
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert1';
+ proname  |  lanname   |   prosrc
+----------+------------+-------------
+ convert1 | plpython2u | return 123l
+(1 row)
+
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert2';
+ proname  |  lanname  |   prosrc
+----------+-----------+-------------
+ convert2 | plpythonu | return 123l
+(1 row)
+
+call convert_python3_all();
+NOTICE:  converting function convert1()
+NOTICE:  converting function convert2()
+\sf convert1()
+CREATE OR REPLACE FUNCTION public.convert1()
+ RETURNS integer
+ LANGUAGE plpython3u
+ IMMUTABLE
+AS $function$return 123$function$
+\sf convert2()
+CREATE OR REPLACE FUNCTION public.convert2()
+ RETURNS integer
+ LANGUAGE plpython3u
+ IMMUTABLE
+AS $function$return 123$function$
+-- clean up
+DROP EXTENSION
+plpython2u;
+DROP EXTENSION
+plpythonu;
+DROP EXTENSION convert_python3;
diff --git a/src/pl/plpython/expected/plpython_stub_1.out b/src/pl/plpython/expected/plpython_stub_1.out
new file mode 100644
index 0000000..bb625b9
--- /dev/null
+++ b/src/pl/plpython/expected/plpython_stub_1.out
@@ -0,0 +1,11 @@
+--
+-- Test stub plpython2 and convert_python3 extension
+--
+-- skip test unless we have python3 installed and the others are available
+SELECT (SELECT count(*) FROM pg_extension WHERE extname = 'plpython3u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpython2u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpythonu') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'convert_python3') = 0
+       AS skip_test \gset
+\if :skip_test
+\quit
diff --git a/src/pl/plpython/sql/plpython_stub.sql b/src/pl/plpython/sql/plpython_stub.sql
new file mode 100644
index 0000000..8c587fd
--- /dev/null
+++ b/src/pl/plpython/sql/plpython_stub.sql
@@ -0,0 +1,60 @@
+--
+-- Test stub plpython2 and convert_python3 extension
+--
+
+-- skip test unless we have python3 installed and the others are available
+SELECT (SELECT count(*) FROM pg_extension WHERE extname = 'plpython3u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpython2u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpythonu') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'convert_python3') = 0
+       AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+
+-- Funny formatting of some commands below is to prevent
+-- regress-python3-mangle.mk from being "helpful".
+
+-- install the additional extensions
+CREATE EXTENSION
+plpython2u;
+CREATE EXTENSION
+plpythonu;
+CREATE EXTENSION convert_python3;
+
+-- create some functions that need conversion
+create function convert1() returns int
+as 'return 123l'
+language
+plpython2u
+immutable;
+
+-- disable syntax checking since if plpythonu is python 3, this'll fail
+set check_function_bodies = false;
+
+create function convert2() returns int
+as 'return 123l'
+language
+plpythonu
+immutable;
+
+-- can't use \sf here because mangling would affect the LANGUAGE clauses
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert1';
+
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert2';
+
+call convert_python3_all();
+
+\sf convert1()
+\sf convert2()
+
+-- clean up
+DROP EXTENSION
+plpython2u;
+DROP EXTENSION
+plpythonu;
+DROP EXTENSION convert_python3;
diff --git a/src/pl/stub_plpython2/Makefile b/src/pl/stub_plpython2/Makefile
new file mode 100644
index 0000000..67e7559
--- /dev/null
+++ b/src/pl/stub_plpython2/Makefile
@@ -0,0 +1,38 @@
+# src/pl/stub_plpython2/Makefile
+
+# Note that this Makefile only builds and installs a quasi-dummy
+# version of plpython2.so.  The control and script files for the
+# plpythonu and plpython2u extensions are installed by ../plpython;
+# they are the same whether we're using real or stub plpython2.
+# (We'd probably not have this separate subdirectory at all, except
+# that Makefile.shlib can only build one shlib per directory.)
+
+subdir = src/pl/stub_plpython2
+top_builddir = ../../..
+include $(top_builddir)/src/Makefile.global
+
+PGFILEDESC = "PL/Python - procedural language stub for Python 2"
+
+NAME = plpython2
+
+OBJS = \
+    $(WIN32RES) \
+    stub_plpython2.o
+
+include $(top_srcdir)/src/Makefile.shlib
+
+all: all-lib
+
+# Ensure parallel safety if a build is started in this directory
+$(OBJS): | submake-generated-headers
+
+install: all install-lib
+
+installdirs: installdirs-lib
+
+uninstall: uninstall-lib
+
+clean distclean: clean-lib
+    rm -f $(OBJS)
+
+maintainer-clean: distclean
diff --git a/src/pl/stub_plpython2/stub_plpython2.c b/src/pl/stub_plpython2/stub_plpython2.c
new file mode 100644
index 0000000..4ade88b
--- /dev/null
+++ b/src/pl/stub_plpython2/stub_plpython2.c
@@ -0,0 +1,105 @@
+/*
+ * PL/Python stub for Python 2, in an environment that has only Python 3
+ *
+ * Our strategy is to pass through "plpythonu" functions to Python 3,
+ * but throw a not-implemented error for "plpython2u".
+ *
+ * Pass-through is implemented by using dfmgr.c to look up the appropriate
+ * function in plpython3.so, rather than trying to resolve the reference
+ * directly.  This greatly simplifies building this as an independent
+ * shared library, and it ensures that we can't somehow pull in a different
+ * version of plpython3 (and thence libpython) than would get loaded for
+ * a plpython3u function.
+ *
+ * src/pl/stub_plpython2/stub_plpython2.c
+ */
+
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#define PLPYTHON_LIBNAME "$libdir/plpython3"
+
+/*
+ * exported functions
+ */
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(plpython_validator);
+PG_FUNCTION_INFO_V1(plpython_call_handler);
+PG_FUNCTION_INFO_V1(plpython_inline_handler);
+
+PG_FUNCTION_INFO_V1(plpython2_validator);
+PG_FUNCTION_INFO_V1(plpython2_call_handler);
+PG_FUNCTION_INFO_V1(plpython2_inline_handler);
+
+
+Datum
+plpython_validator(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_validator = NULL;
+
+    if (plpython3_validator == NULL)
+        plpython3_validator =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_validator",
+                                   true, NULL);
+
+    return (*plpython3_validator) (fcinfo);
+}
+
+Datum
+plpython_call_handler(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_call_handler = NULL;
+
+    if (plpython3_call_handler == NULL)
+        plpython3_call_handler =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_call_handler",
+                                   true, NULL);
+
+    return (*plpython3_call_handler) (fcinfo);
+}
+
+Datum
+plpython_inline_handler(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_inline_handler = NULL;
+
+    if (plpython3_inline_handler == NULL)
+        plpython3_inline_handler =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_inline_handler",
+                                   true, NULL);
+
+    return (*plpython3_inline_handler) (fcinfo);
+}
+
+Datum
+plpython2_validator(PG_FUNCTION_ARGS)
+{
+    /* It seems more convenient to do nothing here than throw an error. */
+    PG_RETURN_VOID();
+}
+
+Datum
+plpython2_call_handler(PG_FUNCTION_ARGS)
+{
+    ereport(ERROR,
+            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+             errmsg("Python 2 is no longer supported"),
+             errhint("Convert the function to use plpython3u.")));
+    PG_RETURN_NULL();            /* keep compiler quiet */
+}
+
+Datum
+plpython2_inline_handler(PG_FUNCTION_ARGS)
+{
+    ereport(ERROR,
+            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+             errmsg("Python 2 is no longer supported"),
+             errhint("Convert the DO block to use plpython3u.")));
+    PG_RETURN_NULL();            /* keep compiler quiet */
+}
diff --git a/src/tools/msvc/Install.pm b/src/tools/msvc/Install.pm
index 1a92ed2..cf8b1e2 100644
--- a/src/tools/msvc/Install.pm
+++ b/src/tools/msvc/Install.pm
@@ -154,6 +154,7 @@ sub Install
         my @pldirs             = ('src/pl/plpgsql/src');
         push @pldirs, "src/pl/plperl"   if $config->{perl};
         push @pldirs, "src/pl/plpython" if $config->{python};
+        push @pldirs, "src/pl/stub_plpython2" if $config->{python2_stub};
         push @pldirs, "src/pl/tcl"      if $config->{tcl};
         File::Find::find(
             {
diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm
index 727a8fd..64d7431 100644
--- a/src/tools/msvc/Mkvcbuild.pm
+++ b/src/tools/msvc/Mkvcbuild.pm
@@ -531,6 +531,13 @@ sub mkvcbuild
             'ltree',                        'contrib');
         $ltree_plpython->AddDefine(
             'PLPYTHON_LIBNAME="plpython' . $pymajorver . '"');
+
+        # Ignore --with-python2-stub if building real plpython2
+        $solution->{options}->{python2_stub} = 0 if $pymajorver < 3;
+    }
+    else
+    {
+        $solution->{options}->{python2_stub} = 0;
     }

     if ($solution->{options}->{perl})
diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm
index 6b4a6ee..848e118 100644
--- a/src/tools/msvc/Solution.pm
+++ b/src/tools/msvc/Solution.pm
@@ -1145,6 +1145,7 @@ sub GetFakeConfigure
     $cfg .= ' --with-tcl'           if ($self->{options}->{tcl});
     $cfg .= ' --with-perl'          if ($self->{options}->{perl});
     $cfg .= ' --with-python'        if ($self->{options}->{python});
+    $cfg .= ' --with-python2-stub'  if ($self->{options}->{python2_stub});

     return $cfg;
 }
diff --git a/src/tools/msvc/clean.bat b/src/tools/msvc/clean.bat
index 672bb2d..4ad5f07 100755
--- a/src/tools/msvc/clean.bat
+++ b/src/tools/msvc/clean.bat
@@ -26,6 +26,7 @@ if exist src\interfaces\ecpg\test\win32ver.rc del /q src\interfaces\ecpg\test\wi
 if exist src\pl\plperl\win32ver.rc del /q src\pl\plperl\win32ver.rc
 if exist src\pl\plpgsql\src\win32ver.rc del /q src\pl\plpgsql\src\win32ver.rc
 if exist src\pl\plpython\win32ver.rc del /q src\pl\plpython\win32ver.rc
+if exist src\pl\stub_plpython2\win32ver.rc del /q src\pl\stub_plpython2\win32ver.rc
 if exist src\pl\tcl\win32ver.rc del /q src\pl\tcl\win32ver.rc
 if exist src\test\isolation\win32ver.rc del /q src\test\isolation\win32ver.rc
 if exist src\test\regress\win32ver.rc del /q src\test\regress\win32ver.rc
diff --git a/src/tools/msvc/config_default.pl b/src/tools/msvc/config_default.pl
index 2ef2cfc..d7f9b60 100644
--- a/src/tools/msvc/config_default.pl
+++ b/src/tools/msvc/config_default.pl
@@ -16,6 +16,7 @@ our $config = {
     tcl       => undef,    # --with-tcl=<path>
     perl      => undef,    # --with-perl=<path>
     python    => undef,    # --with-python=<path>
+    python2_stub => 1,     # --with-python2-stub (ignored unless Python is v3)
     openssl   => undef,    # --with-openssl=<path>
     uuid      => undef,    # --with-uuid=<path>
     xml       => undef,    # --with-libxml=<path>

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

26 February 2020, 01:06:03

I wrote:
> I set up the MSVC scripts to default to building the stub extension.
> I don't know if we really want to commit it that way, but the idea
> for the moment is to try to get the cfbot to test it on Windows.

No joy there --- now that I look closer, it seems the cfbot doesn't
build any of the external-language PLs on Windows.  I'll have to
wait for some reviewer to try it.

            regards, tom lane

Re: Resolving the python 2 -> python 3 mess

From

Andrew Dunstan

Date:

26 February 2020, 02:12:30

On 2/25/20 5:06 PM, Tom Lane wrote:
> I wrote:
>> I set up the MSVC scripts to default to building the stub extension.
>> I don't know if we really want to commit it that way, but the idea
>> for the moment is to try to get the cfbot to test it on Windows.
> No joy there --- now that I look closer, it seems the cfbot doesn't
> build any of the external-language PLs on Windows.  I'll have to
> wait for some reviewer to try it.
>
>             



What are the requirements for testing? bowerbird builds with python 2.7,
although I guess I should really try to upgrade it  3.x.


cheers


andrew


-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

26 February 2020, 03:08:04

Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> On 2/25/20 5:06 PM, Tom Lane wrote:
>> No joy there --- now that I look closer, it seems the cfbot doesn't
>> build any of the external-language PLs on Windows.  I'll have to
>> wait for some reviewer to try it.

> What are the requirements for testing? bowerbird builds with python 2.7,
> although I guess I should really try to upgrade it  3.x.

Has to be python 3, unfortunately; the patch has no effect on a
python 2 build.

            regards, tom lane

Re: Resolving the python 2 -> python 3 mess

From

Andrew Dunstan

Date:

26 February 2020, 04:24:21

On 2/25/20 7:08 PM, Tom Lane wrote:
> Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
>> On 2/25/20 5:06 PM, Tom Lane wrote:
>>> No joy there --- now that I look closer, it seems the cfbot doesn't
>>> build any of the external-language PLs on Windows.  I'll have to
>>> wait for some reviewer to try it.
>> What are the requirements for testing? bowerbird builds with python 2.7,
>> although I guess I should really try to upgrade it  3.x.
> Has to be python 3, unfortunately; the patch has no effect on a
> python 2 build.
>
>             



Yeah, I have python3 working on drongo, I'll test there.


cheers


andrew


-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Resolving the python 2 -> python 3 mess

From

Andrew Dunstan

Date:

26 February 2020, 10:47:29

On 2/25/20 8:24 PM, Andrew Dunstan wrote:
> On 2/25/20 7:08 PM, Tom Lane wrote:
>> Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
>>> On 2/25/20 5:06 PM, Tom Lane wrote:
>>>> No joy there --- now that I look closer, it seems the cfbot doesn't
>>>> build any of the external-language PLs on Windows.  I'll have to
>>>> wait for some reviewer to try it.
>>> What are the requirements for testing? bowerbird builds with python 2.7,
>>> although I guess I should really try to upgrade it  3.x.
>> Has to be python 3, unfortunately; the patch has no effect on a
>> python 2 build.
>>
>>             
>
>
> Yeah, I have python3 working on drongo, I'll test there.
>

It's almost there, you need to add something like this to Mkvcbuild.pm:


    if ($solution->{options}->{python2_stub})
    {
        my $plpython2_stub =
          $solution->AddProject('plpython2', 'dll', 'PLs',
'src/pl/stub_plpython2');
        $plpython2_stub->AddReference($postgres);
    }

cheers


andrew

-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Resolving the python 2 -> python 3 mess

From

Andrew Dunstan

Date:

26 February 2020, 11:17:21

On 2/26/20 2:47 AM, Andrew Dunstan wrote:
> On 2/25/20 8:24 PM, Andrew Dunstan wrote:
>> On 2/25/20 7:08 PM, Tom Lane wrote:
>>> Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
>>>> On 2/25/20 5:06 PM, Tom Lane wrote:
>>>>> No joy there --- now that I look closer, it seems the cfbot doesn't
>>>>> build any of the external-language PLs on Windows.  I'll have to
>>>>> wait for some reviewer to try it.
>>>> What are the requirements for testing? bowerbird builds with python 2.7,
>>>> although I guess I should really try to upgrade it  3.x.
>>> Has to be python 3, unfortunately; the patch has no effect on a
>>> python 2 build.
>>>
>>>             
>>
>> Yeah, I have python3 working on drongo, I'll test there.
>>
> It's almost there, you need to add something like this to Mkvcbuild.pm:
>
>
>     if ($solution->{options}->{python2_stub})
>     {
>         my $plpython2_stub =
>           $solution->AddProject('plpython2', 'dll', 'PLs',
> 'src/pl/stub_plpython2');
>         $plpython2_stub->AddReference($postgres);
>     }





However, when it get to testing contrib it complains like this:


============================================================
Checking hstore_plpython
C:/prog/bf/root/HEAD/pgsql/Release/pg_regress/pg_regress
--bindir=C:/prog/bf/root/HEAD/pgsql/Release/psql
--dbname=contrib_regression --load-ex
tension=hstore --load-extension=plpythonu
--load-extension=hstore_plpythonu hstore_plpython
(using postmaster on localhost, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== installing hstore                      ==============
CREATE EXTENSION
============== installing plpythonu                   ==============
CREATE EXTENSION
============== installing hstore_plpythonu            ==============
ERROR:  could not access file "$libdir/hstore_plpython2": No such file
or directory
command failed: "C:/prog/bf/root/HEAD/pgsql/Release/psql/psql" -X -c
"CREATE EXTENSION IF NOT EXISTS \"hstore_plpythonu\"" "contrib_regression"


So there's a bit more work to do.


cheers


andrew



-- 

Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Resolving the python 2 -> python 3 mess

From

Andrew Dunstan

Date:

26 February 2020, 14:15:58

On 2/26/20 3:17 AM, Andrew Dunstan wrote:
> On 2/26/20 2:47 AM, Andrew Dunstan wrote:
>> On 2/25/20 8:24 PM, Andrew Dunstan wrote:
>>> On 2/25/20 7:08 PM, Tom Lane wrote:
>>>> Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
>>>>> On 2/25/20 5:06 PM, Tom Lane wrote:
>>>>>> No joy there --- now that I look closer, it seems the cfbot doesn't
>>>>>> build any of the external-language PLs on Windows.  I'll have to
>>>>>> wait for some reviewer to try it.
>>>>> What are the requirements for testing? bowerbird builds with python 2.7,
>>>>> although I guess I should really try to upgrade it  3.x.
>>>> Has to be python 3, unfortunately; the patch has no effect on a
>>>> python 2 build.
>>>>
>>>>             
>>> Yeah, I have python3 working on drongo, I'll test there.
>>>
>> It's almost there, you need to add something like this to Mkvcbuild.pm:
>>
>>
>>     if ($solution->{options}->{python2_stub})
>>     {
>>         my $plpython2_stub =
>>           $solution->AddProject('plpython2', 'dll', 'PLs',
>> 'src/pl/stub_plpython2');
>>         $plpython2_stub->AddReference($postgres);
>>     }
>
>
>
>
> However, when it get to testing contrib it complains like this:
>
>
> ============================================================
> Checking hstore_plpython
> C:/prog/bf/root/HEAD/pgsql/Release/pg_regress/pg_regress
> --bindir=C:/prog/bf/root/HEAD/pgsql/Release/psql
> --dbname=contrib_regression --load-ex
> tension=hstore --load-extension=plpythonu
> --load-extension=hstore_plpythonu hstore_plpython
> (using postmaster on localhost, default port)
> ============== dropping database "contrib_regression" ==============
> DROP DATABASE
> ============== creating database "contrib_regression" ==============
> CREATE DATABASE
> ALTER DATABASE
> ============== installing hstore                      ==============
> CREATE EXTENSION
> ============== installing plpythonu                   ==============
> CREATE EXTENSION
> ============== installing hstore_plpythonu            ==============
> ERROR:  could not access file "$libdir/hstore_plpython2": No such file
> or directory
> command failed: "C:/prog/bf/root/HEAD/pgsql/Release/psql/psql" -X -c
> "CREATE EXTENSION IF NOT EXISTS \"hstore_plpythonu\"" "contrib_regression"
>
>
> So there's a bit more work to do.
>
>


This seems to fix it.


cheers


andrew



-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment

py3-fix-vcregress.patch

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

26 February 2020, 18:03:14

Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> This seems to fix it.

OK, so we need that *and* the AddProject addition you mentioned?

            regards, tom lane

Re: Resolving the python 2 -> python 3 mess

From

Andrew Dunstan

Date:

26 February 2020, 23:04:52

On Thu, Feb 27, 2020 at 1:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> > This seems to fix it.
>
> OK, so we need that *and* the AddProject addition you mentioned?
>
>

Yes, the first one builds it, the second one fixes the tests to run correctly.

cheers

andrew

-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

28 February 2020, 00:11:05

Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> On Thu, Feb 27, 2020 at 1:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> OK, so we need that *and* the AddProject addition you mentioned?

> Yes, the first one builds it, the second one fixes the tests to run correctly.

Thanks, here's a patchset incorporating those fixes.  Otherwise
same as before.

            regards, tom lane

diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index 1921915..ac989a3 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -164,13 +164,6 @@
   </para>

   <para>
-   See also the
-   document <ulink url="https://docs.python.org/3/whatsnew/3.0.html">What's
-   New In Python 3.0</ulink> for more information about porting to
-   Python 3.
-  </para>
-
-  <para>
    It is not allowed to use PL/Python based on Python 2 and PL/Python
    based on Python 3 in the same session, because the symbols in the
    dynamic modules would clash, which could result in crashes of the
@@ -179,6 +172,90 @@
    a mismatch is detected.  It is possible, however, to use both
    PL/Python variants in the same database, from separate sessions.
   </para>
+
+  <sect2 id="plpython-python3-conversion">
+   <title>Converting from Python 2 to Python 3</title>
+
+   <para>
+    See the
+    document <ulink url="https://docs.python.org/3/whatsnew/3.0.html">What's
+    New In Python 3.0</ulink> for the Python community's information and
+    recommendations about porting to Python 3.
+   </para>
+
+   <para>
+    <productname>PostgreSQL</productname> provides some support for helping
+    you to convert existing Python 2 routines to Python 3.  In an
+    installation built with Python 3, there is an
+    extension <filename>convert_python3</filename> that changes functions
+    and procedures from the <literal>plpythonu</literal>
+    and <literal>plpython2u</literal> languages to
+    the <literal>plpython3u</literal> language.  While doing so, it applies
+    the <filename>2to3</filename> tool described in the above document to
+    the body of each such routine.
+   </para>
+
+   <para>
+    Using <filename>convert_python3</filename> can be as simple as:
+<programlisting>
+CREATE EXTENSION convert_python3;
+CALL convert_python3_all();
+</programlisting>
+    This must be done as database superuser.  If you wish, you can drop the
+    extension once you're done converting everything.
+   </para>
+
+   <para>
+    Since <filename>convert_python3</filename> is Python 3 code, be careful
+    not to install or run it in a session that has previously executed any
+    Python 2 code.  As explained above, that won't work.
+   </para>
+
+   <para>
+    <function>convert_python3_all</function> has two optional arguments: the
+    name of the conversion tool to use (by default <literal>2to3</literal>,
+    but you might for instance need to provide a full path name) and any
+    special command-line options to provide to it.  You might for example
+    want to adjust the set of <quote>fixer</quote> rules
+    that <literal>2to3</literal> applies:
+<programlisting>
+CALL convert_python3_all(options => '-f idioms -x apply');
+</programlisting>
+    See <literal>2to3</literal>'s
+    <ulink url="https://docs.python.org/3/library/2to3.html">documentation</ulink>
+    for more information.
+   </para>
+
+   <para>
+    The <filename>convert_python3</filename> extension also provides a
+    procedure that converts just one Python 2 function at a time:
+<programlisting>
+CALL convert_python3_one('myfunc(int)');
+</programlisting>
+    The argument is the target function's OID, which can be written as
+    a <type>regprocedure</type> constant (see
+    <xref linkend="datatype-oid"/>).  The main reason to use this would be
+    if you need to use different options for different functions.  It has
+    the same optional arguments as <function>convert_python3_all</function>:
+<programlisting>
+CALL convert_python3_one('otherfunc(text)', tool => '/usr/bin/2to3',
+                         options => '-f idioms');
+</programlisting>
+   </para>
+
+   <para>
+    If you have needs that go beyond this, consult the source code for
+    the <filename>convert_python3</filename> extension (it's just a
+    couple of <literal>plpython3u</literal> procedures) and adapt those
+    procedures as necessary.
+   </para>
+
+   <para>
+    Keep in mind that if you've constructed any <command>DO</command> blocks
+    that use Python 2 code, those will have to be fixed up manually,
+    wherever the source code for them exists.
+   </para>
+  </sect2>
  </sect1>

  <sect1 id="plpython-funcs">
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 9e95285..03f858b 100644
--- a/src/pl/plpython/Makefile
+++ b/src/pl/plpython/Makefile
@@ -38,6 +38,9 @@ DATA = $(NAME)u.control $(NAME)u--1.0.sql
 ifeq ($(python_majorversion),2)
 DATA += plpythonu.control plpythonu--1.0.sql
 endif
+ifeq ($(python_majorversion),3)
+DATA += convert_python3.control convert_python3--1.0.sql
+endif

 # header files to install - it's not clear which of these might be needed
 # so install them all.
diff --git a/src/pl/plpython/convert_python3--1.0.sql b/src/pl/plpython/convert_python3--1.0.sql
new file mode 100644
index 0000000..3444ac0
--- /dev/null
+++ b/src/pl/plpython/convert_python3--1.0.sql
@@ -0,0 +1,149 @@
+/* convert_python3--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION convert_python3" to load this file. \quit
+
+-- This module provides two procedures, one to convert all python2
+-- functions and one to do just one.  They're nearly identical, and
+-- in principle convert_python3_all() could be written as a loop
+-- around convert_python3_one().  It's not done that way since
+-- creating a temp directory for each function in a bulk conversion
+-- could get expensive.
+
+-- For some benighted reason, lib2to3 has exactly no documented API,
+-- so we must use the command-line API "2to3" instead.  User may pass
+-- in the name of that program (in case it's not in the server's PATH)
+-- as well as any desired options for it (perhaps some -f switches).
+
+create procedure convert_python3_all(tool text default '2to3',
+                                     options text default '')
+language plpython3u as $$
+import re, subprocess, tempfile
+
+# pattern to extract just the function header from pg_get_functiondef result
+aspat = re.compile("^(.*?\nAS )", re.DOTALL)
+# pattern for replacing LANGUAGE portion
+langpat = re.compile("\n LANGUAGE plpython2?u\n")
+
+# collect info about functions to update
+rv = plpy.execute("""
+select p.oid::pg_catalog.regprocedure as funcid,
+       pg_catalog.pg_get_functiondef(p.oid) as fd,
+       prosrc as body
+from pg_catalog.pg_proc p join pg_catalog.pg_language l on p.prolang = l.oid
+where lanname in ('plpythonu', 'plpython2u')
+order by proname
+""")
+
+# Make a temp directory to hold the file for 2to3 to work on.
+with tempfile.TemporaryDirectory() as tmpdirname:
+
+    # process each function
+    for r in rv:
+        # emit notices so user can tell which function failed, if one does
+        plpy.notice("converting function " + r["funcid"])
+
+        # extract everything but the body from pg_get_functiondef result
+        m = aspat.match(r["fd"])
+        if not m:
+            raise ValueError('unexpected match failure')
+        fheader = m.group(1)
+
+        # replace the language clause
+        fheader = langpat.sub("\n LANGUAGE plpython3u\n", fheader, 1)
+
+        # put body in a temp file so we can apply 2to3
+        f = open(tmpdirname + "/temp.py", mode = 'w')
+        f.write(r["body"])
+        f.close()
+
+        # apply 2to3 to body
+        subprocess.check_call(tool + " " + options + " --no-diffs -w " + tmpdirname + "/temp.py", shell=True)
+        f = open(tmpdirname + "/temp.py", mode = 'r')
+        fbody = f.read()
+        f.close()
+
+        # ensure check_function_bodies is enabled
+        plpy.execute("set local check_function_bodies = true")
+
+        # construct and execute SQL command to replace the function
+        newstmt = fheader + plpy.quote_literal(fbody)
+        # uncomment this for debugging purposes:
+        # plpy.info(newstmt)
+        plpy.execute(newstmt)
+
+        # commit after each successful replacement, in case a later one fails
+        plpy.commit()
+$$;
+
+-- The above procedure has to be superuser-only since it trivially allows
+-- executing random programs.  But you'd have to be superuser anyway
+-- to replace the definitions of plpython functions.
+
+revoke all on procedure convert_python3_all(text, text) from public;
+
+
+-- Here's the one-function version.
+
+create procedure convert_python3_one(funcid regprocedure,
+                                     tool text default '2to3',
+                                     options text default '')
+language plpython3u as $$
+import re, subprocess, tempfile
+
+# pattern to extract just the function header from pg_get_functiondef result
+aspat = re.compile("^(.*?\nAS )", re.DOTALL)
+# pattern for replacing LANGUAGE portion
+langpat = re.compile("\n LANGUAGE plpython2?u\n")
+
+# collect info about function to update, making sure it's the right language
+plan = plpy.prepare("""
+select p.oid::pg_catalog.regprocedure as funcid,
+       pg_catalog.pg_get_functiondef(p.oid) as fd,
+       prosrc as body
+from pg_catalog.pg_proc p join pg_catalog.pg_language l on p.prolang = l.oid
+where p.oid = $1 and lanname in ('plpythonu', 'plpython2u')
+""", ["pg_catalog.regprocedure"])
+
+rv = plpy.execute(plan, [funcid])
+
+# Make a temp directory to hold the file for 2to3 to work on.
+with tempfile.TemporaryDirectory() as tmpdirname:
+
+    # process each function (we only expect one, but it's easy to loop)
+    for r in rv:
+        # extract everything but the body from pg_get_functiondef result
+        m = aspat.match(r["fd"])
+        if not m:
+            raise ValueError('unexpected match failure')
+        fheader = m.group(1)
+
+        # replace the language clause
+        fheader = langpat.sub("\n LANGUAGE plpython3u\n", fheader, 1)
+
+        # put body in a temp file so we can apply 2to3
+        f = open(tmpdirname + "/temp.py", mode = 'w')
+        f.write(r["body"])
+        f.close()
+
+        # apply 2to3 to body
+        subprocess.check_call(tool + " " + options + " --no-diffs -w " + tmpdirname + "/temp.py", shell=True)
+        f = open(tmpdirname + "/temp.py", mode = 'r')
+        fbody = f.read()
+        f.close()
+
+        # ensure check_function_bodies is enabled
+        plpy.execute("set local check_function_bodies = true")
+
+        # construct and execute SQL command to replace the function
+        newstmt = fheader + plpy.quote_literal(fbody)
+        # uncomment this for debugging purposes:
+        # plpy.info(newstmt)
+        plpy.execute(newstmt)
+$$;
+
+-- The above procedure has to be superuser-only since it trivially allows
+-- executing random programs.  But you'd have to be superuser anyway
+-- to replace the definitions of plpython functions.
+
+revoke all on procedure convert_python3_one(regprocedure, text, text) from public;
diff --git a/src/pl/plpython/convert_python3.control b/src/pl/plpython/convert_python3.control
new file mode 100644
index 0000000..8debb3b
--- /dev/null
+++ b/src/pl/plpython/convert_python3.control
@@ -0,0 +1,5 @@
+# convert_python3 extension
+comment = 'convert plpython[2]u functions to plpython3u'
+default_version = '1.0'
+relocatable = true
+requires = 'plpython3u'
diff --git a/configure b/configure
index d2c74e5..3feebf6 100755
--- a/configure
+++ b/configure
@@ -714,6 +714,7 @@ with_ldap
 with_krb_srvnam
 krb_srvtab
 with_gssapi
+with_python2_stub
 with_python
 with_perl
 with_tcl
@@ -847,6 +848,7 @@ with_tcl
 with_tclconfig
 with_perl
 with_python
+with_python2_stub
 with_gssapi
 with_krb_srvnam
 with_pam
@@ -1546,6 +1548,7 @@ Optional Packages:
   --with-tclconfig=DIR    tclConfig.sh is in DIR
   --with-perl             build Perl modules (PL/Perl)
   --with-python           build Python modules (PL/Python)
+  --with-python2-stub     build Python 2 compatibility stub
   --with-gssapi           build with GSSAPI support
   --with-krb-srvnam=NAME  default service principal name in Kerberos (GSSAPI)
                           [postgres]
@@ -7644,6 +7647,32 @@ fi
 $as_echo "$with_python" >&6; }


+
+
+
+# Check whether --with-python2-stub was given.
+if test "${with_python2_stub+set}" = set; then :
+  withval=$with_python2_stub;
+  case $withval in
+    yes)
+      :
+      ;;
+    no)
+      :
+      ;;
+    *)
+      as_fn_error $? "no argument expected for --with-python2-stub option" "$LINENO" 5
+      ;;
+  esac
+
+else
+  with_python2_stub=no
+
+fi
+
+
+
+
 #
 # GSSAPI
 #
@@ -9745,6 +9774,12 @@ $as_echo "${python_libspec} ${python_additional_libs}" >&6; }



+  # Disable building Python 2 stub if primary version isn't Python 3
+  if test "$python_majorversion" -lt 3; then
+    with_python2_stub=no
+  fi
+else
+  with_python2_stub=no
 fi

 if test "$cross_compiling" = yes && test -z "$with_system_tzdata"; then
diff --git a/configure.in b/configure.in
index 0b0a871..ffa49c9 100644
--- a/configure.in
+++ b/configure.in
@@ -766,6 +766,9 @@ PGAC_ARG_BOOL(with, python, no, [build Python modules (PL/Python)])
 AC_MSG_RESULT([$with_python])
 AC_SUBST(with_python)

+PGAC_ARG_BOOL(with, python2-stub, no, [build Python 2 compatibility stub])
+AC_SUBST(with_python2_stub)
+
 #
 # GSSAPI
 #
@@ -1042,6 +1045,12 @@ fi
 if test "$with_python" = yes; then
   PGAC_PATH_PYTHON
   PGAC_CHECK_PYTHON_EMBED_SETUP
+  # Disable building Python 2 stub if primary version isn't Python 3
+  if test "$python_majorversion" -lt 3; then
+    with_python2_stub=no
+  fi
+else
+  with_python2_stub=no
 fi

 if test "$cross_compiling" = yes && test -z "$with_system_tzdata"; then
diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml
index cc242dc..ff81ea8 100644
--- a/doc/src/sgml/installation.sgml
+++ b/doc/src/sgml/installation.sgml
@@ -877,6 +877,23 @@ build-postgresql:
       </varlistentry>

       <varlistentry>
+       <term><option>--with-python2-stub</option></term>
+       <listitem>
+        <para>
+         Build a stub version of the Python
+         2 <application>PL/Python</application> language, to aid in
+         transitioning old <application>PL/Python</application> code.
+         This option is recommended if you are
+         building <application>PL/Python</application> for Python 3
+         and do not intend to also build a version for Python 2.
+         This option is ignored if you do not also
+         specify <option>--with-python</option>, or if the selected Python
+         implementation is Python 2.
+        </para>
+       </listitem>
+      </varlistentry>
+
+      <varlistentry>
        <term><option>--with-tcl</option></term>
        <listitem>
         <para>
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index ac989a3..9014750 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -77,13 +77,7 @@
      <para>
       The language named <literal>plpythonu</literal> implements
       PL/Python based on the default Python language variant, which is
-      currently Python 2.  (This default is independent of what any
-      local Python installations might consider to be
-      their <quote>default</quote>, for example,
-      what <filename>/usr/bin/python</filename> might be.)  The
-      default will probably be changed to Python 3 in a distant future
-      release of PostgreSQL, depending on the progress of the
-      migration to Python 3 in the Python community.
+      determined by the person who builds and installs PostgreSQL.
      </para>
     </listitem>
    </itemizedlist>
@@ -103,9 +97,22 @@
     The built variant depends on which Python version was found during
     the installation or which version was explicitly set using
     the <envar>PYTHON</envar> environment variable;
-    see <xref linkend="install-procedure"/>.  To make both variants of
-    PL/Python available in one installation, the source tree has to be
-    configured and built twice.
+    see <xref linkend="install-procedure"/>.  To make working versions of
+    both variants of PL/Python available in one installation, the source
+    tree has to be configured and built twice.
+   </para>
+
+   <para>
+    However, if the builder specifies both <option>--with-python</option>
+    and <option>--with-python2-stub</option> while building with Python 3,
+    then in addition to the working Python-3-based PL/Python,
+    a <quote>stub</quote> version of <literal>plpython2u</literal> is
+    produced.  This stub is non-functional and will simply throw errors if
+    any <literal>plpython2u</literal> function is executed, but its
+    presence allows such functions to be loaded into the database in
+    preparation for conversion to <literal>plpython3u</literal>.  This
+    configuration also causes <literal>plpythonu</literal> to become
+    Python 3 rather than Python 2.
    </para>
   </tip>

@@ -115,49 +122,38 @@
    <itemizedlist>
     <listitem>
      <para>
-      Existing users and users who are currently not interested in
-      Python 3 use the language name <literal>plpythonu</literal> and
-      don't have to change anything for the foreseeable future.  It is
-      recommended to gradually <quote>future-proof</quote> the code
-      via migration to Python 2.6/2.7 to simplify the eventual
-      migration to Python 3.
+      Use the language name <literal>plpythonu</literal> if you have
+      simple Python code that works for either Python 2 or Python 3;
+      or if you are prepared to migrate your code whenever your packager
+      decides to change the default Python version.
      </para>

      <para>
       In practice, many PL/Python functions will migrate to Python 3
-      with few or no changes.
+      with few or no changes.  However, there are some cases that
+      will require more work.
      </para>
     </listitem>

     <listitem>
      <para>
-      Users who know that they have heavily Python 2 dependent code
-      and don't plan to ever change it can make use of
+      If you know that you have heavily Python 2 dependent code
+      and don't plan to ever change it, you can make use of
       the <literal>plpython2u</literal> language name.  This will
-      continue to work into the very distant future, until Python 2
-      support might be completely dropped by PostgreSQL.
-     </para>
-    </listitem>
-
-    <listitem>
-     <para>
-      Users who want to dive into Python 3 can use
-      the <literal>plpython3u</literal> language name, which will keep
-      working forever by today's standards.  In the distant future,
-      when Python 3 might become the default, they might like to
-      remove the <quote>3</quote> for aesthetic reasons.
+      continue to work for as long as your operating system platform
+      continues to provide Python 2.  PostgreSQL itself may eventually
+      drop support for this option, but that is unlikely to happen
+      as long as any platform support remains in the wild.
      </para>
     </listitem>

     <listitem>
      <para>
-      Daredevils, who want to build a Python-3-only operating system
-      environment, can change the contents of
-      <literal>plpythonu</literal>'s extension control and script files
-      to make <literal>plpythonu</literal> be equivalent
-      to <literal>plpython3u</literal>, keeping in mind that this
-      would make their installation incompatible with most of the rest
-      of the world.
+      If you know your code requires Python 3, use
+      the <literal>plpython3u</literal> language name.  You can also
+      use this language name to tag functions that have been successfully
+      converted from Python 2, so as to keep track of which functions have
+      been converted and which have not.
      </para>
     </listitem>
    </itemizedlist>
@@ -171,6 +167,8 @@
    Python major versions in a session, which will abort the session if
    a mismatch is detected.  It is possible, however, to use both
    PL/Python variants in the same database, from separate sessions.
+   (Also, the <quote>stub</quote> version of <literal>plpython2u</literal>
+   does not present any conflicts.)
   </para>

   <sect2 id="plpython-python3-conversion">
diff --git a/src/Makefile.global.in b/src/Makefile.global.in
index e4db3e8..1f880b6 100644
--- a/src/Makefile.global.in
+++ b/src/Makefile.global.in
@@ -180,6 +180,7 @@ bitcodedir = $(pkglibdir)/bitcode
 with_icu    = @with_icu@
 with_perl    = @with_perl@
 with_python    = @with_python@
+with_python2_stub = @with_python2_stub@
 with_tcl    = @with_tcl@
 with_openssl    = @with_openssl@
 with_readline    = @with_readline@
diff --git a/src/pl/Makefile b/src/pl/Makefile
index c4a0d1c..fe9cd0f 100644
--- a/src/pl/Makefile
+++ b/src/pl/Makefile
@@ -26,6 +26,12 @@ else
 ALWAYS_SUBDIRS += plpython
 endif

+ifeq ($(with_python2_stub), yes)
+SUBDIRS += stub_plpython2
+else
+ALWAYS_SUBDIRS += stub_plpython2
+endif
+
 ifeq ($(with_tcl), yes)
 SUBDIRS += tcl
 else
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 03f858b..ac262fa 100644
--- a/src/pl/plpython/Makefile
+++ b/src/pl/plpython/Makefile
@@ -37,6 +37,9 @@ OBJS = \
 DATA = $(NAME)u.control $(NAME)u--1.0.sql
 ifeq ($(python_majorversion),2)
 DATA += plpythonu.control plpythonu--1.0.sql
+else ifeq ($(with_python2_stub), yes)
+# install extension files for the stub module (see ../stub_plpython2)
+DATA += plpythonu.control plpythonu--1.0.sql plpython2u.control plpython2u--1.0.sql
 endif
 ifeq ($(python_majorversion),3)
 DATA += convert_python3.control convert_python3--1.0.sql
@@ -109,6 +112,7 @@ REGRESS = \
     plpython_composite \
     plpython_subtransaction \
     plpython_transaction \
+    plpython_stub \
     plpython_drop

 REGRESS_PLPYTHON3_MANGLE := $(REGRESS)
diff --git a/src/pl/plpython/expected/plpython_stub.out b/src/pl/plpython/expected/plpython_stub.out
new file mode 100644
index 0000000..2a565b4
--- /dev/null
+++ b/src/pl/plpython/expected/plpython_stub.out
@@ -0,0 +1,71 @@
+--
+-- Test stub plpython2 and convert_python3 extension
+--
+-- skip test unless we have python3 installed and the others are available
+SELECT (SELECT count(*) FROM pg_extension WHERE extname = 'plpython3u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpython2u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpythonu') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'convert_python3') = 0
+       AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+-- Funny formatting of some commands below is to prevent
+-- regress-python3-mangle.mk from being "helpful".
+-- install the additional extensions
+CREATE EXTENSION
+plpython2u;
+CREATE EXTENSION
+plpythonu;
+CREATE EXTENSION convert_python3;
+-- create some functions that need conversion
+create function convert1() returns int
+as 'return 123l'
+language
+plpython2u
+immutable;
+-- disable syntax checking since if plpythonu is python 3, this'll fail
+set check_function_bodies = false;
+create function convert2() returns int
+as 'return 123l'
+language
+plpythonu
+immutable;
+-- can't use \sf here because mangling would affect the LANGUAGE clauses
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert1';
+ proname  |  lanname   |   prosrc
+----------+------------+-------------
+ convert1 | plpython2u | return 123l
+(1 row)
+
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert2';
+ proname  |  lanname  |   prosrc
+----------+-----------+-------------
+ convert2 | plpythonu | return 123l
+(1 row)
+
+call convert_python3_all();
+NOTICE:  converting function convert1()
+NOTICE:  converting function convert2()
+\sf convert1()
+CREATE OR REPLACE FUNCTION public.convert1()
+ RETURNS integer
+ LANGUAGE plpython3u
+ IMMUTABLE
+AS $function$return 123$function$
+\sf convert2()
+CREATE OR REPLACE FUNCTION public.convert2()
+ RETURNS integer
+ LANGUAGE plpython3u
+ IMMUTABLE
+AS $function$return 123$function$
+-- clean up
+DROP EXTENSION
+plpython2u;
+DROP EXTENSION
+plpythonu;
+DROP EXTENSION convert_python3;
diff --git a/src/pl/plpython/expected/plpython_stub_1.out b/src/pl/plpython/expected/plpython_stub_1.out
new file mode 100644
index 0000000..bb625b9
--- /dev/null
+++ b/src/pl/plpython/expected/plpython_stub_1.out
@@ -0,0 +1,11 @@
+--
+-- Test stub plpython2 and convert_python3 extension
+--
+-- skip test unless we have python3 installed and the others are available
+SELECT (SELECT count(*) FROM pg_extension WHERE extname = 'plpython3u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpython2u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpythonu') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'convert_python3') = 0
+       AS skip_test \gset
+\if :skip_test
+\quit
diff --git a/src/pl/plpython/sql/plpython_stub.sql b/src/pl/plpython/sql/plpython_stub.sql
new file mode 100644
index 0000000..8c587fd
--- /dev/null
+++ b/src/pl/plpython/sql/plpython_stub.sql
@@ -0,0 +1,60 @@
+--
+-- Test stub plpython2 and convert_python3 extension
+--
+
+-- skip test unless we have python3 installed and the others are available
+SELECT (SELECT count(*) FROM pg_extension WHERE extname = 'plpython3u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpython2u') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'plpythonu') = 0 OR
+       (SELECT count(*) FROM pg_available_extensions WHERE name = 'convert_python3') = 0
+       AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+
+-- Funny formatting of some commands below is to prevent
+-- regress-python3-mangle.mk from being "helpful".
+
+-- install the additional extensions
+CREATE EXTENSION
+plpython2u;
+CREATE EXTENSION
+plpythonu;
+CREATE EXTENSION convert_python3;
+
+-- create some functions that need conversion
+create function convert1() returns int
+as 'return 123l'
+language
+plpython2u
+immutable;
+
+-- disable syntax checking since if plpythonu is python 3, this'll fail
+set check_function_bodies = false;
+
+create function convert2() returns int
+as 'return 123l'
+language
+plpythonu
+immutable;
+
+-- can't use \sf here because mangling would affect the LANGUAGE clauses
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert1';
+
+select proname, lanname, prosrc
+from pg_proc p join pg_language l on prolang = l.oid
+where proname = 'convert2';
+
+call convert_python3_all();
+
+\sf convert1()
+\sf convert2()
+
+-- clean up
+DROP EXTENSION
+plpython2u;
+DROP EXTENSION
+plpythonu;
+DROP EXTENSION convert_python3;
diff --git a/src/pl/stub_plpython2/Makefile b/src/pl/stub_plpython2/Makefile
new file mode 100644
index 0000000..67e7559
--- /dev/null
+++ b/src/pl/stub_plpython2/Makefile
@@ -0,0 +1,38 @@
+# src/pl/stub_plpython2/Makefile
+
+# Note that this Makefile only builds and installs a quasi-dummy
+# version of plpython2.so.  The control and script files for the
+# plpythonu and plpython2u extensions are installed by ../plpython;
+# they are the same whether we're using real or stub plpython2.
+# (We'd probably not have this separate subdirectory at all, except
+# that Makefile.shlib can only build one shlib per directory.)
+
+subdir = src/pl/stub_plpython2
+top_builddir = ../../..
+include $(top_builddir)/src/Makefile.global
+
+PGFILEDESC = "PL/Python - procedural language stub for Python 2"
+
+NAME = plpython2
+
+OBJS = \
+    $(WIN32RES) \
+    stub_plpython2.o
+
+include $(top_srcdir)/src/Makefile.shlib
+
+all: all-lib
+
+# Ensure parallel safety if a build is started in this directory
+$(OBJS): | submake-generated-headers
+
+install: all install-lib
+
+installdirs: installdirs-lib
+
+uninstall: uninstall-lib
+
+clean distclean: clean-lib
+    rm -f $(OBJS)
+
+maintainer-clean: distclean
diff --git a/src/pl/stub_plpython2/stub_plpython2.c b/src/pl/stub_plpython2/stub_plpython2.c
new file mode 100644
index 0000000..4ade88b
--- /dev/null
+++ b/src/pl/stub_plpython2/stub_plpython2.c
@@ -0,0 +1,105 @@
+/*
+ * PL/Python stub for Python 2, in an environment that has only Python 3
+ *
+ * Our strategy is to pass through "plpythonu" functions to Python 3,
+ * but throw a not-implemented error for "plpython2u".
+ *
+ * Pass-through is implemented by using dfmgr.c to look up the appropriate
+ * function in plpython3.so, rather than trying to resolve the reference
+ * directly.  This greatly simplifies building this as an independent
+ * shared library, and it ensures that we can't somehow pull in a different
+ * version of plpython3 (and thence libpython) than would get loaded for
+ * a plpython3u function.
+ *
+ * src/pl/stub_plpython2/stub_plpython2.c
+ */
+
+#include "postgres.h"
+
+#include "fmgr.h"
+
+#define PLPYTHON_LIBNAME "$libdir/plpython3"
+
+/*
+ * exported functions
+ */
+
+PG_MODULE_MAGIC;
+
+PG_FUNCTION_INFO_V1(plpython_validator);
+PG_FUNCTION_INFO_V1(plpython_call_handler);
+PG_FUNCTION_INFO_V1(plpython_inline_handler);
+
+PG_FUNCTION_INFO_V1(plpython2_validator);
+PG_FUNCTION_INFO_V1(plpython2_call_handler);
+PG_FUNCTION_INFO_V1(plpython2_inline_handler);
+
+
+Datum
+plpython_validator(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_validator = NULL;
+
+    if (plpython3_validator == NULL)
+        plpython3_validator =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_validator",
+                                   true, NULL);
+
+    return (*plpython3_validator) (fcinfo);
+}
+
+Datum
+plpython_call_handler(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_call_handler = NULL;
+
+    if (plpython3_call_handler == NULL)
+        plpython3_call_handler =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_call_handler",
+                                   true, NULL);
+
+    return (*plpython3_call_handler) (fcinfo);
+}
+
+Datum
+plpython_inline_handler(PG_FUNCTION_ARGS)
+{
+    static PGFunction plpython3_inline_handler = NULL;
+
+    if (plpython3_inline_handler == NULL)
+        plpython3_inline_handler =
+            load_external_function(PLPYTHON_LIBNAME,
+                                   "plpython3_inline_handler",
+                                   true, NULL);
+
+    return (*plpython3_inline_handler) (fcinfo);
+}
+
+Datum
+plpython2_validator(PG_FUNCTION_ARGS)
+{
+    /* It seems more convenient to do nothing here than throw an error. */
+    PG_RETURN_VOID();
+}
+
+Datum
+plpython2_call_handler(PG_FUNCTION_ARGS)
+{
+    ereport(ERROR,
+            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+             errmsg("Python 2 is no longer supported"),
+             errhint("Convert the function to use plpython3u.")));
+    PG_RETURN_NULL();            /* keep compiler quiet */
+}
+
+Datum
+plpython2_inline_handler(PG_FUNCTION_ARGS)
+{
+    ereport(ERROR,
+            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+             errmsg("Python 2 is no longer supported"),
+             errhint("Convert the DO block to use plpython3u.")));
+    PG_RETURN_NULL();            /* keep compiler quiet */
+}
diff --git a/src/tools/msvc/Install.pm b/src/tools/msvc/Install.pm
index 1a92ed2..cf8b1e2 100644
--- a/src/tools/msvc/Install.pm
+++ b/src/tools/msvc/Install.pm
@@ -154,6 +154,7 @@ sub Install
         my @pldirs             = ('src/pl/plpgsql/src');
         push @pldirs, "src/pl/plperl"   if $config->{perl};
         push @pldirs, "src/pl/plpython" if $config->{python};
+        push @pldirs, "src/pl/stub_plpython2" if $config->{python2_stub};
         push @pldirs, "src/pl/tcl"      if $config->{tcl};
         File::Find::find(
             {
diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm
index 834c2c3..b5be405 100644
--- a/src/tools/msvc/Mkvcbuild.pm
+++ b/src/tools/msvc/Mkvcbuild.pm
@@ -531,6 +531,22 @@ sub mkvcbuild
             'ltree',                        'contrib');
         $ltree_plpython->AddDefine(
             'PLPYTHON_LIBNAME="plpython' . $pymajorver . '"');
+
+        # Ignore --with-python2-stub if building real plpython2
+        $solution->{options}->{python2_stub} = 0 if $pymajorver < 3;
+
+        # Otherwise, if the stub is requested then build it
+        if ($solution->{options}->{python2_stub})
+        {
+            my $plpython2_stub =
+              $solution->AddProject('plpython2', 'dll', 'PLs',
+                'src/pl/stub_plpython2');
+            $plpython2_stub->AddReference($postgres);
+        }
+    }
+    else
+    {
+        $solution->{options}->{python2_stub} = 0;
     }

     if ($solution->{options}->{perl})
diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm
index 6b4a6ee..848e118 100644
--- a/src/tools/msvc/Solution.pm
+++ b/src/tools/msvc/Solution.pm
@@ -1145,6 +1145,7 @@ sub GetFakeConfigure
     $cfg .= ' --with-tcl'           if ($self->{options}->{tcl});
     $cfg .= ' --with-perl'          if ($self->{options}->{perl});
     $cfg .= ' --with-python'        if ($self->{options}->{python});
+    $cfg .= ' --with-python2-stub'  if ($self->{options}->{python2_stub});

     return $cfg;
 }
diff --git a/src/tools/msvc/clean.bat b/src/tools/msvc/clean.bat
index 672bb2d..4ad5f07 100755
--- a/src/tools/msvc/clean.bat
+++ b/src/tools/msvc/clean.bat
@@ -26,6 +26,7 @@ if exist src\interfaces\ecpg\test\win32ver.rc del /q src\interfaces\ecpg\test\wi
 if exist src\pl\plperl\win32ver.rc del /q src\pl\plperl\win32ver.rc
 if exist src\pl\plpgsql\src\win32ver.rc del /q src\pl\plpgsql\src\win32ver.rc
 if exist src\pl\plpython\win32ver.rc del /q src\pl\plpython\win32ver.rc
+if exist src\pl\stub_plpython2\win32ver.rc del /q src\pl\stub_plpython2\win32ver.rc
 if exist src\pl\tcl\win32ver.rc del /q src\pl\tcl\win32ver.rc
 if exist src\test\isolation\win32ver.rc del /q src\test\isolation\win32ver.rc
 if exist src\test\regress\win32ver.rc del /q src\test\regress\win32ver.rc
diff --git a/src/tools/msvc/config_default.pl b/src/tools/msvc/config_default.pl
index 2ef2cfc..d7f9b60 100644
--- a/src/tools/msvc/config_default.pl
+++ b/src/tools/msvc/config_default.pl
@@ -16,6 +16,7 @@ our $config = {
     tcl       => undef,    # --with-tcl=<path>
     perl      => undef,    # --with-perl=<path>
     python    => undef,    # --with-python=<path>
+    python2_stub => 1,     # --with-python2-stub (ignored unless Python is v3)
     openssl   => undef,    # --with-openssl=<path>
     uuid      => undef,    # --with-uuid=<path>
     xml       => undef,    # --with-libxml=<path>
diff --git a/src/tools/msvc/vcregress.pl b/src/tools/msvc/vcregress.pl
index 82dca29..a1bba87 100644
--- a/src/tools/msvc/vcregress.pl
+++ b/src/tools/msvc/vcregress.pl
@@ -423,15 +423,15 @@ sub subdircheck

         @opts = grep { $_ !~ /plpythonu/ } @opts;

-        if (-d "$topdir/$Config/plpython2")
+        if (-d "$topdir/$Config/plpython3")
         {
-            push @opts, "--load-extension=plpythonu";
-            push @opts, '--load-extension=' . $module . 'u';
+            @tests = mangle_plpython3(\@tests);
         }
-        else
+        elsif (-d "$topdir/$Config/plpython2")
         {
-            # must be python 3
-            @tests = mangle_plpython3(\@tests);
+            # if python3 doesn't exist, this is real python2 not the stub
+            push @opts, "--load-extension=plpythonu";
+            push @opts, '--load-extension=' . $module . 'u';
         }
     }

Re: Resolving the python 2 -> python 3 mess

From

Marco Atzeri

Date:

26 March 2020, 08:46:05

Am 17.02.2020 um 17:49 schrieb Tom Lane:
> We've had multiple previous discussions of $SUBJECT (eg [1][2]),
> without any resolution of what to do exactly.  Thinking about this
> some more, I had an idea that I don't think has been discussed.
> To wit:
> 
> 1. On platforms where Python 2.x is still supported, recommend that
> packagers continue to build both plpython2 and plpython3, same as now.
> 

there is some documentation on how to build both ?
The INSTALL gives no hint.

And how to build for multiples 3.x ?

Currently for Cygwin package I am building only 2.x and it is clearly
not a good situation.

Regards
Marco

Re: Resolving the python 2 -> python 3 mess

From

Peter Eisentraut

Date:

26 March 2020, 12:14:54

On 2020-03-26 06:46, Marco Atzeri wrote:
> Am 17.02.2020 um 17:49 schrieb Tom Lane:
>> We've had multiple previous discussions of $SUBJECT (eg [1][2]),
>> without any resolution of what to do exactly.  Thinking about this
>> some more, I had an idea that I don't think has been discussed.
>> To wit:
>>
>> 1. On platforms where Python 2.x is still supported, recommend that
>> packagers continue to build both plpython2 and plpython3, same as now.
>>
> 
> there is some documentation on how to build both ?

You have to configure and build the sources twice with different PYTHON 
settings.  It depends on your packaging system how to best arrange that.

> And how to build for multiples 3.x ?

That is not supported.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

26 March 2020, 17:26:25

Marco Atzeri <marco.atzeri@gmail.com> writes:
> Am 17.02.2020 um 17:49 schrieb Tom Lane:
>> 1. On platforms where Python 2.x is still supported, recommend that
>> packagers continue to build both plpython2 and plpython3, same as now.

> there is some documentation on how to build both ?
> The INSTALL gives no hint.

It's explained in the plpython documentation: basically you have to
configure and build the source tree twice (although I think the
second time you can just cd into src/pl/plpython and build/install
only that much).

            regards, tom lane

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

02 April 2020, 16:19:28

I wrote:
> [ a couple patches ]

Ping?  I wish somebody would review this.  I'm not wedded to any
of the details, but it would be an embarrassment for us to ship v13
without any response to the fact that Python 2 is EOL.

            regards, tom lane

Re: Resolving the python 2 -> python 3 mess

From

Noah Misch

Date:

28 May 2020, 11:03:44

On Wed, Feb 19, 2020 at 08:42:36PM +0100, Peter Eisentraut wrote:
> I think there should just
> be an option "plpython is: {2|3|don't build it at all}".  Then packagers can
> match this to what their plan for /usr/bin/python* is -- which appears to be
> different everywhere.

Today, we do not give packagers this sort of discretion over SQL-level
behavior.  We formerly had --disable-integer-datetimes, but I view that as the
lesser of two evils, the greater of which would have been to force dump/reload
of terabyte-scale clusters.  We should continue to follow today's principle,
which entails not inviting packagers to align the nature of "LANGUAGE
plpythonu" with the nature of /usr/bin/python.  Users wouldn't benefit from
alignment.  Moreover, it has long been conventional for the host to be a VM
dedicated to PostgreSQL, in which case users of the DBMS aren't users of its
host.  I don't have an opinion on which version "LANGUAGE plpythonu" should
denote in PostgreSQL v13, but the version should be constant across v13
configurations.  (If some builds make it unavailable or make it an error-only
stub, that is no problem.)

I reviewed all code:

On Thu, Feb 27, 2020 at 04:11:05PM -0500, Tom Lane wrote:
> --- a/configure.in
> +++ b/configure.in
> @@ -766,6 +766,9 @@ PGAC_ARG_BOOL(with, python, no, [build Python modules (PL/Python)])
>  AC_MSG_RESULT([$with_python])
>  AC_SUBST(with_python)
>  
> +PGAC_ARG_BOOL(with, python2-stub, no, [build Python 2 compatibility stub])
> +AC_SUBST(with_python2_stub)
> +
>  #
>  # GSSAPI
>  #
> @@ -1042,6 +1045,12 @@ fi
>  if test "$with_python" = yes; then
>    PGAC_PATH_PYTHON
>    PGAC_CHECK_PYTHON_EMBED_SETUP
> +  # Disable building Python 2 stub if primary version isn't Python 3
> +  if test "$python_majorversion" -lt 3; then
> +    with_python2_stub=no
> +  fi

Standard PostgreSQL practice would be to AC_MSG_ERROR in response to the
infeasible option, not to ignore the option.

> --- /dev/null
> +++ b/src/pl/plpython/sql/plpython_stub.sql

> +call convert_python3_all();

Building with PYTHON=python3 --with-python2-stub, this fails on my RHEL 7.8
installation.  I had installed python34-tools, which provides /usr/bin/2to3-3.
I had not installed python-tools (v2.7.5), which provides /usr/bin/2to3.
Making a 2to3 -> 2to3-3 symlink let the test pass.  Requiring such a step to
pass tests may or may not be fine; what do you think?  (I am attaching
regression.diffs; to my knowledge, it's not interesting.)

> --- a/src/tools/msvc/config_default.pl
> +++ b/src/tools/msvc/config_default.pl
> @@ -16,6 +16,7 @@ our $config = {
>      tcl       => undef,    # --with-tcl=<path>
>      perl      => undef,    # --with-perl=<path>
>      python    => undef,    # --with-python=<path>
> +    python2_stub => 1,     # --with-python2-stub (ignored unless Python is v3)

This default should not depend on whether one uses the MSVC build system or
uses the GNU make build system.

> --- /dev/null
> +++ b/src/pl/plpython/convert_python3--1.0.sql

> +create procedure convert_python3_all(tool text default '2to3',
> +                                     options text default '')
> +language plpython3u as $$
> +import re, subprocess, tempfile
> +
> +# pattern to extract just the function header from pg_get_functiondef result
> +aspat = re.compile("^(.*?\nAS )", re.DOTALL)

This fails on:

create function convert1("
AS " int) returns int
AS $$return 123l$$
language
plpython2u
immutable;

That's not up to project standard, but I'm proceeding to ignore this since the
subject is an untrusted language and ~nobody uses such argument names.

Attachment

regression.diffs

Re: Resolving the python 2 -> python 3 mess

From

Tom Lane

Date:

28 May 2020, 17:44:30

Noah Misch <noah@leadboat.com> writes:
> On Wed, Feb 19, 2020 at 08:42:36PM +0100, Peter Eisentraut wrote:
>> I think there should just
>> be an option "plpython is: {2|3|don't build it at all}".  Then packagers can
>> match this to what their plan for /usr/bin/python* is -- which appears to be
>> different everywhere.

> Today, we do not give packagers this sort of discretion over SQL-level
> behavior.  We formerly had --disable-integer-datetimes, but I view that as the
> lesser of two evils, the greater of which would have been to force dump/reload
> of terabyte-scale clusters.  We should continue to follow today's principle,
> which entails not inviting packagers to align the nature of "LANGUAGE
> plpythonu" with the nature of /usr/bin/python.

FWIW, I've abandoned this patch.  We've concluded (by default, at least)
that nothing is getting done in v13, and by the time v14 is out it will
be too late to have any useful effect.  I expect that the situation
on-the-ground by 2021 will be that packagers build with PYTHON=python3
and package whatever they get from that.  That means (1) plpythonu won't
exist anymore and (2) users will be left to their own devices to convert
existing plpython code.  Now, (1) corresponds to not providing any
/usr/bin/python executable, only python3 -- and that is a really common
choice for distros to make, AFAIK, so I don't feel too awful about it.
I find (2) less than ideal, but there's evidently not enough interest
in doing anything about it.  There's certainly going to be no point in
shipping a solution for (2) if we fail to do so before v14; people
will already have done the work by hand.

We should, however, consider updating the plpython docs to reflect
current reality.  Notably, the existing wording in section 45.1
suggests that we'll eventually redefine "plpythonu" as Python 3,
and it seems to me that that's not going to happen.

            regards, tom lane