Peter,
Native sequences in PostgreSQL with Torque are tricky but can be made to
work. Native sequence turns into a PostgreSQL "serial" column, which
implicitly creates a sequence. Only problem is, PostgreSQL autogenerates
the name for the sequence (usually "[table]_[column]_seq" but either the
column name or table name may be truncated), while Torque always looks for
the sequence named "[table]_seq".
The solution is to provide the sequence name explicitly to torque:
<table id-method="native" ...>
<column name="..." primaryKey="..." autoIncrement="..."/>
<id-method-parameter value="[table]_[column]_SEQ"/>
</table>
where "[table]_[column]_SEQ" is the sequence name that PostgreSQL will
create.
This is messy because it requires knowing what sequence name postgresql will
generate before creating the SQL to create the table. (Usually you can
guess, but in the case of long table and column names you don't know how it
will be truncated.)
A better approach might be to abandon "serial" and have the Torque templates
explicitly create the sequence of its choice, and create the column as
"colname int8 default nextval('[SEQ]')".
This would seem preferable because of another problem with PostgreSQL:
"serial" columns only get a 4 byte-wide sequence values; sequences need to
be configured explicitly to give you 8 bytes. Likewise you have to
explicitly define the column type as "int8" since "serial" gives you an
"int4" by default.
-- Bill
----- Original Message -----
From: "Peter Courcoux" <[EMAIL PROTECTED]>
To: "Turbine Torque Users List" <[EMAIL PROTECTED]>
Sent: Sunday, September 01, 2002 6:53 PM
Subject: Postgresql using native sequences for autoincrement
> Hi all,
>
> I have seen several posts to various lists about torque, postgresql
> sequences and autoincrementing primary keys. However, it is not clear
> whether anyone has successfully managed to configure torque to use
> postgresql's native sequences. So my question : has anyone successfully
> managed this with torque b4 and if so a short howto would be gratefully
> received.
>
> Regards,
>
> Peter
>
>
>
>
>
> --
> To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
