11 заметок с тегом

oracle

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

Ещё в копилку проблем с ПХП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;

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

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

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

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

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, должно помочь.

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

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

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

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

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

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

Преобразование 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);
}

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

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 комментариев    112   2014   googlego   oracle
Ранее Ctrl + ↓