Thread: Fwd: Bug#325114: Postgres Rolling back for no reason

Hi PostgreSQL developers!

In Debian we recently received the bug report below, but since I
don't know the guts of PostgreSQL so well, could someone please take a
look at it?

Thanks in advance!

Martin

----- Forwarded message from Michael Blake <mike@netagi.com> -----

Subject: Bug#325114: Postgres Rolling back for no reason
Reply-To: Michael Blake <mike@netagi.com>, 325114@bugs.debian.org
Date: Fri, 26 Aug 2005 18:23:28 +1000
From: Michael Blake <mike@netagi.com>
To: submit@bugs.debian.org

Package: postgresql
Version: 7.4.8


Summary: We have encountered a problem where a single row continually rolls=
=20
back to a much older version of itself.

- row in question is:
CREATE TABLE infobase_version (control character varying NOT NULL);
COPY infobase_version (control) FROM stdin;
243
\.

update infobase_version set control =3D 243;
select * FROM infobase_version;
control=20
---------
    243
select * FROM infobase_version;
control=20
---------
    243
select * FROM infobase_version;
control=20
---------
    243
... etc for about 30 seconds ...
select * FROM infobase_version;
control=20
---------
    161

No one else is using the database. 161 was a value that was put into the ro=
w=20
a LONG time ago. As far as we can figure it seems like postgres is=20
continually rolling back a transaction which isn't taking place.

We've tried doing a vacuum, removing the table and re-adding it. Removing=
=20
the row and re-adding it and the same procedure continually happens. The ro=
w=20
is USUALLY written to using transactions and some large updates can be run=
=20
on other tables in the same transaction. If the transaction fails the=20
remaining updates are attempted anyway (which fail if something else before=
=20
it failed - as per norm.) and then COMMIT is called at the very end.=20
When 'exporting' the data using pg_dump (just plain SQL, no --format=3Dt) t=
he=20
table shows only ONE value in it so we know it's not a weird duplicate bug=
=20
of some sort.

Our *guess* is that there might be some form of bad cache/rollback happenin=
g=20
inside postgres


----- End forwarded message -----

--=20
Martin Pitt              http://www.piware.de
Ubuntu Developer   http://www.ubuntulinux.org
Debian Developer        http://www.debian.org

Martin Pitt <martin@piware.de> writes:
> No one else is using the database. 161 was a value that was put into the row
> a LONG time ago. As far as we can figure it seems like postgres is
> continually rolling back a transaction which isn't taking place.

How long ago is "a LONG time"?  More than 2 billion transactions,
perhaps?  I'm wondering if you've got transaction-wraparound problems.
It would be interesting to look at the system columns for the row
(oid, ctid, xmin etc) and note whether they seem to change at the same
time as the data.

Is it *really* true that nothing else is using the database?  I've never
seen or heard of a Postgres bug in which data appears to change when
"nothing is happening".  Certainly no one else has had problems similar
to your report.

It might be worth running some memory diagnostics --- perhaps what's
really happening here is that RAM drops a bit after a period of time.

            regards, tom lane