Uploaded image for project: 'MusicBrainz Server'
  1. MusicBrainz Server
  2. MBS-9084

Replace custom unaccent extension with pure SQL solution

XMLWordPrintable

    • Icon: Improvement Improvement
    • Resolution: Fixed
    • Icon: Normal Normal
    • Postgres 12
    • None
    • Database setup
    • None

      PostgreSQL on Amazon RDS does not support custom extensions. This means that the service can't be used as the backend for MusicBrainz Slave Server, which relies on two custom extensions: unaccent and collate.

      Luckily, it appears that the entire unaccent extension can be implemented in pure SQL:

      CREATE EXTENSION unaccent;
      
      CREATE OR REPLACE FUNCTION musicbrainz_unaccent(txt text) RETURNS text AS $$
      BEGIN
          RETURN unaccent(txt);
      END;
      $$ LANGUAGE 'plpgsql' IMMUTABLE;
      
      CREATE TEXT SEARCH DICTIONARY musicbrainz_unaccentdict (
          TEMPLATE = unaccent,
          RULES='unaccent'
      );
      
      SELECT musicbrainz_unaccent('Hôtel') = 'Hotel';
      SELECT musicbrainz_unaccent('ľščťžýáí') = 'lsctzyai';
      SELECT musicbrainz_unaccent('foo—bar‒baz') = 'foo—bar‒baz';
      SELECT musicbrainz_unaccent('nonunicode') = 'nonunicode';
      SELECT musicbrainz_unaccent('') = '';
      SELECT musicbrainz_unaccent(null) IS NULL;
      SELECT musicbrainz_unaccent(repeat('ä', 65536)) = repeat('a', 65536);
      SELECT ts_lexize('musicbrainz_unaccentdict', 'ľščťžýáí foo—bar‒baz nonunicode') = E'{"lsctzyai foo—bar‒baz nonunicode"}';
      

      Implementing this would get us half way to being able to use PostgreSQL on Amazon RDS

        1. unaccent.csv
          21 kB
          Michael Wiencek

            bitmap Michael Wiencek
            umka.dk UmkaDK
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved:

                Version Package
                Postgres 12