PG13 compatibility issue 🔥

Bug
1

What is this about?

This migration introduces NULL NOT DISTINCT on an index on the problem_check_trackers table. DEV: Fix problem check tracker unique index not respecting NULLs by Drenmi · Pull Request #29169 · discourse/discourse · GitHub

What is the problem?

By default, when checking uniqueness on a tuple for the purposes of enforcing a unique index, PostgreSQL considers NULLs to be distinct values. Because of this we could incorrectly have multiple entries with { identifier: "rails_env", target: nil } created due to race conditions. This would then cause errors at runtime.

How does this solve it?

Drop the existing index and recreate it with the NULLS NOT DISTINCT option.

Problem

However, NULLS NOT DISTINCT was introduced in Postgres 15 beta2. The current Postgres version on a standard install is Postgres 13 and that does not support this feature.

Consequences

  • this change will not have any effect on PG13 since the NULLS NOT DISTINCT will be ignored (source)
  • attempting to restore a backup from a PG15 server to a PG13 server will fail with the following error
ERROR:  syntax error at or near "NULLS"
LINE 1: ...m_check_trackers USING btree (identifier, target) NULLS NOT ...
^
EXCEPTION: psql failed:                                                              ^
/var/www/discourse/lib/backup_restore/database_restorer.rb:92:in `restore_dump'

(full line: CREATE UNIQUE INDEX index_problem_check_trackers_on_identifier_and_target ON public.problem_check_trackers USING btree (identifier, target) NULLS NOT DISTINCT;)

@tgxworld @drenmi

1 Like
2

@drenmi Looks like we have to revert the migration and reconsider another solution. This is probably causing self hosted installs to error out.