💢 OCI8: проблемы с переходом на PHP7

Ещё в копилку проблем с ПХП7: если при привязке переменных в Оракле (bind) поля типа LONG/LOB не находятся в конце списка, то вы получите ошибку ORA-24816. Все столбцы таких типов должны быть привязаны последними, в общей куче. Мы пока столкнулись с проблемой только при сохранении (тестирование пока идёт), в итоге в сохраняющем методе модели пересортировали привязки, основываясь на описании модели.
13 комментариев
25 июля 2016 08:34

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

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

У нас довольно много генерируемых простых запросов, иногда они генерировались не очень аккуратно. «Оракл» такое прощает, а «Постгрес» — нет. Запрос, где одни и те же таблицы соединяются дважды «Постгресу» не по вкусу:
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 09: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 15:35

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 21:35

Goracle

В прошлом году немного рассказывал о взаимодействии языка «Гоу» и «Оракла» — я тогда выбирал из двух драйверов и выбрал в итоге ГОракл. Всё в нём хорошо, да вот незадача — некоторые запросы не выполнялись, падало всё с невнятной ошибкой ORA-22060.

Разбирательство привело меня к мысли, что дело в числовых значениях — если запрос возвращал только строки, всё было нормально. Я тогда преобразовал всё при помощи TO_CHAR, разбирая внутри «Гоу» строку в число и успокоился.

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

Так что если вы видите при использовании этого драйвера ошибки похожие на те, что ниже
ERR=&oracle.Error{Code:22060, Message:"[-1] ORA-22060: argument [2] is an invalid or uninitialized number
ORA-22060: argument [2] is an invalid or uninitialized number
Попробуйте обернуть все числовые возвращаемые значения функцией NVL, должно помочь.
2 комментария
1 марта 2015 19:05

Частичный индекс в «Оракле»

В «Оракле», в отличие от «Постгреса», частичных индексов в чистом виде нет, но есть возможность их сэмулировать — достаточно построить функциональный индекс и возвращать NULL на значениях, которые нам в индексе не нужны. Например:
CREATE INDEX resolution_notice_d_o_r ON resolution_notice(
    CASE WHEN is_send_paper = 1 THEN org_id END,
    TRUNC(rdate)
)
Это немного видоизменённый реальный пример — таблица хранит море метаинформации, но нас намного чаще (в логике приложения) интересуют те записи, у которых флаг is_send_paper в значении «истина».

Чтобы «Оракл» узнал искомое в индексе, сравнение в запросе должно выглядеть так же. В общем, всё как в случае любого функционального индекса (лишнее я убрал):
SELECT …
     FROM …
INNER JOIN resolution_notice rn ON …
    WHERE dn.n = 0
      AND CASE WHEN is_send_paper = 1 THEN dn.org_id END = :org_id
     AND TRUNC(rn.rdate) BETWEEN :start_date AND :end_date
GROUP BY …
ORDER BY rdate DESC, …
Мне стало интересно — если попробовать сунуть условие индекса в сам индекс, догадается ли «Оракл» дальше не дофильтровывать по нему? Ну, то есть, сделать что-то такое:
CREATE INDEX resolution_notice_d_o_r ON resolution_notice(
     org_id,
     TRUNC(rdate),
     CASE WHEN is_send_paper = 1 THEN 1 END
)
А в запрос добавить:
… CASE WHEN is_send_paper = 1 THEN 1 END = 1…
Оказалось, «Оракл» не догадывается (у нас он 11-й, может 12-й тут сможет оптимизацию сделать): Условие (34.98КиБ) Несмотря на то, что в выборке по индексу значений с is_send_paper других значений, кроме единицы нет, «Оракл» всё равно дофильтровывает по этому значению.
11 комментариев
16 февраля 2015 19:20

Ошибка оракула

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

Всё работало хорошо, пока ПХП был 32-битным. Проверка is_int в этом языке возвращала «истину» только для чисел в диапазоне -2147483648…2147483647, всё что шире, трактовалось как float. В 64-битном интерпретаторе целыми считаются числа из гораздо более широкого диапазона, соответственно, в каком-то коде такое значение попало в драйвер «Оракла», промаркированное как SQLT_INT.

Беда в том, что драйвер (в этом месте), похоже, остался в пределах 32 бит, код:
$var = null;
$st = oci_parse($con, "SELECT :int FROM DUAL");
oci_bind_by_name($st, 'int', $var, -1, SQLT_INT);

foreach ([100500, PHP_INT_MAX, 2147483648] as $var) {
	oci_execute($st);
	echo oci_fetch_array($st, OCI_NUM)[0], "\n";
}
Выведет: 100500, -1, -2147483648. Решение я пока знаю только одно: не использовать для целых, которые не помещаются в 32 бита указание типа SQLT_INT.

Проблема в файле oci8_statement.c (это текущий код драйвера из PHP 5.7):
switch (bind->array.type) {
	case SQLT_NUM:
	case SQLT_INT:
	case SQLT_LNG:

		...
		ZVAL_LONG(entry, ((ub4 *)(bind->array.elements))[i]);
		...
Значение в этом месте приводится к четырёхбайтному целому.
2 комментария
20 декабря 2014 15:02

ENUM

Не понимаю, почему в «Оракле» (это СУБД) нет такой полезной вещи как тип ENUM. Вещь совершенно необходимая, как по мне. Вот посмотрите, что понятнее:
CREATE TABLE event_queue (
     …
     status ENUM('done', 'new', 'failed', 'running'),
     …
);
или
CREATE TABLE event_queue (
     …
     status NUMBER(1), -- тут у нас 4 значения будет
     …
);
Я как-то за первый вариант — всюду сразу видно что означает конкретное поле. Это по-моему, так очевидно, что и обсуждать тут нечего. Вместо этого в запросах появляются либо некие магические константы, либо приходится в них пропихивать что-то именованное из кода, но при этом в выборке всё равно будут те же номера.

Кроме удобства программиста, есть очевидная польза и для самой СУБД. Например, возьмём запрос к таблице выше, где мы определили поле status как число. Мы знаем, что статусов у нас всего четыре, это знаем мы, но это не знает «Оракл». Теперь взглянем на два совершенно эквивалентных с точки зрения программиста запроса (ведь он знает, что значений четыре) и посмотрим планы их выполнения:
SELECT *
FROM event_queue
WHERE status IN (1,2)
AND target='search' AND attempts < 3

План №1 (17.65КиБ)
SELECT *
FROM event_queue
WHERE status NOT IN (3,4)
AND target='search' AND attempts < 3

План №2 (17.77КиБ) Как видите, разница в стоимости аж в три раза. Спрашивается, почему? Ответ очень простой, конечно: в первом случае если status оказался равен единице, вторая проверка уже не нужна, во втором случае, всегда нужны обе проверки. Почему цифры отличаются именно в два раза, а не в три, я не скажу, это, видимо, прикидка, основанная на статистике использования этой таблицы — одно из этих значений (статус «задание провалено») вообще ещё ни разу не записывалось.

Вообще, есть несколько способов имитировать тип ENUM: добавить ограничение (check), ввести новый тип. Но первое никак не сказывается на плане и требует перехода к строковому типу, а со вторым я ещё не эксперементировал.

Обладая Оракл информацией о том, что у нас в этом поле всего четыре возможных значения в этом поле, он мог бы запросто инвертировать значения и снизить стоимость.
21 комментарий
27 сентября 2014 15:52

Преобразование tnsnames.ora в JSON

По работе сделал разбор файла tnsnames.ora — файла оракловой конфигурации на ПХП в массив:
function Ora2Array($str)
{
    // убираю комментарии
    $str = preg_replace('/^\s*#.*$/m', '', $str);

    // преобразование скаляров
    $str = preg_replace('/\((\w+)\s*=\s*([^()]+)\)/m', '"$1": "$2", ', $str);

    // преобразование ассоциативных массивов
    do {
        $str = preg_replace('/\((\w+)\s*=\s*([^()]+?)[\s,]*\)/sS', '"$1": { $2 },', $str, -1, $count);
    } while ($count);

    // преобразование верхнеуровневых значений
    $str = preg_replace('/(\w+)\s*=\s*([^()]+?)[\s,]*(?:(?=\w+\s*=)|\s*$)/s', '"$1": { $2 },', $str);

    return json_decode('{' . trim($str, ' ,') . '}', true);
}
Не разбираются значения в кавычках — такого в наших конфигурациях не встречается, но добавляется несложно.
2 комментария
11 сентября 2014 14:34

Go и Oracle

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

Для начала, проблема возникла с кодировкой. У нас в базе используется кодовая таблица 1251. Сначала (тут я сам дурак) мне показалось, что драйвер не учитывает кодировку из переменных откружения (проблема оказалась в 11-й строчке примера (который идёт с драйвером), где очищалась переменная NLS_LANG), поэтому пришлось соорудить патч:
charset := C.CString(charset_dsn)
defer C.free(unsafe.Pointer(charset))

env := new(C.OCIEnv)
rv = C.OCIEnvNlsCreate(
    (**C.OCIEnv)(unsafe.Pointer(&env)),
    C.OCI_DEFAULT | C.OCI_OBJECT,
    nil, nil, nil, nil, 0, nil, 0, 0,
)
charset_id := C.OCINlsCharSetNameToId(unsafe.Pointer(env), (*C.oratext)(unsafe.Pointer(charset)))

rv = C.OCIEnvNlsCreate(
    (**C.OCIEnv)(unsafe.Pointer(&conn.env)),
    C.OCI_DEFAULT | C.OCI_OBJECT | C.OCI_THREADED | C.OCI_NO_MUTEX,
    nil, nil, nil, nil, 0, nil,
    charset_id, charset_id,
)
Этот код должен работать вместо OCIEnvInit, если нужно использовать кодировку. Но, повторюсь, это проблема не драйвера, просто этот тот случай, когда «ложечки нашлись, а осадок остался».

Потом оказалось, что в драйвере нет превыборки (prefetch), из-за чего чтение из таблицы было очень медленным. Чашу терпения же переполнило отстутствие работы с типом CLOB — при попытке получить данные этого типа размером более 4000 байт, драйвер сообщил, что нехватает буфера.

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

Единственная неприятность — драйвер все строки возвращает в кодировке UTF-8, т.е. они конвертируются из любой другой автоматически. Мне их приходится конвертировать обратно, но скорость чтения настолько велика (на порядок быстрее ПХП), что меня это пока не волнует.
5 комментариев
3 февраля 2014 08:07

Удаление умершей ноды в кластере «Оракла»

Небольшой видеоурок по удалению умершей ноды из кластера «Оракла». В одном месте там требуется, чтобы на машине был своп не менее 500МБ, у нас не было (на машине 96ГБ памяти, зачем ей своп), мы его просто создали на диске в файле.
2 комментария
12 ноября 2011 18:32