Sequences not affected by transactions?

postgresql transactions rollback database-sequence
Earlz picture Earlz · Jan 19, 2010 · Viewed 14.4k times · Source

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?

Answer

Aaron picture Aaron · Jan 19, 2010

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.

Related questions

Are PostgreSQL functions transactional?

Is a PostgreSQL function such as the following automatically transactional? CREATE OR REPLACE FUNCTION refresh_materialized_view(name) RETURNS integer AS $BODY$ DECLARE _table_name ALIAS FOR $1; _entry materialized_views%ROWTYPE; _result INT; BEGIN EXECUTE 'TRUNCATE TABLE ' || _table_name; …

Read More
now() default values are all showing same timestamp

I have created my tables with a column (type: timestamp with timezone) and set its default value to now() (current_timestamp()). I run a series of inserts in separate statements in a single function and I noticed all the timestamps …

Read More
How do I do large non-blocking updates in PostgreSQL?

I want to do a large update on a table in PostgreSQL, but I don't need the transactional integrity to be maintained across the entire operation, because I know that the column I'm changing is not going to be written …

Read More