Create extension pg_trgm in the NixOS module

[?]

Jun 20, 2019, 12:53 PM

TOLUNFACYKO3KHNZBULYO37LYJB7DB7JEY7NMCZCCDJF3EU6TVFQC

Dependencies

[2] TTZ26BJQ Unify Hydra's NixOS module with the one used for hydra.nixos.org
[3] 2KLEQJTG sql: refactor some sql statements to lowercase
[4] B7ENVLRS hydra-queue-runner: Make build notification more reliable
[5] 6COLDXKS Create a pg_trgm index on builds.drvpath
[*] D3MDJONY
[*] V4R3SIRM hydra-module.nix: Don't use a password
[*] N22GPKYT * Put info about logs / build products in the DB.

Change contents

edit in hydra-module.nix at line 276

[8.393]

[8.393]

echo "create extension if not exists pg_trgm" | runuser -u ${config.services.postgresql.superUser} -- ${config.services.postgresql.package}/bin/psql hydra
edit in hydra-module.nix at line 419

[2.2546]

[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

[4.19]→[4.19:94](∅→∅),[4.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);

[4.19]

[4.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

[4.211]→[3.109:217](∅→∅)

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

[4.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$$;