Delete user: foreign key constraint issues #7

New Issue

noah · 2022-10-21T04:29:25Z

noah commented

2022-10-21 04:29:25 +00:00

A user wanted their account deleted but were running into database constraint errors in doing so. The two specific errors encountered were:

2022/10/21 04:21:42 /home/nonshy/git/website/pkg/models/deletion/delete_user.go:187 ERROR: update or delete on table "comments" violates foreign key constraint "fk_threads_comment" on table "threads" (SQLSTATE 23503)
[0.976ms] [rows:0] DELETE FROM "comments" WHERE table_name = 'threads' AND table_id IN (16,17)

2022/10/21 04:23:41 /home/nonshy/git/website/pkg/models/photo.go:259 ERROR: update or delete on table "photos" violates foreign key constraint "fk_users_profile_photo" on table "users" (SQLSTATE 23503)
[1.109ms] [rows:0] DELETE FROM "photos" WHERE "photos"."id" = 154

In the first: the user had created two threads on the forum (ID 16 and 17) and the threads.comment_id column was referring to comments (ID 110, 111) that were trying to be deleted.

In the second: their profile photo wasn't removed because their users.profile_photo_id column still referenced its ID.

The deletion was unblocked by manually running Postgres queries to break the foreign key relationships:

nonshy=> delete from threads where id in (16, 17);
DELETE 2
nonshy=> update users set profile_photo_id=null where username='REDACTED';
UPDATE 1

These constraint errors were not seen on local dev with a SQLite3 database so seems to reproduce only on PostgreSQL. The delete_user.go module should probably need an update to better handle this case.

A user wanted their account deleted but were running into database constraint errors in doing so. The two specific errors encountered were: ``` 2022/10/21 04:21:42 /home/nonshy/git/website/pkg/models/deletion/delete_user.go:187 ERROR: update or delete on table "comments" violates foreign key constraint "fk_threads_comment" on table "threads" (SQLSTATE 23503) [0.976ms] [rows:0] DELETE FROM "comments" WHERE table_name = 'threads' AND table_id IN (16,17) 2022/10/21 04:23:41 /home/nonshy/git/website/pkg/models/photo.go:259 ERROR: update or delete on table "photos" violates foreign key constraint "fk_users_profile_photo" on table "users" (SQLSTATE 23503) [1.109ms] [rows:0] DELETE FROM "photos" WHERE "photos"."id" = 154 ``` In the first: the user had created two threads on the forum (ID 16 and 17) and the threads.comment_id column was referring to comments (ID 110, 111) that were trying to be deleted. In the second: their profile photo wasn't removed because their users.profile_photo_id column still referenced its ID. The deletion was unblocked by manually running Postgres queries to break the foreign key relationships: ```sql nonshy=> delete from threads where id in (16, 17); DELETE 2 nonshy=> update users set profile_photo_id=null where username='REDACTED'; UPDATE 1 ``` These constraint errors were not seen on local dev with a SQLite3 database so seems to reproduce only on PostgreSQL. The delete_user.go module should probably need an update to better handle this case.

noah added the

bug

label 2022-10-21 04:29:25 +00:00

noah closed this issue

2023-03-24 22:48:33 +00:00

Sign in to join this conversation.