Удаление секции без эксклюзивной блокировки на родительскую таблицу

Актуально для СУБД Pangolin версии 6.1.0 и выше в связи с добавлением доработки ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY ядра PostgreSQL 14 версии.

Раздел содержит способ инициализации отсоединения секции без потери доступа к самой таблице с помощью конструкции DETACH PARTITION ... CONCURRENTLY.

Ручное секционирование

Тест 1. Выполнение команды SELECT при отсоединении секции

1. Создайте таблицы, секции, данные:

   drop table if exists test_table_mnl_part;
   create table test_table_mnl_part
   (
    id bigint,
    someint bigint,
    txt text,
    isThat bool
   ) PARTITION BY RANGE (someint);
   
   do $$
   declare
   n_from int = 1;
   n_to int = 100000;
   begin
     FOR i IN 1..100 loop  
        execute
            'CREATE TABLE test_table_mnl_part' || i || ' PARTITION OF test_table_mnl_part
                    FOR VALUES FROM (' || n_from || ') TO (' || n_to || ');';
        n_from = n_to;
        n_to = n_from + 100000;
    END LOOP;
   end;
   $$;
   
   insert into test_table_mnl_part(id, someint, txt, isThat)
     select x, x, md5((random() * 10)::text), false from generate_series(1,9999999) as x;

2. Подготовьте три терминала (t1, t2, t3), в каждом из которых будут выполняться команды из тестов.

   • Терминал t1. Открытие транзакции и обращение к таблице:

     begin;
     select count(*) from test_table_mnl_part;

   • Терминал t2. Отсоединение секции в режиме concurrently:

     alter table test_table_mnl_part detach partition test_table_mnl_part10 concurrently;

     Заметьте, что при выполнении команды ALTER TABLE произошло зависание, транзакция не завершится, пока t1 не будет завершена («закомичена»). Тем не менее, в таблице блокировок можно убедиться, что блокировок на отношение (relation) нет, но есть на virtualxid.

     А сама секция отмечена как DETACH PENDING (\d+ test_table_mnl_part):

     ...
     test_table_mnl_part10 FOR VALUES FROM ('900000') TO ('1000000') (DETACH PENDING),
     ...
     */

   • Терминал t3:

     Проверка блокировок с помощью таблицы pg_locks:

     select pl.locktype, pl.relation, pl.virtualxid, pl.transactionid, pl.pid, pl.mode, psa.query
         from pg_locks as pl
         left join pg_stat_activity as psa on pl.pid = psa.pid
     order by psa.query;
     
     /*
     locktype  | relation | virtualxid | transactionid |   pid   |      mode       |                                             query                                            
     ------------+----------+------------+---------------+---------+-----------------+-----------------------------------------------------------------------------------------------
      virtualxid |          | 16/18      |               | 3149851 | ShareLock       | alter table test_table_mnl_part detach partition test_table_mnl_part10 concurrently;
       virtualxid |          | 14/19      |               | 3149851 | ExclusiveLock   | alter table test_table_mnl_part detach partition test_table_mnl_part10 concurrently;
        relation   |    19340 |            |               | 3149849 | AccessShareLock | select count(*) from test_table_mnl_part;
         relation   |    19335 |            |               | 3149849 | AccessShareLock | select count(*) from test_table_mnl_part;
     ...
     */

     Запрос количества строк у таблицы:

     select count(*) from test_table_mnl_part;
     /*
     count 
     ---------
      9899999
     (1 row)
     */

3. Ознакомьтесь с результатом выполнения команд:

   Несмотря на то, что секция test_table_mnl_part10 до сих пор числится в составе таблицы test_table_mnl_part, она помечается как DETACH PENDING и не учитывается в запросе SELECT в сессии №3.

   Внимание!

   Если по каким-то причинам команда ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY была завершена некорректно или вовсе отменена, секция так и останется в состоянии DETACH PENDING. Для завершения операции по отсоединению необходимо будет выполнить команду ALTER TABLE ... DETACH PARTITION ... FINALIZE.

4. Заключительные действия произведите в терминале t1. Завершите транзакции и удалите созданные ранее тестовые таблицы:

   end;
   drop table if exists test_table_mnl_part;
   drop table if exists test_table_mnl_part10;

Тест 2. Присоединение новой секции при отсоединении старой

1. Создайте таблицы, секции, данные:

   drop table if exists test_table_mnl_part;
   create table test_table_mnl_part
   (
    id bigint,
    someint bigint,
    txt text,
    isThat bool
   ) PARTITION BY RANGE (someint);
   
   do $$
   declare
   n_from int = 1;
   n_to int = 100000;
   begin
    FOR i IN 1..100 loop
        execute
            'CREATE TABLE test_table_mnl_part' || i || ' PARTITION OF test_table_mnl_part
                    FOR VALUES FROM (' || n_from || ') TO (' || n_to || ');';
        n_from = n_to;
        n_to = n_from + 100000;
    END LOOP;
   end;
   $$;
    
   insert into test_table_mnl_part(id, someint, txt, isThat)
       select x, x, md5((random() * 10)::text), false from generate_series(1,9999999) as x;

2. Подготовьте три терминала (t1, t2, t3), в каждом из которых будут выполняться команды из тестов.

   • Терминал t1. Открытие транзакции и обращение к таблице:

     begin;
     select count(*) from test_table_mnl_part;

   • Терминал t2. Отсоединение секции в режиме concurrently:

     alter table test_table_mnl_part detach partition test_table_mnl_part10 concurrently;

   • Терминал t3. Создание новой секции, наполнение данными и присоединение:

     drop table if exists test_table_mnl_part_n1000;
     create table test_table_mnl_part_n1000
     (
      id bigint,
      someint bigint,
      txt text,
      isThat bool
     );
     
     insert into test_table_mnl_part_n1000(id, someint, txt, isThat)
         select x, x, md5((random() * 10)::text), false from generate_series(10000010,10000090) as x;
     
     alter table test_table_mnl_part attach partition test_table_mnl_part_n1000 FOR VALUES FROM ('10000000') TO ('10100000');

3. Ознакомьтесь с результатом выполнения команд:

   Команда по присоединению новой секции выполнена успешно, секция вставлена несмотря на то, что t1 и t2 не завершены. Для того, чтобы убедиться, что новая секция есть в таблице, можно выполнить следущий запрос:

   SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
    FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
   WHERE c.oid = i.inhrelid AND i.inhparent = 'test_table_mnl_part'::regclass
   ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;

   Внимание!

   Создание таблицы, как секции, сразу невозможно, пока t1 не будет завершена. В такой конструкции произойдет зависание:

   CREATE TABLE test_table_mnl_part_n1000 PARTITION OF test_table_mnl_part FOR VALUES FROM ('10000000') TO ('10100000');

4. Произведите заключительные действия в терминале t1. Завершите транзакции и удалите созданные ранее тестовые таблицы:

   end;
   drop table if exists test_table_mnl_part;
   drop table if exists test_table_mnl_part10;
   drop table if exists test_table_mnl_part_n1000;

Автоматическое секционирование

1. Создайте секционированную таблицу через генерацию данных и секций:

   drop table if exists test_table_au_part;
   create table test_table_au_part
   (
       id bigint,
       someint bigint,
       txt text,
       isThat bool
   ) auto partition by range (someint) period (100000::bigint);
    
   insert into test_table_au_part(id, someint, txt, isThat)
       select x, x, md5((random() * 10)::text), false from    generate_series(1,9999999) as x;

2. Подготовить три терминала (t1, t2, t3), в каждом из которых будут выполняться команды из тестов.

   • Терминал t1. Открытие транзакции и обращение к таблице:

     begin;
     select count(*) from test_table_au_part;

   • Терминал t2. Отсоединение секции:

     alter table test_table_au_part detach partition test_table_au_part_p9 concurrently;

   • Терминал t3. Выполнение команды INSERT, которая должна сгенерировать новую секцию:

     insert into test_table_au_part(id, someint, txt, isThat)
         select x, x, md5((random() * 10)::text), false from generate_series(10000010,10000090) as x;

3. Ознакомьтесь с результатом выполнения команд:

   Несмотря на то, что операция из терминала t2 не может быть до конца выполнена, пока открыта транзакция на терминале t1, операция из третьего терминала t3 выполнилась успешно. Была сгенерирована новая секция, и в неё были добавлены новые строки.

   Существует возможность обращения к таблице pg_locks, чтобы удостовериться, что блокировка установлена на виртуальный идентификатор (virtualxid), а не на таблицу (relation):

   select pl.locktype, pl.relation, pl.virtualxid, pl.transactionid, pl.pid, pl.mode, psa.query
    from pg_locks as pl
    left join pg_stat_activity as psa on pl.pid = psa.pid
   order by psa.query;
    
   /*
   locktype  |relation|virtualxid|transactionid|pid    |mode              |query                                                                                  |
   ----------+--------+----------+-------------+-------+---------------+-   ----------------------------------------------------------------------   -------------+
   virtualxid|        |14/16     |             |3149851|ExclusiveLock     |alter table test_table_au_part detach partition test_table_au_part_p9    concurrently; |
   virtualxid|        |16/10     |             |3149851|ShareLock         |alter table test_table_au_part detach partition test_table_au_part_p9    concurrently; |
   relation  |   18721|          |                |3149849|AccessShareLock|select count(*) from    test_table_au_part;                                            |
   relation  |   18716|          |                |3149849|AccessShareLock|select count(*) from    test_table_au_part;                                            |
   ...
   */

4. Произведите заключительные действия в терминале t1. Завершите транзакции и удалите созданные ранее тестовые таблицы:

   end;
   drop table if exists test_table_au_part;
   drop table if exists test_table_au_part_p9;

Итоги тестирования

1. При выполнении detach concurrently операции на таблице не блокируются.
2. Если операция detach concurrently завершена неудачно, потребуется повторить операцию с ключом finalize.
3. Для создания новой секции нужно использовать конструкцию CREATE TABLE -> ALTER TABLE ... ATTACH, конструкция CREATE TABLE ... PARTITION OF ... будет зависеть от ранее открытых транзакций и ожидать их завершения.
4. При использовании автоматического секционирования создание новых секций проходит без проблем и задержек.

Ссылки на дополнительные источники

1. Разделение таблицы
2. DETACH PARTITION