Thread: Dropping a broken function

I have a problem dropping a function which attribute type was already
deleted. So basically this is what has happened:

1) a type 'lo' was created
2) a function lo_oid(lo) was created
3) type 'lo' was dropped

Now the function cannot be dropped and the broken function is preventing
dumping the db.

    Joonas Makkonen

"Joonas Makkonen" <joonas.makkonen@infosto.fi> writes:
> 1) a type 'lo' was created
> 2) a function lo_oid(lo) was created
> 3) type 'lo' was dropped

Yeah, there's no interlock against that :-(

> Now the function cannot be dropped and the broken function is preventing
> dumping the db.

Find out the OID of the function, eg with

    select oid,* from pg_proc where proname = 'lo_oid';

and then zap its pg_proc entry by OID:

    delete from pg_proc where oid = whatever;

(Actually, any other unique WHERE condition will do, but OID is usually
the most reliable way, particularly for functions where there can be
multiple items with the same name.)

You'll need to be logged in as superuser to do this DELETE, of course.

            regards, tom lane

Thanks, that worked.

    -j

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Joonas Makkonen" <joonas.makkonen@infosto.fi>
Cc: <pgsql-novice@postgresql.org>
Sent: Tuesday, November 14, 2000 5:47 PM
Subject: Re: [NOVICE] Dropping a broken function


"Joonas Makkonen" <joonas.makkonen@infosto.fi> writes:
> 1) a type 'lo' was created
> 2) a function lo_oid(lo) was created
> 3) type 'lo' was dropped

Yeah, there's no interlock against that :-(

> Now the function cannot be dropped and the broken function is preventing
> dumping the db.

Find out the OID of the function, eg with

select oid,* from pg_proc where proname = 'lo_oid';

and then zap its pg_proc entry by OID:

delete from pg_proc where oid = whatever;

(Actually, any other unique WHERE condition will do, but OID is usually
the most reliable way, particularly for functions where there can be
multiple items with the same name.)

You'll need to be logged in as superuser to do this DELETE, of course.

regards, tom lane