"Herouth Maoz" <herouth@unicell.co.il> writes:
> I was instructed to delete old records from one of the tables in our production system. The deletion took hours and I
hadto 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
morningand 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