Postgres Pro
Downloads
Home   >   mailing lists

Thread: plpgsql.dll path

plpgsql.dll path

From
"Felipe Nievinski"
Date:
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.

Re: plpgsql.dll path

From
Jason Tishler
Date:
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

Re: plpgsql.dll path

From
Barry Pederson
Date:
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

Re: plpgsql.dll path

From
Jason Tishler
Date:
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

Re: plpgsql.dll path

From
"Felipe Nievinski"
Date:
Hi.

Jason Tishler wrote:
...
>> Are you upgrading from 7.1.x (or earlier) to 7.2.x?
Yes, I am.

...
>> 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?
>>

Barry Pederson wrote:
> The path to a dll for a procedural language is stored in the
> database.
...
> 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 clarification.

I've dumped the database in a plain text file, corrected by hand the
path to plpgsql.dll and then restored it in a new db. My db now is
using the file at /usr/lib/postgresql/

Thank you again.

Regards,
Felipe N.