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
> [EMAIL PROTECTED]
>
> --
> 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]>
