db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Shevek <she...@anarres.org>
Subject Re: Sequence bug in Postgres
Date Thu, 22 Apr 2004 00:25:39 GMT
On Thu, 22 Apr 2004, Scott Eade wrote:

> Shevek wrote:
> 
> >File DBPostgres.java
> >
> >    /**
> >     * @param name The name of the field (should be of type
> >     *      <code>String</code>).
> >     * @return SQL to retreive the next database key.
> >     * @see org.apache.torque.adapter.DB#getIDMethodSQL(Object)
> >     */
> >    public String getIDMethodSQL(Object name)
> >    {
> >        return ("select currval('" + name + "')");
> >    }
> >
> >This should clearly be nextval not currval. This unfortunately makes 
> >PostgreSQL unusable with Torque until fixed. Oracle, SAP, etc do not have 
> >this bug.
> >
> The id is retrieved after the insert so currval() is correct.  I think 
> you will find that there are quite a few people that are successfully 
> using Torque with PostgreSQL sequences.

It does work fine if you use the idbroker. I suspect that your existing 
users are using the id broker. Now that I look at the code in further 
depth, the Velocity templates for postgres look fine, they set up the 
sequences OK. However, DBPostgres should be using SEQUENCE not 
AUTO_INCREMENT since the Velocity templates output sequences, not 
AUTO_INCREMENT, and that this currval should be nextval.

If you do not use the id broker, then it fails with currval but works
with nextval. Remember, that setting defaultIdMethod to native still
leaves you using the idbroker (See other bug), so make a table with 

    486     <table
    487         name="wordset"
    488         javaName="Wordset"
    489         idMethod="native">
    490 
    491         <column
    492             name="id"
    493             javaName="Id"
    494             type="INTEGER"
    495             primaryKey="true"
    496             autoIncrement="true"
    497             required="true"
    498             />
    499 
    500         <!-- Torque can't currently cope with generating keys for
    501              tables with no non-autogenerated columns. -->
    502         <column
    503             name="dummy"
    504             javaName="Dummy"
    505             type="INTEGER"
    506             />
    507     </table>

and try to insert two instances. Compare the generated schema SQL
with what the runtime driver is trying to do. It simply doesn't
add up.  In order to use a sequence, you have to select nextval from
it. nextval is not selected from any sequence anywhere in the Torque
source for postgres.

I think that this is clearly a bug and that it is outstanding. If
you use the existing code, the insert fails.

> >Please Cc me in any important replies. (Or perhaps just to let me know
> >that someone has seen this?) Many more of these and I probably have to
> >join the list anyway.
> >
> If you want to make posts and see replies then please do subscribe to 
> the list.

I subscribed after the third or fourth bug I found. Ccs no longer 
necessary.

> >Oh, and postgres isn't capable of upgrading itself using the
> >project-schema.sql script since it doesn't drop things in the right order.  
> >No patch for this yet.
> >
> This has already been addressed in CVS by adding "cascade" to the drop 
> table statements for PostgreSQL.
> 
> BTW: If you were The Borg then you would have assimilated this 
> information already from the mailing list archive and the Torque wiki. :-)

I have been randomly assimilating things, but I find the source tends to
be so much simpler to read and frequently more factual than the
documentation. I am reading 3.1 source.

S.

-- 
Shevek                                    http://www.anarres.org/
I am the Borg.                         http://www.gothnicity.org/

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


Mime
View raw message