grahamc/hydra2 - Change TOLUNFACYKO3KHNZBULYO37LYJB7DB7JEY7NMCZCCDJF3EU6TVFQC

Create extension pg_trgm in the NixOS module

The creation of the pg_trgm extension needs superuser power. So, this patch makes the extension creation in the Hydra NixOS module when a local database is used.

If it is not possible to create this extension (remote database for instance with nosuperuser), the creation of the pg_trgm index is skipped (this index speedup queries on builds.drvpath) and warnings are emitted:

initialising the Hydra database schema...
WARNING:  Can not create extension pg_trgm: permission denied to create extension &quot;pg_trgm&quot;
WARNING:  HINT: Temporary provide superuser role to your Hydra Postgresql user and run the script src&#x2f;sql&#x2f;upgrade-57.sql
WARNING:  The pg_trgm index on builds.drvpath has been skipped (slower complex queries on builds.drvpath)

This allows to keep smooth migrations: the migration process doesn't require a manual step (but this manual step is recommended on big remote databases).

Created by Antoine Eiche on June 20, 2019

TOLUNFACYKO3KHNZBULYO37LYJB7DB7JEY7NMCZCCDJF3EU6TVFQC

Dependencies

In channels

main

Change contents

Insertion in hydra-module.nix at line 276 [5.1]

[4.393]

            echo "create extension if not exists pg_trgm" | runuser -u ${config.services.postgresql.superUser} -- ${config.services.postgresql.package}/bin/psql hydra

Insertion in hydra-module.nix at line 419 [5.1]

[2.2546]

        # The postgres user is used to create the pg_trgm extension for the hydra database
        hydra-users postgres postgres

Replacement in src/sql/hydra.sql at line 693 [7.1]

B:BD[6.19] → [6.19:94]

B:BD[6.94] → [3.0:108]

-- Provide an index used by LIKE operator on builds.drvpath (search query)
create extension pg_trgm;
create index IndexTrgmBuildsOnDrvpath on builds using gin (drvpath gin_trgm_ops);

[6.19]

[6.202]

-- The pg_trgm extension has to be created by a superuser. The NixOS
-- module creates this extension in the systemd prestart script. We
-- then ensure the extension has been created before creating the
-- index. If it is not possible to create the extension, a warning
-- message is emitted to inform the user the index creation is skipped
-- (slower complex queries on builds.drvpath).
do $$
begin
    create extension if not exists pg_trgm;
    -- Provide an index used by LIKE operator on builds.drvpath (search query)
    create index IndexTrgmBuildsOnDrvpath on builds using gin (drvpath gin_trgm_ops);
exception when others then
    raise warning 'Can not create extension pg_trgm: %', SQLERRM;
    raise warning 'HINT: Temporary provide superuser role to your Hydra Postgresql user and run the script src/sql/upgrade-57.sql';
    raise warning 'The pg_trgm index on builds.drvpath has been skipped (slower complex queries on builds.drvpath)';
end$$;

Replacement in src/sql/upgrade-57.sql at line 1 [6.211]

B:BD[6.211] → [3.109:217]

create extension pg_trgm;
create index IndexTrgmBuildsOnDrvpath on builds using gin (drvpath gin_trgm_ops);

[6.211]

-- The pg_trgm extension has to be created by a superuser. The NixOS
-- module creates this extension in the systemd prestart script. We
-- then ensure the extension has been created before creating the
-- index. If it is not possible to create the extension, a warning
-- message is emitted to inform the user the index creation is skipped
-- (slower complex queries on builds.drvpath).
do $$
begin
    create extension if not exists pg_trgm;
    -- Provide an index used by LIKE operator on builds.drvpath (search query)
    create index IndexTrgmBuildsOnDrvpath on builds using gin (drvpath gin_trgm_ops);
exception when others then
    raise warning 'Can not create extension pg_trgm: %', SQLERRM;
    raise warning 'HINT: Temporary provide superuser role to your Hydra Postgresql user and run the script src/sql/upgrade-57.sql';
    raise warning 'The pg_trgm index on builds.drvpath has been skipped (slower complex queries on builds.drvpath)';
end$$;