postgresql serial type

[Aaron Brashears <gi...@gila.org>]

In the process of configuring jetspeed, I ran into some problems with
the sql used to create the tables for turbine. I'm not familier with
Torque, but the generated sql is not totally compatible with
postgresql 7.0.x. 

Here's a snippit of the most recent postgresql-turbine.sql file:

drop sequence TURBINE_PERMISSION_SEQ;
drop table TURBINE_PERMISSION;
CREATE TABLE TURBINE_PERMISSION
(
    PERMISSION_ID serial,
    PERMISSION_NAME varchar (99) NOT NULL,
    OBJECTDATA oid,
    PRIMARY KEY(PERMISSION_ID),
    UNIQUE (PERMISSION_NAME)
);

I don't know the behavior under postgresql 6.5.x, but in 7, this
creates the following relations:

                 List of relations
              Name               |   Type   | Owner 
---------------------------------+----------+-------
 turbine_permi_permission_id_seq | sequence | aaron
 turbine_permission              | table    | aaron
(2 rows)

This doesn't match the 'drop sequence' command above because it will
attempt to drop the turbine_permission_seq which does not exist.  The
upshot of this is that running the sql script into the same database
destroys the tables since the creation will fail because the sequence
generated by the 'serial' type already exists.

I would suggest configuring Torque (if possible) to either generate
the correct sequence name to drop, or to create/drop the sequence
manually rather than through implicit creation. Since the former may
be because of incompatabilities with pg 6.5, I would suggest the
former. The sql above would be turned into:

drop table turbine_permission;
drop sequence turbine_permission_id_seq;

create sequence turbine_permission_id_seq;
create table turbine_permission
(
    permission_id INT4 PRIMARY KEY DEFAULT nextval('turbine_permission_id_seq'),
    permission_name varchar (99) NOT NULL UNIQUE,
    objectdata oid,
);

---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org