Thread: BUG #5642: pg_upgrade does not handle shared libraries for language handlers

The following bug has been logged online:

Bug reference:      5642
Logged by:          David Platt
Email address:      davidplatt@davidplatt.com
PostgreSQL version: 9.0 RC1
Operating system:   CentOS 5.5
Description:        pg_upgrade does not handle shared libraries for language
handlers
Details:

The following definition is my database:

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
    LANGUAGE c
    AS '/opt/postgres/8.4.1/lib/plpgsql', 'plpgsql_call_handler';

The file /opt/postgres/9.rc1/lib/plpgsql.o exists but the pg_upgrade run
fails on an error loading /opt/postgres/8.4.1/lib/plpgsql.o.

David Platt wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5642
> Logged by:          David Platt
> Email address:      davidplatt@davidplatt.com
> PostgreSQL version: 9.0 RC1
> Operating system:   CentOS 5.5
> Description:        pg_upgrade does not handle shared libraries for language
> handlers
> Details:
>
> The following definition is my database:
>
> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
>     LANGUAGE c
>     AS '/opt/postgres/8.4.1/lib/plpgsql', 'plpgsql_call_handler';
>
> The file /opt/postgres/9.rc1/lib/plpgsql.o exists but the pg_upgrade run
> fails on an error loading /opt/postgres/8.4.1/lib/plpgsql.o.

What is the error?  What old version of PG are you migrating from?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> writes:
> David Platt wrote:
>> The following definition is my database:
>>
>> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
>> LANGUAGE c
>> AS '/opt/postgres/8.4.1/lib/plpgsql', 'plpgsql_call_handler';
>>
>> The file /opt/postgres/9.rc1/lib/plpgsql.o exists but the pg_upgrade run
>> fails on an error loading /opt/postgres/8.4.1/lib/plpgsql.o.

> What is the error?  What old version of PG are you migrating from?

Well, it's obviously going to fail, because it will try to load an 8.4
version of plpgsql.so into 9.0.  The same would happen if you tried to
pg_dump and reload --- it's by no means the fault of pg_upgrade.

IMO this is just pilot error.  The call handler should never have been
declared like that, precisely because the definition will not port to
other releases or even other installation locations.  The right way for
the definition to look like is

    ... AS '$libdir/plpgsql'

or perhaps even just

    ... AS 'plpgsql'

if you'd like to rely on the dynamic_library_path setting.

I suspect David thinks that pg_upgrade should try to edit the library
path name, but IMO that would be seriously dangerous, as well as not
necessary if reasonable practices have been followed.

            regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > David Platt wrote:
> >> The following definition is my database:
> >>
> >> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
> >> LANGUAGE c
> >> AS '/opt/postgres/8.4.1/lib/plpgsql', 'plpgsql_call_handler';
> >>
> >> The file /opt/postgres/9.rc1/lib/plpgsql.o exists but the pg_upgrade run
> >> fails on an error loading /opt/postgres/8.4.1/lib/plpgsql.o.
>
> > What is the error?  What old version of PG are you migrating from?
>
> Well, it's obviously going to fail, because it will try to load an 8.4
> version of plpgsql.so into 9.0.  The same would happen if you tried to
> pg_dump and reload --- it's by no means the fault of pg_upgrade.
>
> IMO this is just pilot error.  The call handler should never have been
> declared like that, precisely because the definition will not port to
> other releases or even other installation locations.  The right way for
> the definition to look like is
>
>     ... AS '$libdir/plpgsql'
>
> or perhaps even just
>
>     ... AS 'plpgsql'
>
> if you'd like to rely on the dynamic_library_path setting.
>
> I suspect David thinks that pg_upgrade should try to edit the library
> path name, but IMO that would be seriously dangerous, as well as not
> necessary if reasonable practices have been followed.

I am confused how it got defined that way?  Who would be defining their
own plpgsql handler?  I am concerned there is some packaging that is
impoperly defining it.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

No, that was defined in the database through pg_dump, edit the path, the
loading the pg_dump using psql.  For each time the database has been
upgraded.

We started using Postgres in 2000 and even plpgsql had to be created as a
language back then, there was no creation by default.  Evidently the
documentation indicated that the path to the shared library had to be
declared because I don't remember any example showing $library.

-----Original Message-----
From: Bruce Momjian [mailto:bruce@momjian.us]
Sent: Tuesday, September 07, 2010 10:30 PM
To: Tom Lane
Cc: David Platt; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #5642: pg_upgrade does not handle shared libraries
for language handlers

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > David Platt wrote:
> >> The following definition is my database:
> >>
> >> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
> >> LANGUAGE c
> >> AS '/opt/postgres/8.4.1/lib/plpgsql', 'plpgsql_call_handler';
> >>
> >> The file /opt/postgres/9.rc1/lib/plpgsql.o exists but the pg_upgrade
run
> >> fails on an error loading /opt/postgres/8.4.1/lib/plpgsql.o.
>
> > What is the error?  What old version of PG are you migrating from?
>
> Well, it's obviously going to fail, because it will try to load an 8.4
> version of plpgsql.so into 9.0.  The same would happen if you tried to
> pg_dump and reload --- it's by no means the fault of pg_upgrade.
>
> IMO this is just pilot error.  The call handler should never have been
> declared like that, precisely because the definition will not port to
> other releases or even other installation locations.  The right way for
> the definition to look like is
>
>     ... AS '$libdir/plpgsql'
>
> or perhaps even just
>
>     ... AS 'plpgsql'
>
> if you'd like to rely on the dynamic_library_path setting.
>
> I suspect David thinks that pg_upgrade should try to edit the library
> path name, but IMO that would be seriously dangerous, as well as not
> necessary if reasonable practices have been followed.

I am confused how it got defined that way?  Who would be defining their
own plpgsql handler?  I am concerned there is some packaging that is
impoperly defining it.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

Postgres 8.4.1.  The configure command was run with
--prefix=/opt/postgres/8.4.1 and therefore the shared libraries are in
/opt/postgres/8.4.1/lib.  Same for 9.0rc1.  The configure command was run
with --prefix=/opt/postgres/9.rc1 and the shared libraries are in
/opt/postgres/9.rc1/lib.

-----Original Message-----
From: Bruce Momjian [mailto:bruce@momjian.us]
Sent: Tuesday, September 07, 2010 9:54 PM
To: David Platt
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #5642: pg_upgrade does not handle shared libraries
for language handlers

David Platt wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5642
> Logged by:          David Platt
> Email address:      davidplatt@davidplatt.com
> PostgreSQL version: 9.0 RC1
> Operating system:   CentOS 5.5
> Description:        pg_upgrade does not handle shared libraries for
language
> handlers
> Details:
>
> The following definition is my database:
>
> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
>     LANGUAGE c
>     AS '/opt/postgres/8.4.1/lib/plpgsql', 'plpgsql_call_handler';
>
> The file /opt/postgres/9.rc1/lib/plpgsql.o exists but the pg_upgrade run
> fails on an error loading /opt/postgres/8.4.1/lib/plpgsql.o.

What is the error?  What old version of PG are you migrating from?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

"David Platt" <davidplatt@davidplatt.com> writes:
> No, that was defined in the database through pg_dump, edit the path, the
> loading the pg_dump using psql.  For each time the database has been
> upgraded.

> We started using Postgres in 2000 and even plpgsql had to be created as a
> language back then, there was no creation by default.  Evidently the
> documentation indicated that the path to the shared library had to be
> declared because I don't remember any example showing $library.

$libdir was implemented in 2001, so you've been doing it the hard way
for awhile :-(.  However, I don't immediately find anything in the
release notes suggesting that people change to using that, so it's
definitely not all your fault.

            regards, tom lane

Thank you.  I believe we had implemented version 6.x of Postgres back when
Postgres had stored procedures, views, triggers, etc. and MySQL didn't.  I
said that I had no intention of using a database without views so we went
with Postgres.  Even our Configuration Management Software vendor has
selected Postgres for their next version of software.  They had been placing
metadata in their proprietary repository and it was becoming a bottleneck
since the metadata could not be indexed and they basically did the
equivalent of a table scan for every query.  They have modified their C
daemon to insert records and delete records from a Postgres database and
used triggers and stored procedures to capture history.  They chose Postgres
because it performed rings around the other open source dbs.  Procedures
that could take 30 minutes to run now run in less than 10 seconds with
tables with 140,000,000 rows.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, September 07, 2010 11:17 PM
To: David Platt
Cc: 'Bruce Momjian'; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #5642: pg_upgrade does not handle shared libraries
for language handlers

"David Platt" <davidplatt@davidplatt.com> writes:
> No, that was defined in the database through pg_dump, edit the path, the
> loading the pg_dump using psql.  For each time the database has been
> upgraded.

> We started using Postgres in 2000 and even plpgsql had to be created as a
> language back then, there was no creation by default.  Evidently the
> documentation indicated that the path to the shared library had to be
> declared because I don't remember any example showing $library.

$libdir was implemented in 2001, so you've been doing it the hard way
for awhile :-(.  However, I don't immediately find anything in the
release notes suggesting that people change to using that, so it's
definitely not all your fault.

            regards, tom lane