oracle

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

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

У нас довольно много генерируемых простых запросов, иногда они генерировались не очень аккуратно. «Оракл» такое прощает, а «Постгрес» — нет. Запрос, где одни и те же таблицы соединяются дважды «Постгресу» не по вкусу:

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;

В следующий раз буду описывать более комплексные примеры.

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

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

Например:

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;

Часть с /*/**/ и до —*/ видит только «Оракл» — для него это выглядит так: комментарий открывается, сразу закрывается, идёт код, который он и воспринимает, а последняя строка закоментирована при помощи двух минусов — это стандартный коментарий в эскуэле.

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

Мы переводим свои некоторые продукты с «Оракла» на «Постргрес» и постоянно сталкиваемся с какими-то различиями разной величины. Сегодняшнее открытие — «Постгрес» (в отличие от «Оракла») сортирует числовые значения без учёта регистра:

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 строки)

Правда в последнем случае неясно сможет ли «Постгрес» использовать индекс — мы пока таких испытаний не проводили.

В прошлом году немного рассказывал о взаимодействии языка «Гоу» и «Оракла» — я тогда выбирал из двух драйверов и выбрал в итоге ГОракл. Всё в нём хорошо, да вот незадача — некоторые запросы не выполнялись, падало всё с невнятной ошибкой 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, должно помочь.

В «Оракле», в отличие от «Постгреса», частичных индексов в чистом виде нет, но есть возможность их сэмулировать — достаточно построить функциональный индекс и возвращать 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-й тут сможет оптимизацию сделать):

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

В одном из проектов при исправлении ошибки всплыло неожиданное. Кусок кода, который работал ошибочно, родился где-то в двухтысячных и по всей видимости в те времена работал правильно. В нём проверялось — если тип переменной «целое», то переменная передавалась в «Оракл» с флагом 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]);
        ...

Значение в этом месте приводится к четырёхбайтному целому.

Не понимаю, почему в «Оракле» (это СУБД) нет такой полезной вещи как тип 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

 

 

SELECT *
FROM event_queue
WHERE status NOT IN (3,4)
AND target='search' AND attempts < 3

 

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

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

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

По работе сделал разбор файла 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);
}

Не разбираются значения в кавычках — такого в наших конфигурациях не встречается, но добавляется несложно.

На работе произошло кое-что необычное: вдруг пригодились знания Гоу. Не то чтобы нельзя было написать требуемое на другом языке, но нужно было быстро и на чём-то компилируемом. На Си всё работало бы значительно быстрее, но писалось бы неделю или более, поэтому я написал прототип на Гоу, управившись всего за два дня. Скорость работы оказалась выше ожидаемой и теперь, как кажется, прототип уже начинает превращаться в продукт.

Проблемы начались, когда я попытался подключиться из прототипа к СУБД «Оракл» (такое было условие задачи). Сначала я, как и  наверное многие до меня, нашёл драйвер 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, т. е. они конвертируются из любой другой автоматически. Мне их приходится конвертировать обратно, но скорость чтения настолько велика (на порядок быстрее ПХП), что меня это пока не волнует.

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