BUG #1883: Renaming a schema leaves inconsistent sequence names

The following bug has been logged online:

Bug reference: 1883
Logged by: Kouber Saparev
Email address: postgresql(at)saparev(dot)com
PostgreSQL version: 8.0.3
Operating system: Linux 2.6.11.4
Description: Renaming a schema leaves inconsistent sequence names
Details:

When I rename a schema, all the serial fields are pointing to the old
schema, which no longer exists. So trying to insert new records fails.

Here there is an example:

------ begin ------

bugs=# create schema sch1;
CREATE SCHEMA

bugs=# create table sch1.test (id serial primary key, name char(1)) without
oids;
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for serial
column "test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey"
for table "test"
CREATE TABLE

bugs=# \d sch1.test
Table "sch1.test"
Column | Type | Modifiers
--------+--------------+----------------------------------------------------

id | integer | not null default nextval('sch1.test_id_seq'::text)
name | character(1) |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)

bugs=# insert into sch1.test (name) values ('a');
INSERT 0 1

bugs=# alter schema sch1 rename to sch2;
ALTER SCHEMA

bugs=# \d sch2.test
Table "sch2.test"
Column | Type | Modifiers
--------+--------------+----------------------------------------------------

id | integer | not null default nextval('sch1.test_id_seq'::text)
name | character(1) |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)

bugs=# insert into sch2.test (name) values ('b');
ERROR: schema "sch1" does not exist

------ end ------

As you see, the default value of the serial field is pointing to a sequence
in schema "sch1" which is now "sch2". Changing the default value manually
fixes the problem, but it's not very convenient in case when there are a lot
of tables.

After I looked over the bugs submitted so far, I've found that the problem
is already reported, but I'm not sure whether it's well described there.
Take a look at
- http://article.gmane.org/gmane.comp.db.postgresql.bugs/3033/

I apologize, if it is a known bug.

Regards,
Kouber Saparev

This item has been added to the 8.1 bugs list:

http://momjian.postgresql.org/cgi-bin/pgbugs

---------------------------------------------------------------------------

Kouber Saparev wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1883
> Logged by: Kouber Saparev
> Email address: postgresql(at)saparev(dot)com
> PostgreSQL version: 8.0.3
> Operating system: Linux 2.6.11.4
> Description: Renaming a schema leaves inconsistent sequence names
> Details:
>
> When I rename a schema, all the serial fields are pointing to the old
> schema, which no longer exists. So trying to insert new records fails.
>
> Here there is an example:
>
> ------ begin ------
>
> bugs=# create schema sch1;
> CREATE SCHEMA
>
> bugs=# create table sch1.test (id serial primary key, name char(1)) without
> oids;
> NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for serial
> column "test.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey"
> for table "test"
> CREATE TABLE
>
> bugs=# \d sch1.test
> Table "sch1.test"
> Column | Type | Modifiers
> --------+--------------+----------------------------------------------------
>
> id | integer | not null default nextval('sch1.test_id_seq'::text)
> name | character(1) |
> Indexes:
> "test_pkey" PRIMARY KEY, btree (id)
>
> bugs=# insert into sch1.test (name) values ('a');
> INSERT 0 1
>
> bugs=# alter schema sch1 rename to sch2;
> ALTER SCHEMA
>
> bugs=# \d sch2.test
> Table "sch2.test"
> Column | Type | Modifiers
> --------+--------------+----------------------------------------------------
>
> id | integer | not null default nextval('sch1.test_id_seq'::text)
> name | character(1) |
> Indexes:
> "test_pkey" PRIMARY KEY, btree (id)
>
> bugs=# insert into sch2.test (name) values ('b');
> ERROR: schema "sch1" does not exist
>
> ------ end ------
>
> As you see, the default value of the serial field is pointing to a sequence
> in schema "sch1" which is now "sch2". Changing the default value manually
> fixes the problem, but it's not very convenient in case when there are a lot
> of tables.
>
> After I looked over the bugs submitted so far, I've found that the problem
> is already reported, but I'm not sure whether it's well described there.
> Take a look at
> - http://article.gmane.org/gmane.comp.db.postgresql.bugs/3033/
>
> I apologize, if it is a known bug.
>
> Regards,
> Kouber Saparev
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> This item has been added to the 8.1 bugs list:
> http://momjian.postgresql.org/cgi-bin/pgbugs

This isn't going to be fixed for 8.1. I think it's really a variant of
the TODO item
o %Have ALTER TABLE RENAME rename SERIAL sequence names

To my mind the appropriate fix is to come up with some arrangement
that allows the stored form of NEXTVAL(seq) to specify the referenced
sequence directly (by OID) rather than as a text string. There are some
speculations about this in the archives, but no really workable solution
yet.

(wanders off, wondering if a "regclass" literal value would help ...)

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > This item has been added to the 8.1 bugs list:
> > http://momjian.postgresql.org/cgi-bin/pgbugs
>
> This isn't going to be fixed for 8.1. I think it's really a variant of
> the TODO item
> o %Have ALTER TABLE RENAME rename SERIAL sequence names

Well, it might be a variant, but its failure is much worse. For a table
rename, you just get a strange \d display:

test=> CREATE TABLE test (x SERIAL);
NOTICE: CREATE TABLE will create implicit sequence "test_x_seq" for serial column "test.x"
CREATE TABLE
test=> ALTER TABLE test RENAME TO test2;
ALTER TABLE
test=> INSERT INTO test2 VALUES (DEFAULT);
INSERT 0 1
test=> \d test2
Table "public.test2"
Column | Type | Modifiers
--------+---------+-----------------------------------------------------
x | integer | not null default nextval('public.test_x_seq'::text)

The insert into the table still works. For the schema rename, the
insert into the table doesn't work anymore. The odds that a schema
rename is going to have _no_ sequence dependencies in the same schema
seems pretty unlikely, meaning rename schema is almost guarantted to
create some broken table defaults. With this behavior, if we can't fix
it in 8.1, I am wonderingf we should just disable the feature:

test=> CREATE SCHEMA aa;
CREATE SCHEMA
test=> CREATE TABLE aa.test (x SERIAL);
NOTICE: CREATE TABLE will create implicit sequence "test_x_seq" for serial column "test.x"
CREATE TABLE
test=> ALTER SCHEMA aa RENAME TO bb;
ALTER SCHEMA
test=> INSERT INTO bb.test VALUES (DEFAULT);
ERROR: SCHEMA "aa" does NOT exist
test=> \d bb.test
Table "bb.test"
Column | Type | Modifiers
--------+---------+-------------------------------------------------
x | integer | not null default nextval('aa.test_x_seq'::text)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thu, Sep 22, 2005 at 10:29:21PM -0400, Tom Lane wrote:

> To my mind the appropriate fix is to come up with some arrangement
> that allows the stored form of NEXTVAL(seq) to specify the referenced
> sequence directly (by OID) rather than as a text string. There are some
> speculations about this in the archives, but no really workable solution
> yet.
>
> (wanders off, wondering if a "regclass" literal value would help ...)

Wouldn't it be easy to create a nextval(oid) function, and have SERIAL
emit that instead?

--
Alvaro Herrera Architect, http://www.EnterpriseDB.com
"Endurecerse, pero jamás perder la ternura" (E. Guevara)

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> On Thu, Sep 22, 2005 at 10:29:21PM -0400, Tom Lane wrote:
>> (wanders off, wondering if a "regclass" literal value would help ...)

> Wouldn't it be easy to create a nextval(oid) function, and have SERIAL
> emit that instead?

Well, for one thing, "nextval(583726163)" isn't likely to dump and
restore nicely. Making the literal be type regclass rather than bare
OID will probably help that, but I'm not sure it's a 100% solution.
If I recall the previous discussions correctly there were still some
gotchas :-(

regards, tom lane

I wrote:
> If I recall the previous discussions correctly there were still some
> gotchas :-(

One point that comes to mind after more thought is that you'd really
like "create table ... default nextval('foo') ..." to understand that
there is a dependency from the column's default expression to sequence
foo. Currently we hack this to sort-of-work for SERIAL columns, but
I'd like to have a cleaner mechanism.

Is it sane for a literal like 'foo'::regclass to, in itself, create a
dependency? Not sure.

regards, tom lane

With the following errors caused by ALTER SCHEMA RENAME, I recommend we
remove this feature for 8.1 and revisit it for 8.2. I would just remove
the grammar construct for it and the documentation.

To fix this, we would need to redesign the way we store DEFAULT sequence
assignments, and I don't think that is a good thing to do during beta.
I see people wanting bitmapped scans ASAP, not renaming of schemas. Our
beta time is better spent on other things than getting this to work now.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > This item has been added to the 8.1 bugs list:
> > > http://momjian.postgresql.org/cgi-bin/pgbugs
> >
> > This isn't going to be fixed for 8.1. I think it's really a variant of
> > the TODO item
> > o %Have ALTER TABLE RENAME rename SERIAL sequence names
>
> Well, it might be a variant, but its failure is much worse. For a table
> rename, you just get a strange \d display:
>
> test=> CREATE TABLE test (x SERIAL);
> NOTICE: CREATE TABLE will create implicit sequence "test_x_seq" for serial column "test.x"
> CREATE TABLE
> test=> ALTER TABLE test RENAME TO test2;
> ALTER TABLE
> test=> INSERT INTO test2 VALUES (DEFAULT);
> INSERT 0 1
> test=> \d test2
> Table "public.test2"
> Column | Type | Modifiers
> --------+---------+-----------------------------------------------------
> x | integer | not null default nextval('public.test_x_seq'::text)
>
> The insert into the table still works. For the schema rename, the
> insert into the table doesn't work anymore. The odds that a schema
> rename is going to have _no_ sequence dependencies in the same schema
> seems pretty unlikely, meaning rename schema is almost guarantted to
> create some broken table defaults. With this behavior, if we can't fix
> it in 8.1, I am wonderingf we should just disable the feature:
>
> test=> CREATE SCHEMA aa;
> CREATE SCHEMA
> test=> CREATE TABLE aa.test (x SERIAL);
> NOTICE: CREATE TABLE will create implicit sequence "test_x_seq" for serial column "test.x"
> CREATE TABLE
> test=> ALTER SCHEMA aa RENAME TO bb;
> ALTER SCHEMA
> test=> INSERT INTO bb.test VALUES (DEFAULT);
> ERROR: SCHEMA "aa" does NOT exist
> test=> \d bb.test
> Table "bb.test"
> Column | Type | Modifiers
> --------+---------+-------------------------------------------------
> x | integer | not null default nextval('aa.test_x_seq'::text)
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> With the following errors caused by ALTER SCHEMA RENAME, I recommend we
> remove this feature for 8.1 and revisit it for 8.2.

It's a mistake to blame ALTER SCHEMA RENAME for this problem, as you can
cause it equally well by renaming the sequence itself, or by moving it
into another schema with ALTER TABLE SET SCHEMA. Will you also insist
on disabling the latter new feature?

I experimented a little bit with defining nextval() and friends as
taking "regclass" instead of text, and it seems like that works pretty
nicely for these problems, once you've got the literal in the form of
regclass (ie, internally an OID). For actual SERIAL columns that
doesn't seem like a big deal, because the default expression doesn't
appear literally in dumps (at least not dumps made with a recent version
of pg_dump). All we'd have to do is tweak the parser to generate a call
to nextval(regclass) instead of nextval(text) when expanding SERIAL.

For dumps that contain explicit calls, like
keycol int default nextval('foo'::text)
I really don't think there is anything much we can do :-( except to
recommend that people update the default expressions. You'd need to
change it to
keycol int default nextval('foo'::regclass)
to be safe against renamings of 'foo', and I don't see any very good
way to force that to happen automatically.

I think that a reasonable answer for 8.1 would be to add
nextval(regclass) (and I guess parallel versions of currval and setval,
too), leaving the existing text-based functions available, and modifying
the parser to use nextval(regclass) instead of nextval(text) in SERIAL
defaults.

In the long run it would be nice to deprecate and eventually remove
the text-based functions, but I don't see how to do that in the short
run without introducing an implicit text-to-regclass cast for
compatibility purposes. That seems a bit risky.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > With the following errors caused by ALTER SCHEMA RENAME, I recommend we
> > remove this feature for 8.1 and revisit it for 8.2.
>
> It's a mistake to blame ALTER SCHEMA RENAME for this problem, as you can
> cause it equally well by renaming the sequence itself, or by moving it
> into another schema with ALTER TABLE SET SCHEMA. Will you also insist
> on disabling the latter new feature?

Sure. (Silly question, silly answer.)

Seriously, you have to consider the likelihood of breakage, and the
surprise factor. If someone moves a sequence to another schema or
renames it, they would assume breakage, but moving all tables/sequences
together would not suggest sequence breakage.

So, below, you are saying that once 8.0.X dumps are loaded into 8.1,
that the renaming of those schemas would succeed, at least for SERIAL,
but not for manual sequence defaults. That seems OK, I guess, in the
hope that people who are creating defaults manually based on sequences
are going to know how to fix things.

---------------------------------------------------------------------------

> I experimented a little bit with defining nextval() and friends as
> taking "regclass" instead of text, and it seems like that works pretty
> nicely for these problems, once you've got the literal in the form of
> regclass (ie, internally an OID). For actual SERIAL columns that
> doesn't seem like a big deal, because the default expression doesn't
> appear literally in dumps (at least not dumps made with a recent version
> of pg_dump). All we'd have to do is tweak the parser to generate a call
> to nextval(regclass) instead of nextval(text) when expanding SERIAL.
>
> For dumps that contain explicit calls, like
> keycol int default nextval('foo'::text)
> I really don't think there is anything much we can do :-( except to
> recommend that people update the default expressions. You'd need to
> change it to
> keycol int default nextval('foo'::regclass)
> to be safe against renamings of 'foo', and I don't see any very good
> way to force that to happen automatically.
>
> I think that a reasonable answer for 8.1 would be to add
> nextval(regclass) (and I guess parallel versions of currval and setval,
> too), leaving the existing text-based functions available, and modifying
> the parser to use nextval(regclass) instead of nextval(text) in SERIAL
> defaults.
>
> In the long run it would be nice to deprecate and eventually remove
> the text-based functions, but I don't see how to do that in the short
> run without introducing an implicit text-to-regclass cast for
> compatibility purposes. That seems a bit risky.
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073