Skip to content

Cleanup PK conversion for events table

Krasimir Angelov requested to merge 414777/events/cleanup into master

What does this MR do and why?

For GitLab.com, remove events.target_id_convert_to_bigint integer columns and the related trigger.

This step from the process to convert column to bigint is described here - https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations.html#remove-the-trigger-and-old-integer-columns-release-n--2.

The column swaps for this table were done with Swap events.target_id to bigint (!126786 - merged).

UPDATE Jul. 25th 19:32 UTC

  • target_id was converted to a bigint
  • index_events_on_target_type_and_target_id_bigint_fingerprint was created and renamed to index_events_on_target_type_and_target_id_and_fingerprint
Table "public.events"
           Column            |           Type           | Collation | Nullable |              Default               
-----------------------------+--------------------------+-----------+----------+------------------------------------
 project_id                  | integer                  |           |          | 
 author_id                   | integer                  |           | not null | 
 target_id_convert_to_bigint | integer                  |           |          | 
 created_at                  | timestamp with time zone |           | not null | 
 updated_at                  | timestamp with time zone |           | not null | 
 action                      | smallint                 |           | not null | 
 target_type                 | character varying        |           |          | 
 group_id                    | bigint                   |           |          | 
 fingerprint                 | bytea                    |           |          | 
 id                          | bigint                   |           | not null | nextval('events_id_seq'::regclass)
 target_id                   | bigint                   |           |          | 
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
    "index_events_on_target_type_and_target_id_and_fingerprint" UNIQUE, btree (target_type, target_id, fingerprint)
    "analytics_index_events_on_created_at_and_author_id" btree (created_at, author_id)
    "index_events_author_id_project_id_action_target_type_created_at" btree (author_id, project_id, action, target_type, created_at)
    "index_events_for_followed_users" btree (author_id, target_type, action, id)
    "index_events_for_group_activity" btree (group_id, target_type, action, id) WHERE group_id IS NOT NULL
    "index_events_for_project_activity" btree (project_id, target_type, action, id)
    "index_events_on_author_id_and_created_at" btree (author_id, created_at)
    "index_events_on_author_id_and_created_at_merge_requests" btree (author_id, created_at) WHERE target_type::text = 'MergeRequest'::text
    "index_events_on_author_id_and_id" btree (author_id, id)
    "index_events_on_created_at_and_id" btree (created_at, id) WHERE created_at > '2021-08-27 00:00:00+00'::timestamp with time zone
    "index_events_on_group_id_and_id" btree (group_id, id) WHERE group_id IS NOT NULL
    "index_events_on_group_id_partial" btree (group_id) WHERE group_id IS NOT NULL
    "index_events_on_project_id_and_created_at" btree (project_id, created_at)
    "index_events_on_project_id_and_id" btree (project_id, id)
    "index_on_events_to_improve_contribution_analytics_performance" btree (project_id, target_type, action, created_at, author_id, id)
Check constraints:
    "check_97e06e05ad" CHECK (octet_length(fingerprint) <= 128)
Foreign-key constraints:
    "fk_61fbf6ca48" FOREIGN KEY (group_id) REFERENCES namespaces(id) ON DELETE CASCADE
    "fk_edfd187b6f" FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
    "fk_rails_0434b48643" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
Referenced by:
    TABLE "push_event_payloads" CONSTRAINT "fk_36c74129da" FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
Triggers:
    trigger_cd1aeb22b34a BEFORE INSERT OR UPDATE ON events FOR EACH ROW EXECUTE FUNCTION trigger_cd1aeb22b34a()

Related to #414777 (closed).

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Leonardo da Rosa

Merge request reports