You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@turbine.apache.org by Steve Stock <st...@technolope.org> on 2002/03/02 08:29:25 UTC
Decoupled torque 3.0-dev and postgresql native sequences broken?
Is there a known problem using PostgreSQL native sequences with the
decoupled torque 3.0 dev (CVS version from 2002-02-24) and postgres 7.2?
To make a long story short I had to make three one line changes to the
postgres database adapter to get sequences working. What confuses me is
that the original code seems to be blatantly wrong, like it has never
been tested. The id method is set to auto increment (not sequence)
and the use of the nextval and currval sequence functions is reversed.
Thus back to my question, is this supposed to work somehow or is it
known to be broken?
Side note, I know that idBroker works, but I have an existing database
and torque won't be the only thing inserting new rows.
Other than boolean not working (next post) torque is working very well.
My bytea and text columns worked right from the start, and I like the
foreign key helper methods that torque adds. Thanks to all the torque
developers for a nice OR mapping layer.
Steve Stock
steve@technolope.org
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Decoupled torque 3.0-dev and postgresql native sequences broken?
Posted by Steve Stock <st...@technolope.org>.
On Sun, Mar 03, 2002 at 05:04:12PM -0500, Bill Schneider wrote:
> I also had problems with PostgreSQL native sequences and Torque 3.0 dev, but
> my workaround took a slightly different approach: everything in the DB
> adapter actually works fine, except that it tries to use the wrong sequence
> name. So instead of modifying the DB adapters I modified the SQL-generation
> templates, to change the sequence name from the one PG creates for you, to a
> new one with the name the DB adapter expects. (I think I submitted a patch
> for this, which is related to another Torque/PostgreSQL problem: the
> autogenerated DROP TABLE statements don't drop the auto-generated sequences
> that PostgreSQL creates, which much be dropped *separately* from the table)
I create and drop sequences independently of the table, partly an oracle
carry-over, but I also like to be explicit when possible. To make it
work I set the sequence name for the table using the id-method-parameter
tag in the table schema, example:
<table name="preview" idMethod="native">
...
<id-method-parameter value="preview_id_seq"/>
</table>
Keep in mind that I've never used the Torque table creation stuff,
my database already existed.
> PostgreSQL "serial" columns are really a hybrid of the sequence and
> auto-increment approach: they're like autoincrement columns that use
> sequences under the hood.
[some sql snipped]
> You don't have to specify foo_id on INSERT, because it defaults to "nextval
> ...". This is really the key distinction between Torque's "sequence" and
> "autoincrement" type: does Torque need to specify the ID when I insert, or
> is that done by the db? The fact that serial columns happen to be
> implemented with sequences is irrelevant as far as Torque is concerned
> (aside from the get-current-value SQL snippet).
I didn't quite understanding how torque viewed a sequence vs an auto
increment primary key, you've clarified that point for me. While I don't
use the serial type, I do use the default nextval in the table definition.
Makes me wonder why I couldn't get it working the first time, I'll have
to try it again and see why it wasn't behaving correctly.
> In PostgreSQL you get the ID of the last row inserted with
> currval('sequence_name'). That's probably why you thought currval/nextval
> were backwards, as was my initial thought as well--the nextval is taken care
> of for you by the column default. And everything works fine as long as the
> sequence name Torque looks for is actually there.
According to the docs in DBPostgres something is backwards. The function
getIDMethodSQL is documented as returning "SQL to retreive the next
database key", but it calls currval. The getSequenceSql method uses
nextval but is documented as "Returns the last auto-increment key."
Because I don't know under which case these methods are used and it
wasn't working for me I assumed the documentation was correct.
Steve Stock
steve@technolope.org
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Decoupled torque 3.0-dev and postgresql native sequences broken?
Posted by Bill Schneider <bs...@vecna.com>.
I also had problems with PostgreSQL native sequences and Torque 3.0 dev, but
my workaround took a slightly different approach: everything in the DB
adapter actually works fine, except that it tries to use the wrong sequence
name. So instead of modifying the DB adapters I modified the SQL-generation
templates, to change the sequence name from the one PG creates for you, to a
new one with the name the DB adapter expects. (I think I submitted a patch
for this, which is related to another Torque/PostgreSQL problem: the
autogenerated DROP TABLE statements don't drop the auto-generated sequences
that PostgreSQL creates, which much be dropped *separately* from the table)
PostgreSQL "serial" columns are really a hybrid of the sequence and
auto-increment approach: they're like autoincrement columns that use
sequences under the hood.
CREATE TABLE foo (foo_id SERIAL) is roughly equivalent to
CREATE SEQUENCE foo_foo_id_seq;
CREATE TABLE foo (foo_id INTEGER PRIMARY KEY default
nextval('foo_foo_id_seq'), UNIQUE(foo_id));
CREATE INDEX foo_foo_id_idx ON foo (foo_id);
You don't have to specify foo_id on INSERT, because it defaults to "nextval
...". This is really the key distinction between Torque's "sequence" and
"autoincrement" type: does Torque need to specify the ID when I insert, or
is that done by the db? The fact that serial columns happen to be
implemented with sequences is irrelevant as far as Torque is concerned
(aside from the get-current-value SQL snippet).
In PostgreSQL you get the ID of the last row inserted with
currval('sequence_name'). That's probably why you thought currval/nextval
were backwards, as was my initial thought as well--the nextval is taken care
of for you by the column default. And everything works fine as long as the
sequence name Torque looks for is actually there. So what i did was
generate SQL like
CREATE TABLE foo (foo_id SERIAL) ;
CREATE SEQUENCE foo_seq;
ALTER TABLE foo COLUMN foo_id SET DEFAULT nextval('foo_seq');
to change the sequence name used for the foo_id column.
-- Bill
> Is there a known problem using PostgreSQL native sequences with the
> decoupled torque 3.0 dev (CVS version from 2002-02-24) and postgres 7.2?
>
> To make a long story short I had to make three one line changes to the
> postgres database adapter to get sequences working. What confuses me is
> that the original code seems to be blatantly wrong, like it has never
> been tested. The id method is set to auto increment (not sequence)
> and the use of the nextval and currval sequence functions is reversed.
> Thus back to my question, is this supposed to work somehow or is it
> known to be broken?
>
> Side note, I know that idBroker works, but I have an existing database
> and torque won't be the only thing inserting new rows.
>
> Other than boolean not working (next post) torque is working very well.
> My bytea and text columns worked right from the start, and I like the
> foreign key helper methods that torque adds. Thanks to all the torque
> developers for a nice OR mapping layer.
>
> Steve Stock
> steve@technolope.org
>
> --
> To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: transactionIsolation level in DBConnection?
Posted by Bob Swerdlow <rs...@transpose.com>.
Daniel -Thanks. So, I should not call close() on the Connection - just
releaseConnection() on the DBConnection that created it? I'll give that a
try.
Thanks,
Bob
----- Original Message -----
From: "Daniel Rall" <dl...@finemaltcoding.com>
To: "Turbine Users List" <tu...@jakarta.apache.org>
Sent: Saturday, March 02, 2002 4:55 PM
Subject: Re: transactionIsolation level in DBConnection?
> "Bob Swerdlow" <rs...@transpose.com> writes:
>
> > DBConnection does support getConnect(), which returns a JDBC connection,
but
> > then an I using the pooled connection? If so, I guess I can do all my
work
> > through the Connection.
>
> Yes, that'll do it.
>
> > Do I then close() the Connection when I'm done or just call
> > releaseConnection(DBConnection db) for the DBConnection that gave me
> > the Connection (that seems risky because it must invalidate the
> > Connection as a side effect of closing the DBConnection).
>
> Call releaseConnection() to return the DBConnection to the pool.
>
> --
> To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: transactionIsolation level in DBConnection?
Posted by Daniel Rall <dl...@finemaltcoding.com>.
"Bob Swerdlow" <rs...@transpose.com> writes:
> DBConnection does support getConnect(), which returns a JDBC connection, but
> then an I using the pooled connection? If so, I guess I can do all my work
> through the Connection.
Yes, that'll do it.
> Do I then close() the Connection when I'm done or just call
> releaseConnection(DBConnection db) for the DBConnection that gave me
> the Connection (that seems risky because it must invalidate the
> Connection as a side effect of closing the DBConnection).
Call releaseConnection() to return the DBConnection to the pool.
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
transactionIsolation level in DBConnection?
Posted by Bob Swerdlow <rs...@transpose.com>.
Hi, all -
How do you set the transactionIsolation level when using pooled
DBConnections through Turbine? I see setTransactionIsolation(int level) in
java.sql.Connection, but DBConnection does not implement that interface.
DBConnection does support getConnect(), which returns a JDBC connection, but
then an I using the pooled connection? If so, I guess I can do all my work
through the Connection. Do I then close() the Connection when I'm done or
just call releaseConnection(DBConnection db) for the DBConnection that gave
me the Connection (that seems risky because it must invalidate the
Connection as a side effect of closing the DBConnection).
Thanks for your help!
Bob Swerdlow
Chief Operating Officer
Transpose, LLC
rswerdlow@transpose.com
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>