From: | "Tomas Vondra" <tv(at)fuzzy(dot)cz> |
---|---|
To: | "john gale" <john(at)smadness(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: cannot delete corrupted rows after DB corruption: tuple concurrently updated |
Date: | 2014-02-26 10:59:53 |
Message-ID: | 09954290b001fa6474226e2e21f2ca71.squirrel@sq.gransy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Willy-Bas Loos | 2014-02-26 12:07:50 | Re: str replication failed, restart fixed it |
Previous Message | Willy-Bas Loos | 2014-02-26 09:53:56 | str replication failed, restart fixed it |