Re: schema rename sequence issue - Mailing list 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@compulab.co.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
>>