Задания

Практика 1

Скачать и запустить демонстрационную базу данных с сайта PostgresPRO. Выполните следующие запросы:

1. Вывести какие модели самолётов летают в Уфу
2. Среднее количество людей на рейсах из Санкт-Петербурга в Москву
3. Найти модель самолета с максимальным количеством сидений (учитывается что таких моделей может быть несколько)
4. Вывести рейсы число мест в которых больше чем проданных на них билетов
5. Вывести общую сумму потраченные на билеты каждым пассажиром
6. На каких местах сидел пассажир летающий чаще всего?
7. Выведите таблицу самолетов отсортированных по убыванию количества мест с дополнительным атрибутом, в котором самолёты
8. пронумерованы по частоте полётов.

Практика 2

Задание 1:

1. Создайте новую базу данных в PostgreSQL включающие две таблицы: "accounts" и "transactions". Таблица "accounts" должна содержать следующие поля: id (уникальный идентификатор), name (имя), balance (баланс). Таблица "transactions" должна содержать следующие поля: id (уникальный идентификатор), account_id (ссылка на id в таблице "accounts"), amount ( сумма).
2. Проведите проверку что PostgreSQL не допускается аномалия грязного чтения, объясните почему.
3. Проверьте, что на уровне изоляции Read Committed не предотвращается аномалия фантомного чтения.
4. Начните транзакцию с уровнем изоляции Repeatable Read(и пока не выполняйте в ней никаких команд). В другом сеансе удалите строку и зафиксируйте изменения. Видна ли строка в открытой транзакции? Что изменится, если в начале транзакции выполнить запрос,но не обращаться в нем ни к одной таблице?
5. Напишите функцию, которая позволяет выполнить перевод средств с одного счета на другой, используя транзакции. Функция должна использовать уровень изоляции транзакции "Serializable". Протестируйте функцию с использованием нескольких параллельных сеансов, чтобы убедиться, что переводы не могут быть выполнены дважды.
6. Начните транзакцию Repeatable Read и выполните какой-нибудь запрос. В другом сеансе создайте таблицу. Видно ли в первой транзакции описание таблицы в системном каталоге? Можно ли в ней прочитать строки таблицы?
7. Убедитесь, что команда DROP TABLE транзакционна.

Задание 2:

1. Установите расширение pageinspect. Создать базу данных с именем versions_db. Создать таблицу users со следующими полями: id: уникальный идентификатор пользователя (integer, primary key, auto-increment). username: имя пользователя (varchar(255)). email: электронный адрес пользователя (varchar(255)). version: версия строки (integer).

2. Создать триггер, который будет автоматически увеличивать поле version строки при любом обновлении.

3. Вставить в таблицу users строку с различными данными а затем обновите.

4. При помощи следующего запроса:

   SELECT '(0,' || lp || ')'                             AS ctid,
          t_xmin                                         as xmin,
          t_xmax                                         as xmax,
          CASE WHEN (t_infomask & 256) > 0 THEN 't' END  AS xmin_c,
          CASE WHEN (t_infomask & 512) > 0 THEN 't' END  AS xmin_a,
          CASE WHEN (t_infomask & 1024) > 0 THEN 't' END AS xmax_c,
          CASE WHEN (t_infomask & 2048) > 0 THEN 't' END AS xmax_a
   FROM heap_page_items(get_raw_page('users', 0))
   ORDER BY lp;

   Где, ctid является ссылкой на следующую, более новую, версию той же строки. У самой новой, актуальной, версии строки ctid ссылается на саму эту версию xmin и xmax определяют видимость данной версии строки в терминах начального и конечного номеров транзакций. xmin_c, xmin_a, xmax_c, xmax_a содержит ряд битов, определяющих свойства данной версии

5. Выведите информацию о версиях строк, узнав сколько версий строк щас находится в таблице и сравнить их с атрибутом ( version)

   • Опустошим таблицу при помощи TRUNCATE;
   • Начините транзакцию и вставьте новую строку и узнайте номер текущий транзакции (это можно сделать при помощи след команды: INSERT INTO users(...) VALUES (...) RETURNING *, ctid, xmin, xmax;
   • Поставьте точку сохранения и добавьте новую строку использовав команду из пункта 7.
   • Откатимся к точке сохранения и добавим новую строчку аналогично 7 и 8 пункту.
   • Выведите сведения о версиях строк.

Задание 3:

1. Создать таблицу t с полями id(integer) и name (char(2000)) с параметром filfactor = 75%.

2. Создать индекс над полем t(name)

3. Установите расширение pageinspect.

4. Создать представление которое будет включать в себя информацию о версиях строк при помощью след запроса:

   CREATE VIEW t_v AS
   SELECT '(0,' || lp || ')'                               AS ctid,
          CASE lp_flags
              WHEN 0 THEN 'unused'
              WHEN 1 THEN 'normal'
              WHEN 2 THEN 'redirect to ' || lp_off
              WHEN 3 THEN 'dead'
              END                                          AS state,
          t_xmin || CASE
                        WHEN (t_infomask & 256) > 0 THEN ' (c)'
                        WHEN (t_infomask & 512) > 0 THEN ' (a)'
                        ELSE ''
              END                                          AS xmin,
          t_xmax || CASE
                        WHEN (t_infomask & 1024) > 0 THEN ' (c)'
                        WHEN (t_infomask & 2048) > 0 THEN ' (a)'
                        ELSE ''
              END                                          AS xmax,
          CASE WHEN (t_infomask2 & 16384) > 0 THEN 't' END AS hhu,
          CASE WHEN (t_infomask2 & 32768) > 0 THEN 't' END AS hot,
          t_ctid
   FROM heap_page_items(get_raw_page('t', 0))
   ORDER BY lp;

   флаг Heap Hot Updated показывает, что надо идти по цепочке ctid, флаг Heap Only Tuple показывает, что на данную версию строки нет ссылок из индексов.

5. Спроецировать ситуацию в таблице t, при которой произойдет внутристраничная очистка без участия HOT-обновлений.

6. После воспроизвести ситуацию но уже с HOT-обновлением

★ Использовать фактор заполнения на 80% и на 50%, указать в отчете в чем разница между разными факторами. Какой лучше всего фактор заполнения использовать для этой практики?

Практика 3

Задание 1.

1. Создать базу данных с именем vacuum_db.

2. Создать таблицу users, отключив параметр автоочистки (CREATE TABLE ... WITH (autovacuum_enabled = off);), со следующими полями: id: уникальный идентификатор пользователя (integer, primary key, auto-increment). username: имя пользователя (varchar(255)). email: электронный адрес пользователя (varchar(255)). category: категория (char(3))

3. Написать скрипт заполняющий таблицу users 1000000 рандомными записями, в поле category всегда должна находиться запись ‘FOO’.

4. Используя оператор Explain выведите из таблицы users все записи которые в поле category имеют значение ‘FOO’;

5. Выполните команду ANALYZE;

6. Используя оператор Explain выведите из таблицы users все записи которые в поле category имеют значение ‘FOO’;

7. Отличаются ли методы доступа к данным и почему

8. Временно уменьшите значение maintenance_work_mem чтоб оно стало равно 1MB (не забудьте выполнить функцию pg_reload_conf())

9. Измените значение поля category на ‘BPP’

10. Запустите очистку VACUUM VERBOSE. Заодно через небольшое время в другом сеансе обратитесь к pg_stat_progress_vacuum.

11. Верните значение maintenance_work_mem к исходному значению.

Задание 2.

1. Узнать текущий размер файла данных таблицы users при помощи функции: pg_size_pretty(pg_table_size('название таблицы'))
2. Удалите 90% случайных строк (Случайность важна, чтобы в каждой странице остались какие-нибудь не удаленные строки)
3. Выполните очистку
4. Ещё раз узнайте текущий размер файла данных таблицы users и сравните его с первым пунктом. Объясните результат
5. Заново заполните таблицу и повторите пункты 1 и 2.
6. Выполните полную очистку
7. Ещё раз узнайте текущий размер файла данных таблицы users и сравните его с результатом пункта 5. Объясните результат

Задание 3.

1. Включите параметр автоочистки в таблице users
2. Настройте автоочистку на запуск при изменении 10 % строк, время «сна» — одна секунда (autovacuum_vacuum_threshold = 0,
3. autovacuum_vacuum_scale_factor = 0.1, autovacuum_naptime = '1s')
4. Заполните таблицу users до 1000000 записей
5. Узнать текущий размер файла данных таблицы users при помощи функции: pg_size_pretty(pg_table_size('название таблицы'))
6. Напишите скрипт который двадцать раз с интервалом в несколько секунд изменяет по 5 % случайных строк. Каждое изменение
7. выполняйте в отдельной транзакции.
8. При помощи pg_stat_all_tables узнайте сколько раз выполнялась автоочистка (autovacuum_count)
9. Сравнить размеры таблицы до и после обновлений
10. Совпадают ли результаты с ожидаемыми и как их объяснить?

Практики 4-8