[Dbix-class] how to implement Transactions (over different db tables) in a Catalyst project

[Rob Kinyon]

On Tue, Jul 7, 2009 at 06:34, Tomas Doran<bobtfish at bobtfish.net> wrote:
> kakimoto at tpg.com.au wrote:
>>
>> 2) I noticed that whilst the rollback was successful. the sequence do
>> not get rolled back. IS this desired behaviour of DBIx::Class?
>
> This is a postgres thing, not a DBIC thing.
>
> And no, sequences don't get rolled back.
>
> Consider, imagining sequence starts at 1:
> A: {start trans, INSERT ROW (seq 2)}
> B: {start trans, INSERT ROW (seq 3)}
> Seq is now 3
> B: {commit}
> A: {rollback}?
>
> If you roll the sequence back 1 as A rolled back, the sequence is 2, so when
> you next insert you have a duplicate seq (as B is committed).
>
> Couldn't work, without keeping track of which previous sequence values had
> been rolled back. It'd also not be a sequence if you could ask for two
> values and the second was lower than the first :)

Furthermore, a sequence is SOLELY to generate AN UNUSED NUMBER so that
you can have a guaranteed value that will work for a primary key. You
should not depend on that value for anything other than uniqueness.
You cannot sort by it to get the rows in insertion order. You cannot
guarantee that there are no gaps. Nothing. If you need anything
predictable, add it yourself.

And this is true for all RDBMS sequences, whether in Postgres, Oracle,
MySQL, Sybase, or MSSQL. It would be best if everyone just treated a
sequence as a UUID generator - a random number that is guaranteed to
be unique and has NO OTHER PROPERTIES.

Rob