Skip site navigation (1) Skip section navigation (2)

Re: BUG #3403: ver 8.2 can't add serial column to temp table,but 8.1 can

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)COM>
Cc: Jasen Betts <jasen(at)treshna(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3403: ver 8.2 can't add serial column to temp table,but 8.1 can
Date: 2007-06-22 10:18:45
Message-ID: 467BA205.6090709@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Zdenek Kotala wrote:
> Jasen Betts wrote:
>> template1=# create temp table foo ( x text);
>> CREATE TABLE
>> template1=# alter table foo add column y text ;
>> ALTER TABLE
>> template1=# alter table foo add column id serial;
>> NOTICE:  ALTER TABLE will create implicit sequence "foo_id_seq" for 
>> serial
>> colum
>> n "foo.id"
>> ERROR:  relation "public.foo" does not exist
>> template1=#
> 
> It does not work on 8.2.4 as well. It seems PG lost information about 
> schema and try to use default schema. Following command works well:
> 
> alter table pg_temp.foo add column id serial;
> 
> It could be use as workaround.

8.1 creates the sequence in wrong schema:

postgres=# create temp table foo ( x text);
CREATE TABLE
postgres=# alter table foo add column id serial;
NOTICE:  ALTER TABLE will create implicit sequence "foo_id_seq" for 
serial column "foo.id"
ALTER TABLE
postgres=# \d
               List of relations
   Schema   |    Name    |   Type   |  Owner
-----------+------------+----------+----------
  pg_temp_1 | foo        | table    | hlinnaka
  public    | foo_id_seq | sequence | hlinnaka
(2 rows)

The problem seems to be in transformColumnDefinition, where the schema 
of the to-be-created sequence is determined from the relation name 
given. The default creation schema is used, if the user didn't specify 
the schame of the table explicitly, but since it's an ALTER TABLE, it 
really should use the schema of the existing table.

Patch against 8.2 attached, seems to apply to 8.1 and CVS head though I 
haven't tested them.. This is not my area of expertise, so I'm not 100% 
sure this is the right way to fix it.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Attachment: fix-alter-table-add-serial-schema.patch
Description: text/x-diff (3.0 KB)

In response to

Responses

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2007-06-22 11:55:14
Subject: Temp table woes
Previous:From: Katsuhiko OkanoDate: 2007-06-22 09:06:48
Subject: Re: BUG #3401: PITR does not work in the case ofrecovery_target_xid = 'SELECT_only_transaction_ID'

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group