Skip site navigation (1) Skip section navigation (2)

Re: Lengthy deletion

From: Herouth Maoz <herouth(at)unicell(dot)co(dot)il>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Lengthy deletion
Date: 2011-11-29 08:51:46
Message-ID: BB44747E-AED4-4A71-B02C-4BA7B7EEF784@unicell.co.il (view raw or flat)
Thread:
Lists: pgsql-general
On 29/11/2011, at 09:13, Tom Lane wrote:

> "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.


Makes sense. But shouldn't that be figured into the EXPLAIN plan?

--
חרות מעוז
יוניסל פתרונות סלולריים מתקדמים
☎ 03-5181717 שלוחה 742

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2011-11-29 09:10:03
Subject: Re: Lengthy deletion
Previous:From: Tom LaneDate: 2011-11-29 07:13:01
Subject: Re: Lengthy deletion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group