Error after upgrade to v0.2.1

[JSmith-Aura]

Howdy, after upgrading and running the project (with db migrations) I get this following log line emitted on startup.

Doesnt seem to cause any issues that anyone has mentioned (and I've started a job and it has worked). So not sure if this is something to fix

2024/03/25 14:11:09 /app/api/internal/db/db.go:87 ERROR: index row size 2784 exceeds btree version 4 maximum 2704 for index "idx_uniq" (SQLSTATE 54000)
[28.572ms] [rows:0] CREATE UNIQUE INDEX IF NOT EXISTS "idx_uniq" ON "potfile_entries" ("hash","plaintext_hex","hash_type")

[lachlan2k]

Looks like when the unique index constraint is added to the potfile table, certain hashes will be too long for the postgres btree.

Possible solutions

• Hash (maybe even just sha1) the inputs to the index.
• Investigate if a hashtable index can be used instead of btree (preferred), but might not be possible for uniq index.
• Use a constraint instead of an index - the reason for using an index is because constraints were a bit tricky to define in gorm for this.
• Instead of using a constraint, use client-side logic in a db transaction to look if the hash is already present, and only insert if unique.

Concerns:

• Insertions for this index might be slow if the btree needs re-balanced, im not sure how postgres handles btree balancing.
• The hash field will probably need indexed anyway

[lachlan2k]

On reflection, I think I will use the following solution:

• Use a hashtable index for the hash column
• Use client-side logic to prevent duplicates, rather than db constraints
  • This is a bit gross, but I also think I don't care.
  • It allows for smart-ish things down the line, if I want to treat variations of salting as the same type of hash for de-duplication purposes, etc.

[lachlan2k]

Cool, I've pushed the above.

Will require the db migration drop index idx_uniq; (will put in release notes for 0.3.0)