Sequences not affected by transactions?

Question

I have a table

create table testtable(
  testtable_rid serial not null,
  data integer not null,
  constraint pk_testtable primary key(testtable_rid)
);

So lets say I do this code about 20 times:

begin;
insert into testtable (data) values (0);
rollback;

and then I do

begin;
insert into testtable (data) values (0);
commit;

And finally a

select * from testtable

Result:
row0: testtable_rid=21 | data=0
Expected result:
row0: testtable_rid=1 | data=0

As you can see, sequences do not appear to be affected by transaction rollbacks. They continue to increment as if the transaction was committed and then the row was deleted. Is there some way to prevent sequences from behaving in this way?

Demetricedemetris

Answer 1

It would not be a good idea to rollback sequences. Imagine two transactions happening at the same time, each of which uses the sequence for a unique id. If the second transaction commits and the first transaction rolls back, then the second inserted a row with "2" while the first rolls the sequence back to "1".

If that sequence is then used again, the value of the sequence will become "2" which could lead to a unique constraint problem.

Milla

If the second transaction commits, this transaction should get sequence number 1. Since the first transaction rolled back, no sequence number would have been taken for the first transaction. Sure, PostgreSQL did not implement that, neither Oracle, but imho there are no conceptual constraints against transactional sequencing, only constraints in implementation. – Rusk

@HartmutP. You're incorrect. The constraint is that a sequence is used to generate unique number without scanning any data structure to determine if that number is in use. The only way to ensure uniqueness without a scan is to discard values on their first fetch, even if the transaction obtaining them is not committed. – Negron

It depends on the transaction isolation level. Serializable Isolation will prevent the problem, you have explained. – Spears

Answer 2

No, there isn't. See the note at the bottom of this page. It's a bad idea to do something like that anyway. If you have two transactions running at the same time, each inserting one row, you want them to insert rows with different IDs.

Juliusjullundur