Re: BUG #5009: Loss of information

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@terra.com.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