Lost plpgsql function

[lnd@hnit.is]

After copied pg database from one PC to another

-I could not find plpgsql function(s) in the copied database.
-had to instal plpgsql language handler again

-whilst tables and data moved fine

The copy included all under /cygwin/usr/local/pgsql/data and database was
down while making a copy.


What could I forget to copy as far plpgsql is concerned ?
Are pgsql stored objects stored in the database itself (a table pg_proc,
column prosrc)?
Catalog tables like pg_proc are just a part of database cluster data files,
aren't they?

Thank you in advance, Laimis

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Uwe C. Schroeder]

Re: Lost plpgsql function

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Happened to me too sometime.
If you look at the messages generated by your import you should see errors
complaining about the missing plpgsql handler. If there is no handler the
procedures will not be imported. The import code usually has something to
install the handler, but on my system this didn't work because of access
rights.
Therefor what I do now is to initialize the new database and add the handlers
for the stored procedures. After that any user can import the file without
errors.

Hope that helps

UC


On Wednesday 21 January 2004 02:29 pm, lnd@hnit.is wrote:[color=blue]
> After copied pg database from one PC to another
>
> -I could not find plpgsql function(s) in the copied database.
> -had to instal plpgsql language handler again
>
> -whilst tables and data moved fine
>
> The copy included all under /cygwin/usr/local/pgsql/data and database was
> down while making a copy.
>
>
> What could I forget to copy as far plpgsql is concerned ?
> Are pgsql stored objects stored in the database itself (a table pg_proc,
> column prosrc)?
> Catalog tables like pg_proc are just a part of database cluster data files,
> aren't they?
>
> Thank you in advance, Laimis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)[/color]

- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFADwHMjqG XBvRToM4RAqiJAK DKCsui92pz4SwbK AZyap5ahS8NbQCg iHrD
gvP/Skyt92IXdSzXvoZ IdJI=
=yXmU
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Tom Lane]

Re: Lost plpgsql function

<lnd@hnit.is> writes:[color=blue]
> After copied pg database from one PC to another
> -I could not find plpgsql function(s) in the copied database.
> -had to instal plpgsql language handler again[/color]

Please define "could not find plpgsql function(s)". What *exactly*
did you do, and what exact error messages did you get?

The theory in the back of my mind is that you had the plpgsql handler
function defined using the old method of specifying an absolute path
to plpgsql.so, and that the correct path on the new machine was
something different.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Richard Huxton]

Re: Lost plpgsql function

On Wednesday 21 January 2004 22:29, lnd@hnit.is wrote:[color=blue]
> After copied pg database from one PC to another
>
> -I could not find plpgsql function(s) in the copied database.
> -had to instal plpgsql language handler again
>
> -whilst tables and data moved fine
>
> The copy included all under /cygwin/usr/local/pgsql/data and database was
> down while making a copy.
>
> What could I forget to copy as far plpgsql is concerned ?[/color]

Hmm - are the paths still the same? It might be that the path to the
plpgsql.so (.dll on cygwin?) was different, so it never got loaded.

Otherwise, plpgsql functions are stored in tables just like any other piece of
data.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Mattias Kregert]

Re: Lost plpgsql function

I ran into this problem a couple of days ago when I upgraded from 7.0.3 to 7.4.1.
I used the 7.4.1 pg_dumpall, but it created a dump file which tried to load the old plpgsql.so.
I had to fix the dump file manually before loading it into the new db. There were some other annoyances too, like the 7.4.1 pg_dumpall dumping out CR in the file, and then the 7.4.1. psql comlained about it and told me to use \r instead... I had to take out the "LOCATION" in create database too because it complained about "no such environment variable". I guess pg_dumpall doesn't understand the differences between the different versions, so you always have to check the dump file manually if you are changing version.

/M


----- Original Message -----
From: <lnd@hnit.is>
To: <pgsql-general@postgre sql.org>
Sent: Wednesday, January 21, 2004 11:29 PM
Subject: [GENERAL] Lost plpgsql function

[color=blue]
> After copied pg database from one PC to another
>
> -I could not find plpgsql function(s) in the copied database.
> -had to instal plpgsql language handler again
>
> -whilst tables and data moved fine
>
> The copy included all under /cygwin/usr/local/pgsql/data and database was
> down while making a copy.
>
>
> What could I forget to copy as far plpgsql is concerned ?
> Are pgsql stored objects stored in the database itself (a table pg_proc,
> column prosrc)?
> Catalog tables like pg_proc are just a part of database cluster data files,
> aren't they?
>
> Thank you in advance, Laimis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Richard Huxton]

Re: Lost plpgsql function

On Thursday 22 January 2004 09:58, Mattias Kregert wrote:[color=blue]
> I ran into this problem a couple of days ago when I upgraded from 7.0.3 to
> 7.4.1. I used the 7.4.1 pg_dumpall, but it created a dump file which tried
> to load the old plpgsql.so. I had to fix the dump file manually before
> loading it into the new db. There were some other annoyances too, like the
> 7.4.1 pg_dumpall dumping out CR in the file, and then the 7.4.1. psql
> comlained about it and told me to use \r instead... I had to take out the
> "LOCATION" in create database too because it complained about "no such
> environment variable". I guess pg_dumpall doesn't understand the
> differences between the different versions, so you always have to check the
> dump file manually if you are changing version.[/color]

In reality, there probably always will be "one more thing" when upgrading a
large database, but that doesn't mean the problems you're having can't be
fixed.

Put together small examples and submit them as bugs. If you can offer patches
too, that would make the developers happy.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Tom Lane]

Re: Lost plpgsql function

Richard Huxton <dev@archonet.c om> writes:[color=blue]
> On Thursday 22 January 2004 09:58, Mattias Kregert wrote:[color=green]
>> I ran into this problem a couple of days ago when I upgraded from 7.0.3 to
>> 7.4.1. I used the 7.4.1 pg_dumpall, but it created a dump file which tried
>> to load the old plpgsql.so. I had to fix the dump file manually before
>> loading it into the new db. There were some other annoyances too, like the
>> 7.4.1 pg_dumpall dumping out CR in the file, and then the 7.4.1. psql
>> comlained about it and told me to use \r instead... I had to take out the
>> "LOCATION" in create database too because it complained about "no such
>> environment variable". I guess pg_dumpall doesn't understand the
>> differences between the different versions, so you always have to check the
>> dump file manually if you are changing version.[/color][/color]
[color=blue]
> In reality, there probably always will be "one more thing" when upgrading a
> large database, but that doesn't mean the problems you're having can't be
> fixed.[/color]
[color=blue]
> Put together small examples and submit them as bugs. If you can offer patches
> too, that would make the developers happy.[/color]

None of those items are likely to get fixed.

The plpgsql path issue is not pg_dump's fault: the problem is that there
is an absolute path to the shared library file recorded in the pg_proc
entry for plpgsql's call handler. That was how we did things back then.
The correct entry nowadays is "$libdir/plpgsql" which sidesteps the
question of exactly where the Postgres shared libraries live; but there
isn't any reasonable way AFAICS for pg_dump to make that substitution.
It'd have to replace *any* path in a pg_proc entry with $libdir, which
would undoubtedly break as many cases as it fixed.

The CR problem is likewise essentially a bug in the older server, or at
least a definitional incompatibility . The only way to fix it would be
a retroactive fix in 7.0.3 and other ancient versions; which requires a
time machine we haven't got :-( The simplest workaround I can think of
is to use pg_dump's "dump using INSERTs" option when upgrading from a
pre-7.2 release to 7.4.

As for "no such environment variable", that's plain old pilot error.
If you wanna use environment-variable-defined locations, you gotta
remember to set the environment variable for the postmaster.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Mattias Kregert]

Re: Lost plpgsql function


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa .us>
To: "Richard Huxton" <dev@archonet.c om>
Cc: "Mattias Kregert" <mattias@kreger t.se>; <lnd@hnit.is> ; <pgsql-general@postgre sql.org>
Sent: Thursday, January 22, 2004 4:35 PM
Subject: Re: [GENERAL] Lost plpgsql function

[color=blue]
> Richard Huxton <dev@archonet.c om> writes:[color=green]
> > On Thursday 22 January 2004 09:58, Mattias Kregert wrote:[color=darkred]
> >> I ran into this problem a couple of days ago when I upgraded from 7.0.3 to
> >> 7.4.1. I used the 7.4.1 pg_dumpall, but it created a dump file which tried
> >> to load the old plpgsql.so. I had to fix the dump file manually before
> >> loading it into the new db. There were some other annoyances too, like the
> >> 7.4.1 pg_dumpall dumping out CR in the file, and then the 7.4.1. psql
> >> comlained about it and told me to use \r instead... I had to take out the
> >> "LOCATION" in create database too because it complained about "no such
> >> environment variable". I guess pg_dumpall doesn't understand the
> >> differences between the different versions, so you always have to check the
> >> dump file manually if you are changing version.[/color][/color]
>[color=green]
> > In reality, there probably always will be "one more thing" when upgrading a
> > large database, but that doesn't mean the problems you're having can't be
> > fixed.[/color]
>[color=green]
> > Put together small examples and submit them as bugs. If you can offer patches
> > too, that would make the developers happy.[/color]
>
> None of those items are likely to get fixed.
>
> The plpgsql path issue is not pg_dump's fault: the problem is that there
> is an absolute path to the shared library file recorded in the pg_proc
> entry for plpgsql's call handler. That was how we did things back then.
> The correct entry nowadays is "$libdir/plpgsql" which sidesteps the
> question of exactly where the Postgres shared libraries live; but there
> isn't any reasonable way AFAICS for pg_dump to make that substitution.
> It'd have to replace *any* path in a pg_proc entry with $libdir, which
> would undoubtedly break as many cases as it fixed.
>
> The CR problem is likewise essentially a bug in the older server, or at
> least a definitional incompatibility . The only way to fix it would be
> a retroactive fix in 7.0.3 and other ancient versions; which requires a
> time machine we haven't got :-( The simplest workaround I can think of
> is to use pg_dump's "dump using INSERTs" option when upgrading from a
> pre-7.2 release to 7.4.
>
> As for "no such environment variable", that's plain old pilot error.
> If you wanna use environment-variable-defined locations, you gotta
> remember to set the environment variable for the postmaster.
>
> regards, tom lane
>[/color]

No no... "no such environment variable" is not "pilot error". I have never used env variables to define locations.
pg_dumpall 7.4.1 puts this stuff in the dump file when dumping a 7.0.3 db. Did this kind of alternate location thing even exist in 7.0.3?? Maybe it is interpreting something wrong because the db is too old?

/M


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Tom Lane]

Re: Lost plpgsql function

"Mattias Kregert" <mattias@kreger t.se> writes:[color=blue]
> No no... "no such environment variable" is not "pilot error". I have never used env variables to define locations.
> pg_dumpall 7.4.1 puts this stuff in the dump file when dumping a 7.0.3 db. Did this kind of alternate location thing even exist in 7.0.3?? Maybe it is interpreting something wrong because the db is too old?[/color]

Oh? Can you provide a test case?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Re: Lost plpgsql function

> Oh? Can you provide a test case?

Never mind, I was able to duplicate it here. It looks like 7.0 is not
consistent with later versions about what it keeps in pg_database.dat path.
I'll have to go back and see what it's doing exactly ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Tom Lane]

Re: Lost plpgsql function

> Never mind, I was able to duplicate it here. It looks like 7.0 is not[color=blue]
> consistent with later versions about what it keeps in pg_database.dat path.
> I'll have to go back and see what it's doing exactly ...[/color]

Attached is the patch for 7.4.*, if you need it.

regards, tom lane

Index: pg_dump.c
=============== =============== =============== =============== =======
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.355.2.1
diff -c -r1.355.2.1 pg_dump.c
*** pg_dump.c 19 Dec 2003 14:21:43 -0000 1.355.2.1
--- pg_dump.c 22 Jan 2004 19:06:19 -0000
***************
*** 1144,1154 ****
selectSourceSch ema("pg_catalog ");

/* Get the database owner and parameters from pg_database */
! appendPQExpBuff er(dbQry, "select (select usename from pg_user where usesysid = datdba) as dba,"
! " pg_encoding_to_ char(encoding) as encoding,"
! " datpath from pg_database"
! " where datname = ");
! appendStringLit eral(dbQry, datname, true);

res = PQexec(g_conn, dbQry->data);
if (!res ||
--- 1144,1176 ----
selectSourceSch ema("pg_catalog ");

/* Get the database owner and parameters from pg_database */
! if (g_fout->remoteVersio n >= 70100)
! {
! appendPQExpBuff er(dbQry, "SELECT "
! "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
! "pg_encoding_to _char(encoding) as encoding, "
! "datpath "
! "FROM pg_database "
! "WHERE datname = ");
! appendStringLit eral(dbQry, datname, true);
! }
! else
! {
! /*
! * In 7.0, datpath is either the same as datname, or the user-given
! * location with "/" and the datname appended. We must strip this
! * junk off to produce a correct LOCATION value.
! */
! appendPQExpBuff er(dbQry, "SELECT "
! "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
! "pg_encoding_to _char(encoding) as encoding, "
! "CASE WHEN length(datpath) > length(datname) THEN "
! "substr(datpath ,1,length(datpa th)-length(datname)-1) "
! "ELSE '' END as datpath "
! "FROM pg_database "
! "WHERE datname = ");
! appendStringLit eral(dbQry, datname, true);
! }

res = PQexec(g_conn, dbQry->data);
if (!res ||
Index: pg_dumpall.c
=============== =============== =============== =============== =======
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.28
diff -c -r1.28 pg_dumpall.c
*** pg_dumpall.c 23 Sep 2003 22:48:53 -0000 1.28
--- pg_dumpall.c 22 Jan 2004 19:06:20 -0000
***************
*** 430,435 ****
--- 430,439 ----
else
{
/*
+ * In 7.0, datpath is either the same as datname, or the user-given
+ * location with "/" and the datname appended. We must strip this
+ * junk off to produce a correct LOCATION value.
+ *
* Note: 7.0 fails to cope with sub-select in COALESCE, so just
* deal with getting a NULL by not printing any OWNER clause.
*/
***************
*** 437,443 ****
"SELECT datname, "
"(select usename from pg_shadow where usesysid=datdba ), "
"pg_encoding_to _char(d.encodin g), "
! "'f' as datistemplate, datpath, '' as datacl "
"FROM pg_database d "
"ORDER BY 1");
}
--- 441,451 ----
"SELECT datname, "
"(select usename from pg_shadow where usesysid=datdba ), "
"pg_encoding_to _char(d.encodin g), "
! "'f' as datistemplate, "
! "CASE WHEN length(datpath) > length(datname) THEN "
! "substr(datpath ,1,length(datpa th)-length(datname)-1) "
! "ELSE '' END as datpath, "
! "'' as datacl "
"FROM pg_database d "
"ORDER BY 1");
}

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly