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
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 = 243;
select * FROM infobase_version;
control
---------
243
select * FROM infobase_version;
control
---------
243
select * FROM infobase_version;
control
---------
243
... etc for about 30 seconds ...
select * FROM infobase_version;
control
---------
161
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.
We've tried doing a vacuum, removing the table and re-adding it. Removing
the row and re-adding it and the same procedure continually happens. The row
is USUALLY written to using transactions and some large updates can be run
on other tables in the same transaction. If the transaction fails the
remaining updates are attempted anyway (which fail if something else before
it failed - as per norm.) and then COMMIT is called at the very end.
When 'exporting' the data using pg_dump (just plain SQL, no --format=t) the
table shows only ONE value in it so we know it's not a weird duplicate bug
of some sort.
Our *guess* is that there might be some form of bad cache/rollback happening
inside postgres
----- End forwarded message -----
--
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