Обсуждение: Renaming a schema

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

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

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

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