Skip to content

Add organization_id to stage event hashes table

Adam Hegyi requested to merge 462758-add-organization-id-to-stage-event-hashes into master

What does this MR do and why?

This MR adds the organization_id column to the analytics_cycle_analytics_stage_event_hashes table to prepare it for the cells architecture.

Strategy (we make the assumption that customers are not using non-default organization):

  1. Add the organization_id with default value (1, exits on all installations).
  2. Create a new unique index scoped to organization_id and a foreign key.
  3. Update the application code to create records with the organization_id.
  4. Remove the old unique index.
  5. Remove the default value from the organization_id column.

Database:

Up 
main: == [advisory_lock_connection] object_id: 128220, pg_backend_pid: 727094
main: == 20240618081110 AddOrganizationIdColumnToStageEventHashes: migrating ========
main: -- add_column(:analytics_cycle_analytics_stage_event_hashes, :organization_id, :bigint, {:null=>false, :default=>1})
main:    -> 0.0096s
main: == 20240618081110 AddOrganizationIdColumnToStageEventHashes: migrated (0.0140s)

main: == [advisory_lock_connection] object_id: 128220, pg_backend_pid: 727094
ci: == [advisory_lock_connection] object_id: 128520, pg_backend_pid: 727096
ci: == 20240618081110 AddOrganizationIdColumnToStageEventHashes: migrating ========
ci: -- add_column(:analytics_cycle_analytics_stage_event_hashes, :organization_id, :bigint, {:null=>false, :default=>1})
ci:    -> 0.0016s
ci: == 20240618081110 AddOrganizationIdColumnToStageEventHashes: migrated (0.0089s)

ci: == [advisory_lock_connection] object_id: 128520, pg_backend_pid: 727096
main: == [advisory_lock_connection] object_id: 128720, pg_backend_pid: 727099
main: == 20240618081525 AddStageEventHashesOrganizationIdIndex: migrating ===========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0026s
main: -- index_exists?(:analytics_cycle_analytics_stage_event_hashes, [:organization_id, :hash_sha256], {:name=>"index_cycle_analytics_stage_event_hashes_on_org_id_sha_256", :unique=>true, :algorithm=>:concurrently})
main:    -> 0.0026s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- add_index(:analytics_cycle_analytics_stage_event_hashes, [:organization_id, :hash_sha256], {:name=>"index_cycle_analytics_stage_event_hashes_on_org_id_sha_256", :unique=>true, :algorithm=>:concurrently})
main:    -> 0.0018s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20240618081525 AddStageEventHashesOrganizationIdIndex: migrated (0.0202s) ==

main: == [advisory_lock_connection] object_id: 128720, pg_backend_pid: 727099
ci: == [advisory_lock_connection] object_id: 128860, pg_backend_pid: 727101
ci: == 20240618081525 AddStageEventHashesOrganizationIdIndex: migrating ===========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- index_exists?(:analytics_cycle_analytics_stage_event_hashes, [:organization_id, :hash_sha256], {:name=>"index_cycle_analytics_stage_event_hashes_on_org_id_sha_256", :unique=>true, :algorithm=>:concurrently})
ci:    -> 0.0021s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:analytics_cycle_analytics_stage_event_hashes, [:organization_id, :hash_sha256], {:name=>"index_cycle_analytics_stage_event_hashes_on_org_id_sha_256", :unique=>true, :algorithm=>:concurrently})
ci:    -> 0.0009s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20240618081525 AddStageEventHashesOrganizationIdIndex: migrated (0.0158s) ==

ci: == [advisory_lock_connection] object_id: 128860, pg_backend_pid: 727101
main: == [advisory_lock_connection] object_id: 129840, pg_backend_pid: 727104
main: == 20240618082217 AddStageEventHashesOrganizationIdForeignKey: migrating ======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE analytics_cycle_analytics_stage_event_hashes ADD CONSTRAINT fk_0839874e4f FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0094s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- execute("ALTER TABLE analytics_cycle_analytics_stage_event_hashes VALIDATE CONSTRAINT fk_0839874e4f;")
main:    -> 0.0016s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20240618082217 AddStageEventHashesOrganizationIdForeignKey: migrated (0.0299s)

main: == [advisory_lock_connection] object_id: 129840, pg_backend_pid: 727104
ci: == [advisory_lock_connection] object_id: 130580, pg_backend_pid: 727106
ci: == 20240618082217 AddStageEventHashesOrganizationIdForeignKey: migrating ======
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE analytics_cycle_analytics_stage_event_hashes ADD CONSTRAINT fk_0839874e4f FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0018s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- execute("ALTER TABLE analytics_cycle_analytics_stage_event_hashes VALIDATE CONSTRAINT fk_0839874e4f;")
ci:    -> 0.0016s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0001s
ci: == 20240618082217 AddStageEventHashesOrganizationIdForeignKey: migrated (0.0272s)

ci: == [advisory_lock_connection] object_id: 130580, pg_backend_pid: 727106
main: == [advisory_lock_connection] object_id: 130840, pg_backend_pid: 727109
main: == 20240618091008 RemoveStageEventHashesUniqueIndex: migrating ================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0006s
main: -- indexes(:analytics_cycle_analytics_stage_event_hashes)
main:    -> 0.0023s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:analytics_cycle_analytics_stage_event_hashes, {:algorithm=>:concurrently, :name=>"index_cycle_analytics_stage_event_hashes_on_hash_sha_256"})
main:    -> 0.0008s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240618091008 RemoveStageEventHashesUniqueIndex: migrated (0.0112s) =======

main: == [advisory_lock_connection] object_id: 130840, pg_backend_pid: 727109
ci: == [advisory_lock_connection] object_id: 148300, pg_backend_pid: 727111
ci: == 20240618091008 RemoveStageEventHashesUniqueIndex: migrating ================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- indexes(:analytics_cycle_analytics_stage_event_hashes)
ci:    -> 0.0023s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- remove_index(:analytics_cycle_analytics_stage_event_hashes, {:algorithm=>:concurrently, :name=>"index_cycle_analytics_stage_event_hashes_on_hash_sha_256"})
ci:    -> 0.0009s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20240618091008 RemoveStageEventHashesUniqueIndex: migrated (0.0151s) =======
Down 
main: == [advisory_lock_connection] object_id: 127620, pg_backend_pid: 721445
main: == 20240618091008 RemoveStageEventHashesUniqueIndex: reverting ================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0022s
main: -- index_exists?(:analytics_cycle_analytics_stage_event_hashes, :hash_sha256, {:name=>"index_cycle_analytics_stage_event_hashes_on_hash_sha_256", :unique=>true, :algorithm=>:concurrently})
main:    -> 0.0027s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0006s
main: -- indexes(:analytics_cycle_analytics_stage_event_hashes)
main:    -> 0.0020s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:analytics_cycle_analytics_stage_event_hashes, {:algorithm=>:concurrently, :name=>"index_cycle_analytics_stage_event_hashes_on_hash_sha_256"})
main:    -> 0.0008s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: -- add_index(:analytics_cycle_analytics_stage_event_hashes, :hash_sha256, {:name=>"index_cycle_analytics_stage_event_hashes_on_hash_sha_256", :unique=>true, :algorithm=>:concurrently})

ci: == [advisory_lock_connection] object_id: 127620, pg_backend_pid: 722018
ci: == 20240618091008 RemoveStageEventHashesUniqueIndex: reverting ================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0021s
ci: -- index_exists?(:analytics_cycle_analytics_stage_event_hashes, :hash_sha256, {:name=>"index_cycle_analytics_stage_event_hashes_on_hash_sha_256", :unique=>true, :algorithm=>:concurrently})
ci:    -> 0.0020s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:analytics_cycle_analytics_stage_event_hashes, :hash_sha256, {:name=>"index_cycle_analytics_stage_event_hashes_on_hash_sha_256", :unique=>true, :algorithm=>:concurrently})
ci:    -> 0.0091s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20240618091008 RemoveStageEventHashesUniqueIndex: reverted (0.0286s) =======


main: == [advisory_lock_connection] object_id: 127620, pg_backend_pid: 722441
main: == 20240618082217 AddStageEventHashesOrganizationIdForeignKey: reverting ======
main: -- remove_foreign_key(:analytics_cycle_analytics_stage_event_hashes, {:column=>:organization_id})
main:    -> 0.0028s
main: == 20240618082217 AddStageEventHashesOrganizationIdForeignKey: reverted (0.0295s)
main: == [advisory_lock_connection] object_id: 127620, pg_backend_pid: 722441

ci: == [advisory_lock_connection] object_id: 127620, pg_backend_pid: 723122
ci: == 20240618082217 AddStageEventHashesOrganizationIdForeignKey: reverting ======
ci: -- remove_foreign_key(:analytics_cycle_analytics_stage_event_hashes, {:column=>:organization_id})
ci:    -> 0.0028s
ci: == 20240618082217 AddStageEventHashesOrganizationIdForeignKey: reverted (0.0323s)


main: == [advisory_lock_connection] object_id: 127620, pg_backend_pid: 723796
main: == 20240618081525 AddStageEventHashesOrganizationIdIndex: reverting ===========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0021s
main: -- indexes(:analytics_cycle_analytics_stage_event_hashes)
main:    -> 0.0024s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:analytics_cycle_analytics_stage_event_hashes, {:algorithm=>:concurrently, :name=>"index_cycle_analytics_stage_event_hashes_on_org_id_sha_256"})
main:    -> 0.0010s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20240618081525 AddStageEventHashesOrganizationIdIndex: reverted (0.0165s) ==


ci: == [advisory_lock_connection] object_id: 127620, pg_backend_pid: 724198
ci: == 20240618081525 AddStageEventHashesOrganizationIdIndex: reverting ===========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0023s
ci: -- indexes(:analytics_cycle_analytics_stage_event_hashes)
ci:    -> 0.0022s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- remove_index(:analytics_cycle_analytics_stage_event_hashes, {:algorithm=>:concurrently, :name=>"index_cycle_analytics_stage_event_hashes_on_org_id_sha_256"})
ci:    -> 0.0008s


main: == [advisory_lock_connection] object_id: 127620, pg_backend_pid: 725764
main: == 20240618081110 AddOrganizationIdColumnToStageEventHashes: reverting ========
main: -- remove_column(:analytics_cycle_analytics_stage_event_hashes, :organization_id, :bigint, {:null=>false, :default=>1})
main:    -> 0.0014s
main: == 20240618081110 AddOrganizationIdColumnToStageEventHashes: reverted (0.0146s)
main: == [advisory_lock_connection] object_id: 127620, pg_backend_pid: 725764


ci: == [advisory_lock_connection] object_id: 127620, pg_backend_pid: 726229
ci: == 20240618081110 AddOrganizationIdColumnToStageEventHashes: reverting ========
ci: -- remove_column(:analytics_cycle_analytics_stage_event_hashes, :organization_id, :bigint, {:null=>false, :default=>1})
ci:    -> 0.0092s
ci: == 20240618081110 AddOrganizationIdColumnToStageEventHashes: reverted (0.0199s)

Validate

  1. Ensure you're on premium or ultimate.
  2. Seed a project: FILTER=cycle_analytics SEED_VSA=1 bundle exec rake db:seed_fu
  3. Go to rails c and verify organization_id presence: Analytics::CycleAnalytics::Stage.last.stage_event_hash.organization_id

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #462758 (closed)

Edited by Adam Hegyi

Merge request reports