Thread: pg_restore error: function plpgsql_call_handler already exists with same argument types
pg_restore error: function plpgsql_call_handler already exists with same argument types
From
"Nick Fankhauser"
Date:
Hi- I'm trying to do a dump & restore of a complete database using tar archive format. I've previously used the text dump approach, so I'm very new to this method. I created the dump using: pg_dump -Ft alpha > alpha.dump.tar I'm trying to restore into an empty db using: pg_restore -d alpha2 alpha.dump.tar and I get this error: pg_restore: [archiver (db)] could not execute query: ERROR: function plpgsql_call_handler already exists with same argument types I found a brief mention of this error in an old novice list posting, but it seemed to be connected to a plain text dump file, so I'm still mystified. Any thoughts? Thanks. -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
On Wed, 2002-11-27 at 13:16, Nick Fankhauser wrote:
> Hi-
>
> I'm trying to do a dump & restore of a complete database using tar archive
> format. I've previously used the text dump approach, so I'm very new to this
> method.
>
> I created the dump using:
>
> pg_dump -Ft alpha > alpha.dump.tar
>
>
> I'm trying to restore into an empty db using:
>
> pg_restore -d alpha2 alpha.dump.tar
>
> and I get this error:
>
> pg_restore: [archiver (db)] could not execute query: ERROR: function
> plpgsql_call_handler already exists with same argument types
>
> I found a brief mention of this error in an old novice list posting, but it
> seemed to be connected to a plain text dump file, so I'm still mystified.
>
> Any thoughts?
It sounds as though you are trying to restore an item which is already
in the database. Perhaps this snippet from the pg_restore man page may
be relevent:
If your installation has any local additions to the template1
database, be careful to load the output of pg_restore into a
truly empty database; otherwise you are likely to get errors due
to duplicate definitions of the added objects. To make an empty
database without any local additions, copy from template0 not
template1, for example:
CREATE DATABASE foo WITH TEMPLATE = template0;
--
Oliver Elphick <olly@lfix.co.uk>
LFIX Limited
Attachment
Re: pg_restore error: function plpgsql_call_handleralready exists with same argument types
From
"Nick Fankhauser"
Date:
Oliver- Thanks for the idea. Unfortunately, it still won't go. We've never touched template1, but just to make sure, I tried using template0 to ensure an empty DB with the same results: nickf@morgai:~$ createdb -D PG_ALPHA -T template0 test CREATE DATABASE nickf@morgai:~$ pg_restore -dalpha test.dump.tar pg_restore: [archiver (db)] could not execute query: ERROR: function plpgsql_call_handler already exists with same argument types nickf@morgai:~$ I'm a bit surprised about this one, because we're running a plain 7.2 (debian package) installation & it seems as if this is a standard sort of operation that other people would be using often. -Nick > It sounds as though you are trying to restore an item which is already > in the database. Perhaps this snippet from the pg_restore man page may > be relevent: > > If your installation has any local additions to the template1 > database, be careful to load the output of pg_restore into a > truly empty database; otherwise you are likely to get errors due > to duplicate definitions of the added objects. To make an empty > database without any local additions, copy from template0 not > template1, for example: > > CREATE DATABASE foo WITH TEMPLATE = template0;
On Wed, 2002-11-27 at 14:28, Nick Fankhauser wrote: > Oliver- > > Thanks for the idea. Unfortunately, it still won't go. > > We've never touched template1, but just to make sure, I tried using > template0 to ensure an empty DB with the same results: Does the same happen with a plain text dump (restored with "psql -d database <dumpfile") -- Oliver Elphick <olly@lfix.co.uk> LFIX Limited
Attachment
Re: pg_restore error: function plpgsql_call_handleralready exists with same argument types
From
Tom Lane
Date:
"Nick Fankhauser" <nickf@ontko.com> writes:
> We've never touched template1, but just to make sure, I tried using
> template0 to ensure an empty DB with the same results:
> nickf@morgai:~$ createdb -D PG_ALPHA -T template0 test
> CREATE DATABASE
> nickf@morgai:~$ pg_restore -dalpha test.dump.tar
> pg_restore: [archiver (db)] could not execute query: ERROR: function
> plpgsql_call_handler already exists with same argument types
> nickf@morgai:~$
Er, it looks like you created an empty 'test' and then tried (again)
to restore into the not-so-empty 'alpha'.
regards, tom lane
If I have to take a guess, it is that you have a language defined in template1 (and hence in the new db) or in the database you are loading into and that is conflicting with the load of the dump. --------------------------------------------------------------------------- Nick Fankhauser wrote: > Hi- > > I'm trying to do a dump & restore of a complete database using tar archive > format. I've previously used the text dump approach, so I'm very new to this > method. > > I created the dump using: > > pg_dump -Ft alpha > alpha.dump.tar > > > I'm trying to restore into an empty db using: > > pg_restore -d alpha2 alpha.dump.tar > > and I get this error: > > pg_restore: [archiver (db)] could not execute query: ERROR: function > plpgsql_call_handler already exists with same argument types > > I found a brief mention of this error in an old novice list posting, but it > seemed to be connected to a plain text dump file, so I'm still mystified. > > Any thoughts? > > Thanks. > > -Nick > > > > -------------------------------------------------------------------------- > Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 > Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Re: pg_restore error: function plpgsql_call_handler already exists with same argument types
From
"Nick Fankhauser"
Date:
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@morgai:/data1/db-backup$ pg_dump -Ft alpha > inhoward.02_12_02_02_00_01.dump.tar nickf@morgai:/data1/db-backup$ dropdb alpha DROP DATABASE nickf@morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha CREATE DATABASE nickf@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@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@morgai:/data1/db-backup$ dropdb alpha DROP DATABASE nickf@morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha CREATE DATABASE nickf@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@morgai:/data1/db-backup$ dropdb alpha DROP DATABASE nickf@morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha CREATE DATABASE nickf@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@morgai:/data1/db-backup$ dropdb alpha DROP DATABASE nickf@morgai:/data1/db-backup$ createdb -D PG_ALPHA -T template0 alpha CREATE DATABASE nickf@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@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
Re: pg_restore error: function plpgsql_call_handler already exists with same argument types
From
Tom Lane
Date:
"Nick Fankhauser" <nickf@ontko.com> writes:
> 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.
Probably. You could check this by running pg_restore with query logging
turned on, to see what commands it's actually issuing -- or just do
"pg_restore -s" into a text file and eyeball the generated script.
There are a lot of situations where pg_dump fails to pick a safe reload
order at the moment (that's why pg_restore has that wild and woolly set
of options for manual adjustment of the reload order).
regards, tom lane
On Mon, 2002-12-02 at 14:47, Nick Fankhauser wrote: > 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. The post-installation script enables it in all writable databases, including template1. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "For the mountains shall depart, and the hills be removed; but my kindness shall not depart from thee, neither shall the covenant of my peace be removed, saith the LORD that hath mercy on thee." Isaiah 54:10
Re: pg_restore error: function plpgsql_call_handler already exists with same argument types
From
"Nick Fankhauser"
Date:
> You could check this by running pg_restore with query logging > turned on, to see what commands it's actually issuing -- or just do > "pg_restore -s" into a text file and eyeball the generated script. I did this, and there is a view created before the table it refers to. > There are a lot of situations where pg_dump fails to pick a safe reload > order at the moment (that's why pg_restore has that wild and woolly set > of options for manual adjustment of the reload order). So... it looks like my best option at this point is to use the -l switch to create an archive list, reorder the list as needed, and then invoke pg_restore with the -L switch. The DB is pretty stable, so this wouldn't be too painful, but it seems like given this limitation, a person with room to spare might want to do both an older style test dump of the whole DB and an archive format dump to cover both wholesale and piecemeal recovery scenarios in a convenient way. Is this considered a bug, or a generally accepted limitation? -NF
Re: pg_restore error: function plpgsql_call_handler already exists with same argument types
From
Tom Lane
Date:
"Nick Fankhauser" <nickf@ontko.com> writes:
>> There are a lot of situations where pg_dump fails to pick a safe reload
>> order at the moment (that's why pg_restore has that wild and woolly set
>> of options for manual adjustment of the reload order).
> Is this considered a bug, or a generally accepted limitation?
It's a bug, but one of long standing. It will not be fixed before 7.4
at the earliest (I *hope* someone is gonna tackle this for 7.4).
regards, tom lane