Enable write lock for newly created database partitions

What does this MR do and why?

Related to #398179 (closed)

GitLab runs on two databases: main and ci. The database schemas for these two databases are the same. So both databases have the same tables. In order to prevent writing data in the wrong database, we lock tables for writes.

When a table for ci is created in main database, we automatically enable a write lock on that table. This MR will extend this write lock setting to newly created table partitions.

Implementation

Periodically, the PartitionManagementWorker is started to add new partitions to partitioned tables like audit_events. This Worker is using PartitionManager.sync_partitions method. That method is now also setting write locks on each partition.

How to set up and validate locally

Preparation: Prevent overwriting database.yml: Add gitlab/config/database.yml to protected_config_files section. You can remove this after testing.

Script to be used with gdk psql

-- Insert data directly in partitioned table on Main
\connect gitlabhq_development
INSERT INTO gitlab_partitions_dynamic.audit_events_202310 (author_id,entity_id,entity_type,details,ip_address,author_name,entity_path,target_details,created_at,target_type,target_id) values (1,1,'User','---
:with: standard
:target_id: 1
:target_type: User
:target_details: Administrator
:author_name: Administrator
',NULL,'Administrator',NULL,'Administrator','2023-10-09 12:00:00','User',1);

-- Insert data directly in partitioned table on CI
\connect gitlabhq_development_ci
INSERT INTO gitlab_partitions_dynamic.audit_events_202310 (author_id,entity_id,entity_type,details,ip_address,author_name,entity_path,target_details,created_at,target_type,target_id) values (1,1,'User','---
:with: standard
:target_id: 1
:target_type: User
:target_details: Administrator
:author_name: Administrator
',NULL,'Administrator',NULL,'Administrator','2023-10-09 12:00:00','User',1);

Decomposed database setup

On master:

• gdk reset-data
• Feature.enable(:automatic_lock_writes_on_partition_tables)
• run script using psql
• Result: No errors

On this branch

• gdk reset-data
• Feature.enable(:automatic_lock_writes_on_partition_tables)
• run script using psql
• Result: ERROR: Table: "audit_events_202310" is write protected within this Gitlab database.

Single database setup

On master:

• gdk reset-data
• Feature.enable(:automatic_lock_writes_on_partition_tables)
• Comment out ci connection in database.yml
• run script using psql
• Result: No errors

On this branch

• gdk reset-data
• Comment out ci connection in database.yml
• Feature.enable(:automatic_lock_writes_on_partition_tables)
• run script using psql
• Result: No erros

Single database, two connections setup

On master:

• gdk reset-data
• Let ci point to main database in database.yml
• Feature.enable(:automatic_lock_writes_on_partition_tables)
• run script using psql
• Result: No errors

On this branch

• gdk reset-data
• Let ci point to main database in database.yml
• Feature.enable(:automatic_lock_writes_on_partition_tables)
• run script using psql
• Result: No errors

MR acceptance checklist

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

• I have evaluated the MR acceptance checklist for this MR.