Thread: Lost plpgsql function
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
-----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:
> 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 YourEmailAddressHere" to majordomo@postgresql.org)
- --
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)
iD8DBQFADwHMjqGXBvRToM4RAqiJAKDKCsui92pz4SwbKAZyap5ahS8NbQCgiHrD
gvP/Skyt92IXdSzXvoZIdJI=
=yXmU
-----END PGP SIGNATURE-----
<lnd@hnit.is> writes:
> 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
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
On Wednesday 21 January 2004 22:29, lnd@hnit.is wrote: > 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 ? 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
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.1pg_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 checkthe dump file manually if you are changing version. /M ----- Original Message ----- From: <lnd@hnit.is> To: <pgsql-general@postgresql.org> Sent: Wednesday, January 21, 2004 11:29 PM Subject: [GENERAL] Lost plpgsql function > 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 YourEmailAddressHere" to majordomo@postgresql.org) >
On Thursday 22 January 2004 09:58, Mattias Kregert wrote: > 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. 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
Richard Huxton <dev@archonet.com> writes:
> On Thursday 22 January 2004 09:58, Mattias Kregert wrote:
>> 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.
> 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.
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
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Richard Huxton" <dev@archonet.com> Cc: "Mattias Kregert" <mattias@kregert.se>; <lnd@hnit.is>; <pgsql-general@postgresql.org> Sent: Thursday, January 22, 2004 4:35 PM Subject: Re: [GENERAL] Lost plpgsql function > Richard Huxton <dev@archonet.com> writes: > > On Thursday 22 January 2004 09:58, Mattias Kregert wrote: > >> 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. > > > 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. > > 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 > 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 evenexist in 7.0.3?? Maybe it is interpreting something wrong because the db is too old? /M
"Mattias Kregert" <mattias@kregert.se> writes:
> 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
evenexist in 7.0.3?? Maybe it is interpreting something wrong because the db is too old?
Oh? Can you provide a test case?
regards, tom lane
> 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.datpath.
I'll have to go back and see what it's doing exactly ...
regards, tom lane
> 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.datpath.
> I'll have to go back and see what it's doing exactly ...
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 ****
selectSourceSchema("pg_catalog");
/* Get the database owner and parameters from pg_database */
! appendPQExpBuffer(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 = ");
! appendStringLiteral(dbQry, datname, true);
res = PQexec(g_conn, dbQry->data);
if (!res ||
--- 1144,1176 ----
selectSourceSchema("pg_catalog");
/* Get the database owner and parameters from pg_database */
! if (g_fout->remoteVersion >= 70100)
! {
! appendPQExpBuffer(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 = ");
! appendStringLiteral(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.
! */
! appendPQExpBuffer(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(datpath)-length(datname)-1) "
! "ELSE '' END as datpath "
! "FROM pg_database "
! "WHERE datname = ");
! appendStringLiteral(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.encoding), "
! "'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.encoding), "
! "'f' as datistemplate, "
! "CASE WHEN length(datpath) > length(datname) THEN "
! "substr(datpath,1,length(datpath)-length(datname)-1) "
! "ELSE '' END as datpath, "
! "'' as datacl "
"FROM pg_database d "
"ORDER BY 1");
}