Thread: alter table schema, default sequences stay the same
I am using 8.2.17
I added a new schema and moved tables into it using
ALTER TABLE tblname SET SCHEMA newschema;
This moves the sequences referred to by the table to the new schema as
is specified by the manual.
> Associated indexes, constraints, and sequences owned by table columns
> are moved as well.
I was very surprised to find that the default nextval functions still
refer to the sequence public.sequencename
I discovered this when I tried to insert and it told me the sequence
does not exist.
> id integer NOT NULL DEFAULT
> nextval(('public.tblname_id_seq'::text)::regclass)
Shouldn't this change automatically as well?
Is there an easy way to modify all the default values now?
Thanks
Sim
In response to Sim Zacks :
> I am using 8.2.17
>
> I added a new schema and moved tables into it using
>
> ALTER TABLE tblname SET SCHEMA newschema;
>
>
> This moves the sequences referred to by the table to the new schema as
> is specified by the manual.
>
> > Associated indexes, constraints, and sequences owned by table columns
> > are moved as well.
>
> I was very surprised to find that the default nextval functions still
> refer to the sequence public.sequencename
>
> I discovered this when I tried to insert and it told me the sequence
> does not exist.
>
>
> > id integer NOT NULL DEFAULT
> > nextval(('public.tblname_id_seq'::text)::regclass)
> Shouldn't this change automatically as well?
> Is there an easy way to modify all the default values now?
Just for info: works well with 8.4:
test=*# create schema bla;
CREATE SCHEMA
test=*# create table public.s (i serial);
NOTICE: CREATE TABLE will create implicit sequence "s_i_seq" for serial column "s.i"
CREATE TABLE
test=*# \d s
Table "public.s"
Column | Type | Modifiers
--------+---------+-----------------------------------------------
i | integer | not null default nextval('s_i_seq'::regclass)
test=*# alter table s set schema bla;
ALTER TABLE
test=*# \d bla.s
Table "bla.s"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
i | integer | not null default nextval('bla.s_i_seq'::regclass)
test=*# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 64-bit
(1 row)
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On Tue, Jun 29, 2010 at 8:20 AM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:
> In response to Sim Zacks :
>> I am using 8.2.17
>>
>> I added a new schema and moved tables into it using
>>
>> ALTER TABLE tblname SET SCHEMA newschema;
>>
>>
>> This moves the sequences referred to by the table to the new schema as
>> is specified by the manual.
>>
>> > Associated indexes, constraints, and sequences owned by table columns
>> > are moved as well.
>>
>> I was very surprised to find that the default nextval functions still
>> refer to the sequence public.sequencename
>>
>> I discovered this when I tried to insert and it told me the sequence
>> does not exist.
>>
>>
>> > id integer NOT NULL DEFAULT
>> > nextval(('public.tblname_id_seq'::text)::regclass)
>> Shouldn't this change automatically as well?
>> Is there an easy way to modify all the default values now?
>
>
>
> Just for info: works well with 8.4:
>
> test=*# create schema bla;
> CREATE SCHEMA
> test=*# create table public.s (i serial);
> NOTICE: CREATE TABLE will create implicit sequence "s_i_seq" for serial column "s.i"
> CREATE TABLE
> test=*# \d s
> Table "public.s"
> Column | Type | Modifiers
> --------+---------+-----------------------------------------------
> i | integer | not null default nextval('s_i_seq'::regclass)
>
> test=*# alter table s set schema bla;
> ALTER TABLE
> test=*# \d bla.s
> Table "bla.s"
> Column | Type | Modifiers
> --------+---------+---------------------------------------------------
> i | integer | not null default nextval('bla.s_i_seq'::regclass)
>
> test=*# select version();
> version
> --------------------------------------------------------------------------------------------------------
> PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1.1) 4.3.2, 64-bit
> (1 row)
Works in 8.3.9 on ubuntu 9.10...
On 6/29/2010 3:49 PM, Scott Marlowe wrote:
> On Tue, Jun 29, 2010 at 8:20 AM, A. Kretschmer
> <andreas.kretschmer@schollglas.com> wrote:
>
>> In response to Sim Zacks :
>>
>>> I am using 8.2.17
>>>
>>> I added a new schema and moved tables into it using
>>>
>>> ALTER TABLE tblname SET SCHEMA newschema;
>>>
>>>
>>> This moves the sequences referred to by the table to the new schema as
>>> is specified by the manual.
>>>
>>>
>>>> Associated indexes, constraints, and sequences owned by table columns
>>>> are moved as well.
>>>>
>>> I was very surprised to find that the default nextval functions still
>>> refer to the sequence public.sequencename
>>>
>>> I discovered this when I tried to insert and it told me the sequence
>>> does not exist.
>>>
>>>
>>>
>>>> id integer NOT NULL DEFAULT
>>>> nextval(('public.tblname_id_seq'::text)::regclass)
>>>>
>>> Shouldn't this change automatically as well?
>>> Is there an easy way to modify all the default values now?
>>>
>>
>>
>> Just for info: works well with 8.4:
>>
>>
>>
> Works in 8.3.9 on ubuntu 9.10...
>
>
In 8.2.17 I just tried and when creating a table and then changing the
schema it works fine.
However, when i looked at the table definition in pgadmin it showed the
type as serial without any defaut nextval.
After I moved its schema, the definition showed a datatype of int and
the default nextval but without any schema qualification for the sequence.
For some reason, (could be because of an upgrade or data restore) all of
my table definitions show default nextval(public.
They were not defined that way, they were defined as serial (if that
makes any difference).
I guess my big question is: how would I change all the default values to
remove the schema qualification. I suppose updating the pg_attrdef table
is not recommended, if it would work at all.
Sim
In response to Scott Marlowe :
> On Tue, Jun 29, 2010 at 8:20 AM, A. Kretschmer
> <andreas.kretschmer@schollglas.com> wrote:
> >
> >
> > Just for info: works well with 8.4:
>
> Works in 8.3.9 on ubuntu 9.10...
>
I think, this is the problem:
You have created the table first and later the sequence, like this:
test=# create table public.s (i int);
CREATE TABLE
test=*# create sequence my_seq;
CREATE SEQUENCE
test=*# alter table s alter column i set default nextval('my_seq');
ALTER TABLE
test=*# create schema bla;
CREATE SCHEMA
test=*# alter table s set schema bla;
ALTER TABLE
test=*# \d bla.s
Table "bla.s"
Column | Type | Modifiers
--------+---------+-------------------------------------
i | integer | default nextval('my_seq'::regclass)
You have now a table in schema bla and the sequence still in public. To
avoid that you have to alter the sequence too:
test=# create table public.s (i int);
CREATE TABLE
test=*# create sequence my_seq;
CREATE SEQUENCE
test=*# alter table s alter column i set default nextval('my_seq');
ALTER TABLE
test=*# alter sequence my_seq owned by s.i;
ALTER SEQUENCE
test=*# create schema bla;
CREATE SCHEMA
test=*# alter table s set schema bla;
ALTER TABLE
test=*# \d bla.s
Table "bla.s"
Column | Type | Modifiers
--------+---------+-----------------------------------------
i | integer | default nextval('bla.my_seq'::regclass)
But i'm not sure if 'alter sequence owned ...' available in 8.2.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On 6/29/2010 4:24 PM, A. Kretschmer wrote:
> In response to Scott Marlowe :
>
>> On Tue, Jun 29, 2010 at 8:20 AM, A. Kretschmer
>> <andreas.kretschmer@schollglas.com> wrote:
>>
>>>
>>> Just for info: works well with 8.4:
>>>
>> Works in 8.3.9 on ubuntu 9.10...
>>
>>
> I think, this is the problem:
> You have created the table first and later the sequence, like this:
>
>
> test=# create table public.s (i int);
> CREATE TABLE
> test=*# create sequence my_seq;
> CREATE SEQUENCE
> test=*# alter table s alter column i set default nextval('my_seq');
> ALTER TABLE
> test=*# create schema bla;
> CREATE SCHEMA
> test=*# alter table s set schema bla;
> ALTER TABLE
> test=*# \d bla.s
> Table "bla.s"
> Column | Type | Modifiers
> --------+---------+-------------------------------------
> i | integer | default nextval('my_seq'::regclass)
>
>
> You have now a table in schema bla and the sequence still in public. To
> avoid that you have to alter the sequence too:
>
> test=# create table public.s (i int);
> CREATE TABLE
> test=*# create sequence my_seq;
> CREATE SEQUENCE
> test=*# alter table s alter column i set default nextval('my_seq');
> ALTER TABLE
> test=*# alter sequence my_seq owned by s.i;
> ALTER SEQUENCE
> test=*# create schema bla;
> CREATE SCHEMA
> test=*# alter table s set schema bla;
> ALTER TABLE
> test=*# \d bla.s
> Table "bla.s"
> Column | Type | Modifiers
> --------+---------+-----------------------------------------
> i | integer | default nextval('bla.my_seq'::regclass)
>
>
> But i'm not sure if 'alter sequence owned ...' available in 8.2.
>
No. The sequences were all created automatically using the serial type.
When I do the alter table it actually moves the sequences.
The only thing it doesn't do is change the default value. The strange
thing I noticed is that all the default values show public.sequencename.
instead of serial.
Sim Zacks <sim@compulab.co.il> writes:
>> id integer NOT NULL DEFAULT
>> nextval(('public.tblname_id_seq'::text)::regclass)
> Shouldn't this change automatically as well?
It would have changed automatically if the default expression were what
it's supposed to be, namely nextval('sequencename'::regclass). What
you've got there is a text constant, which of course is static.
I haven't consumed enough caffeine today to recall the details, but
I think you could have ended up with default expressions like the above
if the database had been dumped and reloaded from 8.0 or earlier.
nextval(regclass) was introduced in 8.1 precisely to solve this type
of problem.
> Is there an easy way to modify all the default values now?
Not especially :-(. You're going to need to run around and change them
all to the right name. Be sure to lose the ::text bit while at it,
so it works right the next time.
regards, tom lane
> I haven't consumed enough caffeine today to recall the details, but
> I think you could have ended up with default expressions like the above
> if the database had been dumped and reloaded from 8.0 or earlier.
> nextval(regclass) was introduced in 8.1 precisely to solve this type
> of problem.
>
>
>> Is there an easy way to modify all the default values now?
>>
> Not especially :-(. You're going to need to run around and change them
> all to the right name. Be sure to lose the ::text bit while at it,
> so it works right the next time.
>
> regards, tom lane
>
This is the query I used to generate the alter stmt for all the defaults
that used public sequences in the new schema
select 'alter table ' || relname || ' alter column ' || attname || ' set
default ' ||
replace(replace(replace(b.adsrc,'public.',''),'::text',''),'::regclass','')
|| ';'
from pg_attribute a
join pg_attrdef b on a.attrelid=b.adrelid and attnum=adnum
join pg_class c on c.oid=a.attrelid
where adsrc like '%nextval((''public.%'
and relnamespace=558726861
order by adsrc
My first database was an 8.0 and at some point we upgraded to 8.2 so
that must have caused the ?corruption?
Sim Zacks <sim@compulab.co.il> writes:
>> I haven't consumed enough caffeine today to recall the details, but
>> I think you could have ended up with default expressions like the above
>> if the database had been dumped and reloaded from 8.0 or earlier.
>> nextval(regclass) was introduced in 8.1 precisely to solve this type
>> of problem.
> My first database was an 8.0 and at some point we upgraded to 8.2 so
> that must have caused the ?corruption?
Yeah, that would fit. The upgrade process was intentionally designed to
work that way, because the pre-8.1 behavior of DEFAULT nextval('foo')
was that it always referred to the sequence currently named foo, because
'foo' was just a text constant. We didn't want to risk breaking things
for anyone who was depending on that behavior. But now you have to
explicitly put in ::text if that's how you want it to behave ---
otherwise you get a regclass constant, which will track the original
sequence's identity despite renamings.
regards, tom lane