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