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
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.353
diff -c -r1.353 analyze.c
*** src/backend/parser/analyze.c 11 Oct 2006 16:42:59 -0000 1.353
--- src/backend/parser/analyze.c 22 Jun 2007 10:16:20 -0000
***************
*** 69,74 ****
--- 69,75 ----
{
const char *stmtType; /* "CREATE TABLE" or "ALTER TABLE" */
RangeVar *relation; /* relation to create */
+ Oid namespace; /* namespace of relation to alter */
List *inhRelations; /* relations to inherit from */
bool hasoids; /* does relation have an OID column? */
bool isalter; /* true if altering existing table */
***************
*** 945,950 ****
--- 946,952 ----
cxt.stmtType = "CREATE TABLE";
cxt.relation = stmt->relation;
+ cxt.namespace = InvalidOid;
cxt.inhRelations = stmt->inhRelations;
cxt.isalter = false;
cxt.columns = NIL;
***************
*** 1087,1093 ****
* quite unlikely to be a problem, especially since few people would
* need two serial columns in one table.
*/
! snamespaceid = RangeVarGetCreationNamespace(cxt->relation);
snamespace = get_namespace_name(snamespaceid);
sname = ChooseRelationName(cxt->relation->relname,
column->colname,
--- 1089,1098 ----
* quite unlikely to be a problem, especially since few people would
* need two serial columns in one table.
*/
! if (OidIsValid(cxt->namespace))
! snamespaceid = cxt->namespace;
! else
! snamespaceid = RangeVarGetCreationNamespace(cxt->relation);
snamespace = get_namespace_name(snamespaceid);
sname = ChooseRelationName(cxt->relation->relname,
column->colname,
***************
*** 3010,3015 ****
--- 3015,3021 ----
List *newcmds = NIL;
bool skipValidation = true;
AlterTableCmd *newcmd;
+ Relation relation;
cxt.stmtType = "ALTER TABLE";
cxt.relation = stmt->relation;
***************
*** 3024,3029 ****
--- 3030,3045 ----
cxt.alist = NIL;
cxt.pkey = NULL;
+ relation = heap_openrv(stmt->relation, AccessShareLock);
+ if (relation->rd_rel->relkind != RELKIND_RELATION)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("relation \"%s\" is not a table",
+ stmt->relation->relname)));
+
+ cxt.namespace = RelationGetNamespace(relation);
+
+
/*
* The only subtypes that currently require parse transformation handling
* are ADD COLUMN and ADD CONSTRAINT. These largely re-use code from
***************
*** 3166,3171 ****
--- 3182,3194 ----
*extras_before = list_concat(*extras_before, cxt.blist);
*extras_after = list_concat(cxt.alist, *extras_after);
+ /*
+ * Close the parent rel, but keep our AccessShareLock on it until xact
+ * commit. That will prevent someone else from deleting or ALTERing the
+ * table before we get to execute the changes.
+ */
+ heap_close(relation, NoLock);
+
return qry;
}
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
