Re: Sequence

Hi,
        I am look at usings sequences for transaction.

         Testing from psql -

          If I START TRANSACTION, INSERT and ROLLBACK the sequence is
not rolled back.
          This make sense, so my id column will have gaps if there is a
ROLLBACK or a transaction failure.
          If another thread/session does an insert it will get the next
sequence.


psql=> commit;
COMMIT
psql=> start transaction;

START TRANSACTION
psql=> select currval('test_id_seq');
 currval
---------
    5063
(1 row)

psql=> insert into test (query) values('xyz');
INSERT 89646 1
psql=> select currval('test_id_seq');
 currval
---------
    5064
(1 row)

psql=> rollback;
ROLLBACK
psql=> select currval('test_id_seq');
 currval
---------
    5064
(1 row)

psql=> commit;
WARNING:  COMMIT: no transaction in progress
COMMIT
psql=> select max(id) from test;
 max
------
 5063
(1 row)

psql=> insert into test (query) values('xyz');
INSERT 89647 1
psql=> select max(id) from test;
 max
------
 5065
(1 row)

Regards,
Simon




Ross J. Reedstrom wrote:

>On Sat, Jan 11, 2003 at 08:41:31PM -0500, Dave Cramer wrote:
>
>
>>Alan, Simon,
>>
>>You can't do this, at least not safely.
>>
>>Sequences can't be rolled back and are visible across transactions. In
>>other words if thread 1 inserted a row, and before you read the sequence
>>thread b inserted a row, you would get the same value for both threads.
>>The only way I know is to get the sequence before hand and insert it.
>>The overhead is the same.
>>
>>
>
>Dave -
>You really should test these things before stating with such assurance
>what will happen. Yes, sequences are outside transactions, but they
>_do_ honor connections. So, if  your two hypothetical threads are
>using seperate connections (which they _must_ do, BTW), each can use
>the currval(seqname) to retrieve the value used in that connection,
>regardless of what happens in the other.
>
>Ross
>
>
>