Thread: schema rename sequence issue
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 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'); I checked the table and it looked correct. However, when I try to insert into the table I get an error that public.sequence name is not found Am I missing a reference or table update somewhere? Thank You Sim
On Jan 30, 2006, at 18:51 , Sim Zacks wrote: > However, when I try to insert into the table I get an error that > public.sequence name is not found > > Am I missing a reference or table update somewhere? You probably need to update the defaults for the columns that call the sequences. They may still be using the previous schema in their nextval calls. Hope this helps. Michael Glaesemann grzm myrealbox com
I thought the pg_attrdef table was the defaults. Is there another table that contains the defaults Thank You Sim ________________________________________________________________________________ On Jan 30, 2006, at 18:51 , Sim Zacks wrote: > However, when I try to insert into the table I get an error that > public.sequence name is not found > > Am I missing a reference or table update somewhere? You probably need to update the defaults for the columns that call the sequences. They may still be using the previous schema in their nextval calls. Hope this helps. Michael Glaesemann grzm myrealbox com
On Jan 30, 2006, at 19:03 , Sim Zacks wrote:
> I thought the pg_attrdef table was the defaults.
> Is there another table that contains the defaults
What I'm thinking of is this situation:
create table foo
(
foo_id serial primary key
, foo_value text not null unique
);
This will create a sequence (public.foo_id_key_seq, I think) as well
as a table
public.foo
(
foo_id integer primary key default nextval('public.foo_id_key_seq')
, foo_value text not null unique
);
(And some indexes as well, but we'll set those aside for now.)
You've renamed the schema, but I think you need to update the default
for the foo_id column manually, e.g.,
alter table stock.foo alter column foo_id set default = nextval
('stock.foo_id_key_seq');
Syntax not checked.
If this isn't what it is, perhaps someone else has an idea.
Michael Glaesemann
grzm myrealbox com
Thank you for your thoughts, but I am hoping that I don't have to
update 400 tables manually.
The way I understand the postgresql structure is that each field is
listed in the pg_attribute table and those fields with default values
have records in the pg_attrdef table. I changed both the text and the
binary to reflect the new schema and using PGAdmin3, everything
"looks" correct. So when I click on the table it tells me the sequence
is in the correct schema.
Unfortunately, Insert doesn't work.
Thank You
Sim
________________________________________________________________________________
On Jan 30, 2006, at 19:03 , Sim Zacks wrote:
> I thought the pg_attrdef table was the defaults.
> Is there another table that contains the defaults
What I'm thinking of is this situation:
create table foo
(
foo_id serial primary key
, foo_value text not null unique
);
This will create a sequence (public.foo_id_key_seq, I think) as well
as a table
public.foo
(
foo_id integer primary key default nextval('public.foo_id_key_seq')
, foo_value text not null unique
);
(And some indexes as well, but we'll set those aside for now.)
You've renamed the schema, but I think you need to update the default
for the foo_id column manually, e.g.,
alter table stock.foo alter column foo_id set default = nextval
('stock.foo_id_key_seq');
Syntax not checked.
If this isn't what it is, perhaps someone else has an idea.
Michael Glaesemann
grzm myrealbox com
It seems to be an in-memory issue or something like that.
5 hours later it gave me another error implying that it was reading the
pg_attrdef table.
Apparantly the first number in the byte string is the number of
characters in the string, and since my new schema is 1 character shorter
then the old one, it is croaking.
So I changed it in one case to see if it would make a difference and I
got the exact same error. Which implies, that this table is read into
memory and reread at intervals. So now I'll wait till tomorrow to see if
the fix I did works and then I'll be able to determine if that fix is it
or not.
Sim Zacks wrote:
> Thank you for your thoughts, but I am hoping that I don't have to
> update 400 tables manually.
>
> The way I understand the postgresql structure is that each field is
> listed in the pg_attribute table and those fields with default values
> have records in the pg_attrdef table. I changed both the text and the
> binary to reflect the new schema and using PGAdmin3, everything
> "looks" correct. So when I click on the table it tells me the sequence
> is in the correct schema.
>
> Unfortunately, Insert doesn't work.
>
> Thank You
> Sim
> ________________________________________________________________________________
>
>
> On Jan 30, 2006, at 19:03 , Sim Zacks wrote:
>
>> I thought the pg_attrdef table was the defaults.
>> Is there another table that contains the defaults
>
> What I'm thinking of is this situation:
>
> create table foo
> (
> foo_id serial primary key
> , foo_value text not null unique
> );
>
> This will create a sequence (public.foo_id_key_seq, I think) as well
> as a table
> public.foo
> (
> foo_id integer primary key default nextval('public.foo_id_key_seq')
> , foo_value text not null unique
> );
>
> (And some indexes as well, but we'll set those aside for now.)
>
> You've renamed the schema, but I think you need to update the default
> for the foo_id column manually, e.g.,
>
> alter table stock.foo alter column foo_id set default = nextval
> ('stock.foo_id_key_seq');
>
> Syntax not checked.
>
> If this isn't what it is, perhaps someone else has an idea.
>
> Michael Glaesemann
> grzm myrealbox com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
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
"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 >
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 >>