Thread: BUG #5009: Loss of information

BUG #5009: Loss of information

From
"Fernando Velloso Tanure"
Date:
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.

Re: BUG #5009: Loss of information

From
Craig Ringer
Date:
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

Re: BUG #5009: Loss of information

From
David Fetter
Date:
On Tue, Aug 25, 2009 at 12:07:44PM +0000, 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.

That is rollback's defined behavior in every database management
system, not just PostgreSQL.

> 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.

This, too, is defined behavior.  Sequences provide uniqueness, and
only that.  If you wish to create some entity which has some other
properties, be aware that such entities require locking, which sharply
limits their speed.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate