On 21/11/2003, at 7:43 AM, Bryan Encina wrote:
I have a different problem, but the same situation - the same upgrade
7.1.3 to 7.4, by a clean fresh install on a new computer (which made it
the obvious moment to do it). No problems with restoring some simple
databases, no functions.

But the dump of the DB with functions won't restore:

$pg_restore -O -d cbisdb SampleTableChange.tar
pg_restore: [archiver (db)] could not execute query: ERROR: permission
denied for language c

"language c"? never saw that before.
"permissions"? - I have checked the permissions in
/usr/local/pgsql/lib/ libraries, all are readable & executable by me,
and yet this does seem to be relevant, because if I try it as user
postgres, I get a different error:
pg_restore -O -d cbisdb SampleTableChange.tar
pg_restore: [archiver (db)] could not execute query: ERROR: function
"plpgsql_call_handler" already exists with same argument types

Can anyone give me some clues here?

thanks very much
Cath
--
Cath dot Lawrence at anu dot edu dot au
Senior Scientific Programmer, Centre for BioInformation Science,
Australian National University

Cath Lawrence <Cath.Lawrence (AT) anu (DOT) edu.au> writes:
Apparently your dump contains some function definitions for C-language
functions. You must run the dump script as a Postgres superuser to load
these definitions.

Best to drop and recreate the database before re-trying the restore,
else you'll get complaints about pre-existing objects.

regards, tom lane

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

On Thu, 2004-01-08 at 14:05, Cath Lawrence wrote:
Hi Cath,

I see this all the time doing restores.

I think there are some situations (on Debian, at least) where pl/pgsql
gets added to the template1 database. Once that happens you will always
have to do a "DROP LANGUAGE plpgsql CASCADE" after creating the database
and before restoring your dump.

PL/PgSQL is a bit awkward in this way - it's not sure whether it's a
standard component or not. Certainly every database I have ever
designed has it there, so from that point of view having it in the
template1 is useful. On the other hand it means that you always run
into this problem during restore...

If you only ever restore into this installation then you maybe want to
drop the language from the template1 database as well.

Ultimately some sort of a flag on pg_restore to "don't try and restore
languages" would be a good enhancement, I guess.

Regards,
Andrew McMillan

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
http://survey.net.nz/ - any more questions?
-------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Andrew McMillan <andrew (AT) catalyst (DOT) net.nz> writes:
The correct way to avoid this issue is to create the database you are
about to restore into as a clone of template0, not template1.

$ createdb -T template0 cbisdb
$ pg_restore -O -d cbisdb SampleTableChange.tar

You can get away without this fine point only so long as you don't add
any site-local objects to template1. plpgsql is just the most common
example of a site-local object you might want to add there ...

regards, tom lane

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