By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,693 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,693 IT Pros & Developers. It's quick & easy.

Lost plpgsql function

P: n/a
lnd
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 ma*******@postgresql.org)

Nov 22 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
-----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, ln*@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 ma*******@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)

iD8DBQFADwHMjqGXBvRToM4RAqiJAKDKCsui92pz4SwbKAZyap 5ahS8NbQCgiHrD
gvP/Skyt92IXdSzXvoZIdJI=
=yXmU
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #2

P: n/a
<ln*@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

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

http://archives.postgresql.org

Nov 22 '05 #3

P: n/a
On Wednesday 21 January 2004 22:29, ln*@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

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

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

Nov 22 '05 #4

P: n/a
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: <ln*@hnit.is>
To: <pg***********@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 ma*******@postgresql.org)


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

http://archives.postgresql.org

Nov 22 '05 #5

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #6

P: n/a
Richard Huxton <de*@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

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

Nov 22 '05 #7

P: n/a

----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Richard Huxton" <de*@archonet.com>
Cc: "Mattias Kregert" <ma*****@kregert.se>; <ln*@hnit.is>; <pg***********@postgresql.org>
Sent: Thursday, January 22, 2004 4:35 PM
Subject: Re: [GENERAL] Lost plpgsql function

Richard Huxton <de*@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 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 ma*******@postgresql.org

Nov 22 '05 #8

P: n/a
"Mattias Kregert" <ma*****@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 even exist 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

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

Nov 22 '05 #9

P: n/a
> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #10

P: n/a
> 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");
}

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

Nov 22 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.