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@xxxxxxxxxxxxxx> 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
