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

Replace custom unaccent extension with pure SQL solution

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: Postgres 12
    • Component/s: Database setup
    • Labels:
      None

      Description

      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

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Packages

                Version Package
                Postgres 12