Postgres Pro
Downloads
Home   >   mailing lists

Thread: Renaming a schema

Renaming a schema

From
Jeffrey Melloy
Date:
Recently, I decided to rename one of my schemas from "adium" to "im".

Then, all inserts started failing.

I recreated a couple functions, changed the search path, and all inserts
are still failing due to referential integrity checks going against
"adium" still.  Is there any way I can fix this, short of manually
dropping every constraint and recreating them?

Jeff

Re: Renaming a schema

From
Tom Lane
Date:
Jeffrey Melloy <jmelloy@visualdistortion.org> writes:
> Recently, I decided to rename one of my schemas from "adium" to "im".
> Then, all inserts started failing.
> I recreated a couple functions, changed the search path, and all inserts
> are still failing due to referential integrity checks going against
> "adium" still.  Is there any way I can fix this, short of manually
> dropping every constraint and recreating them?

I couldn't replicate this ... AFAICS the RI triggers reference tables by
OID, not name.  What PG version are you using?  Can you give a
self-contained test case?

            regards, tom lane

Re: Renaming a schema

From
Jeffrey Melloy
Date:
On Jun 24, 2004, at 4:46 PM, Tom Lane wrote:

> Jeffrey Melloy <jmelloy@visualdistortion.org> writes:
>> Recently, I decided to rename one of my schemas from "adium" to "im".
>> Then, all inserts started failing.
>> I recreated a couple functions, changed the search path, and all
>> inserts
>> are still failing due to referential integrity checks going against
>> "adium" still.  Is there any way I can fix this, short of manually
>> dropping every constraint and recreating them?
>
> I couldn't replicate this ... AFAICS the RI triggers reference tables
> by
> OID, not name.  What PG version are you using?  Can you give a
> self-contained test case?
>
>             regards, tom lane
>
I'm using PostgreSQL 7.4.2, and have a script that reproduces my
problem:

Upon further inspection, the problem isn't the RI triggers; it's the
default sequence not changing.

Jeff

Re: Renaming a schema

From
Tom Lane
Date:
Jeffrey Melloy <jmelloy@visualdistortion.org> writes:
> Upon further inspection, the problem isn't the RI triggers; it's the
> default sequence not changing.

Ah.  Yes, that's a known issue.  We need to fix things so that the
sequence is (internally) referenced by OID rather than by name.
There's been some talk about how to do this but it's a bit harder
than it might sound ...

            regards, tom lane