bug: migration 20220524042504_bg.sql is invalid in PG 17+

[iExalt]

Version

main

Describe the bug.

Migration 20220524042504_bg.sql uses unqualified calls to uuid_nil() in functions which are then used in CREATE INDEX statements.

PG 17 changes functions to use a safe search_path during maintenance operations making the unqualified calls invalid.

Minimum reproducible example

Create a PG17+ database and attempt to run the migrations against it.

Migration 20211119192131_initial.sql will succeed, but the next migration 20220524042504_bg.sql will fail.

Relevant log output

Other/Misc.

Calls should be qualified with public or the explicit nil UUID '00000000-0000-0000-0000-000000000000'::uuid can be used.

Code of Conduct

• I agree to follow NCX Infra Controller's Code of Conduct
• I have searched the open bugs and have found no duplicates for this bug report

[ajf]

Unfortunately old migrations can't be changed, since sqlx hashes the contents of the file for recording purposes - and this migration is about 4 years old now. So I'm not quite sure how to fix this other than reset all the migrations. @chet put together a tool to squash all migrations down to one in #906 which may give us the opportunity to update old migrations for new versions of PostgreSQL.

[chet]

Yeah I need to swing back to that PR @ajf & @iExalt -- was out last week. Took @kensimon's feedback into consideration and have an adjusted approach that takes what he recommended + some additional bits. I need to think about if that would solve this particular issue, but we should be able to extend it if not to make it compatible.

[github-actions]

This issue has been inactive for 60 days and will be closed soon.
Please comment to keep it open.