Skip site navigation (1) Skip section navigation (2)

Re: schema rename sequence issue

From: Sim Zacks <sim(at)compulab(dot)co(dot)il>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: schema rename sequence issue
Date: 2006-01-31 13:51:06
Message-ID: drnq2u$5rq$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-general
OK. This is working in my test environment.
I changed the schema name
I changed the binary and text description in the attrdef table.
I subtract 1 from the number both before the [ and after the ] because 
my new schema has 1 letter less then the old schema.

It seems to be working.

Can anybody think of a possible problem with this?

Note: I am not planning on doing this more then once. If it will not 
break anything this time, I don't care that it may not work in the future.

Code used
--changes schema name from public to stock
update pg_attrdef set 
adsrc=replace(adsrc,'public.','stock.'),adbin=replace(adbin,'112 117 98 
108 105 99 46','115 116 111 99 107 46');

--function to change the number of characters in the binary string
create or replace function fixattrs(text) returns text as
$$
import string
y=args[0]
x=y.split()
x[30]=`int(x[30])-1`
x[32]=`int(x[32])-1`
return string.joinfields(x," ")
$$
language 'plpythonu';

--update the binary string with the new numbers.
update pg_attrdef set adbin=fixattrs(adbin) where adsrc like 'nextval%'

Thanks
Sim

Sim Zacks wrote:
> "I may not be a smart man, but I do know what love is"
> Of course I did this on a database that I didn't care about. This is my 
> development machine. If it actually fully worked after testing, I would 
> have considered moving it into production code.
> 
> Is there a more approved way of doing this, besides upgrading the server?
> 
>  >Did you start a fresh session after modifying the catalog?
> Is that all I needed to do? As I mentioned in another post, I screwed up 
> something else in there and I'll be playing with that tomorrow. If 
> there's an easier way to do it, please let me know.
> 
> 
> Tom Lane wrote:
>> Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:
>>> PostGreSQL 8.01 Gentoo
>>> I renamed my schema from public to stock, and then I found out it 
>>> didn't change the schemas of the sequences. After searching through 
>>> the archives, I found that this was on a bug list and would probably 
>>> be fixed in version 8.2
>>
>> Actually, it's fixed in 8.1.
>>
>>> I needed to fix the sequences in any case, so I tried to do it 
>>> manually using this code:
>>
>>> update pg_attrdef set 
>>> adsrc=replace(adsrc,'public.','stock.'),adbin=replace(adbin,'112 117 
>>> 98 108 105 99 46','115 116 111 99 107 46');
>>
>> Like to live dangerously, eh?  Hope you weren't doing this on a database
>> containing data you cared about.
>>
>>> However, when I try to insert into the table I get an error that 
>>> public.sequence name is not found
>>
>> Did you start a fresh session after modifying the catalog?  I don't
>> think that cached relation descriptors will react to manual hacks
>> on pg_attrdef.
>>
>>             regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>>

In response to

pgsql-general by date

Next:From: Doug McNaughtDate: 2006-01-31 14:05:47
Subject: Re: PostgreSQL + hibernate
Previous:From: Thomas HallgrenDate: 2006-01-31 11:51:25
Subject: Re: New project launched : PostgreSQL GUI

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group