Re: ERROR: attempted to delete invisible tuple

[Bryan Murphy <bmurphy1976@xxxxxxxxx>]

Could I run pg_resetxlog on a warm spare?  Would that give the same result?  Unfortunately, this is our production system and I simply cannot bring it down at the moment to run pg_resetxlog.

Bryan

On Mon, Aug 17, 2009 at 11:35 AM, Greg Stark <gsstark@xxxxxxx> wrote:

On Mon, Aug 17, 2009 at 4:23 PM, Bryan Murphy<bmurphy1976@xxxxxxxxx> wrote:
>  I've identified 82 bad records.  When I try to query for the records,
> we get the following:
> ERROR:  missing chunk number 0 for toast value 25692661 in pg_toast_25497233
> That's fine.  I've run into that in a few other tables and have just been
> deleting the offending records.  However, in this particular table, when I
> try and delete the records, I get the following error message:
> ERROR:  attempted to delete invisible tuple
> I'm at a loss what to do next.

Can you select xmin,xmax,ctid from the bad tuples?

And also the output of pg_resetxlog -n. I suspect you may have failed
to restore properly and have tuples from the "future" in your
database. Perhaps you're missing at the last WAL file in your
recovery?

If that's the case you may be able to get out of it with pg_resetxlog
and then cleaning up but it would be good to see the data first before
changing things.

--
greg
http://mit.edu/~gsstark/resume.pdf