hishtory icon indicating copy to clipboard operation
hishtory copied to clipboard

fix for huge deletion requests: make them chunked

Open GRbit opened this issue 1 year ago • 0 comments

Hi again @ddworken !

This PR solves the panic when deleting too many records:

172.23.0.1:51826 GET "/api/v1/query?device_id=0ae47f88-45ca-4851-a82f-261e234f03ab&user_id=SSjDq-sveVfzqLz9rY9VZC5D2akkYgPOqyY_3SEvM3c=&queryReason=preload" v0.302 6m16.859437843s 0 B
DB error at /build/hishtory/backend/server/internal/server/api_handlers.go:108:
result.Error: extended protocol limited to 65535 parameters
caught panic: DB error at /build/hishtory/backend/server/internal/server/api_handlers.go:108:
result.Error: extended protocol limited to 65535 parameters

I'm still on my run for more space in terminal. I wanted to rename one of my hosts to a shorter name and then I decided to re-export history deleting all old records and pushing the new ones. With hishtory redact hostname:long-old-hostname, which as I understood created unbearably huge deletion request with mine 49k records.

I wanted to go for chunks of 10k, but it panicked due to time out. It turned out that I can only delete ~255 records at a time on my server, which takes ~2 seconds (ryzen 5850U, PostgreSQL)

I think that's not the best fix that could be done here, the best approach would be to remove the code that handles older records which don't have encrypted_id, always use encrypted_id for deletion and adding (user_id, encrypted_id) index. I've tested it, in that case deletion goes very fast.

I believe you won't like it because you want to support some older clients, but at the same time, for living project it's the only way to move forward. Please, tell me what you think about it.

UPD: I've also added results of go mod tidy, otherwise it won't compile.

GRbit avatar Jul 17 '24 17:07 GRbit