src/dirbs/partition_utils.py
def repartition_registration_list(conn, *, num_physical_shards):
    """
    Function to repartition the registration_list table.

    Arguments:
        conn: dirbs db connection object
        num_physical_shards: number of physical shard used to repartition
    """
    with conn.cursor() as cursor, utils.db_role_setter(conn, role_name='dirbs_core_power_user'):
        # Create parent partition
        cursor.execute(
            """CREATE TABLE historic_registration_list_new (
                   LIKE historic_registration_list INCLUDING DEFAULTS
                                                   INCLUDING IDENTITY
                                                   INCLUDING CONSTRAINTS
                                                   INCLUDING STORAGE
                                                   INCLUDING COMMENTS
               )
               PARTITION BY RANGE (virt_imei_shard)
            """
        )
        _grant_perms_registration_list(conn, part_name='historic_registration_list_new')

        # Create child partitions
        create_imei_shard_partitions(conn, tbl_name='historic_registration_list_new',
                                     num_physical_shards=num_physical_shards,
                                     perms_func=_grant_perms_registration_list, fillfactor=80)

        # Insert data from original partition
        cursor.execute("""INSERT INTO historic_registration_list_new
                               SELECT *
                                 FROM historic_registration_list""")

        # Add in indexes to each partition
        idx_metadata = [IndexMetadatum(idx_cols=['imei_norm'],
                                       is_unique=True,
                                       partial_sql='WHERE end_date IS NULL')]
        add_indices(conn, tbl_name='historic_registration_list_new', idx_metadata=idx_metadata)

        # Drop old view + table, rename tables, indexes and constraints
        cursor.execute('DROP VIEW registration_list')
        cursor.execute('DROP TABLE historic_registration_list CASCADE')
        rename_table_and_indices(conn, old_tbl_name='historic_registration_list_new',
                                 new_tbl_name='historic_registration_list', idx_metadata=idx_metadata)

        cursor.execute("""CREATE OR REPLACE VIEW registration_list AS
                              SELECT imei_norm, make, model, status, virt_imei_shard, model_number, brand_name,
                                     device_type, radio_interface, device_id
                                FROM historic_registration_list
                               WHERE end_date IS NULL WITH CHECK OPTION""")
        cursor.execute("""GRANT SELECT ON registration_list
                          TO dirbs_core_classify, dirbs_core_api, dirbs_core_import_registration_list""")

    Function to repartition the registration_list table.

    Arguments:
        conn: dirbs db connection object
        num_physical_shards: number of physical shard used to repartition
    
Function to repartition the registration_list table.
Function to repartition the registration_list table .
def repartition_registration_list(conn, *, num_physical_shards):
    with conn.cursor() as cursor, utils.db_role_setter(conn, role_name='dirbs_core_power_user'):
        cursor.execute(
            """CREATE TABLE historic_registration_list_new (
                   LIKE historic_registration_list INCLUDING DEFAULTS
                                                   INCLUDING IDENTITY
                                                   INCLUDING CONSTRAINTS
                                                   INCLUDING STORAGE
                                                   INCLUDING COMMENTS
               )
               PARTITION BY RANGE (virt_imei_shard)
            """
        )
        _grant_perms_registration_list(conn, part_name='historic_registration_list_new')
        create_imei_shard_partitions(conn, tbl_name='historic_registration_list_new',
                                     num_physical_shards=num_physical_shards,
                                     perms_func=_grant_perms_registration_list, fillfactor=80)
        cursor.execute("""INSERT INTO historic_registration_list_new
                               SELECT *
                                 FROM historic_registration_list""")
        idx_metadata = [IndexMetadatum(idx_cols=['imei_norm'],
                                       is_unique=True,
                                       partial_sql='WHERE end_date IS NULL')]
        add_indices(conn, tbl_name='historic_registration_list_new', idx_metadata=idx_metadata)
        cursor.execute('DROP VIEW registration_list')
        cursor.execute('DROP TABLE historic_registration_list CASCADE')
        rename_table_and_indices(conn, old_tbl_name='historic_registration_list_new',
                                 new_tbl_name='historic_registration_list', idx_metadata=idx_metadata)
        cursor.execute("""CREATE OR REPLACE VIEW registration_list AS
                              SELECT imei_norm, make, model, status, virt_imei_shard, model_number, brand_name,
                                     device_type, radio_interface, device_id
                                FROM historic_registration_list
                               WHERE end_date IS NULL WITH CHECK OPTION""")
        cursor.execute("""GRANT SELECT ON registration_list
                          TO dirbs_core_classify, dirbs_core_api, dirbs_core_import_registration_list""")
def repartition_registration_list ( conn , * , num_physical_shards ) : with conn . cursor ( ) as cursor , utils . db_role_setter ( conn , role_name = 'dirbs_core_power_user' ) : cursor . execute ( """CREATE TABLE historic_registration_list_new (
                   LIKE historic_registration_list INCLUDING DEFAULTS
                                                   INCLUDING IDENTITY
                                                   INCLUDING CONSTRAINTS
                                                   INCLUDING STORAGE
                                                   INCLUDING COMMENTS
               )
               PARTITION BY RANGE (virt_imei_shard)
            """ ) _grant_perms_registration_list ( conn , part_name = 'historic_registration_list_new' ) create_imei_shard_partitions ( conn , tbl_name = 'historic_registration_list_new' , num_physical_shards = num_physical_shards , perms_func = _grant_perms_registration_list , fillfactor = 80 ) cursor . execute ( """INSERT INTO historic_registration_list_new
                               SELECT *
                                 FROM historic_registration_list""" ) idx_metadata = [ IndexMetadatum ( idx_cols = [ 'imei_norm' ] , is_unique = True , partial_sql = 'WHERE end_date IS NULL' ) ] add_indices ( conn , tbl_name = 'historic_registration_list_new' , idx_metadata = idx_metadata ) cursor . execute ( 'DROP VIEW registration_list' ) cursor . execute ( 'DROP TABLE historic_registration_list CASCADE' ) rename_table_and_indices ( conn , old_tbl_name = 'historic_registration_list_new' , new_tbl_name = 'historic_registration_list' , idx_metadata = idx_metadata ) cursor . execute ( """CREATE OR REPLACE VIEW registration_list AS
                              SELECT imei_norm, make, model, status, virt_imei_shard, model_number, brand_name,
                                     device_type, radio_interface, device_id
                                FROM historic_registration_list
                               WHERE end_date IS NULL WITH CHECK OPTION""" ) cursor . execute ( """GRANT SELECT ON registration_list
                          TO dirbs_core_classify, dirbs_core_api, dirbs_core_import_registration_list""" )
Function to repartition the registration_list table.
Function to repartition the registration_list table .
"""
    Function to repartition the registration_list table.

    Arguments:
        conn: dirbs db connection object
        num_physical_shards: number of physical shard used to repartition
    """ # Create parent partition # Create child partitions # Insert data from original partition # Add in indexes to each partition # Drop old view + table, rename tables, indexes and constraints
conn
dirbs db connection object
dirbs db connection object
num_physical_shards
number of physical shard used to repartition
number of physical shard used to repartition