Re: Dropping PL language retains support functions

I have a report related to pg_upgrade where the user states that
dropping a PL language retains the PL support functions, and retains the
dependency on the PL library, which causes pg_upgrade to complain. The
exact case is that the user was using plpython2u in PG 9.0, but the PG
9.1 one-click installer only supplies plpython3u.

Pg_upgrade rightly complains that the $libdir/plpython2 is missing. The
user removed their plpython2 functions, and then tried pg_upgrade again,
and they still got the report of the missing $libdir/plpython2 library.

I tested this myself on PG HEAD, and got the same results:

CREATE LANGUAGE plpython2u;
CREATE LANGUAGE

CREATE OR REPLACE FUNCTION pymax (a integer, b integer) RETURNS integer
AS
$$
if a > b:
return a
return b
$$ LANGUAGE plpython2u;
CREATE FUNCTION

DROP LANGUAGE plpython2u CASCADE;
NOTICE: drop cascades to function pymax(integer,integer)
DROP LANGUAGE

SELECT proname,probin FROM pg_proc WHERE probin LIKE '%python%';
proname | probin
--------------------------+-------------------
plpython2_call_handler | $libdir/plpython2
plpython2_inline_handler | $libdir/plpython2
plpython2_validator | $libdir/plpython2
(3 rows)

I looked at our C code, and we basically set up this dependency:

user plpython2 function
depends on
plpython2 language
depends on
plpython2_* support functions

By doing a DROP CASCADE on plpython2, you drop the user functions, but
not the support functions.

This certainly looks like a bug. Should I work on a patch?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> By doing a DROP CASCADE on plpython2, you drop the user functions, but
> not the support functions.

Well, yeah. The language depends on the support functions, not the
other way around.

> This certainly looks like a bug. Should I work on a patch?

It's not a bug, and it's unlikely you can "fix" it in pg_upgrade without
making things worse.

The long-run plan is that the procedural language and its support
functions are all part of an extension and what you do is drop the
extension. We're not quite there yet. As of 9.1, if you do "create
extension plpython2" to start with, dropping the extension does drop the
support functions too ... but if you use the legacy "create language"
syntax, that doesn't happen, because an extension object isn't created.

regards, tom lane

On Mon, Mar 05, 2012 at 11:38:33PM -0500, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > By doing a DROP CASCADE on plpython2, you drop the user functions, but
> > not the support functions.
>
> Well, yeah. The language depends on the support functions, not the
> other way around.
>
> > This certainly looks like a bug. Should I work on a patch?
>
> It's not a bug, and it's unlikely you can "fix" it in pg_upgrade without
> making things worse.
>
> The long-run plan is that the procedural language and its support
> functions are all part of an extension and what you do is drop the
> extension. We're not quite there yet. As of 9.1, if you do "create
> extension plpython2" to start with, dropping the extension does drop the
> support functions too ... but if you use the legacy "create language"
> syntax, that doesn't happen, because an extension object isn't created.

Well, if CREATE LANGUAGE created those functions, it seems logical that
DROP FUNCTION removes them. Why is that not a bug? I am not saying we
have to fix it, but sure seems like a bug to me. Are you saying other
objects might rely on those functions?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> On Mon, Mar 05, 2012 at 11:38:33PM -0500, Tom Lane wrote:
>> Well, yeah. The language depends on the support functions, not the
>> other way around.

> Well, if CREATE LANGUAGE created those functions, it seems logical that
> DROP FUNCTION removes them. Why is that not a bug?

You can call it a bug all you want, but changing the way those
dependencies work is not a good idea. As I said, the right path forward
is to work towards putting the PL and its support functions into an
extension, and that change doesn't seem like a "bug fix" so much as a
fundamental revision of how PLs work.

> Are you saying other
> objects might rely on those functions?

IIRC we have cases where multiple PLs share the same support functions;
at least, the CREATE LANGUAGE code is written with the assumption that
that's okay. Perhaps we'd have to stop doing that in order to let them
be treated as independent extensions. It needs some thought.

To my mind this is all bound up with getting rid of pg_pltemplate,
which was last discussed in this thread:
http://archives.postgresql.org/pgsql-hackers/2011-08/msg01045.php
We don't seem to quite have consensus on how to proceed forward.

regards, tom lane

On Tue, Mar 06, 2012 at 10:38:31AM -0500, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > On Mon, Mar 05, 2012 at 11:38:33PM -0500, Tom Lane wrote:
> >> Well, yeah. The language depends on the support functions, not the
> >> other way around.
>
> > Well, if CREATE LANGUAGE created those functions, it seems logical that
> > DROP FUNCTION removes them. Why is that not a bug?
>
> You can call it a bug all you want, but changing the way those
> dependencies work is not a good idea. As I said, the right path forward
> is to work towards putting the PL and its support functions into an
> extension, and that change doesn't seem like a "bug fix" so much as a
> fundamental revision of how PLs work.

I understand.

> > Are you saying other
> > objects might rely on those functions?
>
> IIRC we have cases where multiple PLs share the same support functions;
> at least, the CREATE LANGUAGE code is written with the assumption that
> that's okay. Perhaps we'd have to stop doing that in order to let them
> be treated as independent extensions. It needs some thought.

Good point on sharing those functions.

> To my mind this is all bound up with getting rid of pg_pltemplate,
> which was last discussed in this thread:
> http://archives.postgresql.org/pgsql-hackers/2011-08/msg01045.php
> We don't seem to quite have consensus on how to proceed forward.

OK. At least I understand what is happening, but it certainly surprised
me. We need to pass on the idea that users have to manually delete
those functions if they are going from plpython2 to plpython3 with
pg_upgrade. I think users have been doing it in the past by not
switching from plpython2 to plpython3 during the upgrade, but the
one-click installer change makes that necessary.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On mån, 2012-03-05 at 19:37 -0500, Bruce Momjian wrote:
> The exact case is that the user was using plpython2u in PG 9.0, but
> the PG 9.1 one-click installer only supplies plpython3u.

That seems like a pretty stupid choice to me, if it's true.

That doesn't address your issue, but users shouldn't be forced to drop
their languages during an upgrade in the first place.

On Tue, Mar 6, 2012 at 3:28 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On mån, 2012-03-05 at 19:37 -0500, Bruce Momjian wrote:
>> The exact case is that the user was using plpython2u in PG 9.0, but
>> the PG 9.1 one-click installer only supplies plpython3u.
>
> That seems like a pretty stupid choice to me, if it's true.
>
> That doesn't address your issue, but users shouldn't be forced to drop
> their languages during an upgrade in the first place.

Hmm. I had been thinking that it is only possible to support one or
the other, thus the need for a compatibility break at some point would
be forced, but reading the documentation, it seems that it we can ship
both as long as nobody tries to use both at the same time. I wonder
why we didn't do that.

Dave?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On tis, 2012-03-06 at 16:15 -0500, Robert Haas wrote:
> On Tue, Mar 6, 2012 at 3:28 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> > On mån, 2012-03-05 at 19:37 -0500, Bruce Momjian wrote:
> >> The exact case is that the user was using plpython2u in PG 9.0, but
> >> the PG 9.1 one-click installer only supplies plpython3u.
> >
> > That seems like a pretty stupid choice to me, if it's true.
> >
> > That doesn't address your issue, but users shouldn't be forced to drop
> > their languages during an upgrade in the first place.
>
> Hmm. I had been thinking that it is only possible to support one or
> the other, thus the need for a compatibility break at some point would
> be forced, but reading the documentation, it seems that it we can ship
> both as long as nobody tries to use both at the same time. I wonder
> why we didn't do that.

Even if only one version were allowed, it would have been way too early
to switch to Python 3.