Schema dump differs between main and ci databases
Since we switched to multiple database for GDK, gdk update
results in changes in db/structure.sql
(see bellow for the full diff).
The reason is that with multiple databases we execute db:migrate
twice - first for main
and then for ci
. After each db:migrate
we also do db:structure:dump
, so the last task executed is db:structure:dump
for ci
database.
As a result all partitioned tables from main
end up at different position in structure.sql
. The reason is that because if table has any partitions attached (which is the case for main
) it is listed earlier in structure.sql
, and if there are no partitions attached is listed in alphabetical order with all other tables. Gitlab::Database::Partitioning.sync_partitions
sync partition only for the database the models belong to, and we end up with Number of partitions: 0
for these tables in ci
. The same will happen in the opposite direction if we have partitioned table for ci
, which is not yet the case, but will change soon as we plan to partition tables from ci
.
This results in confusion and overhead during development, especially if the change includes database migrations, as developers have to restore structure.sql
to its original state, and then manually apply only the intended changes.
Click to see full diff
diff --git a/db/structure.sql b/db/structure.sql
index 2bdc8d52f0c..6b652dc6c01 100644
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -230,26 +230,6 @@ RETURN NULL;
END
$$;
-CREATE TABLE audit_events (
- id bigint NOT NULL,
- author_id integer NOT NULL,
- entity_id integer NOT NULL,
- entity_type character varying NOT NULL,
- details text,
- ip_address inet,
- author_name text,
- entity_path text,
- target_details text,
- created_at timestamp without time zone NOT NULL,
- target_type text,
- target_id bigint,
- CONSTRAINT check_492aaa021d CHECK ((char_length(entity_path) <= 5500)),
- CONSTRAINT check_83ff8406e2 CHECK ((char_length(author_name) <= 255)),
- CONSTRAINT check_97a8c868e7 CHECK ((char_length(target_type) <= 255)),
- CONSTRAINT check_d493ec90b5 CHECK ((char_length(target_details) <= 5500))
-)
-PARTITION BY RANGE (created_at);
-
CREATE TABLE batched_background_migration_job_transition_logs (
id bigint NOT NULL,
batched_background_migration_job_id bigint NOT NULL,
@@ -264,26 +244,6 @@ CREATE TABLE batched_background_migration_job_transition_logs (
)
PARTITION BY RANGE (created_at);
-CREATE TABLE incident_management_pending_alert_escalations (
- id bigint NOT NULL,
- rule_id bigint NOT NULL,
- alert_id bigint NOT NULL,
- process_at timestamp with time zone NOT NULL,
- created_at timestamp with time zone NOT NULL,
- updated_at timestamp with time zone NOT NULL
-)
-PARTITION BY RANGE (process_at);
-
-CREATE TABLE incident_management_pending_issue_escalations (
- id bigint NOT NULL,
- rule_id bigint NOT NULL,
- issue_id bigint NOT NULL,
- process_at timestamp with time zone NOT NULL,
- created_at timestamp with time zone NOT NULL,
- updated_at timestamp with time zone NOT NULL
-)
-PARTITION BY RANGE (process_at);
-
CREATE TABLE loose_foreign_keys_deleted_records (
id bigint NOT NULL,
partition bigint DEFAULT 1 NOT NULL,
@@ -297,36 +257,6 @@ CREATE TABLE loose_foreign_keys_deleted_records (
)
PARTITION BY LIST (partition);
-CREATE TABLE verification_codes (
- created_at timestamp with time zone DEFAULT now() NOT NULL,
- visitor_id_code text NOT NULL,
- code text NOT NULL,
- phone text NOT NULL,
- CONSTRAINT check_9b84e6aaff CHECK ((char_length(code) <= 8)),
- CONSTRAINT check_ccc542256b CHECK ((char_length(visitor_id_code) <= 64)),
- CONSTRAINT check_f5684c195b CHECK ((char_length(phone) <= 50))
-)
-PARTITION BY RANGE (created_at);
-
-COMMENT ON TABLE verification_codes IS 'JiHu-specific table';
-
-CREATE TABLE web_hook_logs (
- id bigint NOT NULL,
- web_hook_id integer NOT NULL,
- trigger character varying,
- url character varying,
- request_headers text,
- request_data text,
- response_headers text,
- response_body text,
- response_status character varying,
- execution_duration double precision,
- internal_error_message character varying,
- updated_at timestamp without time zone NOT NULL,
- created_at timestamp without time zone NOT NULL
-)
-PARTITION BY RANGE (created_at);
-
CREATE TABLE analytics_cycle_analytics_issue_stage_events (
stage_event_hash_id bigint NOT NULL,
issue_id bigint NOT NULL,
@@ -11628,6 +11558,26 @@ CREATE SEQUENCE atlassian_identities_user_id_seq
ALTER SEQUENCE atlassian_identities_user_id_seq OWNED BY atlassian_identities.user_id;
+CREATE TABLE audit_events (
+ id bigint NOT NULL,
+ author_id integer NOT NULL,
+ entity_id integer NOT NULL,
+ entity_type character varying NOT NULL,
+ details text,
+ ip_address inet,
+ author_name text,
+ entity_path text,
+ target_details text,
+ created_at timestamp without time zone NOT NULL,
+ target_type text,
+ target_id bigint,
+ CONSTRAINT check_492aaa021d CHECK ((char_length(entity_path) <= 5500)),
+ CONSTRAINT check_83ff8406e2 CHECK ((char_length(author_name) <= 255)),
+ CONSTRAINT check_97a8c868e7 CHECK ((char_length(target_type) <= 255)),
+ CONSTRAINT check_d493ec90b5 CHECK ((char_length(target_details) <= 5500))
+)
+PARTITION BY RANGE (created_at);
+
CREATE TABLE audit_events_external_audit_event_destinations (
id bigint NOT NULL,
namespace_id bigint NOT NULL,
@@ -16144,6 +16094,16 @@ CREATE SEQUENCE incident_management_oncall_shifts_id_seq
ALTER SEQUENCE incident_management_oncall_shifts_id_seq OWNED BY incident_management_oncall_shifts.id;
+CREATE TABLE incident_management_pending_alert_escalations (
+ id bigint NOT NULL,
+ rule_id bigint NOT NULL,
+ alert_id bigint NOT NULL,
+ process_at timestamp with time zone NOT NULL,
+ created_at timestamp with time zone NOT NULL,
+ updated_at timestamp with time zone NOT NULL
+)
+PARTITION BY RANGE (process_at);
+
CREATE SEQUENCE incident_management_pending_alert_escalations_id_seq
START WITH 1
INCREMENT BY 1
@@ -16153,6 +16113,16 @@ CREATE SEQUENCE incident_management_pending_alert_escalations_id_seq
ALTER SEQUENCE incident_management_pending_alert_escalations_id_seq OWNED BY incident_management_pending_alert_escalations.id;
+CREATE TABLE incident_management_pending_issue_escalations (
+ id bigint NOT NULL,
+ rule_id bigint NOT NULL,
+ issue_id bigint NOT NULL,
+ process_at timestamp with time zone NOT NULL,
+ created_at timestamp with time zone NOT NULL,
+ updated_at timestamp with time zone NOT NULL
+)
+PARTITION BY RANGE (process_at);
+
CREATE SEQUENCE incident_management_pending_issue_escalations_id_seq
START WITH 1
INCREMENT BY 1
@@ -21846,6 +21816,19 @@ CREATE SEQUENCE users_statistics_id_seq
ALTER SEQUENCE users_statistics_id_seq OWNED BY users_statistics.id;
+CREATE TABLE verification_codes (
+ created_at timestamp with time zone DEFAULT now() NOT NULL,
+ visitor_id_code text NOT NULL,
+ code text NOT NULL,
+ phone text NOT NULL,
+ CONSTRAINT check_9b84e6aaff CHECK ((char_length(code) <= 8)),
+ CONSTRAINT check_ccc542256b CHECK ((char_length(visitor_id_code) <= 64)),
+ CONSTRAINT check_f5684c195b CHECK ((char_length(phone) <= 50))
+)
+PARTITION BY RANGE (created_at);
+
+COMMENT ON TABLE verification_codes IS 'JiHu-specific table';
+
CREATE TABLE vulnerabilities (
id bigint NOT NULL,
milestone_id bigint,
@@ -22328,6 +22311,23 @@ CREATE SEQUENCE vulnerability_user_mentions_id_seq
ALTER SEQUENCE vulnerability_user_mentions_id_seq OWNED BY vulnerability_user_mentions.id;
+CREATE TABLE web_hook_logs (
+ id bigint NOT NULL,
+ web_hook_id integer NOT NULL,
+ trigger character varying,
+ url character varying,
+ request_headers text,
+ request_data text,
+ response_headers text,
+ response_body text,
+ response_status character varying,
+ execution_duration double precision,
+ internal_error_message character varying,
+ updated_at timestamp without time zone NOT NULL,
+ created_at timestamp without time zone NOT NULL
+)
+PARTITION BY RANGE (created_at);
+
CREATE SEQUENCE web_hook_logs_id_seq
START WITH 1
INCREMENT BY 1