Page MenuHomeSoftware Heritage
Differential D8718

pre-commit, tox: Bump pre-commit, codespell, black and flake8
ClosedPublic
Actions

Authored by douardda on Oct 18 2022, 7:15 PM.

Details

Reviewers
None
Group Reviewers
Reviewers
Commits
rDJNL2c6d9e05eca6: pre-commit, tox: Bump pre-commit, codespell, black and flake8
Summary
  • pre-commit from 4.1.0 to 4.3.0,
  • codespell from 2.2.1 to 2.2.2,
  • black from 22.3.0 to 22.10.0 and
  • flake8 from 4.0.1 to 5.0.4. Also freeze flake8 dependencies.

Also change flake8's repo config to github (the gitlab mirror
being outdated).

Diff Detail

Event Timeline

douardda created this revision.Oct 18 2022, 7:15 PM
Herald added a reviewer: Reviewers. · View Herald TranscriptOct 18 2022, 7:15 PM
swh-public-ci added a comment.Oct 18 2022, 7:24 PM

Build has FAILED

Patch application report for D8718 (id=31442)

Rebasing onto 1f1b03cfbd...

Current branch diff-target is up to date.
Changes applied before test
commit 2c6d9e05eca6a483f6aebfd3f8e71cedb907eb74
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Oct 18 18:20:52 2022 +0200

    pre-commit, tox: Bump pre-commit, codespell, black and flake8
    
    - pre-commit from 4.1.0 to 4.3.0,
    - codespell from 2.2.1 to 2.2.2,
    - black from 22.3.0 to 22.10.0 and
    - flake8 from 4.0.1 to 5.0.4. Also freeze flake8 dependencies.
    
    Also change flake8's repo config to github (the gitlab mirror
    being outdated).

Link to build: https://jenkins.softwareheritage.org/job/DJNL/job/tests-on-diff/224/
See console output for more information: https://jenkins.softwareheritage.org/job/DJNL/job/tests-on-diff/224/console

Harbormaster returned this revision to the author for changes because remote builds failed.Oct 18 2022, 7:24 PM
Harbormaster failed remote builds in B32388: Diff 31442!
swh-public-ci added a comment.Oct 18 2022, 7:44 PM

Build is green

Patch application report for D8718 (id=31442)

Rebasing onto 1f1b03cfbd...

Current branch diff-target is up to date.
Changes applied before test
commit 2c6d9e05eca6a483f6aebfd3f8e71cedb907eb74
Author: David Douard <david.douard@sdfa3.org>
Date:   Tue Oct 18 18:20:52 2022 +0200

    pre-commit, tox: Bump pre-commit, codespell, black and flake8
    
    - pre-commit from 4.1.0 to 4.3.0,
    - codespell from 2.2.1 to 2.2.2,
    - black from 22.3.0 to 22.10.0 and
    - flake8 from 4.0.1 to 5.0.4. Also freeze flake8 dependencies.
    
    Also change flake8's repo config to github (the gitlab mirror
    being outdated).

See https://jenkins.softwareheritage.org/job/DJNL/job/tests-on-diff/225/ for more details.

Harbormaster completed remote builds in B32388: Diff 31442.Oct 18 2022, 7:44 PM
douardda requested review of this revision.Oct 18 2022, 7:44 PM
This revision was not accepted when it landed; it landed in state Needs Review.Oct 18 2022, 8:01 PM
Closed by commit rDJNL2c6d9e05eca6: pre-commit, tox: Bump pre-commit, codespell, black and flake8 (authored by douardda). · Explain Why
This revision was automatically updated to reflect the committed changes.
douardda added a commit: rDJNL2c6d9e05eca6: pre-commit, tox: Bump pre-commit, codespell, black and flake8.

Revision Contents
Changeset List

PathSize
12 lines
9 lines

Diff 31442

View Options

.pre-commit-config.yaml

-- psql variables to get the current database flavor
select swh_get_dbflavor() = 'read_replica' as dbflavor_read_replica \gset
select swh_get_dbflavor() != 'read_replica' as dbflavor_deduplication \gset
select swh_get_dbflavor() = 'mirror' as dbflavor_mirror \gset
select swh_get_dbflavor() = 'default' as dbflavor_default \gset
-- content -- content
\if :dbflavor_deduplication
create unique index concurrently content_pkey on content(sha1); create unique index concurrently content_pkey on content(sha1);
create unique index concurrently on content(sha1_git); create unique index concurrently on content(sha1_git);
create index concurrently on content(sha256);
create index concurrently on content(blake2s256);
create index concurrently on content(ctime); -- TODO use a BRIN index here (postgres >= 9.5)
create unique index concurrently on content(object_id); create unique index concurrently on content(object_id);
alter table content add primary key using index content_pkey; alter table content add primary key using index content_pkey;
\else
create index concurrently content_pkey on content(sha1);
create index concurrently on content(sha1_git);
\endif
create index concurrently on content(sha256);
create index concurrently on content(blake2s256);
create index concurrently on content(ctime); -- ?
-- origin -- origin
create unique index concurrently origin_pkey on origin(id); create unique index concurrently origin_pkey on origin(id);
\if :dbflavor_deduplication
create unique index concurrently on origin using btree(url); create unique index concurrently on origin using btree(url);
\else
create index concurrently on origin using btree(url);
\endif
create index concurrently on origin using gin (url gin_trgm_ops); create index concurrently on origin using gin (url gin_trgm_ops);
create index concurrently on origin using btree(digest(url, 'sha1')); create index concurrently on origin using btree(digest(url, 'sha1'));
\if :dbflavor_deduplication
alter table origin add primary key using index origin_pkey; alter table origin add primary key using index origin_pkey;
\endif
-- skipped_content -- skipped_content
\if :dbflavor_deduplication
alter table skipped_content add constraint skipped_content_sha1_sha1_git_sha256_key unique (sha1, sha1_git, sha256); alter table skipped_content add constraint skipped_content_sha1_sha1_git_sha256_key unique (sha1, sha1_git, sha256);
\endif
create index concurrently on skipped_content(sha1); create index concurrently on skipped_content(sha1);
create index concurrently on skipped_content(sha1_git); create index concurrently on skipped_content(sha1_git);
create index concurrently on skipped_content(sha256); create index concurrently on skipped_content(sha256);
create index concurrently on skipped_content(blake2s256); create index concurrently on skipped_content(blake2s256);
create unique index concurrently on skipped_content(object_id); create unique index concurrently on skipped_content(object_id);
\if :dbflavor_default
alter table skipped_content add constraint skipped_content_origin_fkey foreign key (origin) references origin(id) not valid; alter table skipped_content add constraint skipped_content_origin_fkey foreign key (origin) references origin(id) not valid;
alter table skipped_content validate constraint skipped_content_origin_fkey; alter table skipped_content validate constraint skipped_content_origin_fkey;
\endif
-- directory -- directory
\if :dbflavor_deduplication
create unique index concurrently directory_pkey on directory(id); create unique index concurrently directory_pkey on directory(id);
alter table directory add primary key using index directory_pkey; alter table directory add primary key using index directory_pkey;
\else
create index concurrently directory_pkey on directory(id);
\endif
create index concurrently on directory using gin (dir_entries); -- ?
create index concurrently on directory using gin (file_entries); -- ?
create index concurrently on directory using gin (rev_entries); -- ?
create unique index concurrently on directory(object_id); -- ?
create index concurrently on directory using gin (dir_entries);
create index concurrently on directory using gin (file_entries);
create index concurrently on directory using gin (rev_entries);
create unique index concurrently on directory(object_id);
-- directory_entry_dir -- directory_entry_dir
\if :dbflavor_deduplication
create unique index concurrently directory_entry_dir_pkey on directory_entry_dir(id); create unique index concurrently directory_entry_dir_pkey on directory_entry_dir(id);
alter table directory_entry_dir add primary key using index directory_entry_dir_pkey; alter table directory_entry_dir add primary key using index directory_entry_dir_pkey;
create unique index concurrently on directory_entry_dir(target, name, perms); create unique index concurrently on directory_entry_dir(target, name, perms);
\else
create index concurrently directory_entry_dir_pkey on directory_entry_dir(id);
\endif
-- directory_entry_file -- directory_entry_file
\if :dbflavor_deduplication
create unique index concurrently directory_entry_file_pkey on directory_entry_file(id); create unique index concurrently directory_entry_file_pkey on directory_entry_file(id);
alter table directory_entry_file add primary key using index directory_entry_file_pkey; alter table directory_entry_file add primary key using index directory_entry_file_pkey;
create unique index concurrently on directory_entry_file(target, name, perms); create unique index concurrently on directory_entry_file(target, name, perms);
\else
create index concurrently directory_entry_file_pkey on directory_entry_file(id);
\endif
-- directory_entry_rev -- directory_entry_rev
\if :dbflavor_deduplication
create unique index concurrently directory_entry_rev_pkey on directory_entry_rev(id); create unique index concurrently directory_entry_rev_pkey on directory_entry_rev(id);
alter table directory_entry_rev add primary key using index directory_entry_rev_pkey; alter table directory_entry_rev add primary key using index directory_entry_rev_pkey;
create unique index concurrently on directory_entry_rev(target, name, perms); create unique index concurrently on directory_entry_rev(target, name, perms);
\else
create index concurrently directory_entry_rev_pkey on directory_entry_rev(id);
\endif
-- person -- person
\if :dbflavor_deduplication
create unique index concurrently person_pkey on person(id); create unique index concurrently person_pkey on person(id);
alter table person add primary key using index person_pkey; alter table person add primary key using index person_pkey;
create unique index concurrently on person(fullname); create unique index concurrently on person(fullname);
create index concurrently on person(name); \else
create index concurrently on person(email); create index concurrently person_pkey on person(id);
create index concurrently on person(fullname);
\endif
create index concurrently on person(name); -- ?
ardumontUnsubmitted
Not Done
Inline Actions

Wondering whether those indexes (and the one before marked as -- ?) were not
installed in the first place to ease some introspection done independently from
the production (with the guest user a long time ago)...

Which finally made it through here so we got no more discrepancy between code
and production... (going the wrong way though... that is, it should have been
the other way around, dropping the unused indexes instead of adding in the
code...)

ardumont: Wondering whether those indexes (and the one before marked as `-- ?`) were not installed in the…
create index concurrently on person(email); -- ?
-- revision -- revision
\if :dbflavor_deduplication
create unique index concurrently revision_pkey on revision(id); create unique index concurrently revision_pkey on revision(id);
alter table revision add primary key using index revision_pkey; alter table revision add primary key using index revision_pkey;
\else
create index concurrently revision_pkey on revision(id);
\endif
\if :dbflavor_deduplication
alter table revision add constraint revision_author_fkey foreign key (author) references person(id) not valid; alter table revision add constraint revision_author_fkey foreign key (author) references person(id) not valid;
alter table revision validate constraint revision_author_fkey; alter table revision validate constraint revision_author_fkey;
alter table revision add constraint revision_committer_fkey foreign key (committer) references person(id) not valid; alter table revision add constraint revision_committer_fkey foreign key (committer) references person(id) not valid;
alter table revision validate constraint revision_committer_fkey; alter table revision validate constraint revision_committer_fkey;
alter table revision alter table revision
add constraint revision_date_neg_utc_offset_not_null add constraint revision_date_neg_utc_offset_not_null
check (date is null or date_neg_utc_offset is not null) check (date is null or date_neg_utc_offset is not null)
not valid; not valid;
alter table revision alter table revision
add constraint revision_committer_date_neg_utc_offset_not_null add constraint revision_committer_date_neg_utc_offset_not_null
check (committer_date is null or committer_date_neg_utc_offset is not null) check (committer_date is null or committer_date_neg_utc_offset is not null)
not valid; not valid;
alter table revision alter table revision
validate constraint revision_date_neg_utc_offset_not_null; validate constraint revision_date_neg_utc_offset_not_null;
alter table revision alter table revision
validate constraint revision_committer_date_neg_utc_offset_not_null; validate constraint revision_committer_date_neg_utc_offset_not_null;
\endif
create index concurrently on revision(directory); create index concurrently on revision(directory); -- ?
create unique index concurrently on revision(object_id); create unique index concurrently on revision(object_id); -- ?
-- revision_history -- revision_history
\if :dbflavor_deduplication
create unique index concurrently revision_history_pkey on revision_history(id, parent_rank); create unique index concurrently revision_history_pkey on revision_history(id, parent_rank);
alter table revision_history add primary key using index revision_history_pkey; alter table revision_history add primary key using index revision_history_pkey;
\else
create index concurrently revision_history_pkey on revision_history(id, parent_rank);
\endif
create index concurrently on revision_history(parent_id); create index concurrently on revision_history(parent_id); -- ?
\if :dbflavor_deduplication
alter table revision_history add constraint revision_history_id_fkey foreign key (id) references revision(id) not valid; alter table revision_history add constraint revision_history_id_fkey foreign key (id) references revision(id) not valid;
alter table revision_history validate constraint revision_history_id_fkey; alter table revision_history validate constraint revision_history_id_fkey;
\endif
-- snapshot -- snapshot
\if :dbflavor_deduplication
create unique index concurrently snapshot_pkey on snapshot(object_id); create unique index concurrently snapshot_pkey on snapshot(object_id);
alter table snapshot add primary key using index snapshot_pkey; alter table snapshot add primary key using index snapshot_pkey;
create unique index concurrently on snapshot(id); create unique index concurrently on snapshot(id);
\else
create index concurrently on snapshot(id);
\endif
-- snapshot_branch -- snapshot_branch
\if :dbflavor_deduplication
create unique index concurrently snapshot_branch_pkey on snapshot_branch(object_id); create unique index concurrently snapshot_branch_pkey on snapshot_branch(object_id);
alter table snapshot_branch add primary key using index snapshot_branch_pkey; alter table snapshot_branch add primary key using index snapshot_branch_pkey;
create unique index concurrently on snapshot_branch (target_type, target, name); create unique index concurrently on snapshot_branch (target_type, target, name);
alter table snapshot_branch add constraint snapshot_branch_target_check check ((target_type is null) = (target is null)) not valid; alter table snapshot_branch add constraint snapshot_branch_target_check check ((target_type is null) = (target is null)) not valid;
alter table snapshot_branch validate constraint snapshot_branch_target_check; alter table snapshot_branch validate constraint snapshot_branch_target_check;
alter table snapshot_branch add constraint snapshot_target_check check (target_type not in ('content', 'directory', 'revision', 'release', 'snapshot') or length(target) = 20) not valid; alter table snapshot_branch add constraint snapshot_target_check check (target_type not in ('content', 'directory', 'revision', 'release', 'snapshot') or length(target) = 20) not valid;
alter table snapshot_branch validate constraint snapshot_target_check; alter table snapshot_branch validate constraint snapshot_target_check;
create unique index concurrently on snapshot_branch (name) where target_type is null and target is null; create unique index concurrently on snapshot_branch (name) where target_type is null and target is null;
\else :dbflavor_deduplication
create index concurrently snapshot_branch_pkey on snapshot_branch(object_id);
\endif
-- snapshot_branches -- snapshot_branches
\if :dbflavor_deduplication
create unique index concurrently snapshot_branches_pkey on snapshot_branches(snapshot_id, branch_id); create unique index concurrently snapshot_branches_pkey on snapshot_branches(snapshot_id, branch_id);
alter table snapshot_branches add primary key using index snapshot_branches_pkey; alter table snapshot_branches add primary key using index snapshot_branches_pkey;
alter table snapshot_branches add constraint snapshot_branches_snapshot_id_fkey foreign key (snapshot_id) references snapshot(object_id) not valid; alter table snapshot_branches add constraint snapshot_branches_snapshot_id_fkey foreign key (snapshot_id) references snapshot(object_id) not valid;
alter table snapshot_branches validate constraint snapshot_branches_snapshot_id_fkey; alter table snapshot_branches validate constraint snapshot_branches_snapshot_id_fkey;
alter table snapshot_branches add constraint snapshot_branches_branch_id_fkey foreign key (branch_id) references snapshot_branch(object_id) not valid; alter table snapshot_branches add constraint snapshot_branches_branch_id_fkey foreign key (branch_id) references snapshot_branch(object_id) not valid;
alter table snapshot_branches validate constraint snapshot_branches_branch_id_fkey; alter table snapshot_branches validate constraint snapshot_branches_branch_id_fkey;
\else
create index concurrently snapshot_branches_pkey on snapshot_branches(snapshot_id, branch_id);
\endif
-- origin_visit -- origin_visit
\if :dbflavor_deduplication
create unique index concurrently origin_visit_pkey on origin_visit(origin, visit); create unique index concurrently origin_visit_pkey on origin_visit(origin, visit);
alter table origin_visit add primary key using index origin_visit_pkey; alter table origin_visit add primary key using index origin_visit_pkey;
\else
create index concurrently origin_visit_pkey on origin_visit(origin, visit);
\endif
create index concurrently on origin_visit(date); create index concurrently on origin_visit(date); -- ?
create index concurrently origin_visit_type_date on origin_visit(type, date); create index concurrently origin_visit_type_date on origin_visit(type, date); -- ???
\if :dbflavor_deduplication
alter table origin_visit add constraint origin_visit_origin_fkey foreign key (origin) references origin(id) not valid; alter table origin_visit add constraint origin_visit_origin_fkey foreign key (origin) references origin(id) not valid;
alter table origin_visit validate constraint origin_visit_origin_fkey; alter table origin_visit validate constraint origin_visit_origin_fkey;
\endif
-- origin_visit_status -- origin_visit_status
\if :dbflavor_deduplication
create unique index concurrently origin_visit_status_pkey on origin_visit_status(origin, visit, date); create unique index concurrently origin_visit_status_pkey on origin_visit_status(origin, visit, date);
alter table origin_visit_status add primary key using index origin_visit_status_pkey; alter table origin_visit_status add primary key using index origin_visit_status_pkey;
\else
create index concurrently origin_visit_status_pkey on origin_visit_status(origin, visit, date);
\endif
\if :dbflavor_default
alter table origin_visit_status alter table origin_visit_status
add constraint origin_visit_status_origin_visit_fkey add constraint origin_visit_status_origin_visit_fkey
foreign key (origin, visit) foreign key (origin, visit)
references origin_visit(origin, visit) not valid; references origin_visit(origin, visit) not valid;
alter table origin_visit_status validate constraint origin_visit_status_origin_visit_fkey; alter table origin_visit_status validate constraint origin_visit_status_origin_visit_fkey;
\endif
-- release -- release
\if :dbflavor_deduplication
create unique index concurrently release_pkey on release(id); create unique index concurrently release_pkey on release(id);
alter table release add primary key using index release_pkey; alter table release add primary key using index release_pkey;
\else
create index concurrently release_pkey on release(id);
\endif
create index concurrently on release(target, target_type); create index concurrently on release(target, target_type); -- ?
create unique index concurrently on release(object_id); create unique index concurrently on release(object_id); -- ?
\if :dbflavor_deduplication
alter table release add constraint release_author_fkey foreign key (author) references person(id) not valid; alter table release add constraint release_author_fkey foreign key (author) references person(id) not valid;
alter table release validate constraint release_author_fkey; alter table release validate constraint release_author_fkey;
alter table release alter table release
add constraint release_date_neg_utc_offset_not_null add constraint release_date_neg_utc_offset_not_null
check (date is null or date_neg_utc_offset is not null) check (date is null or date_neg_utc_offset is not null)
not valid; not valid;
alter table release alter table release
validate constraint release_date_neg_utc_offset_not_null; validate constraint release_date_neg_utc_offset_not_null;
-- if the author is null, then the date must be null -- if the author is null, then the date must be null
alter table release add constraint release_author_date_check check ((date is null) or (author is not null)) not valid; alter table release add constraint release_author_date_check check ((date is null) or (author is not null)) not valid;
alter table release validate constraint release_author_date_check; alter table release validate constraint release_author_date_check;
\endif
-- metadata_fetcher -- metadata_fetcher
\if :dbflavor_deduplication
create unique index metadata_fetcher_pkey on metadata_fetcher(id); create unique index metadata_fetcher_pkey on metadata_fetcher(id);
alter table metadata_fetcher add primary key using index metadata_fetcher_pkey; alter table metadata_fetcher add primary key using index metadata_fetcher_pkey;
create unique index metadata_fetcher_name_version on metadata_fetcher(name, version); create unique index metadata_fetcher_name_version on metadata_fetcher(name, version);
\else
create index metadata_fetcher_pkey on metadata_fetcher(id);
create index metadata_fetcher_name_version on metadata_fetcher(name, version);
\endif
-- metadata_authority -- metadata_authority
\if :dbflavor_deduplication
create unique index concurrently metadata_authority_pkey on metadata_authority(id); create unique index concurrently metadata_authority_pkey on metadata_authority(id);
alter table metadata_authority add primary key using index metadata_authority_pkey; alter table metadata_authority add primary key using index metadata_authority_pkey;
create unique index metadata_authority_type_url on metadata_authority(type, url); create unique index metadata_authority_type_url on metadata_authority(type, url);
\else
create index concurrently metadata_authority_pkey on metadata_authority(id);
create index metadata_authority_type_url on metadata_authority(type, url);
\endif
-- raw_extrinsic_metadata -- raw_extrinsic_metadata
\if :dbflavor_deduplication
create unique index concurrently raw_extrinsic_metadata_content_authority_date_fetcher on raw_extrinsic_metadata(id, authority_id, discovery_date, fetcher_id); create unique index concurrently raw_extrinsic_metadata_content_authority_date_fetcher on raw_extrinsic_metadata(id, authority_id, discovery_date, fetcher_id);
alter table raw_extrinsic_metadata add constraint raw_extrinsic_metadata_authority_fkey foreign key (authority_id) references metadata_authority(id) not valid; alter table raw_extrinsic_metadata add constraint raw_extrinsic_metadata_authority_fkey foreign key (authority_id) references metadata_authority(id) not valid;
alter table raw_extrinsic_metadata validate constraint raw_extrinsic_metadata_authority_fkey; alter table raw_extrinsic_metadata validate constraint raw_extrinsic_metadata_authority_fkey;
alter table raw_extrinsic_metadata add constraint raw_extrinsic_metadata_fetcher_fkey foreign key (fetcher_id) references metadata_fetcher(id) not valid; alter table raw_extrinsic_metadata add constraint raw_extrinsic_metadata_fetcher_fkey foreign key (fetcher_id) references metadata_fetcher(id) not valid;
alter table raw_extrinsic_metadata validate constraint raw_extrinsic_metadata_fetcher_fkey; alter table raw_extrinsic_metadata validate constraint raw_extrinsic_metadata_fetcher_fkey;
\else
create index concurrently raw_extrinsic_metadata_content_authority_date_fetcher on raw_extrinsic_metadata(id, authority_id, discovery_date, fetcher_id);
\endif
-- object_counts -- object_counts
create unique index concurrently object_counts_pkey on object_counts(object_type); create unique index concurrently object_counts_pkey on object_counts(object_type);
alter table object_counts add primary key using index object_counts_pkey; alter table object_counts add primary key using index object_counts_pkey;
-- object_counts_bucketed -- object_counts_bucketed
create unique index concurrently object_counts_bucketed_pkey on object_counts_bucketed(line); create unique index concurrently object_counts_bucketed_pkey on object_counts_bucketed(line);
alter table object_counts_bucketed add primary key using index object_counts_bucketed_pkey; alter table object_counts_bucketed add primary key using index object_counts_bucketed_pkey;
View Options

tox.ini

-- psql variables to get the current database flavor
select swh_get_dbflavor() = 'read_replica' as dbflavor_read_replica \gset
select swh_get_dbflavor() != 'read_replica' as dbflavor_deduplication \gset
select swh_get_dbflavor() = 'mirror' as dbflavor_mirror \gset
select swh_get_dbflavor() = 'default' as dbflavor_default \gset
-- content -- content
\if :dbflavor_deduplication
create unique index concurrently content_pkey on content(sha1); create unique index concurrently content_pkey on content(sha1);
create unique index concurrently on content(sha1_git); create unique index concurrently on content(sha1_git);
create index concurrently on content(sha256);
create index concurrently on content(blake2s256);
create index concurrently on content(ctime); -- TODO use a BRIN index here (postgres >= 9.5)
create unique index concurrently on content(object_id); create unique index concurrently on content(object_id);
alter table content add primary key using index content_pkey; alter table content add primary key using index content_pkey;
\else
create index concurrently content_pkey on content(sha1);
create index concurrently on content(sha1_git);
\endif
create index concurrently on content(sha256);
create index concurrently on content(blake2s256);
create index concurrently on content(ctime); -- ?
-- origin -- origin
create unique index concurrently origin_pkey on origin(id); create unique index concurrently origin_pkey on origin(id);
\if :dbflavor_deduplication
create unique index concurrently on origin using btree(url); create unique index concurrently on origin using btree(url);
\else
create index concurrently on origin using btree(url);
\endif
create index concurrently on origin using gin (url gin_trgm_ops); create index concurrently on origin using gin (url gin_trgm_ops);
create index concurrently on origin using btree(digest(url, 'sha1')); create index concurrently on origin using btree(digest(url, 'sha1'));
\if :dbflavor_deduplication
alter table origin add primary key using index origin_pkey; alter table origin add primary key using index origin_pkey;
\endif
-- skipped_content -- skipped_content
\if :dbflavor_deduplication
alter table skipped_content add constraint skipped_content_sha1_sha1_git_sha256_key unique (sha1, sha1_git, sha256); alter table skipped_content add constraint skipped_content_sha1_sha1_git_sha256_key unique (sha1, sha1_git, sha256);
\endif
create index concurrently on skipped_content(sha1); create index concurrently on skipped_content(sha1);
create index concurrently on skipped_content(sha1_git); create index concurrently on skipped_content(sha1_git);
create index concurrently on skipped_content(sha256); create index concurrently on skipped_content(sha256);
create index concurrently on skipped_content(blake2s256); create index concurrently on skipped_content(blake2s256);
create unique index concurrently on skipped_content(object_id); create unique index concurrently on skipped_content(object_id);
\if :dbflavor_default
alter table skipped_content add constraint skipped_content_origin_fkey foreign key (origin) references origin(id) not valid; alter table skipped_content add constraint skipped_content_origin_fkey foreign key (origin) references origin(id) not valid;
alter table skipped_content validate constraint skipped_content_origin_fkey; alter table skipped_content validate constraint skipped_content_origin_fkey;
\endif
-- directory -- directory
\if :dbflavor_deduplication
create unique index concurrently directory_pkey on directory(id); create unique index concurrently directory_pkey on directory(id);
alter table directory add primary key using index directory_pkey; alter table directory add primary key using index directory_pkey;
\else
create index concurrently directory_pkey on directory(id);
\endif
create index concurrently on directory using gin (dir_entries); -- ?
create index concurrently on directory using gin (file_entries); -- ?
create index concurrently on directory using gin (rev_entries); -- ?
create unique index concurrently on directory(object_id); -- ?
create index concurrently on directory using gin (dir_entries);
create index concurrently on directory using gin (file_entries);
create index concurrently on directory using gin (rev_entries);
create unique index concurrently on directory(object_id);
-- directory_entry_dir -- directory_entry_dir
\if :dbflavor_deduplication
create unique index concurrently directory_entry_dir_pkey on directory_entry_dir(id); create unique index concurrently directory_entry_dir_pkey on directory_entry_dir(id);
alter table directory_entry_dir add primary key using index directory_entry_dir_pkey; alter table directory_entry_dir add primary key using index directory_entry_dir_pkey;
create unique index concurrently on directory_entry_dir(target, name, perms); create unique index concurrently on directory_entry_dir(target, name, perms);
\else
create index concurrently directory_entry_dir_pkey on directory_entry_dir(id);
\endif
-- directory_entry_file -- directory_entry_file
\if :dbflavor_deduplication
create unique index concurrently directory_entry_file_pkey on directory_entry_file(id); create unique index concurrently directory_entry_file_pkey on directory_entry_file(id);
alter table directory_entry_file add primary key using index directory_entry_file_pkey; alter table directory_entry_file add primary key using index directory_entry_file_pkey;
create unique index concurrently on directory_entry_file(target, name, perms); create unique index concurrently on directory_entry_file(target, name, perms);
\else
create index concurrently directory_entry_file_pkey on directory_entry_file(id);
\endif
-- directory_entry_rev -- directory_entry_rev
\if :dbflavor_deduplication
create unique index concurrently directory_entry_rev_pkey on directory_entry_rev(id); create unique index concurrently directory_entry_rev_pkey on directory_entry_rev(id);
alter table directory_entry_rev add primary key using index directory_entry_rev_pkey; alter table directory_entry_rev add primary key using index directory_entry_rev_pkey;
create unique index concurrently on directory_entry_rev(target, name, perms); create unique index concurrently on directory_entry_rev(target, name, perms);
\else
create index concurrently directory_entry_rev_pkey on directory_entry_rev(id);
\endif
-- person -- person
\if :dbflavor_deduplication
create unique index concurrently person_pkey on person(id); create unique index concurrently person_pkey on person(id);
alter table person add primary key using index person_pkey; alter table person add primary key using index person_pkey;
create unique index concurrently on person(fullname); create unique index concurrently on person(fullname);
create index concurrently on person(name); \else
create index concurrently on person(email); create index concurrently person_pkey on person(id);
create index concurrently on person(fullname);
\endif
create index concurrently on person(name); -- ?
ardumontUnsubmitted
Not Done
Inline Actions

Wondering whether those indexes (and the one before marked as -- ?) were not
installed in the first place to ease some introspection done independently from
the production (with the guest user a long time ago)...

Which finally made it through here so we got no more discrepancy between code
and production... (going the wrong way though... that is, it should have been
the other way around, dropping the unused indexes instead of adding in the
code...)

ardumont: Wondering whether those indexes (and the one before marked as `-- ?`) were not installed in the…
create index concurrently on person(email); -- ?
-- revision -- revision
\if :dbflavor_deduplication
create unique index concurrently revision_pkey on revision(id); create unique index concurrently revision_pkey on revision(id);
alter table revision add primary key using index revision_pkey; alter table revision add primary key using index revision_pkey;
\else
create index concurrently revision_pkey on revision(id);
\endif
\if :dbflavor_deduplication
alter table revision add constraint revision_author_fkey foreign key (author) references person(id) not valid; alter table revision add constraint revision_author_fkey foreign key (author) references person(id) not valid;
alter table revision validate constraint revision_author_fkey; alter table revision validate constraint revision_author_fkey;
alter table revision add constraint revision_committer_fkey foreign key (committer) references person(id) not valid; alter table revision add constraint revision_committer_fkey foreign key (committer) references person(id) not valid;
alter table revision validate constraint revision_committer_fkey; alter table revision validate constraint revision_committer_fkey;
alter table revision alter table revision
add constraint revision_date_neg_utc_offset_not_null add constraint revision_date_neg_utc_offset_not_null
check (date is null or date_neg_utc_offset is not null) check (date is null or date_neg_utc_offset is not null)
not valid; not valid;
alter table revision alter table revision
add constraint revision_committer_date_neg_utc_offset_not_null add constraint revision_committer_date_neg_utc_offset_not_null
check (committer_date is null or committer_date_neg_utc_offset is not null) check (committer_date is null or committer_date_neg_utc_offset is not null)
not valid; not valid;
alter table revision alter table revision
validate constraint revision_date_neg_utc_offset_not_null; validate constraint revision_date_neg_utc_offset_not_null;
alter table revision alter table revision
validate constraint revision_committer_date_neg_utc_offset_not_null; validate constraint revision_committer_date_neg_utc_offset_not_null;
\endif
create index concurrently on revision(directory); create index concurrently on revision(directory); -- ?
create unique index concurrently on revision(object_id); create unique index concurrently on revision(object_id); -- ?
-- revision_history -- revision_history
\if :dbflavor_deduplication
create unique index concurrently revision_history_pkey on revision_history(id, parent_rank); create unique index concurrently revision_history_pkey on revision_history(id, parent_rank);
alter table revision_history add primary key using index revision_history_pkey; alter table revision_history add primary key using index revision_history_pkey;
\else
create index concurrently revision_history_pkey on revision_history(id, parent_rank);
\endif
create index concurrently on revision_history(parent_id); create index concurrently on revision_history(parent_id); -- ?
\if :dbflavor_deduplication
alter table revision_history add constraint revision_history_id_fkey foreign key (id) references revision(id) not valid; alter table revision_history add constraint revision_history_id_fkey foreign key (id) references revision(id) not valid;
alter table revision_history validate constraint revision_history_id_fkey; alter table revision_history validate constraint revision_history_id_fkey;
\endif
-- snapshot -- snapshot
\if :dbflavor_deduplication
create unique index concurrently snapshot_pkey on snapshot(object_id); create unique index concurrently snapshot_pkey on snapshot(object_id);
alter table snapshot add primary key using index snapshot_pkey; alter table snapshot add primary key using index snapshot_pkey;
create unique index concurrently on snapshot(id); create unique index concurrently on snapshot(id);
\else
create index concurrently on snapshot(id);
\endif
-- snapshot_branch -- snapshot_branch
\if :dbflavor_deduplication
create unique index concurrently snapshot_branch_pkey on snapshot_branch(object_id); create unique index concurrently snapshot_branch_pkey on snapshot_branch(object_id);
alter table snapshot_branch add primary key using index snapshot_branch_pkey; alter table snapshot_branch add primary key using index snapshot_branch_pkey;
create unique index concurrently on snapshot_branch (target_type, target, name); create unique index concurrently on snapshot_branch (target_type, target, name);
alter table snapshot_branch add constraint snapshot_branch_target_check check ((target_type is null) = (target is null)) not valid; alter table snapshot_branch add constraint snapshot_branch_target_check check ((target_type is null) = (target is null)) not valid;
alter table snapshot_branch validate constraint snapshot_branch_target_check; alter table snapshot_branch validate constraint snapshot_branch_target_check;
alter table snapshot_branch add constraint snapshot_target_check check (target_type not in ('content', 'directory', 'revision', 'release', 'snapshot') or length(target) = 20) not valid; alter table snapshot_branch add constraint snapshot_target_check check (target_type not in ('content', 'directory', 'revision', 'release', 'snapshot') or length(target) = 20) not valid;
alter table snapshot_branch validate constraint snapshot_target_check; alter table snapshot_branch validate constraint snapshot_target_check;
create unique index concurrently on snapshot_branch (name) where target_type is null and target is null; create unique index concurrently on snapshot_branch (name) where target_type is null and target is null;
\else :dbflavor_deduplication
create index concurrently snapshot_branch_pkey on snapshot_branch(object_id);
\endif
-- snapshot_branches -- snapshot_branches
\if :dbflavor_deduplication
create unique index concurrently snapshot_branches_pkey on snapshot_branches(snapshot_id, branch_id); create unique index concurrently snapshot_branches_pkey on snapshot_branches(snapshot_id, branch_id);
alter table snapshot_branches add primary key using index snapshot_branches_pkey; alter table snapshot_branches add primary key using index snapshot_branches_pkey;
alter table snapshot_branches add constraint snapshot_branches_snapshot_id_fkey foreign key (snapshot_id) references snapshot(object_id) not valid; alter table snapshot_branches add constraint snapshot_branches_snapshot_id_fkey foreign key (snapshot_id) references snapshot(object_id) not valid;
alter table snapshot_branches validate constraint snapshot_branches_snapshot_id_fkey; alter table snapshot_branches validate constraint snapshot_branches_snapshot_id_fkey;
alter table snapshot_branches add constraint snapshot_branches_branch_id_fkey foreign key (branch_id) references snapshot_branch(object_id) not valid; alter table snapshot_branches add constraint snapshot_branches_branch_id_fkey foreign key (branch_id) references snapshot_branch(object_id) not valid;
alter table snapshot_branches validate constraint snapshot_branches_branch_id_fkey; alter table snapshot_branches validate constraint snapshot_branches_branch_id_fkey;
\else
create index concurrently snapshot_branches_pkey on snapshot_branches(snapshot_id, branch_id);
\endif
-- origin_visit -- origin_visit
\if :dbflavor_deduplication
create unique index concurrently origin_visit_pkey on origin_visit(origin, visit); create unique index concurrently origin_visit_pkey on origin_visit(origin, visit);
alter table origin_visit add primary key using index origin_visit_pkey; alter table origin_visit add primary key using index origin_visit_pkey;
\else
create index concurrently origin_visit_pkey on origin_visit(origin, visit);
\endif
create index concurrently on origin_visit(date); create index concurrently on origin_visit(date); -- ?
create index concurrently origin_visit_type_date on origin_visit(type, date); create index concurrently origin_visit_type_date on origin_visit(type, date); -- ???
\if :dbflavor_deduplication
alter table origin_visit add constraint origin_visit_origin_fkey foreign key (origin) references origin(id) not valid; alter table origin_visit add constraint origin_visit_origin_fkey foreign key (origin) references origin(id) not valid;
alter table origin_visit validate constraint origin_visit_origin_fkey; alter table origin_visit validate constraint origin_visit_origin_fkey;
\endif
-- origin_visit_status -- origin_visit_status
\if :dbflavor_deduplication
create unique index concurrently origin_visit_status_pkey on origin_visit_status(origin, visit, date); create unique index concurrently origin_visit_status_pkey on origin_visit_status(origin, visit, date);
alter table origin_visit_status add primary key using index origin_visit_status_pkey; alter table origin_visit_status add primary key using index origin_visit_status_pkey;
\else
create index concurrently origin_visit_status_pkey on origin_visit_status(origin, visit, date);
\endif
\if :dbflavor_default
alter table origin_visit_status alter table origin_visit_status
add constraint origin_visit_status_origin_visit_fkey add constraint origin_visit_status_origin_visit_fkey
foreign key (origin, visit) foreign key (origin, visit)
references origin_visit(origin, visit) not valid; references origin_visit(origin, visit) not valid;
alter table origin_visit_status validate constraint origin_visit_status_origin_visit_fkey; alter table origin_visit_status validate constraint origin_visit_status_origin_visit_fkey;
\endif
-- release -- release
\if :dbflavor_deduplication
create unique index concurrently release_pkey on release(id); create unique index concurrently release_pkey on release(id);
alter table release add primary key using index release_pkey; alter table release add primary key using index release_pkey;
\else
create index concurrently release_pkey on release(id);
\endif
create index concurrently on release(target, target_type); create index concurrently on release(target, target_type); -- ?
create unique index concurrently on release(object_id); create unique index concurrently on release(object_id); -- ?
\if :dbflavor_deduplication
alter table release add constraint release_author_fkey foreign key (author) references person(id) not valid; alter table release add constraint release_author_fkey foreign key (author) references person(id) not valid;
alter table release validate constraint release_author_fkey; alter table release validate constraint release_author_fkey;
alter table release alter table release
add constraint release_date_neg_utc_offset_not_null add constraint release_date_neg_utc_offset_not_null
check (date is null or date_neg_utc_offset is not null) check (date is null or date_neg_utc_offset is not null)
not valid; not valid;
alter table release alter table release
validate constraint release_date_neg_utc_offset_not_null; validate constraint release_date_neg_utc_offset_not_null;
-- if the author is null, then the date must be null -- if the author is null, then the date must be null
alter table release add constraint release_author_date_check check ((date is null) or (author is not null)) not valid; alter table release add constraint release_author_date_check check ((date is null) or (author is not null)) not valid;
alter table release validate constraint release_author_date_check; alter table release validate constraint release_author_date_check;
\endif
-- metadata_fetcher -- metadata_fetcher
\if :dbflavor_deduplication
create unique index metadata_fetcher_pkey on metadata_fetcher(id); create unique index metadata_fetcher_pkey on metadata_fetcher(id);
alter table metadata_fetcher add primary key using index metadata_fetcher_pkey; alter table metadata_fetcher add primary key using index metadata_fetcher_pkey;
create unique index metadata_fetcher_name_version on metadata_fetcher(name, version); create unique index metadata_fetcher_name_version on metadata_fetcher(name, version);
\else
create index metadata_fetcher_pkey on metadata_fetcher(id);
create index metadata_fetcher_name_version on metadata_fetcher(name, version);
\endif
-- metadata_authority -- metadata_authority
\if :dbflavor_deduplication
create unique index concurrently metadata_authority_pkey on metadata_authority(id); create unique index concurrently metadata_authority_pkey on metadata_authority(id);
alter table metadata_authority add primary key using index metadata_authority_pkey; alter table metadata_authority add primary key using index metadata_authority_pkey;
create unique index metadata_authority_type_url on metadata_authority(type, url); create unique index metadata_authority_type_url on metadata_authority(type, url);
\else
create index concurrently metadata_authority_pkey on metadata_authority(id);
create index metadata_authority_type_url on metadata_authority(type, url);
\endif
-- raw_extrinsic_metadata -- raw_extrinsic_metadata
\if :dbflavor_deduplication
create unique index concurrently raw_extrinsic_metadata_content_authority_date_fetcher on raw_extrinsic_metadata(id, authority_id, discovery_date, fetcher_id); create unique index concurrently raw_extrinsic_metadata_content_authority_date_fetcher on raw_extrinsic_metadata(id, authority_id, discovery_date, fetcher_id);
alter table raw_extrinsic_metadata add constraint raw_extrinsic_metadata_authority_fkey foreign key (authority_id) references metadata_authority(id) not valid; alter table raw_extrinsic_metadata add constraint raw_extrinsic_metadata_authority_fkey foreign key (authority_id) references metadata_authority(id) not valid;
alter table raw_extrinsic_metadata validate constraint raw_extrinsic_metadata_authority_fkey; alter table raw_extrinsic_metadata validate constraint raw_extrinsic_metadata_authority_fkey;
alter table raw_extrinsic_metadata add constraint raw_extrinsic_metadata_fetcher_fkey foreign key (fetcher_id) references metadata_fetcher(id) not valid; alter table raw_extrinsic_metadata add constraint raw_extrinsic_metadata_fetcher_fkey foreign key (fetcher_id) references metadata_fetcher(id) not valid;
alter table raw_extrinsic_metadata validate constraint raw_extrinsic_metadata_fetcher_fkey; alter table raw_extrinsic_metadata validate constraint raw_extrinsic_metadata_fetcher_fkey;
\else
create index concurrently raw_extrinsic_metadata_content_authority_date_fetcher on raw_extrinsic_metadata(id, authority_id, discovery_date, fetcher_id);
\endif
-- object_counts -- object_counts
create unique index concurrently object_counts_pkey on object_counts(object_type); create unique index concurrently object_counts_pkey on object_counts(object_type);
alter table object_counts add primary key using index object_counts_pkey; alter table object_counts add primary key using index object_counts_pkey;
-- object_counts_bucketed -- object_counts_bucketed
create unique index concurrently object_counts_bucketed_pkey on object_counts_bucketed(line); create unique index concurrently object_counts_bucketed_pkey on object_counts_bucketed(line);
alter table object_counts_bucketed add primary key using index object_counts_bucketed_pkey; alter table object_counts_bucketed add primary key using index object_counts_bucketed_pkey;
About · Developer information · Legal