Re: pg_restore error: function plpgsql_call_handler already exists with same argument types

Hi-

Thanks for the helpful suggestions on this problem last Wednesday morning- I
spent the rest of the day in a meeting, and I'm now returning to the problem
post-holiday. I apologize for the slow response to your ideas.

Tom- You were correct, I was restoring the wrong database in my example with
template0. When I corrected this problem, I got a new error message!
(progress of a sort <grin>).

Apparently my first problem is the result of plpgsql already being defined
in template1. I haven't touched template1 since my install, but it may be
that template1 comes with this already defined, or it may be that template1
is set up this way only in the Debian package, which I use for installation.
Perhaps Oliver can shed some light on this.

At any rate, using template0 as suggested solved my original problem, and
now I've got a new one. Take a look at the example below:

nickf(at)morgai:/data1/db-backup$ pg_dump -Ft alpha >
inhoward.02_12_02_02_00_01.dump.tar
nickf(at)morgai:/data1/db-backup$ dropdb alpha
DROP DATABASE
nickf(at)morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha
CREATE DATABASE
nickf(at)morgai:/data1/db-backup$ pg_restore -d alpha
inhoward.02_12_02_02_00_01.dump.tar
pg_restore: [archiver (db)] could not execute query: ERROR: Relation
"actor" does not exist
nickf(at)morgai:/data1/db-backup$

When I do a "\d" in psql after receiving this message, it looks like some of
my schema has been restored, but "actor" is indeed missing. I spot-checked a
few tables, and found that none of the data has been restored yet.

It appears that in the process of creating the schema, pg_restore attempted
to create an object that required the existence of actor, which wasn't
restored yet. My conjecture is that the objects are just being created in
the wrong order. To test this I tried a couple of commands after wiping the
database clean again:

pg_restore -s -d alpha inhoward.02_12_02_02_00_01.dump.tar - resulted in
exactly the same error, suggesting again that the error message is probably
being generated while restoring the schema.

pg_restore -t actor -d alpha inhoward.02_12_02_02_00_01.dump.tar - completed
successfully, indicating that the schema & data for "actor" is present in
the dump file.

I consulted the pg_restore documentation, and found several switches that
affected the order of object creation, which I tried with the results shown
below:

nickf(at)morgai:/data1/db-backup$ dropdb alpha
DROP DATABASE
nickf(at)morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha
CREATE DATABASE
nickf(at)morgai:/data1/db-backup$ pg_restore -o -d alpha
inhoward.02_12_02_02_00_01.dump.tar
pg_restore: [archiver (db)] could not execute query: ERROR: relation "rule"
not found

nickf(at)morgai:/data1/db-backup$ dropdb alpha
DROP DATABASE
nickf(at)morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha
CREATE DATABASE
nickf(at)morgai:/data1/db-backup$ pg_restore -N -d alpha
inhoward.02_12_02_02_00_01.dump.tar
pg_restore: [archiver (db)] could not execute query: ERROR: Relation
"actor" does not exist

nickf(at)morgai:/data1/db-backup$ dropdb alpha
DROP DATABASE
nickf(at)morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha
CREATE DATABASE
nickf(at)morgai:/data1/db-backup$ pg_restore -r -d alpha
inhoward.02_12_02_02_00_01.dump.tar
pg_restore: [archiver (db)] could not execute query: ERROR: Relation
"actor" does not exist

My goal in working with pg_restore using the tar format is to be able to
either restore an individual table or recreate the entire database
conveniently should the need arise. I've used the text dump & frequently
done a full restore by piping it into to psql without problems before, so I
can confirm that for our database, the objects get created in the correct
order when using that format.

So... That's the whole story- Any thoughts on what I should try next?

Thanks,

-Nick

--------------------------------------------------------------------------
Nick Fankhauser nickf(at)ontko(dot)com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/