Thread: Question on pg_dump

I'm running Postgres v.7.3.4.
In my database dump file I see this:

CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler   AS
'/home/tmichael/build/relipg21/syncinc/lib.sol2/plpgsql',
'plpgsql_call_handler'   LANGUAGE c;

The hardcoded library path may become an obstacle when loading
data into a different server. Is there a way to avoid this?

Thank you,
Mike.

Michael Brusser <michael@synchronicity.com> writes:
> I'm running Postgres v.7.3.4.
> In my database dump file I see this:

> CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
>     AS '/home/tmichael/build/relipg21/syncinc/lib.sol2/plpgsql',
> 'plpgsql_call_handler'
>     LANGUAGE c;

> The hardcoded library path may become an obstacle when loading
> data into a different server. Is there a way to avoid this?

The preferred way to write it nowadays is '$libdir/plpgsql', but
you evidently have a legacy value embedded in your pg_proc table.
pg_dump will not second-guess this, and so the old full-path
approach will persist over dump/reloads until you do something about it.

I'd suggest editing the dump file before you reload, or even manually
updating pg_proc.probin for this function entry so that future dumps
are right.
        regards, tom lane

I first tried to take care of the problem by removing "-L $libpath"
from the arg list passed to createlang. This worked in a way that 
probin in pg_proc had value "$libdir/plpgsql". 

Later it turned out the embedded library path was used, and install
failed when there was no access to the build environment.

Now I put the "-L $libpath" argument back in place, then I update
pg_proc with the dynamic value. This works fine, but such approach
looks too intrusive. I think I also tried to set env LD_LIBRARY_PATH,
which by some reason did not help.

Is there a better way to handle this and avoid updating pg_proc?

Thank you.

========================================================
> Michael Brusser <michael@synchronicity.com> writes:
> > I'm running Postgres v.7.3.4.
> > In my database dump file I see this:
> 
> > CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
> >     AS '/home/tmichael/build/relipg21/syncinc/lib.sol2/plpgsql',
> > 'plpgsql_call_handler'
> >     LANGUAGE c;
> 
> > The hardcoded library path may become an obstacle when loading
> > data into a different server. Is there a way to avoid this?
> 
> The preferred way to write it nowadays is '$libdir/plpgsql', but
> you evidently have a legacy value embedded in your pg_proc table.
> pg_dump will not second-guess this, and so the old full-path
> approach will persist over dump/reloads until you do something about it.
> 
> I'd suggest editing the dump file before you reload, or even manually
> updating pg_proc.probin for this function entry so that future dumps
> are right.
> 
>             regards, tom lane

Sorry, I forgot to mention that I also considered bypassing createlang
and using direct sql:

... RETURNS LANGUAGE_HANDLER AS '${pglib}/plpgsql' ...

but I'm not sure if this is much better then updating pg_proc.
-------------
>
> I first tried to take care of the problem by removing "-L $libpath"
> from the arg list passed to createlang. This worked in a way that
> probin in pg_proc had value "$libdir/plpgsql".
>
> Later it turned out the embedded library path was used, and install
> failed when there was no access to the build environment.
>
> Now I put the "-L $libpath" argument back in place, then I update
> pg_proc with the dynamic value. This works fine, but such approach
> looks too intrusive. I think I also tried to set env LD_LIBRARY_PATH,
> which by some reason did not help.
>
> Is there a better way to handle this and avoid updating pg_proc?
>
> Thank you.
>
> ========================================================
> > Michael Brusser <michael@synchronicity.com> writes:
> > > I'm running Postgres v.7.3.4.
> > > In my database dump file I see this:
> >
> > > CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
> > >     AS '/home/tmichael/build/relipg21/syncinc/lib.sol2/plpgsql',
> > > 'plpgsql_call_handler'
> > >     LANGUAGE c;
> >
> > > The hardcoded library path may become an obstacle when loading
> > > data into a different server. Is there a way to avoid this?
> >
> > The preferred way to write it nowadays is '$libdir/plpgsql', but
> > you evidently have a legacy value embedded in your pg_proc table.
> > pg_dump will not second-guess this, and so the old full-path
> > approach will persist over dump/reloads until you do something about it.
> >
> > I'd suggest editing the dump file before you reload, or even manually
> > updating pg_proc.probin for this function entry so that future dumps
> > are right.
> >
> >             regards, tom lane
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>