Update `v1.136.0`: PostgresError: relation "idx_originalfilename_trigram" does not exist
11 Replies
:wave: Hey @raldone01,
Thanks for reaching out to us. Please carefully read this message and follow the recommended actions. This will help us be more effective in our support effort and leave more time for building Immich :immich:.
References
- Container Logs:
docker compose logs docs
- Container Status: docker ps -a docs
- Reverse Proxy: https://immich.app/docs/administration/reverse-proxy
- Code Formatting https://support.discord.com/hc/en-us/articles/210298617-Markdown-Text-101-Chat-Formatting-Bold-Italic-Underline#h_01GY0DAKGXDEHE263BCAYEGFJA
Checklist
I have...
1. :ballot_box_with_check: verified I'm on the latest release(note that mobile app releases may take some time).
2. :ballot_box_with_check: read applicable release notes.
3. :ballot_box_with_check: reviewed the FAQs for known issues.
4. :ballot_box_with_check: reviewed Github for known issues.
5. :ballot_box_with_check: tried accessing Immich via local ip (without a custom reverse proxy).
6. :ballot_box_with_check: uploaded the relevant information (see below).
7. :ballot_box_with_check: tried an incognito window, disabled extensions, cleared mobile app cache, logged out and back in, different browsers, etc. as applicable
(an item can be marked as "complete" by reacting with the appropriate number)
Information
In order to be able to effectively help you, we need you to provide clear information to show what the problem is. The exact details needed vary per case, but here is a list of things to consider:
- Your docker-compose.yml and .env files.
- Logs from all the containers and their status (see above).
- All the troubleshooting steps you've tried so far.
- Any recent changes you've made to Immich or your system.
- Details about your system (both software/OS and hardware).
- Details about your storage (filesystems, type of disks, output of commands like fdisk -l and df -h).
- The version of the Immich server, mobile app, and other relevant pieces.
- Any other information that you think might be relevant.
Please paste files and logs with proper code formatting, and especially avoid blurry screenshots.
Without the right information we can't work out what the problem is. Help us help you ;)
If this ticket can be closed you can use the /close command, and re-open it later if needed.
I just updated to
v1.136.0.Successfully submitted, a tag has been added to inform contributors. :white_check_mark:
Hey!
So the database schema deviates from what is expected.
Could you export the schema with
docker exec -ti --user postgres immich_postgres pg_dump --schema-only -d immich and post it here?
I would like to check first if there are any other discrepancies.
If only idx_originalfilename_trigram is affected, then we can easily recreate it to make the update/migration to 1.136+ happy.Here is the fully schema
Thanks!
Drift
The only drift/discrepancy is
idx_originalfilename_trigram from the error message. That's good.
There is idx_originalpath_trigram in place of the missing idx_originalfilename_trigram.
Fix
Why did it happen?
Based on git history git log -S idx_originalpath_trigram:
1. idx_originalpath_trigram created in commit 2f53f6a62cedeb05a1da983b292db18b1ec95618
Date: Tue Mar 5 17:08:35 2024 -0600 feat(web): search by filename (#7624)2.
idx_originalpath_trigram is replaced with idx_originalFileName_trigram in commit 1eea547aa2b6431a147e6a7514d0edc238c21022
Date: Wed Mar 6 21:36:08 2024 -0600 chore(server): search filename using originalFileName (#7691)3. Later,
idx_originalFileName_trigram was renamed to idx_originalfilename_trigram (lowercase).
So idx_originalpath_trigram existed for ~1 day and somehow managed to get into your Immich DB. Maybe it was kind of a feature preview of search by file name in https://github.com/immich-app/immich/releases/tag/v1.98.0 ?[Pull Request] feat(web): search by filename (immich-app/immich#7624)
[Pull Request] chore(server): search filename using originalFileName (immich-app/immich#7691)
Just to verify.
I need to run
CREATE INDEX idx_originalfilename_trigram ON public.assets USING gin (public.f_unaccent(("originalFileName")::text) public.gin_trgm_ops);
DROP INDEX idx_originalpath_trigram; to fix it right?Right, connect to the database
docker exec -ti immich_postgres psql -U postgres -d immich (assuming default DB name and user name)
and run at least the first statement CREATE ...; The second is just cleanup of unused index.I am up and running again! Thanks a ton!
Now I am doing the next update ahahha
I am online and fully updated.