schema rename sequence issue

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:

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:

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: