Add organization_id to stage event hashes table
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):
- Add the
organization_id
with default value (1, exits on all installations). - Create a new unique index scoped to
organization_id
and a foreign key. - Update the application code to create records with the
organization_id
. - Remove the old unique index.
- 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
- Ensure you're on premium or ultimate.
- Seed a project:
FILTER=cycle_analytics SEED_VSA=1 bundle exec rake db:seed_fu
- Go to
rails c
and verifyorganization_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