Re: Lengthy deletion

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Herouth Maoz" <herouth(at)unicell(dot)co(dot)il>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Lengthy deletion
Date: 2011-11-29 07:13:01
Message-ID: 9340.1322550781@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Herouth Maoz" <herouth(at)unicell(dot)co(dot)il> writes:
> I was instructed to delete old records from one of the tables in our production system. The deletion took hours and I had to stop it in mid-operation and reschedule it as a night job. But then I had to do the same when I got up in the morning and it was still running.

> I got an interesting clue, though, when I canceled the deletion the second time around. I got the following error message:

> Cancel request sent
> ERROR: canceling statement due to user request
> CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."sent_messages" x WHERE $1 OPERATOR(pg_catalog.=) "subscription_id" FOR SHARE OF x"

Yup, that's a clue all right. I'll bet a nickel that you don't
have an index on the foreign key's referencing column (ie,
sent_messages.subscription_id). That means each delete in
the referenced table has to seqscan the referencing table to
see if the delete would result in an FK violation.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Herouth Maoz 2011-11-29 08:51:46 Re: Lengthy deletion
Previous Message Herouth Maoz 2011-11-29 06:52:58 Lengthy deletion