Особенности смены типа в PostgreSQL: text → bytea

При обыденной смене типа колонки в «Постгресе» с текстового типа на бинарный получил ошибку «ERROR: invalid input syntax for type bytea». Запрос довольно рядовой и сюрпризов я не ожидал:
ALTER TABLE tbl ALTER COLUMN colmn TYPE bytea USING colmn::bytea;
Решения нагуглить не удалось, поэтому начал исследовать проблему сам. Бинарным поиском нашёл строку на которой запрос запнулся, потом так же вычислил символ. Им оказался обратный слэш. Заэкранировал его и ошибка пропала:
ALTER TABLE tbl ALTER COLUMN colmn TYPE bytea USING REPLACE(colmn, '\', '\\')::bytea;
Всё отработало нормально, сконвертировалось, слеши получаются из базы как и ожидается — одинарными.
Комментировать
9 февраля 2017 12:14

🐘 «Синхронная реплика» PostgreSQL

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

Много любопытного я узнал об этой СУБД, возможно когда-нибудь напишу, если не забудется, но самое примечательное следующее. На «Оракле» у нас, естественно, был кластер, это очень удобно программисту — этакий «чёрный ящик», знай кидай туда запросы, оно само разрулит (на самом деле, это типичная «дырявая абстракция», но чаще всего напрягаться не надо).

На «Постгресе» мы, конечно, захотели аналога и, казалось, нашли его — pgpool-II, который разделяет запросы на пишущие и читающие, которые потом распределяются им на мастер и синхронные реплики.

Наши нагрузочные испытания показали, что на пиковых нагрузках синхронные реплики никакие не синхронные, оказалось, что «синхронность» реплики только в том она говорит мастеру «я всё приняла», когда данные попали на диск и именно в этот момент.

Когда нагрузка близка к пиковой, данные физически на диске есть, а логически они ещё не считываются. То есть это скорее пригодно для того, чтобы в случае смерти мастера быстро поменять его на реплику, чем для схемы с распределением нагрузки.

Это будет исправлено в будущей 9.6, а пока живём с тем, что есть.
6 комментариев
31 марта 2016 15:11

🐘 Подготовленные планы в PostgreSQL

Кто-то меня спрашивал о выгоде преподготовленных планов в «Постгресе». Наши испытания показывают, что выигрыш порядка 10—15%, что очень немало. И я уже рассказывал, что придумал рецепт как делить их в условиях «умирающего» ПХП.

Но вот какая неприятность — при подключении любого балансировщика нагрузки, совершенно неочевидно куда улетает ваш план и таблица в приложении не помогает — она единая, а серверов может быть несколько, у каждого свои планы подготовленные. Если мы начнём подготавливать его заново, а он уже есть, получим ошибку, что план уже существует.

Какой выход? Краткий ответ: пока никакой.

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

С другой стороны у меня появилась идея — добавить в эскуэль конструкцию PREPARE IF NOT EXISTS, ребята из, с которыми я обсуждал эту идею, PostgresPro уже сделали несколько версий патча.

Основа идеи — всегда слать запрос на компиляцию плана, но если он уже был скомпилирован, ничего не делать. Мы его уже погоняли (оттуда и цифры первого абзаца), но на этом пути больше трудностей, чем себе можно представить — в частности, надо патчить ещё и продукты для балансировки и расширять его клиентский протокол.

Очень надеюсь, что патч доведут до ума и мы скоро сможем его использовать.
19 комментариев
27 марта 2016 21:04

🐘 «Рекурсивные» запросы в PostgreSQL

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

Если вы знаете рецепт лучше, поделитесь, пожалуйста.

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

Есть такой и для «рекурсивных» запросов. Нередко «рекурсивный» запрос приходится строить, выбирая не из таблицы, а из подзапроса. Наш билдер был устроен таким образом, что подобный запрос он строил по следующему образцу:
WITH RECURSIVE prior AS (
	WITH virtual AS (
		-- тут у нас подзапрос
	)
	SELECT … FROM virtual WHERE …некий критерий…
	UNION ALL
	SELECT … FROM virtual INNER JOIN prior …критерий соединения…
)
SELECT … FROM prior…
Ну вот что-то такое. Оказалось, конструкция WITH является барьером для постгреского оптимизатора — он не может заглянуть за него и планы строит отдельно — для запроса внутри WITH и того, что снаружи. Разумеет это дико неэффективно. Например, если внутри соединение со слабыми ограничениями (сильные наложатся снаружи — в «рекурсивном» запросе), то внутренний запрос может выбрать гигантское количество данных, что у нас и происходило в нескольких местах.

Эффективнее оказалось убрать внутренний WITH и повторить запрос дважды — в выборе первой строки и внутреннем соединении. Но беда в том, что билдер так сделать не может, а чтобы его научить, нужно было написать парсер запросов — так как критерии соединения должен содержать правильные алиасы. Оборачивание же части запроса в подзапрос тоже даром не проходит — стоимость запроса вырастает.

Я перебрал несколько решений, которые пришли мне в голову и остановился на следующем: в данный момент билдер создаёт временную «вьюху» с определённым именем, которое указывается в качестве источника запроса. Выглядит это так:
CREATE OR REPLACE TEMP VIEW некоеимя AS (
	-- тут у нас подзапрос
);
WITH RECURSIVE prior AS (
	SELECT … FROM некоеимя WHERE …некий критерий…
	UNION ALL
	SELECT … FROM некоеимя INNER JOIN prior …критерий соединения…
)
SELECT … FROM prior…
Выходит достаточно эффективно, но смущают две вещи: во-первых, всё это выглядит очень неизящно, во-вторых, для «вьюхи» нельзя подготовить план — все параметры приходится вставлять в запрос, так что максимальная производительность, естественно, не достигается.
Комментировать
20 марта 2016 21:09

🐘 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 комментариев
29 февраля 2016 21:29

🚨 Особенности перехода с «Оракла» на «Постгрес»

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

У нас довольно много генерируемых простых запросов, иногда они генерировались не очень аккуратно. «Оракл» такое прощает, а «Постгрес» — нет. Запрос, где одни и те же таблицы соединяются дважды «Постгресу» не по вкусу:
SELECT * FROM test INNER JOIN test1 USING(n) INNER JOIN test USING(n);

ERROR:  table name "test" specified more than once
«Постгрес» очень трепетно относится к тому, чтобы у подзапроса был алиас, даже если он потом нигде не используется:
SELECT * FROM (SELECT * FROM test);

ERROR:  subquery in FROM must have an alias
СТРОКА 1: SELECT * FROM (SELECT * FROM test);
                        ^
ПОДСКАЗКА:  For example, FROM (SELECT ...) [AS] foo.
В «Постгресе», в запросах на обновление нельзя указать алиас. Тут это логично, так как синтаксис подзапросов в этом случае у этой СУБД совершенно иной — они указываются в специальном месте.
UPDATE test t SET t.n=1;

ERROR:  column "t" of relation "test" does not exist
СТРОКА 1: UPDATE test t SET t.n=1;
Если «Оракл» спокойно относится к двум и более таблицам с одним алиасом, то «Постгресу» от этого плохеет. Жаль, иногда удобно объединить несколько таблиц алиасом прямо на месте и работать дальше с ним, как с единой таблицой.
SELECT t.n FROM test t, another_test t;

ERROR:  table name "t" specified more than once
В «Оракле» ключевое слово FROM не обязательно в запросах на удаление. У нас было несколько запросов без него.
DELETE test;

ERROR:  syntax error at or near "test"
СТРОКА 1: DELETE test;
В следующий раз буду описывать более комплексные примеры.
4 комментария
24 февраля 2016 10:41

✨ Двойные миграции (Оракл+Постгрес)

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

В этой связи, когда есть различия, приходится миграции писать в двух комплектах — под каждый диалект. Я придумал как писать их так, чтобы каждая БД видела куски кода, предназначенные только ей.

Например:
CREATE UNIQUE INDEX dn_part_num_org_n_cat ON document_n(/*/**/
CASE WHEN d_deleted = 0 AND num IS NULL AND n=0 AND category=0 THEN id END,
CASE WHEN d_deleted = 0 AND num IS NULL AND n=0 AND category=0 THEN org_id END);
--*/ id, org_id) WHERE d_deleted=0 AND num IS NULL AND n=0 AND category = 0;
Часть с /*/**/ и до --*/ видит только «Оракл» — для него это выглядит так: комментарий открывается, сразу закрывается, идёт код, который он и воспринимает, а последняя строка закоментирована при помощи двух минусов — это стандартный коментарий в эскуэле.

«Постгрес» эту часть не видит — он поддерживает вложенные коментарии, поэтому его интерпретация другая: открываются два комментария, первый закрывается сразу, а второй — на последней строке, остаток которой «Постгрес» воспринимает как часть кода.
30 комментариев
20 февраля 2016 16:35

Upsert в PostgreSQL

Ура, появился коммит, добавляющий к оператору INSERT «Постгреса» конструкцию, которая позволяет использовать его как upsert (в «Оракле» это конструкция MERGE, в «Майэскуэеле» — INSERT… ON DUPLICATE KEY UPDATE):
-- Обновить, если не удалась вставка
INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
ON CONFLICT (did) DO UPDATE
SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
WHERE d.zipcode != '21201';

-- Если не удалась вставка, ничего не делать
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
ON CONFLICT (did) WHERE is_active DO NOTHING;
Счастье-то какое!

PostgreSQL: сортировка без учёта регистра

Мы переводим свои некоторые продукты с «Оракла» на «Постргрес» и постоянно сталкиваемся с какими-то различиями разной величины. Сегодняшнее открытие — «Постгрес» (в отличие от «Оракла») сортирует числовые значения без учёта регистра:
test=# SELECT * FROM (SELECT 'Ба' UNION ALL SELECT 'бб' UNION ALL SELECT 'Бв') _ ORDER BY 1;
 ?column?
----------
 Ба
 бб
 Бв
(3 строки)
Ни в одном описании опыта такого же перехода я почему-то не натыкался на такую проблему. Если регистр для вас важен, то есть целых два выхода. Либо сменить принцип сравнения:
test=# SELECT * FROM (SELECT 'Ба' AS str UNION ALL SELECT 'бб' UNION ALL SELECT 'Бв') _ ORDER BY str COLLATE "C";
 str
-----
 Ба
 Бв
 бб
(3 строки)
Либо преобразовать строку к бинарному типу:
test=# SELECT * FROM (SELECT 'Ба' AS str UNION ALL SELECT 'бб' UNION ALL SELECT 'Бв') _ ORDER BY str::bytea;
 str
-----
 Ба
 Бв
 бб
(3 строки)
Правда в последнем случае неясно сможет ли «Постгрес» использовать индекс — мы пока таких испытаний не проводили.
9 комментариев
13 марта 2015 22:35

POSTQUEL

POSTQUEL — это язык запросов, который когда-то поддерживала СУБД «Постгрес». Наверняка многие задумывались почему языков программирования так много, а языков в базах данных, кроме SQL и его диалектов, не видать; SQL оказался исключительно удачным, но у него были когда-то и соперники. «Постквел» один из них.

POSTQUEL расшифровывается как «POSTgres QUEry Language», «язык запросов Постгреса», он был разработам в 1985 году в Калифорнийском университете в Беркли под руководством Майкла Стоунбрейкера, POSTQUEL основан на языке запросов QUEL, который использовался на тот момент в БД «Ингрес».

Примеры того на что был похож POSTQUEL есть в польской Википедии:

Получить размер заработной платы сотрудника Ковальски (pracownicy — сотрудники, płace — зарплата):
retrieve (PRACOWNICY.placa) from PRACOWNICY where PRACOWNICY.nazwisko = "Kowalski" 
Все сотрудники старше 40 лет (wiek — возраст, nazwisko — имя):
retrieve (P.nazwisko) from P in PRACOWNICY where P.wiek > 40
Найти все департаменты, целиком занимающие один этаж (pietro — этаж, dnazwa — сокращение от «название департамента»):
retrieve (DEPART.dnazwa)
where DEPART.pietro NOT-IN {D.pietro from D in DEPART where D.dnazwa != DEPART.dnazwa}
«Постквел» был более формализованным языком, чем Эскюэль, все команды строились по одному образцу и не допускали вариаций. Других подробностей я не нашёл, к сожалению, детального описания языка мне обнаружить пока не удалось, было бы любопытно посмотреть на него и проанализировать сильные стороны.

Добавлено позднее. Я обнаружил документацию 1992 года на «Постгрес», где довольно подробно упоминается и «Постквел» (ссылка есть в комментариях). Интересный язык-то был!

Например, оперировал он объектами, поддерживал наследование (все примеры из упомянутой документации):
create STUD_EMP (location = point) inherits (EMP)
Поддерживал массивы (индексы начинаются с единицы):
create SAL_EMP (name = char[], pay_by_quarter = int4[4])

append SAL_EMP (name = "bill", pay_by_quarter="{10000, 10000, 10000, 10000}")

retrieve (SAL_EMP.name) where SAL_EMP.pay_by_quarter[1] !=SAL_EMP.pay_by_quarter[2]
Поддерживал историю, вот, например, срез данных с первого января 1970 по наши дни:
retrieve (E.salary)
    from E in EMP["Jan 1 00:00:00 1970 GMT", "now"]
    where E.name = "Sam"
Была возможность создавать пользовательские типы (тут создаётся новый тип массива):
define type int_array
    (element = int4, internallength = variable,
    input = array_in, output = array_out)
Так же как и пользовательские функции:
define function eq_area_circle
    (language = "c", returntype = bool)
    arg is (circle, circle)
    as "/usr/postgres/tutorial/circle.o"

define function manager
    (language = "postquel", returntype = EMP)
    arg is (EMP)
    as "retrieve (E.all) from E in EMP
    where E.name = DEPT.manager
    and DEPT.name = $1.dept"

retrieve (EMP.name)
    where name(manager(EMP)) = "Joe"
Была даже перегрузка операторов!
define operator =
    (arg1 = circle, arg2 = circle,
    procedure = eq_area_circle)
И так далее.
23 комментария
25 января 2013 20:50