Пишу, по большей части, про историю, свою жизнь и немного про программирование.

🐘 PostgreSQL, PHP и подготовленные запросы

Одно из отличий «Постгреса» от «Оракла» — подготовленные планы запросов лежат не в разделяемом ресурсе, а в неком хранилище в рамках одного соединения. Программист, в рамках каждого соединения, должен подготовить их оператором PREPARE, в который передаётся выбранный идентификатор запроса и сам запрос, а потом выполнить его при помощи оператора EXECUTE, используя в качестве параметра переданный при подготовке идентификатор и значения параметров.

Польза подготовленных запросов в том, что можно единожды (за время жизни соединения, напоминаю) подготовить запрос, то есть попросить «Постгрес» выполнить его компиляцию в стандартное представление и оптимизацию, а после многократно его использовать, что очевидным образом поднимает производительность приложения.

Схема работы тут очень простая: вычисляем от запроса хеш, ищем его в специальном массиве, если не находим, подготавливаем запрос, используя хеш в качестве идентификатора, после чего хеш добавляем в специальный массив. После чего выполняем подготовленный запрос.

Если план запроса больше не нужен, его можно удалить по идентификатору оператором DEALLOCATE, если же в качестве параметра передать ключевое слово ALL, то будут уничтожены все подготовленные в этом соединении планы. С закрытием соединения, планы так же исчезают.

Теме работы «Постргеса» с ПХП удивительно мало посвящено в руководстве к языку, так что пришлось произвести ряд самостоятельных исследований. Основной вопрос, на который я хотел для себя ответить — есть ли какая-то долгосрочная польза от подготовленных запросов в разрезе того, что обычная модель работы с ПХП — полная смерть скрипта после обработки запроса от клиента.

Оказалось, что это вполне возможно. В ПХП есть «постоянные соединения» — обрабатывающий процесс интерпретатора держит открытым соединение с базой, передавая его новому скрипту, это уменьшает затраты, так как стадия установки соединения и авторизации выполняется только в первый раз.

С открытым соединением в скрипт попадают и подготовленные запросы из предыдущего сеанса, остаётся только как-то получить их имена, но с этим в «Постгресе» проблем нет — они могут быть получены из специальной таблицы.

Код всего описанного выглядит вот так, я его упростил и переписал процедурно:

// открываем постоянное соединение с базой
$con = pg_pconnect($connection_string);

// загружаем подготовленные запросы
$res = pg_query("SELECT name FROM pg_prepared_statements");
$prepared = array_fill_keys(pg_fetch_all_columns($res), true);
pg_free_result($res);

// хеш от запроса, tiger — быстрый алгоритм, я люблю его использовать
$hash = base64_encode(hash('tiger160,3', $sql, true));

// смотрим — был ли уже подготовлен такой запрос
if (!isset($prepared[$hash])) {
    $prepared[$hash] = true;
    pg_prepare($con, $hash, $sql);
}

// выполняем запрос
$res = pg_execute($con, $hash, $params);

Общая идея, думаю, понятна из кода. Пока для меня остаётся открытым вопрос «просачиваются» ли в переданное соединение незакрытые транзакции или временные таблицы (предыдущий его владелец, например, мог умереть из-за нехватки памяти), эксперименты я ещё не проводил, но, думаю, да.

Надо заметить, что модуль «Постгреса» в ПХП в каждое соединение, перед его передачей в приложение, посылает команду RESET ALL, но она, насколько я понял из руководства, лишь сбрасывает различные конфигурационные переметры времени выполнения на значения по-умолчанию и не трогает транзакции и всё остальное (более того, она сама транзакционна).

7 комментариев
Александр Макаров 2016

И что, вот так выбирать prepared statement-ы действительно выгодно в плане производительности?

Евгений Степанищев (bolknote.ru) 2016

Комментарий для Александр Макаров:

Уточните вопрос, вы имеете ввиду вот эту строку что ли?

$res = pg_query(«SELECT name FROM pg_prepared_statements»);

А что вас смущает? Вы считаете, что этот время выполнения этого запроса может перевесить пользу от подготовленных запросов? Если бы польза от них была столь незначительна, то и не имело бы смысла вообще с ними заморачиваться.

Alex Crown 2016

FYI Недавно на хабре статья была про отличия реализации prepared statements в oracle и postgres — https://habrahabr.ru/company/postgrespro/blog/275755

Евгений Степанищев (bolknote.ru) 2016

Комментарий для Alex Crown:

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

Vladimir 2016

Я практически все запросы в хранимые процедуры превращаю. Перейти от параметризированного запроса к ХП достаточно просто, а plpgsql хранит все запросы как prepared.

Евгений Степанищев (bolknote.ru) 2016

Комментарий для Vladimir:

Если бы IDE для баз данных не застряли в 20-м веке, это было бы для нас более чем приемлемо, многие запросы именно туда и уехали бы. А так — никакой пошаговой отладки, интеграции с системой контроля версий, инспекции, ничего.

Vladimir 2016

Комментарий для Евгения Степанищева:

Согласен, предоставляемые БД средства разработки сильно отстают от таковых по индустрии.
Поэтому ХП храним в .sql-файлах рядом с кодом, отлаживаем через raise notice и мечтаем о нормальной структуре типов в plpgsql.