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(at)netagi(dot)com> -----

Subject: Bug#325114: Postgres Rolling back for no reason
Reply-To: Michael Blake <mike(at)netagi(dot)com>, 325114(at)bugs(dot)debian(dot)org
Date: Fri, 26 Aug 2005 18:23:28 +1000
From: Michael Blake <mike(at)netagi(dot)com>
To: submit(at)bugs(dot)debian(dot)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