You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by Shevek <sh...@anarres.org> on 2004/04/21 23:57:47 UTC
Sequence bug in Postgres
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.
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.
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.
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
Re: Sequence bug in Postgres
Posted by Scott Eade <se...@backstagetech.com.au>.
Shevek wrote:
> 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.
No, we are using "native".
> 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 read this entire thread before posting on this topic again:
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=torque-dev@db.apache.org&msgNo=3537
Particularly this final message:
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=torque-dev@db.apache.org&msgNo=3584
which includes:
> Bottom line: you were right, Scott!
I have just added an entry to the wiki to reference this thread:
http://wiki.apache.org/db-torque/PostgreSQLFAQ#head-e2be9dec72b7c31f16be1b4e70364e8190684691
> 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.
This is often true of Open Source projects, though not a valid excuse.
You contributions in updating the documentation would be most welcome.
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Sequence bug in Postgres
Posted by Shevek <sh...@anarres.org>.
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
Re: Sequence bug in Postgres
Posted by Scott Eade <se...@backstagetech.com.au>.
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.
>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.
>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. :-)
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org