Re: Sequence

Yes, the other session gets the next sequence value, try opening two
psql's and see what happens

It seems that currval() remembers the last value of all sequence's
altered in this session; probably for the express purpose of relating
tables.

Dave
On Sun, 2003-01-12 at 01:06, Simon Mitchell wrote:
> 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
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Dave Cramer <Dave@micro-automation.net>