function "XXX" already exists with same argument types

Started by Alexander Farberover 14 years ago6 messagesgeneral

Alexander Farber

alexander.farber@gmail.com

over 14 years ago

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

Michael Glaesemann

grzm@seespotcode.net

over 14 years ago

In reply to: Alexander Farber (#1)

Re: function "XXX" already exists with same argument types

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 added to the new database when it's created.

Michael Glaesemann
grzm seespotcode net

Alexander Farber

alexander.farber@gmail.com

over 14 years ago

In reply to: Michael Glaesemann (#2)

Re: function "XXX" already exists with same argument types

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 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)

sim@compulab.co.il

over 14 years ago

In reply to: Alexander Farber (#3)

Re: function "XXX" already exists with same argument types

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html style="direction: ltr;">
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
<style>body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" bgcolor="#ffffff"
text="#000000">
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 added to 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 functions
Schema | 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 again after failure? It
does not run in a transaction, unless you ask it to, so if it failed
on something and you want to run it again, you should drop the
database and create it again.<br>
<br>
</body>
</html>

laurenz.albe@cybertec.at

over 14 years ago

In reply to: Alexander Farber (#3)

Re: function "XXX" already exists with same argument types

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

Alexander Farber

alexander.farber@gmail.com

over 14 years ago

In reply to: Laurenz Albe (#5)

Re: function "XXX" already exists with same argument types

Thanks for your comments,
the problem has disappeared on the 2nd restore,
but I'll keep you suggestions in mind!

Show quoted text

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.