This behavior bit me today and caused me some pain so hopefully by sharing it it can help someone else not ending up in the same pitfall.

Basically, I use Zope to manage a PostgreSQL database and since Zope is 100% transactional it rolls back queries when exception occur. That's great but what I didn't know is that when it rolls back it doesn't roll back the sequences. Makes sense in retrospect I guess. Here's a proof of that:


test_db=# create table "foo" (id serial primary key, name varchar(10));
CREATE TABLE
test_db=# insert into foo(name) values('Peter');
INSERT 0 1
test_db=# select * from foo;
 id | name  
----+-------
  1 | Peter
(1 row)

test_db=#  select nextval('foo_id_seq');
 nextval 
---------
       2
(1 row)

test_db=# begin;
BEGIN
test_db=# insert into foo(id, name) values(2, 'Sonic');
INSERT 0 1
test_db=# rollback;
ROLLBACK
test_db=#  select nextval('foo_id_seq');
 nextval 
---------
       3
(1 row)

In my application I often use the sequences to predict what the auto generate new ID is going to be for things that the application can use such as redirecting or updating some other tables. As I wasn't expecting this it caused a bug in my web app.

Comments

Your email will never ever be published.

Previous:
Most unusual letters in English language May 12, 2009 Python
Next:
Crossing the world - new feature on Crosstips May 23, 2009 Django
Related by category:
set -ex - The most useful bash trick of the year August 31, 2014 Linux
brotli_static in Nginx November 8, 2024 Linux
Be very careful with your add_header in Nginx! You might make your site insecure February 11, 2018 Linux
Linux tip: du --max-depth=1 September 27, 2007 Linux
Related by keyword:
Note to self about Jeditable November 22, 2007 JavaScript
The stupidity of 'id' as a variable name (or stupidity of me) September 16, 2008 Python