Skip site navigation (1) Skip section navigation (2)

Re: BUG #5009: Loss of information

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5009: Loss of information
Date: 2009-08-25 14:37:21
Message-ID: 4A93F721.4000602@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-bugs
On 25/08/2009 8:07 PM, Fernando Velloso Tanure wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5009
> Logged by:          Fernando Velloso Tanure
> Email address:      f_tanure(at)terra(dot)com(dot)br
> PostgreSQL version: 8.1.11
> Operating system:   CentOS5.3 Red Hat Enterprise Linux RHEL
> Description:        Loss of information
> Details:
>
> Good afternoon
>
> I'm having problem in postgres where some rows inserted or changed during
> the day was lost as a rollback was executed.
> I am using a system developed in Delphi with Zeos components for access to
> the database.
> The sequences used in the inserts are not lost.

That sounds like "database working exactly as designed" to me.

If you issue a sequence of statements like:

-- Given the table structure:
CREATE TABLE x ( id SERIAL PRIMARY KEY, n INTEGER NOT NULL);

-- Program issues:
BEGIN;
INSERT INTO x(n) VALUES (1);
INSERT INTO x(n) VALUES (3);
INSERT INTO x(n) VALUES (9);
INSERT INTO x(n) VALUES (42);


... then either you disconnect, the DB server is restarted, or your 
client issues a ROLLBACK, then you'll end up with an empty table `x' and 
the sequence x_id_seq will be 4.

Why? Because sequences are, by design, not transactional. For good 
reasons. See the manual for the SERIAL pseudo-type and for SEQUENCEs.

http://www.postgresql.org/docs/8.4/static/datatype-numeric.html#DATATYPE-SERIAL

http://www.postgresql.org/docs/8.4/static/functions-sequence.html

http://www.postgresql.org/docs/8.4/static/sql-createsequence.html

Note that with some database driver interfaces the BEGIN may be 
implicit, especially if it defaults to "non-autocommit" or "autocommit 
off" ... so just because you didn't open a transaction doesn't mean one 
wasn't open.

If your application really needs gapless sequences where a rollback also 
rolls back the sequence counter, there are options available - but they 
have HORRIBLE effects on performance, especially with concurrent 
inserts. They can be kind of OK if all you do in the transaction is:

BEGIN;
INSERT blah
COMMIT;

(or just use an implicit autocommit transaction) but if you do anything 
more complex in your transactions you risk deadlocks between concurrent 
transactions, awful performance, huge commit delays, and more.

Search the pgsql-general mailing list archives for "gapless sequence" 
for more information.

--
Craig Ringer

In response to

pgsql-bugs by date

Next:From: David FetterDate: 2009-08-25 14:40:00
Subject: Re: BUG #5009: Loss of information
Previous:From: LampaDate: 2009-08-25 12:15:58
Subject: BUG #5010: perl iconv function returns ? character

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group