Subject: HELP: mystery error message

From: David Feldcamp <[email-masked]>
Date: Tue, 2 Jun 92 17:30:58 -0700

I am persistently getting the following message on a replace command (which immediately
follows another on a different table, but otherwise identical which always works):

WARN:Jun  2 17:28:53:heap_replace: (am)invalid otid

What does this mean??  Does it have something to do with transaction ids?  What can I
do about it?

Here is the monitor output from a monitor execution of the command (with the class states):

Welcome to the C POSTGRES terminal monitor

Go 
* retrieve (graph.all)\g

Query sent to backend is "retrieve (graph.all)"
---------------------------------------------------------
| mid         | node        | port        | edge        |
---------------------------------------------------------
| 0           | Node 1      | (null)      | Edge1       |
---------------------------------------------------------
| 0           | Node 1      | (null)      | Edge2       |
---------------------------------------------------------
| 2           | Node 2      | (null)      | Edge1       |
---------------------------------------------------------
| 2           | Node 2      | (null)      | Edge3       |
---------------------------------------------------------
| 2           | Node 3      | (null)      | Edge2       |
---------------------------------------------------------
| 2           | Node 3      | (null)      | Edge3       |
---------------------------------------------------------

Go 
* retrieve (edge.all)\g

Query sent to backend is "retrieve (edge.all)"
-----------------------------------------------------------------------
| mid         | name        | weight      | width       | color       |
-----------------------------------------------------------------------
| 0           | Edge1       | 1           | 0           | 6           |
-----------------------------------------------------------------------
| 0           | Edge2       | 1           | 0           | 6           |
-----------------------------------------------------------------------
| 0           | Edge3       | 1           | 0           | 6           |
-----------------------------------------------------------------------

Go 
* replace edge (mid = graph.mid) where (graph.mid = 2) and (graph.edge = edge.name) and (edge.mid = 0)\g

Query sent to backend is "replace edge (mid = graph.mid) where (graph.mid = 2) and (graph.edge = edge.name) and (edge.mid = 0)"
WARN:Jun  2 17:28:53:heap_replace: (am)invalid otid

---

From: postarch (Postgres Mailing Archive)
Date: Mon, 15 Jun 92 08:53:09 PDT

you write:
> I am persistently getting the following message on a replace command (which i
 +mmediately
> follows another on a different table, but otherwise identical which always wo
 +rks):
> What does this mean??  Does it have something to do with transaction ids?  Wh
 +at can I
> do about it?
> 
> Go 
> * replace edge (mid = graph.mid) where (graph.mid = 2) and (graph.edge = edge
 +.name) and (edge.mid = 0)\g
> 
> Query sent to backend is "replace edge (mid = graph.mid) where (graph.mid = 2
 +) and (graph.edge = edge.name) and (edge.mid = 0)"
> WARN:Jun  2 17:28:53:heap_replace: (am)invalid otid

This is a system failure.  Given the above replace command, Postgres first
does the scans to find out which tuples in the edge relation it has to
replace.  It then passes what we call tuple ids (page number and offset
within the page) to the access method replace function.  The replace function
checks that the tuple pointed to by the tuple id is valid.  When it
isn't you get the above cryptic error message.  I don't know why this is
happening, but will look into it.

Jeff Meredith
[email-masked]

---

From: postarch (Postgres Mailing Archive)
Date: Tue, 11 Aug 92 14:58:16 PDT

+mmediately
 +at can I
> do about it?

This has to do with non-functional updates.  What is happening is that your
replace query is causing postgres to try to update the same tuple twice.

It used to be the case that postgres would print a NOTICE saying that it
detected this condition, and it would only perform the first update.  Due to a
subtle change in the system non-functional updates suddenly started causing
an earlier check to fail, generating the above error message and aborting
the transaction.  In a nutshell once a tuple is updated in a transaction
it is thereafter seen as 'invalid' (even to the updating xact).

The validity test should not have precedence over the non-functional test,
and I have reversed the order of operations.  In 4.0.1 you will get the
NOTICE, and the xaction will be allowed to continue.

Jeff Meredith