pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDrop Schema Error
5+ messages / 3 participants
[nested] [flat]
* Drop Schema Error
@ 2011-01-28 23:53 Bradley Holbrook <operations_bradley@servillian.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Bradley Holbrook @ 2011-01-28 23:53 UTC (permalink / raw)
To: pgsql-admin
Hello!
I'm trying to:
DROP SCHEMA "_old_permissions";
And I get this error:
ERROR: cannot drop schema _old_permissions because other objects depend on
it
DETAIL: function 17059 depends on schema _old_permissions
function 17060 depends on schema _old_permissions
HINT: Use DROP . CASCADE to drop the dependant objects too.
So, naturally I:
DROP SCHEMA "_old_permissions" CASCADE;
Which produces:
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to function 17059
drop cascades to function 17060
[Err] ERROR: cache lookup failed for function 17060
What is this trying to tell me?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Drop Schema Error
@ 2011-01-29 16:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bradley Holbrook <operations_bradley@servillian.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2011-01-29 16:57 UTC (permalink / raw)
To: Bradley Holbrook <operations_bradley@servillian.ca>; +Cc: pgsql-admin
"Bradley Holbrook" <operations_bradley@servillian.ca> writes:
> DROP SCHEMA "_old_permissions" CASCADE;
> NOTICE: drop cascades to 2 other objects
> DETAIL: drop cascades to function 17059
> drop cascades to function 17060
> [Err] ERROR: cache lookup failed for function 17060
> What is this trying to tell me?
Apparently you've got some dangling references in pg_depend, ie those
functions were dropped already but the pg_depend entries for them were
not cleaned up.
We've heard similar reports before (usually about temp tables not
functions, IIRC) but never been able to reproduce them or identify a
cause. What PG version is this? Have you done anything funny to that
schema or its contents, or had any crashes around the time of
manipulating either?
As far as cleaning up goes, the thing to do would be to manually delete
the relevant pg_depend entries --- for this one, an entry with objid =
17060 is probably what you're looking for. But it would be good to try
to figure out how you got into this state first.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Drop Schema Error
@ 2011-01-29 19:15 Viktor Bojović <viktor.bojovic@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Viktor Bojović @ 2011-01-29 19:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bradley Holbrook <operations_bradley@servillian.ca>; pgsql-admin
On Sat, Jan 29, 2011 at 5:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Bradley Holbrook" <operations_bradley@servillian.ca> writes:
> > DROP SCHEMA "_old_permissions" CASCADE;
> > NOTICE: drop cascades to 2 other objects
> > DETAIL: drop cascades to function 17059
> > drop cascades to function 17060
> > [Err] ERROR: cache lookup failed for function 17060
>
> > What is this trying to tell me?
>
> Apparently you've got some dangling references in pg_depend, ie those
> functions were dropped already but the pg_depend entries for them were
> not cleaned up.
>
> We've heard similar reports before (usually about temp tables not
> functions, IIRC) but never been able to reproduce them or identify a
> cause. What PG version is this? Have you done anything funny to that
> schema or its contents, or had any crashes around the time of
> manipulating either?
>
> As far as cleaning up goes, the thing to do would be to manually delete
> the relevant pg_depend entries --- for this one, an entry with objid =
> 17060 is probably what you're looking for. But it would be good to try
> to figure out how you got into this state first.
>
> regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
i had that problem when renaming schema which had already objects inside.
functions inside that schema had references to objects inside that schema.
some functions still had reference to old schema name.
so i renamed it again to drop those objects.
It was 8.3.13 or prior version @ 32bit debian linux .
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Drop Schema Error
@ 2011-01-31 18:00 Bradley Holbrook <operations_bradley@servillian.ca>
parent: Viktor Bojović <viktor.bojovic@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bradley Holbrook @ 2011-01-31 18:00 UTC (permalink / raw)
To: 'Viktor Bojović' <viktor.bojovic@gmail.com>; 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Worked like a charm, thanks Tom and Viktor.
I’m using version 8.4.5 64bit CentOs Linux
I renamed the schema from permissions to _old_permissions and I believe this caused the error.
From: Viktor Bojović [mailto:viktor.bojovic@gmail.com]
Sent: January-29-11 11:16 AM
To: Tom Lane
Cc: Bradley Holbrook; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Drop Schema Error
On Sat, Jan 29, 2011 at 5:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Bradley Holbrook" <operations_bradley@servillian.ca> writes:
> DROP SCHEMA "_old_permissions" CASCADE;
> NOTICE: drop cascades to 2 other objects
> DETAIL: drop cascades to function 17059
> drop cascades to function 17060
> [Err] ERROR: cache lookup failed for function 17060
> What is this trying to tell me?
Apparently you've got some dangling references in pg_depend, ie those
functions were dropped already but the pg_depend entries for them were
not cleaned up.
We've heard similar reports before (usually about temp tables not
functions, IIRC) but never been able to reproduce them or identify a
cause. What PG version is this? Have you done anything funny to that
schema or its contents, or had any crashes around the time of
manipulating either?
As far as cleaning up goes, the thing to do would be to manually delete
the relevant pg_depend entries --- for this one, an entry with objid =
17060 is probably what you're looking for. But it would be good to try
to figure out how you got into this state first.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
i had that problem when renaming schema which had already objects inside.
functions inside that schema had references to objects inside that schema.
some functions still had reference to old schema name.
so i renamed it again to drop those objects.
It was 8.3.13 or prior version @ 32bit debian linux .
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Drop Schema Error
@ 2011-01-31 18:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bradley Holbrook <operations_bradley@servillian.ca>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2011-01-31 18:24 UTC (permalink / raw)
To: Bradley Holbrook <operations_bradley@servillian.ca>; +Cc: 'Viktor Bojović' <viktor.bojovic@gmail.com>; pgsql-admin
"Bradley Holbrook" <operations_bradley@servillian.ca> writes:
> I renamed the schema from permissions to _old_permissions and I believe this caused the error.
Uh, no, certainly not. pg_depend only cares about the objects' OIDs,
not their names. Testing in 8.3, it works fine:
regression=# create schema old_permissions;
CREATE SCHEMA
regression=# create function old_permissions.foo() returns int as
regression-# 'select 2+2' language sql;
CREATE FUNCTION
regression=# create function old_permissions.foo2(int) returns int as
'begin return $1 + 1; end' language plpgsql;
CREATE FUNCTION
regression=# alter schema old_permissions rename to "_old_permissions";
ALTER SCHEMA
regression=# DROP SCHEMA "_old_permissions" ;
NOTICE: function _old_permissions.foo2(integer) depends on schema _old_permissions
NOTICE: function _old_permissions.foo() depends on schema _old_permissions
ERROR: cannot drop schema _old_permissions because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
regression=# DROP SCHEMA "_old_permissions" CASCADE;
NOTICE: drop cascades to function _old_permissions.foo2(integer)
NOTICE: drop cascades to function _old_permissions.foo()
DROP SCHEMA
regression=#
As I said, we've never seen a reproducible example.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-01-31 18:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-28 23:53 Drop Schema Error Bradley Holbrook <operations_bradley@servillian.ca>
2011-01-29 16:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-29 19:15 ` Viktor Bojović <viktor.bojovic@gmail.com>
2011-01-31 18:00 ` Bradley Holbrook <operations_bradley@servillian.ca>
2011-01-31 18:24 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox