473,287 Members | 1,946 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Lost plpgsql function

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
10 2906
-----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
<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
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
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
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
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

----- 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
"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
> 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
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Martin Marques | last post by:
We are trying to make some things work with plpgsql. The problem is that I built several functions that call one another, and I thought that the way of calling it was just making the assign: ...
1
by: Rajesh Kumar Mallah | last post by:
Hi, profile_row profile_master%ROWTYPE; in a plpgsql function gives the error below tradein_clients=# SELECT general.create_accounts(); WARNING: plpgsql: ERROR during compile of...
14
by: Karl O. Pinc | last post by:
Hi, Thought perhaps some other eyes than mine can tell if I'm doing something wrong here or if there's a bug somewhere. I've never passed a ROWTYPE varaible to a function but I don't see where...
1
by: Thomas Schoen | last post by:
Hi, is it possible to use a parameter of a plpgsql-function to order a selection inside the function? What i would like to do is pass a column-name/alias-name to a plpgsql function and use...
2
by: David Boone | last post by:
I've been trying to create functions with postgres, but it seems that queries run within a function take wayyy too long to complete. The increased time seems to be in the actual queries, not...
2
by: Mark Cave-Ayland | last post by:
Hi everyone, I'm trying to write a recursive plpgsql function in PostgreSQL 7.4.2 that given a tree node id (ictid) will return all the nodes below it in the tree, one row per node. When I try...
1
by: Marcel Boscher | last post by:
Hey guys, i got a question that bothers me quite a while, been looking everywhere but found no answer My idea is to automate this procedure for better performance... Can anybody in here...
9
by: Karl O. Pinc | last post by:
I want to return multiple values, but not a set, only a single row, from a plpgsql function and I can't seem to get it to work. (I suppose I'd be happy to return a set, but I can't seem to make...
1
by: Karl O. Pinc | last post by:
FYI, mostly. But I do have questions as to how to write code that will continue to work in subsequent postgresql versions. See code below. begintest() uses EXIT to exit a BEGIN block from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.