Postgres Pro
Downloads
Home   >   mailing lists

Thread: Dropping functions with missing argument types

Dropping functions with missing argument types

From
ddkilzer@theracingworld.com
Date:
[I didn't find any related info on techdocs.postgresql.org or on the
mailing list archives.]

I goofed up our demo database, and had to restore it from a backup
made by pg_backup.sh (which uses pg_dump in "custom" binary mode).

After restoring the demo database, I tried dumping it again to see
if there were any residual issues from the restore.  I got the
following errors (after restoring the named functions):


$ pg_dump -S postgres demodb > demodb.out
Notice: function "get_sort_name" is not dumped.
Reason: the 0 th argument type name (oid 1388921) not found.
Notice: function "get_sort_name" is not dumped.
Reason: the 0 th argument type name (oid 1389578) not found.
Notice: function "get_name" is not dumped.
Reason: the 0 th argument type name (oid 1388744) not found.
Notice: function "get_name" is not dumped.
Reason: the 0 th argument type name (oid 1388921) not found.
Notice: function "get_name" is not dumped.
Reason: the 0 th argument type name (oid 1389578) not found.
$ psql demodb demouser

demodb=> \df get_sort_name
                List of functions
      Result       |   Function    |  Arguments
-------------------+---------------+-------------
 character varying | get_sort_name | ???
 character varying | get_sort_name | ???
 character varying | get_sort_name | table1
 character varying | get_sort_name | table2
(4 rows)

demodb=> \df get_name
             List of functions
      Result       | Function |  Arguments
-------------------+----------+-------------
 character varying | get_name | ???
 character varying | get_name | ???
 character varying | get_name | ???
 character varying | get_name | table3
 character varying | get_name | table1
 character varying | get_name | table2
(6 rows)

demodb=> \q


I don't believe I will be able to delete these errant functions using
the standard DROP FUNCTION command.

What is the best way to delete them?  Am I able to simply identify and
delete the corresponding rows from the PG_PROC table?  Are there any
other system tables that I need to update if I delete rows from the
PG_PROC table?

Thanks!

Dave

Re: Dropping functions with missing argument types

From
ddkilzer@theracingworld.com
Date:
Sorry...I'm not thinking clearly at the moment.

I'm using PostgreSQL 7.1.3 on Debian GNU/Linux r2.2 ("potato") on the
i386 architecture with a 2.2.19 Linux kernel.

Dave


On Wed, Jan 15, 2003 at 02:50:05PM -0600, ddkilzer@theracingworld.com wrote:

> [...]
>
> What is the best way to delete them?  Am I able to simply identify and
> delete the corresponding rows from the PG_PROC table?  Are there any
> other system tables that I need to update if I delete rows from the
> PG_PROC table?
>
> Thanks!
>
> Dave

Re: Dropping functions with missing argument types

From
Tom Lane
Date:
ddkilzer@theracingworld.com writes:
> What is the best way to delete them?  Am I able to simply identify and
> delete the corresponding rows from the PG_PROC table?

Yup, that should do it.  You will need to be superuser to issue a delete
against pg_proc, but otherwise there's no reason not to.

> Are there any
> other system tables that I need to update if I delete rows from the
> PG_PROC table?

Not unless these were triggers or I/O functions, which I gather they're
not.

            regards, tom lane