Проект для развертывания и изучения PostgreSQL на примере предложенной в книгах тестовой базы данных. Запросы и тд выполнялись в psql. Тут будут приведены некоторые из них.
https://postgrespro.ru/education
PostgreSQL. Основы языка SQL
PostgreSQL. Профессиональный SQL
docker compose up -d
docker exec -it pg_ed-database-1 bash
psql app app -d demo
\i demo-small-20170815.sql
\d
CREATE UNIQUE INDEX aircrafts_unique_model_key ON aircrafts_tmp ( lower( model ) );
До
Создаем частичный индекс
CREATE INDEX bookings_book_date_part_key ON bookings ( book_date ) WHERE total_amount > 1000000;
После
На основе запроса создаем представление,
CREATE VIEW seats_by_fare_cond AS
SELECT aircraft_code, fare_conditions, count( * )
FROM seats
GROUP BY aircraft_code, fare_conditions
ORDER BY aircraft_code, fare_conditions;
CREATE TABLE aircrafts_tmp
AS SELECT * FROM aircrafts;
Апдейтим значние поля в транзакции, но не комитим её
Во втором терминале проверям значние поля которое обновляли в 1 терминале
Таким образом, вторая транзакция не видит изменение значения атрибута range, произведенное в первой — незафиксированной — транзакции. Это объясняется тем, что в PostgreSQL реализация уровня изоляции Read Uncommitted более строгая, чем того требует стандарт языка SQL. Фактически этот уровень тождественен уровню изоляции Read Committed.
На первом терминале выполним следующие команды:
На втором терминале вполняем:
Транзакция перешла в режим ожидания. Команда UPDATE в 1 терминале заблокировала строку в таблице aircrafts_tmp.
Выполним в 1 терминале команду COMMIT; и увидим что во 2 терминале команда заверщилась.
Как видно, были произведены оба изменения. Команда UPDATE во второй транзакции, получив возможность заблокировать строку после завершения первой транзакции и снятия ею блокировки с этой строки, перечитывает строку таблицы и потому обновляет строку, уже обновленную в только что зафиксированной транзакции. Таким образом, эффекта потерянных обновлений не возникает.
Для иллюстрации эффекта неповторяющегося чтения данных проведем совсем простой эксперимент также на двух терминалах. На первом терминале:
Во втром терминале:
Повторим выборку в первой транзакции:
Видим, что теперь получен другой результат, т. к. вторая транзакция завершилась в момент времени между двумя запросами. Таким образом, налицо эффект неповторяющегося чтения данных, который является допустимым на уровне изоляции Read Committed.
Первый терминал:
Второй терминал:
Перейдем в первый терминал:
На первом терминале ничего не изменилось: фантомные строки не видны, и также не видны изменения в уже существующих строках. Это объясняется тем, что снимок данных выполняется на момент начала выполнения первого запроса транзакции. Завершим транзакцию в 1 терминале.
Как видим, одна строка добавлена, а значение атрибута range у самолета Airbus A320-200 стало на 100 больше, чем было.
Для проведения эксперимента создадим специальную таблицу, в которой будет всего два столбца: один — числовой, а второй — текстовый. Назовем эту таблицу modes.
CREATE TABLE modes (num integer,mode text);
Добавим в таблцу две строки:
INSERT INTO modes VALUES ( 1, 'LOW' ), ( 2, 'HIGH' );
Содерджимое таблицы имеет вид
На первом терминале:
На втором терминале тоже начнем транзакцию и обновим другую строку из тех двух строк, которые были показаны выше.
Изменение, произведенное в первой транзакции, вторая транзакция не видит, поскольку на уровне изоляции Serializable каждая транзакция работает с тем снимком базы данных, который был сделан непосредственно перед выполнением ее первого оператора. Поэтому обновляется только одна строка, та, в которой значение поля mode было равно HIGH изначально. Обратите внимание, что обе команды UPDATE были выполнены, ни одна из них не ожидает завершения другой транзакции.
Посмотрим, что получилось в первой транзакции:
А во второй транзакции:
Завершим транзакции.
Во втором терминале видим:
Какое же изменение будет зафиксировано? То, которое сделала транзакция, первой выполнившая фиксацию изменений.
Запрос с вложенными подзапросами
Попробуем ускорить выполнение этого запроса
Время выполнения заметно уменьшилось по сравнению с исходным запросом.
Ускорение достигнуто, но за счет усложнения запроса. Однако можно и упростить его, не потеряв в скорости выполнения.
Вложенный подзапрос, агрегирующий таблицу «Посадочные талоны» (boarding_passes), выполняется дважды. Если бы мы смогли сохранить результат первого выполнения этого подзапроса во временную таблицу, нам не пришлось бы выполнять то же самое действие повторно.
Мы избежали вложенных подзапросов, в результате наш запрос стало легче читать и, что очень важно, время его выполнения еще уменьшилось.
В конструкции WITH можно написать несколько подзапросов, при этом один подзапрос может ссылаться на другие, то есть обращаться к результатам их выполнения. Конечно, циклические ссылки не допускаются. Давайте перенесем оба подзапроса из главного запроса в конструкцию WITH. Теперь все подготовительные действия по группировке данных выполняются в конструкции WITH, а в главном запросе полученные результаты используются так, как будто они хранятся в таблицах.
Эта модификация не ускоряет запрос, но делает его текст более понятным.
Порядок следования подзапросов в конструкции WITH имеет значение.
Подзапросы могут ссылаться на другие подзапросы, размещенные в конструкции WITH после них (так называемые «ссылки вперед»), только если используется предложение RECURSIVE.
В самом этом запросе нет ничего примечательного. Но давайте посмотрим план его выполнения с помощью команды EXPLAIN с параметром ANALYZE:
Число выполнений узла Parallel Seq Scan (отражающееся значением loops) равно 3. Это говорит о том, что параллельно работали три процесса. Значение rows показывает среднее число строк, возвращенных каждым процессом из таблицы «Перелеты» (ticket_flights), общее число строк в которой составляет 1 045 726. Точное число строк для каждого рабочего процесса можно увидеть, если в команду EXPLAIN добавить параметр VERBOSE. Если выполнить эту команду несколько раз, то можно заметить, что число строк, выбираемых каждым процессом, будет незначительно меняться.
Значения полей JSON-объектов, содержащие числовые данные, поддаются агрегированию. Например, можно вычислить среднее значение поля age из предыдущего примера:
Оконные функции, в отличие от обычных агрегатных функций, не требуют группировки строк, а работают на уровне отдельных (несгруппированных) строк. Однако если в запросе присутствуют предложения GROUP BY и HAVING, тогда оконные функции вызываются уже после них. В таком случае оконные функции будут работать со строками, являющимися результатом группировки.