Re: cannot delete corrupted rows after DB corruption: tuple concurrently updated

On 26 Únor 2014, 8:45, john gale wrote:
>
> Does anybody have any ideas about this.
>
> We restarted the postmaster and the issue persists.  So previously in
> 9.0.4 where we could clean corruption, it seems in 9.3.2 we can no longer
> clean corruption.o  I'm assuming this because our data insert environment
> has not changed, so we shouldn't be hitting any different transaction
> concurrency / isolation problems than we did before.
>
> Is there a way to force deletion of a row, ignoring concurrency, similar
> to concurrent updates.  It looks like changing
> default_transaction_isolation did not affect this:
>
> munin2=# delete from testruns where ctid = '(37069305,4)';
> ERROR:  tuple concurrently updated

AFAIK this error is raised when a before trigger modifies the row that is
being deleted. Imagine a trigger that does this

   UPDATE testruns SET mycolumn = 1 WHERE id = OLD.id;
   RETURN OLD;

Given the way MVCC in postgres works (copying row when updating), the
error makes sense. In 9.0 this worked by silently skipping the DELETE
(incidentally, I had a few reports about tables that can't be deleted
because of this in the past month).

Anyway, do you have any triggers on the table? If yes, try to disable
them. I suspect the data are corrupted in a way that causes update on the
deleted row - either directly, or maybe because of a cascading effect.

I'm wondering if it might be caused by RI triggers - maybe yes, but I'm
not aware of any RI trigger doing updates.

That being said, I think that what you're doing is wrong. If you think you
have a corrupted database, I'd strongly suggest doing dump/restore. Or how
do you know there's no other corruption lurking in the files, slowly
spreading to other parts of the database?

Tomas