Thread: function "XXX" already exists with same argument types
Hello,
I use PostgreSQL 8.4 under CentOS 5.7:
# rpm -qa | grep post
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-8.4.9-1PGDG.rhel5
postgresql-server-8.4.9-1PGDG.rhel5
compat-postgresql-libs-4-1PGDG.rhel5
postgresql-libs-8.4.9-1PGDG.rhel5
postgresql-devel-8.4.9-1PGDG.rhel5
And perform nightly backups with this cronjob:
1 1 * * * pg_dump $PGDATABASE | gzip -c >
$HOME/backups/pref-`date +\%F`.sql.gz
I also restored them on the same and another
machines (dev. VMs) often and w/o problems.
Now I'm trying to migrate to another machine
and CentOS 6 and suddenly emits the warnings:
# rpm -qa|grep post
postgresql-libs-8.4.7-2.el6.x86_64
postgresql-devel-8.4.7-2.el6.x86_64
postgresql-8.4.7-2.el6.x86_64
postgresql-docs-8.4.7-2.el6.x86_64
postgresql-server-8.4.7-2.el6.x86_64
pref# \i pref-2011-10-05-a.sql
SET
SET
SET
SET
SET
SET
psql:pref-2011-10-05-a.sql:16: ERROR: language "plpgsql" already exists
ALTER LANGUAGE
SET
CREATE DOMAIN
ALTER DOMAIN
............
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
psql:pref-2011-10-05-a.sql:195: ERROR: function "pref_update_catch"
already exists with same argument types
ALTER FUNCTION
psql:pref-2011-10-05-a.sql:236: ERROR: function "pref_update_game"
already exists with same argument types
ALTER FUNCTION
psql:pref-2011-10-05-a.sql:256: ERROR: function "pref_update_hand"
already exists with same argument types
ALTER FUNCTION
psql:pref-2011-10-05-a.sql:288: ERROR: function "pref_update_luck"
already exists with same argument types
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
psql:pref-2011-10-05-a.sql:339: ERROR: function "pref_update_match"
already exists with same argument types
ALTER FUNCTION
The 1st waring is ok, as I've run "create language plpgsql" before.
But why do I get the function warings?
In my original database I don't see any duplicates with \df or \df+
# psql
psql (8.4.9)
Type "help" for help.
pref=> \df pref_update_catch
List of functions
Schema | Name | Result data type |
Argument data
types
| Type
--------+-------------------+------------------+--------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------+--------
public | pref_update_catch | void | _id character varying, _trix0 i
nteger, _trix1 integer, _trix2 integer, _trix3 integer, _trix4 integer, _trix5 i
nteger, _trix6 integer, _trix7 integer, _trix8 integer, _trix9 integer, _trix10
integer, _trust integer | normal
(1 row)
Same picture in my target database on CentOS 6.0
(it has 8.4.7 and not 8.4.9 though)
Any ideas what is happening please?
Regards
Alex
On Oct 5, 2011, at 11:21, Alexander Farber wrote: > Hello, > > I use PostgreSQL 8.4 under CentOS 5.7: > > # rpm -qa | grep post > compat-postgresql-libs-4-1PGDG.rhel5 > postgresql-8.4.9-1PGDG.rhel5 > postgresql-server-8.4.9-1PGDG.rhel5 > compat-postgresql-libs-4-1PGDG.rhel5 > postgresql-libs-8.4.9-1PGDG.rhel5 > postgresql-devel-8.4.9-1PGDG.rhel5 > > And perform nightly backups with this cronjob: > > 1 1 * * * pg_dump $PGDATABASE | gzip -c > > $HOME/backups/pref-`date +\%F`.sql.gz > > I also restored them on the same and another > machines (dev. VMs) often and w/o problems. > > Now I'm trying to migrate to another machine > and CentOS 6 and suddenly emits the warnings: > > # rpm -qa|grep post > postgresql-libs-8.4.7-2.el6.x86_64 > postgresql-devel-8.4.7-2.el6.x86_64 > postgresql-8.4.7-2.el6.x86_64 > postgresql-docs-8.4.7-2.el6.x86_64 > postgresql-server-8.4.7-2.el6.x86_64 > > pref# \i pref-2011-10-05-a.sql > SET > SET > SET > SET > SET > SET > psql:pref-2011-10-05-a.sql:16: ERROR: language "plpgsql" already exists > ALTER LANGUAGE > SET > CREATE DOMAIN > ALTER DOMAIN > ............ > CREATE FUNCTION > ALTER FUNCTION > CREATE FUNCTION > ALTER FUNCTION > psql:pref-2011-10-05-a.sql:195: ERROR: function "pref_update_catch" > already exists with same argument types > ALTER FUNCTION > psql:pref-2011-10-05-a.sql:236: ERROR: function "pref_update_game" > already exists with same argument types > ALTER FUNCTION > psql:pref-2011-10-05-a.sql:256: ERROR: function "pref_update_hand" > already exists with same argument types > ALTER FUNCTION > psql:pref-2011-10-05-a.sql:288: ERROR: function "pref_update_luck" > already exists with same argument types > ALTER FUNCTION > CREATE FUNCTION > ALTER FUNCTION > psql:pref-2011-10-05-a.sql:339: ERROR: function "pref_update_match" > already exists with same argument types > ALTER FUNCTION > > The 1st waring is ok, as I've run "create language plpgsql" before. > > But why do I get the function warings? Likely someone mistakenly added the functions to template1 of the machine you're restoring onto and they're getting addedto the new database when it's created. Michael Glaesemann grzm seespotcode net
Thank you Michael, but no -
On Wed, Oct 5, 2011 at 5:24 PM, Michael Glaesemann <grzm@seespotcode.net> wrote:
>> psql:pref-2011-10-05-a.sql:339: ERROR: function "pref_update_match"
>> already exists with same argument types
>> ALTER FUNCTION
>
> Likely someone mistakenly added the functions to template1 of the machine you're restoring onto and they're getting
addedto the new database when it's created.
# psql -U postgres -W template1
Password for user postgres:
psql (8.4.7)
Type "help" for help.
template1=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
On 10/05/2011 05:27 PM, Alexander Farber wrote: <blockquote cite="mid:CAADeyWiQJVZ-5ZCK8gYo6H9at_ztfGHN-PXToj8E0ERYTjV2Cg@mail.gmail.com"type="cite"><pre wrap="">Thank you Michael,but no - On Wed, Oct 5, 2011 at 5:24 PM, Michael Glaesemann <a class="moz-txt-link-rfc2396E" href="mailto:grzm@seespotcode.net"><grzm@seespotcode.net></a>wrote: </pre><blockquote type="cite"><blockquote type="cite"><pre wrap="">psql:pref-2011-10-05-a.sql:339: ERROR: function "pref_update_match" already exists with same argument types ALTER FUNCTION </pre></blockquote><pre wrap=""> Likely someone mistakenly added the functions to template1 of the machine you're restoring onto and they're getting addedto the new database when it's created. </pre></blockquote><pre wrap=""> # psql -U postgres -W template1 Password for user postgres: psql (8.4.7) Type "help" for help. template1=# \df List of functionsSchema | Name | Result data type | Argument data types | Type --------+------+------------------+---------------------+------ (0 rows) </pre></blockquote> What is your process for creating the database?<br /> Did you run your script once and then run it againafter failure? It does not run in a transaction, unless you ask it to, so if it failed on something and you want torun it again, you should drop the database and create it again.<br /><br />
Alexander Farber wrote: >>> psql:pref-2011-10-05-a.sql:339: ERROR: function "pref_update_match" >>> already exists with same argument types >>> ALTER FUNCTION >> Likely someone mistakenly added the functions to template1 of the machine you're restoring onto and >> they're getting added to the new database when it's created. > # psql -U postgres -W template1 > Password for user postgres: > psql (8.4.7) > Type "help" for help. > > template1=# \df > List of functions > Schema | Name | Result data type | Argument data types | Type > --------+------+------------------+---------------------+------ > (0 rows) That should be \dfS That the functions are in template1 is just a theory - the fact is that they were already defined in the database where you restored the dump. What is you drop and recreate the database, then run \dfS in a superuser psql session to make sure there is nothing there, then restore. Yours, Laurenz Albe
Thanks for your comments, the problem has disappeared on the 2nd restore, but I'll keep you suggestions in mind! On Thu, Oct 6, 2011 at 10:00 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: >> template1=# \df >> List of functions >> Schema | Name | Result data type | Argument data types | Type >> --------+------+------------------+---------------------+------ >> (0 rows) > > That should be \dfS > > That the functions are in template1 is just a theory - the fact > is that they were already defined in the database where you > restored the dump. > > What is you drop and recreate the database, then run > \dfS in a superuser psql session to make sure there is nothing there, > then restore.