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

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