Postgresql maintainenace queries fail with foreignkey violation

Question

I maintain a Django app with a postgresql backend, where users post interesting URLs (links) and then either leave replies under these or upvote/downvote them. All this is done via 4 postgresql tables links_link, links_publicreply, links_vote and links_seen (links_seen keeps track of which publicreplies were seen by a user; the other 3 tables do what their names indicate).

I'm running maintenance queries whereby I'm deleting mature records from links_publicreply and related rows from links_seen. The queries go like so:

begin;
DELETE FROM links_seen WHERE which_reply_id in (SELECT id FROM links_publicreply where "submitted_on" < now() - interval '7 days');
DELETE FROM links_publicreply WHERE "submitted_on" < now() - interval '7 days';
commit;

This fails with the error:

ERROR: update or delete on table "links_link" violates foreign key constraint "links_vote_link_id_fkey" on table "links_vote" DETAIL: Key (id)=(2207250) is still referenced from table "links_vote".

I've grappled with this for over 10 days, but can't fix it (beginner DBA here). Can anyone point out (i) what the problem is, (ii) what do I need to do to fix it so that my maintenance queries start working (I'm essentially just deleting publicreplies older than 7 days)? An illustrative example would be nice. Thanks in advance!

---

These tables are described as follows. links_link:

                                     Table "public.links_link"
     Column      |           Type           |                        Modifiers                        
-----------------+--------------------------+---------------------------------------------------------
 id              | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description     | text                     | not null
 submitter_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 rank_score      | double precision         | not null
 url             | character varying(250)   | not null
 cagtegory       | character varying(25)    | not null
 image_file      | character varying(100)   | 
 reply_count     | integer                  | default 0
 device          | character varying(10)    | default '1'::character varying
 latest_reply_id | integer                  | 
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_vote" CONSTRAINT "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED

(END)

links_vote:

Table "public.links_vote"
  Column  |  Type   |                        Modifiers                        
----------+---------+---------------------------------------------------------
 id       | integer | not null default nextval('links_vote_id_seq'::regclass)
 voter_id | integer | not null
 link_id  | integer | not null
 value    | integer | 
Indexes:
    "links_vote_pkey" PRIMARY KEY, btree (id)
    "links_vote_link_id" btree (link_id)
    "links_vote_voter_id" btree (voter_id)
Foreign-key constraints:
    "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_vote_voter_id_fkey" FOREIGN KEY (voter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED

links_publicreply:

                                      Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
 category        | character varying(20)    | not null
 seen            | boolean                  | not null
 abuse           | boolean                  | not null
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

links_seen:

                                      Table "public.links_seen"
     Column     |           Type           |                        Modifiers                        
----------------+--------------------------+---------------------------------------------------------
 id             | integer                  | not null default nextval('links_seen_id_seq'::regclass)
 seen_status    | boolean                  | not null
 seen_user_id   | integer                  | not null
 seen_at        | timestamp with time zone | not null
 which_reply_id | integer                  | 
Indexes:
    "links_seen_pkey" PRIMARY KEY, btree (id)
    "links_seen_seen_user_id" btree (seen_user_id)
    "links_seen_which_reply_id" btree (which_reply_id)
Foreign-key constraints:
    "links_seen_seen_user_id_fkey" FOREIGN KEY (seen_user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED

Answer 1

When you delete from links_publicreply, it cascades that to delete child rows from links_link which would otherwise become orphaned (because the constraint is defined with ON DELETE CASCADE).

But some of those rows being deleted in links_link are still referenced by rows in links_vote. The constraint between links_link and links_vote is not defined with ON DELETE CASCADE, so you get an error. If you want to automatically delete the rows from links_vote, change the constraint to be ON DELETE CASCADE. If that is not what you want, then it is not clear what you do want, you will have to explain it some more.