Re: plpgsql.dll path

Hi.
I'm using PostgreSQL v7.2.1-1 on top of MS-Windows98.

When I type
pg_restore -d geosist geosist-maio09.dmp
I get the following error msg:
pg_restore: [archiver (db)] could not execute query: ERROR:
stat failed on file '/usr/lib/plpgsql.dll': No such file or
directory

plpgsql.dll is at /usr/lib/postgresql/ (instead of /usr/lib/, where it
is expected to be by pg_restore).

Creating a link
ln -s /usr/lib/postgresql/plpgsql.dll /usr/lib/plpgsql.dll
makes pg_restore to work fine.

I'm not sure where this file should actually reside. I'll keep it at
both directories while not sure.
Is there any additional info on this?

Thanks for the work on the cygwin port.

Regards,
Felipe N.

Felipe,

On Thu, May 09, 2002 at 01:20:11PM -0200, Felipe Nievinski wrote:
> I'm using PostgreSQL v7.2.1-1 on top of MS-Windows98.
>
> When I type
> pg_restore -d geosist geosist-maio09.dmp
> I get the following error msg:
> pg_restore: [archiver (db)] could not execute query: ERROR:
> stat failed on file '/usr/lib/plpgsql.dll': No such file or
> directory
>
> [snip]
>
> Is there any additional info on this?

Note that I have never used pg_dump/pg_restore before, so I can be
*completely* off-base with the following. I also think that the above
may not be specifically a Cygwin PostgreSQL problem.

Are you upgrading from 7.1.x (or earlier) to 7.2.x? I ask this for the
following reasons:

$ tar -tjf postgresql-7.1.3-2.tar.bz2 | fgrep plpgsql.dll
usr/lib/plpgsql.dll

$ tar -tjf postgresql-7.2.1-1.tar.bz2 | fgrep plpgsql.dll
usr/lib/postgresql/plpgsql.dll

$ strings /usr/bin/pg_restore.exe | fgrep plpgsql.dll

Hence, the path to plpgsql.dll is *not* burned into pg_restore.exe. Is
it in geosist-maio09.dmp? Or, is this kind of info stored in some other
PostgreSQL file?

Can someone knowledgeable is this area please confirm or refute the
above?

> Thanks for the work on the cygwin port.

You are welcome.

Thanks,
Jason

Jason Tishler wrote:
>
> Hence, the path to plpgsql.dll is *not* burned into pg_restore.exe. Is
> it in geosist-maio09.dmp? Or, is this kind of info stored in some other
> PostgreSQL file?
>
> Can someone knowledgeable is this area please confirm or refute the
> above?

The path to a dll for a procedural language is stored in the database. If you
pg_dump the schema, you'll see it in there, like:

-------------------------------------
--
-- TOC Entry ID 9 (OID 22011682)
--
-- Name: "plpgsql_call_handler" () Type: FUNCTION Owner: barryp
--

CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS
'/usr/lib/plpgsql.dll', 'plpgsql_call_handler' LANGUAGE 'C';

--
-- TOC Entry ID 10 (OID 22011683)
--
-- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner:
--

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler"
LANCOMPILER 'PL/pgSQL';
--------------------------------------

The path to the libraries does seem to have changed between 7.1 and 7.2 - and
it's not a cygwin thing, the same deal happened to me on a FreeBSD box.

There are at least two ways you can work around this (I've done both)

1) setup a symlink from the old path to the new path:

ln -s /usr/lib/postgresql/plpgsql.dll /usr/lib/plpgsql.dll

2) dump the schema, fix the path, create a new db, load the fixed schema, and
the restore just the data.

The first solution is easiest, by you have to be sure to keep that symlink
there. The second is harder, but fixes it permanently (or at least until the
next time they change the path).

Barry

Barry,

On Sun, May 12, 2002 at 09:46:09AM -0500, Barry Pederson wrote:
> Jason Tishler wrote:
> >Can someone knowledgeable is this area please confirm or refute the
> >above?
>
> The path to a dll for a procedural language is stored in the database. If
> you pg_dump the schema, you'll see it in there, like:
>
> [snip]
>
> The path to the libraries does seem to have changed between 7.1 and 7.2 -
> and it's not a cygwin thing, the same deal happened to me on a FreeBSD box.

Thanks for the above info. I didn't think this was a Cygwin problem, but
wanted to be sure.

Jason