db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Henning P. Schmiedehausen" <...@intermeta.de>
Subject PostgreSQL ID generation
Date Tue, 24 Aug 2004 15:12:03 GMT

currently, PostgreSQL uses a kind of "autoincrement in the database"
for "native" id generation.

It uses a sequence, created e.g. with

create sequence foo_bar_seq; 

and then teaches the tables to do autoincrementing a la MySQL:

create table foo (
	bar integer default nextval('foo_bar_seq')
	primary key (bar)

While this works well, it is not compatible to e.g. Hibernate which
wants to do its own primary key management with a sequence.

It also makes it impossible to build "better" (e.g. a hi/lo) sequence
generator because the getIDMethodSQL method from the adapter returns
"select currval('<sequence name>')".

I'd propose to change the PostgreSQL adapter to use the native
sequence model. It does the following things:

- Changes the native type of ID generation for PostgreSQL from 

- Makes the DBPostgres adapter return "select nextval('<sequence name>')
  from getIDMethodSQL

- Removes the DEFAULT nextval(..) statement from the ID columns in table

- Allows AUTO_INCREMENT columns in all tables, regardless whether the 
  ID mode is native or not. A "serial" column is really an auto incrementing
  column, no matter how the id elements are generated.

  Please note that the current sql generation makes no sense. If we create
  or SQL with the DEFAULT nextval('sequence'), why restrict serials to tables
  that use native Ids? They don't use this anyway. 

This is the natural model of ID generation with PostgreSQL and is
compatible to e.g. Hibernate (you can use a Hibernate Application and
a Torque Application without any changes in the database schema.

I'd really like to see this patch go in.

On a related note: I don't understand why the sequence creation has
been dropped from the schema generation? They could be generated in a
second file, but as it currently is, the regular sequence of "write a
schema, torque:om, torque:sql, torque:create-db, torque:insert-sql,
run your application" no longer works because the sequences are

I'd very much love to see this revision:


Index: src/java/org/apache/torque/adapter/DBPostgres.java
--- src/java/org/apache/torque/adapter/DBPostgres.java	(revision 2575)
+++ src/java/org/apache/torque/adapter/DBPostgres.java	(working copy)
@@ -65,7 +65,7 @@
     public String getIDMethodType()
-        return AUTO_INCREMENT;
+        return SEQUENCE;
@@ -76,7 +76,7 @@
     public String getIDMethodSQL(Object name)
-        return ("select currval('" + name + "')");
+        return ("select nextval('" + name + "')");
Index: src/generator/src/templates/sql/base/postgresql/columns.vm
--- src/generator/src/templates/sql/base/postgresql/columns.vm	(revision 2575)
+++ src/generator/src/templates/sql/base/postgresql/columns.vm	(working copy)
@@ -9,14 +9,10 @@
         #set ( $size = "" )
-    #if (($table.IdMethod == "native") && ($col.isPrimaryKey()))
-      #set ( $default = "DEFAULT nextval('$table.SequenceName')" )
-    #else
-      #set ( $default = $col.DefaultSetting )
-    #end
+    #set ( $default = $col.DefaultSetting )
     #set ( $nullString = $strings.select($col.isNotNull(), $dbprops.get("NOTNULL"),"") )
     #set ( $entry = "$col.Name $type $size $default $nullString" )
-    #if ($col.isAutoIncrement() && $table.IdMethod == "native")
+    #if ($col.isAutoIncrement())
       #set ( $autoIncrement = $dbprops.get("AUTOINCREMENT") )
       #set ( $autoIncrement = " " )

Dipl.-Inf. (Univ.) Henning P. Schmiedehausen          INTERMETA GmbH
hps@intermeta.de        +49 9131 50 654 0   http://www.intermeta.de/

RedHat Certified Engineer -- Jakarta Turbine Development  -- hero for hire
   Linux, Java, perl, Solaris -- Consulting, Training, Development

"Fighting for one's political stand is an honorable action, but re-
 fusing to acknowledge that there might be weaknesses in one's
 position - in order to identify them so that they can be remedied -
 is a large enough problem with the Open Source movement that it
 deserves to be on this list of the top five problems."
                       -- Michelle Levesque, "Fundamental Issues with
                                    Open Source Software Development"

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

View raw message