Changeset View
Standalone View
sql/swh-indexes.sql
-- content | -- content | ||||
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 unique index concurrently on content(sha256); | 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 index concurrently on content(ctime); -- TODO use a BRIN index here (postgres >= 9.5) | ||||
create index concurrently on content(object_id); | create 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; | ||||
ardumont: This is the constraint which permits to use the upsert scheme in the swh_content_add. | |||||
Not Done Inline ActionsNo it's not. on conflict (foo, bar, baz, quux) will do something if any constraint on columns foo, bar, baz or quux fails. As each checksum column has a unique index, those will always hit before the complex constraint. olasd: No it's not. `on conflict (foo, bar, baz, quux)` will do something if any constraint on columns… | |||||
Not Done Inline Actions
So, if i understood you right (and the doc), i shall update the swh_content_add and swh_skipped_content_add with the 'on conflict do update' (in the 105.sql script).
well no longer all columns since we will drop unicity constraint on sha256 and blake2s256 (as per mentioned above).
Thus, again, rewriting the queries to add to on conflict do nothing seems appropriate. ardumont: > No it's not. on conflict (foo, bar, baz, quux) will do something if any constraint on columns… | |||||
Not Done Inline Actions
If this was possible though, which i doubt... Reading again the documentation... Also if i remember right, having directly one 'on conflict do nothing' (without specifying anything), does not work. Anyway, moving on, that point is, for now, out of scope. ardumont: > Thus, again, rewriting the queries to add to on conflict do nothing seems appropriate.
If… | |||||
-- entity_history | -- entity_history | ||||
create unique index concurrently entity_history_pkey on entity_history(id); | create unique index concurrently entity_history_pkey on entity_history(id); | ||||
create index concurrently on entity_history(uuid); | create index concurrently on entity_history(uuid); | ||||
create index concurrently on entity_history(name); | create index concurrently on entity_history(name); | ||||
alter table entity_history add primary key using index entity_history_pkey; | alter table entity_history add primary key using index entity_history_pkey; | ||||
▲ Show 20 Lines • Show All 49 Lines • ▼ Show 20 Lines | |||||
alter table origin add constraint origin_lister_fkey foreign key (lister) references listable_entity(uuid) not valid; | alter table origin add constraint origin_lister_fkey foreign key (lister) references listable_entity(uuid) not valid; | ||||
alter table origin validate constraint origin_lister_fkey; | alter table origin validate constraint origin_lister_fkey; | ||||
alter table origin add constraint origin_project_fkey foreign key (project) references entity(uuid) not valid; | alter table origin add constraint origin_project_fkey foreign key (project) references entity(uuid) not valid; | ||||
alter table origin validate constraint origin_project_fkey; | alter table origin validate constraint origin_project_fkey; | ||||
-- skipped_content | -- skipped_content | ||||
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); | ||||
Not Done Inline ActionsSame for upsert scheme used in swh_skipped_content_add. ardumont: Same for upsert scheme used in swh_skipped_content_add. | |||||
Not Done Inline ActionsThe same remark applies to this constraint as well. olasd: The same remark applies to this constraint as well. | |||||
create unique index concurrently on skipped_content(sha1); | create unique index concurrently on skipped_content(sha1); | ||||
create unique index concurrently on skipped_content(sha1_git); | create unique index concurrently on skipped_content(sha1_git); | ||||
create unique 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(object_id); | create index concurrently on skipped_content(object_id); | ||||
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; | ||||
-- fetch_history | -- fetch_history | ||||
create unique index concurrently fetch_history_pkey on fetch_history(id); | create unique index concurrently fetch_history_pkey on fetch_history(id); | ||||
▲ Show 20 Lines • Show All 184 Lines • Show Last 20 Lines |
This is the constraint which permits to use the upsert scheme in the swh_content_add.