Keep track of projects a user interacted with

What does this MR do?

This MR adds a table user_interacted_projects, populates it and changes code such that this table is maintained consistently.

For further details see https://gitlab.com/gitlab-org/gitlab-ce/issues/43460.

Migrations

Up:

== 20180223120443 CreateUserInteractedProjectsTable: migrating ================
-- create_table(:user_interacted_projects)
   -> 0.0082s
== 20180223120443 CreateUserInteractedProjectsTable: migrated (0.0083s) =======

== 20180223124427 BuildUserInteractedProjectsTable: migrating =================
-- index_exists?(:events, [:author_id, :project_id], {:name=>"events_user_interactions_temp", :where=>"project_id IS NOT NULL"})
   -> 0.0028s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- add_index(:events, [:author_id, :project_id], {:name=>"events_user_interactions_temp", :where=>"project_id IS NOT NULL", :algorithm=>:concurrently})
   -> 0.0052s
-- execute("INSERT INTO user_interacted_projects (user_id, project_id)\nSELECT e.user_id, e.project_id\nFROM (SELECT DISTINCT author_id AS user_id, project_id FROM events WHERE project_id IS NOT NULL) AS e\nLEFT JOIN user_interacted_projects ucp USING (user_id, project_id)\nWHERE ucp.user_id IS NULL\nLIMIT 100000\n")
   -> 0.0019s
-- execute("INSERT INTO user_interacted_projects (user_id, project_id)\nSELECT e.user_id, e.project_id\nFROM (SELECT DISTINCT author_id AS user_id, project_id FROM events WHERE project_id IS NOT NULL) AS e\nLEFT JOIN user_interacted_projects ucp USING (user_id, project_id)\nWHERE ucp.user_id IS NULL\nLIMIT 100000\n")
   -> 0.0013s
-- index_exists?(:events, [:author_id, :project_id], {:name=>"events_user_interactions_temp", :where=>"project_id IS NOT NULL"})
   -> 0.0048s
-- transaction_open?()
   -> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
   -> 0.0007s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- remove_index(:events, {:name=>"events_user_interactions_temp", :where=>"project_id IS NOT NULL", :algorithm=>:concurrently, :column=>[:author_id, :project_id]})
   -> 0.0071s
-- execute("ANALYZE user_interacted_projects")
   -> 0.0021s
-- index_exists?(:user_interacted_projects, [:project_id, :user_id])
   -> 0.0015s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:user_interacted_projects, [:project_id, :user_id], {:unique=>true, :algorithm=>:concurrently})
   -> 0.0071s
-- foreign_keys(:user_interacted_projects)
   -> 0.0045s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- execute("ALTER TABLE user_interacted_projects\nADD CONSTRAINT fk_0894651f08\nFOREIGN KEY (user_id)\nREFERENCES users (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0034s
-- execute("ALTER TABLE user_interacted_projects VALIDATE CONSTRAINT fk_0894651f08;")
   -> 0.0038s
-- foreign_keys(:user_interacted_projects)
   -> 0.0044s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- execute("ALTER TABLE user_interacted_projects\nADD CONSTRAINT fk_722ceba4f7\nFOREIGN KEY (project_id)\nREFERENCES projects (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0024s
-- execute("ALTER TABLE user_interacted_projects VALIDATE CONSTRAINT fk_722ceba4f7;")
   -> 0.0035s
== 20180223124427 BuildUserInteractedProjectsTable: migrated (5.0614s) ========

Down:

== 20180223120443 CreateUserInteractedProjectsTable: reverting ================
-- drop_table(:user_interacted_projects)
   -> 0.0031s
== 20180223120443 CreateUserInteractedProjectsTable: reverted (0.0031s) =======

== 20180223124427 BuildUserInteractedProjectsTable: reverting =================
-- execute("TRUNCATE user_interacted_projects")
   -> 0.0090s
-- foreign_keys(:user_interacted_projects)
   -> 0.0027s
-- remove_foreign_key(:user_interacted_projects, :users)
   -> 0.0045s
-- foreign_keys(:user_interacted_projects)
   -> 0.0021s
-- remove_foreign_key(:user_interacted_projects, :projects)
   -> 0.0038s
-- indexes(:user_interacted_projects)
   -> 0.0018s
-- transaction_open?()
   -> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
   -> 0.0004s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- remove_index(:user_interacted_projects, {:algorithm=>:concurrently, :name=>"index_user_interacted_projects_on_project_id_and_user_id"})
   -> 0.0022s
== 20180223124427 BuildUserInteractedProjectsTable: reverted (0.0273s) ========

Are there points in the code the reviewer needs to double check?

The migrations have not been tested fully yet (in staging or production). See https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/17327#note_60638997 for what has been run in staging along with timings.

The full batched migration has not yet been tested in stg/prd (and I can't do this yet because no access).

Why was this MR needed?

This is going to be the basis for improving (hopefully many) database queries that are based on the set of projects a user has access to.

Does this MR meet the acceptance criteria?

• Changelog entry added, if necessary
• Documentation created/updated
• API support added
• Tests added for this feature/bug
• Review
  • Has been reviewed by Backend
  • Has been reviewed by Database
• Conform by the merge request performance guides
• Conform by the style guides
• Squashed related commits together
• Internationalization required/considered
• End-to-end tests pass (package-qa manual pipeline job)

What are the relevant issue numbers?

Closes #43460 (closed)