Получение расширения файла в SQL-запросе

Фантазия авторов PostgreSQL поражает. Его разнообразные синтаксические конструкции почти всегда позволяют решить задачу несколькими способами. Например, рассмотрим получение расширения файла из его имени. В отличии от Oracle и MS SQL Server, для PostgreSQL я насчитал 4 варианта.

Удаление базы данных PostgreSQL с активными подключениями

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

Восстановить запись в журнал сообщений PostgreSQL

Обратил внимание на одну маленькую неприятную особенность PostgreSQL – если удалить текущий файл журнала сообщений, то сервер не создаст новый файл, пока не наступит момент ротации файла. Это наблюдается и под Windows и под Linux. Что делать, если log_rotation_age большой или вовсе установлен в ноль и смена файлов по времени не производится? Можно наплевать на подключенных к базам данных пользователей и перегрузить PostgreSQL. Но есть более безболезненный способ.

Ускоряем поиск с использованием LIKE

Прислали мне SQL-запрос с жалобой, что при определенных параметрах он работает около 10 минут. Хотя в большинстве случаев возвращает данные быстрее, чем за секунду. Запрос не простой. В нем объединяется несколько таблиц и вьюшек, а среди всех условий – регистронезависимый поиск по вхождению строки: UPPER(поле) LIKE '%СТРОКА%'. Мои замечания по поводу UPPER и LIKE по вхождению, так же как и предложения изменить запрос или использовать полнотекстовый поиск не приняли, т.к. запрос создан генератором запросов и переписывать его никто не будет. Проблему усугублял планировщик запросов PostgreSQL. Судя по плану, когда запрос возвращал данные, LIKE выполнялся по уже отфильтрованным данным и проверял несколько десятков строк, и время выполнения было приемлемое. А с параметрами при которых результат запроса был пустой, LIKE выполнялся первым условием и перебирал все строки в таблице. Как результат – жуткие тормоза. Это поведение моделировалось на PostgreSQL с 12-й версии по 17-ю. На MS SQL Server план этого запроса составлялся всегда корректно.

Как PostgreSQL под Linux может тормозить запросы

Установили заказчику новую систему. Внедренцы заметили, что иногда поиск грузит процессор под 100% и работает очень долго (по 5-9 секунд). При этом записей в базе данных еще немного. Настроив log_min_duration_statement на 5 секунд, мы получили пример тормозящего запроса. Можно сказать, что он огромный: с чтением почти десятка вьюшек, множеством условий и подзапросов. Для эксперимента залили дамп этой базы на другой сервер и там тормозящий запрос выполнился примерно за 150-170 миллисекунд. Версия PostgreSQL на серверах совпадала (16.6). Разница была в том, что на боевом сервере установлен Debian, а на тестовом MS Windows. Дальше – "веселее". Залили дамп на новую виртуальную машину с Debian – запрос тормозит, залили дамп на виртуальную машину с ALT Linux – запрос тормозит...

Преобразование JSON-строки в тип дата/время используя SQL

Согласно стандарту ECMA-404. The JSON Data Interchange Syntax в JSON нет типа для хранения даты и времени. В каком виде они будут закодированы в каждом конкретном JSON-файле определяет его автор. Я встречал два варианта:

1. Целое число содержащее UNIX-время (UNIX-time или POSIX-time), которое используется в UNIX и других POSIX-совместимых операционных системах, и определяет количество секунд, прошедших с полуночи (00:00:00 UTC) 1 января 1970 года.
2. Строка. Формат строки зависит от фантазии автора файла, но обычно используется стандарт ISO 8601, который представляет дату и время в универсальном формате, легко читаемом как людьми, так и машинами.

Недавно я средствами СУБД парсил JSON в котором дата/временя хранилась в строке формата "YYYY-MM-DDThh:mm:ss.sssZ" (например, "1975-11-21T01:34:53.666Z"). Процедура парсинга мне нужна была для трех СУБД: PostgreSQL, MS SQL Server и Oracle.

Задержка выполнения запроса в PostgreSQL

Обычно у меня спрашивают: "Как ускорить SQL-запрос?". Но недавно мой коллега сказал: "Для тестов с PostgreSQL мне необходимо замедлить мои запросы". Сразу я решил сам написать функцию, которая будет тормозить его запросы. Но в подобных ситуациях не спешите сразу хвататься за реализацию первой идеи. Всегда помните о богатой, иногда извращенной, фантазии разработчиков PostgreSQL. Оказывается, в PostgreSQL есть функция аналогичная функции Sleep из Win32 API, которая приостанавливает выполнение текущего потока программы на некоторое время.

Уникальные индексы и NULL

Согласно стандарту ANSI SQL, NULL – это специальное значение или псевдозначение, которое используется для обозначения отсутствия в поле базы данных какого-либо значения. О таком поле можно сказать, что оно имеет неопределенное значение или то, что оно пустое. Стандарт регламентирует, что NULL не равен NULL даже для полей с одинаковым типом данных. Хотя при этом строки, содержащие в поле NULL, группируются вместе при использовании DISTINCT или GROUP BY. В определении уникального ограничения SQL-92 ни слова не говорит про NULL: "A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns". Вероятно, это означает, что оно должно рассматривать каждый NULL, как уникальное значение. Как это реализовано в различных СУБД?

Сколько строк обработал оператор DML?

    После выполнения оператора DML модифицирующего данные иногда бывает необходимо узнать, сколько строк им было обработано. Это может быть полезно, например для определения успешности выполнения оператора или для ведения журнала операций. Каждая СУБД реализует такую функциональную возможность по-своему.

Конвертация данных при изменении типа столбца таблицы PostgreSQL

    Изменение типа данных столбца таблицы в PostgreSQL делается с использованием команды ALTER TABLE в комбинации с ALTER COLUMN. Согласно документации эта операция "будет успешна, только если все существующие значения в столбце могут быть неявно приведены к новому типу". Но это не совсем верно. Например, мешают еще связанные с этим столбцом ограничения DEFAULT и CHECK, или несовместимость типов данных. Тип VARCHAR(4) можно легко сменить на CHAR(4) или наоборот, а попытка сменить на INTEGER приведет к ошибке. И эта ошибка будет даже для пустой таблицы.

PostgreSQL. Корректировка следующего значения полей SMALLSERIAL, SERIAL и BIGSERIAL

    У PostgreSQL, как и у многих других СУБД, есть возможность создавать в таблицах автоинкрементные столбцы. Для этого предназначены типы данных SMALLSERIAL, SERIAL и BIGSERIAL. К сожалению, авторы PostgreSQL не сделали никаких ограничений на прямую запись в столбцы этих типов. С одной стороны – это удобно. В таблицу можно записать данные, у которых уже есть значения для этого столбца. Но с другой стороны – это большая проблема. Такие действия могут привести к дублированию или к ошибке, когда позже при обычной вставке новой записи в таблицу СУБД попытается заполнить поле автоматически. Для сравнения, можно привести как продуманно это реализовано в MS SQL Server. Что бы записать значение в столбец, помеченный как IDENTITY, нужно это разрешить специальной командой "SET IDENTITY_INSERT": вызываем "SET IDENTITY_INSERT имя_таблицы ON", вставляем нужные записи и вызываем "SET IDENTITY_INSERT имя_таблицы OFF". При этом СУБД сама скорректирует текущее значение счетчика на максимальное значение столбца. Для PostgreSQL эту корректировку надо произвести вручную.

Вызов процедур, функций и других SQL-команд PostgreSQL из базы данных Oracle

    Oracle Heterogeneous Services позволяет легко организовать доступ из базы данных Oracle к информации в базах данных других СУБД. Настройка Oracle Database Gateway и создание DATABASE LINK занимает несколько минут. Но использование DATABASE LINK и Oracle Database Gateway накладывает на SQL-команду ряд существенных ограничений:

• можно выполнять только команды DML (SELECT, INSERT, UPDATE и DELETE);
• синтаксис команд должен быть эквивалентным синтаксису СУБД Oracle;
• нельзя из удаленной базы данных вызвать процедуры и функции.

Установка и настройка Oracle Database Gateway на компьютере без СУБД Oracle

    В предыдущей статье я рассмотрел использование Oracle Database Gateway for ODBC (DG4ODBC) для работы с информацией в базе данных PostgreSQL через базу данных Oracle. Для этого я использовал шлюз, который уже был установлен вместе с Oracle Database 12c Release 2. Но у Oracle существует отдельный инсталлятор с целым набором шлюзов (для Informix, Sybase, MS SQL Server, Teradata, APPC, WebSphere MQ, DRDA и ODBC), позволяющий устанавливать шлюзы на компьютере, на котором не установлена СУБД Oracle. Сегодня пошагово рассмотрим этот вариант установки и настройки Oracle Database Gateway for ODBC.

Работа с информацией в базе данных PostgreSQL через базу данных Oracle

    Информационная система многих крупных предприятий имеет целый "зоопарк" приложений написанных с использованием различных технологий. У каждого приложения может быть не только своя база данных, но они могут использовать даже различные СУБД. Это ставит вопрос доступа к информации другого приложения. Для его решения Oracle предоставляет общую технологию для подключения из баз данных Oracle к базам данных других СУБД - Heterogeneous Services (HS). Для каждой СУБД, отличной от Oracle, запускается отдельный процесс, с помощью которого СУБД Oracle подключается к ней – Heterogeneous Services Agent (HS Agent). Этот процесс называется "шлюз" (Oracle Database Gateway). Он состоит из двух частей: общий код агента и драйвер для доступа.

FireDAC vs UniDAC. Получение значения первичного ключа новой строки

    Два года тому назад я писал о получении в программе значения первичного ключа, который сгенерирован СУБД при добавлении новой строки в таблицу. Мои примеры вызова INSERT с модификатором RETURNING (или OUTPUT в случае MS SQL Server) были с использованием библиотеки для доступа к базам данных UniDAC. Давайте посмотрим, как реализована эта возможность в библиотеке FireDAC, которая уже много лет входит в поставку Delphi и C++Builder.

Сортировка по текстовому полю содержащему комбинацию букв и цифр

    Сортировка результата SQL запроса – это то, с чем программисты сталкиваются постоянно. Обычно СУБД при сортировке текстовых полей для сравнения строк использует лексикографический порядок. Согласно Википедии, он означает, что слово X предшествует слову Y (X < Y), если:

• либо слово X является началом слова Y (например, "МАТЕМАТИК" < "МАТЕМАТИКА");
• либо первые m символов этих слов совпадают, а m+1-й символ слова X меньше m+1-го символа слова Y (например, "АБАК" < "АБРАКАДАБРА", так как первые две буквы у этих слов совпадают, а третья буква у первого слова меньше, чем у второго).

Этот способ сортировки строк подходит для большинства случаев. Проблемы могут возникнуть если строки содержат в себе комбинацию букв и цифр, а пользователи захотят видеть сортировку в "естественном порядке". Естественный порядок сортировки – это упорядочение строк в лексикографическом порядке, за исключением того, что многозначные числа обрабатываются атомарно (то есть, как один символ).

Лексикографический порядок	Естественный порядок
a12	a2a
a22	a2b
a2a	a3a2
a2b	a3a2a
a30z	a12
a3a2	a22
a3a2a	a30z

PostgreSQL и ошибка "столбец pd.adsrc не существует"

    Меня все больше удивляют авторы СУБД PostgreSQL. На моих тестовых серверах и у клиентов установлена ее 11-я версия. Обычно я стараюсь себе на компьютер устанавливать последние версии программного обеспечения. Так же было и с PostgreSQL - когда возникла необходимость ее установить локально, я установил 12-ю версию. Залил дамп базы, запустил свою утилиту и получил ошибку "column pd.adsrc does not exist". Я сразу подумал, что при импорте дампа возникла ошибка и загрузил его снова. Но ошибка повторилась. Установил PostgreSQL на другой компьютер и получил "столбец pd.adsrc не существует".

Обмен сообщениями между базой данных и программой

    Многие современные СУБД представляют возможности для обмена сообщениями между процессами. Чаще всего это уведомления о каких-то событиях в базе данных. Например, об изменении данных или об окончании выполнения процедуры. Принцип реализации этого механизма у всех СУБД одинаковый: один процесс (приложение) подписывается на событие от базы данных и ожидает его, а второй процесс отправляет уведомление об этом событии. В Oracle это позволяют делать процедуры пакетов DBMS_ALERT и DBMS_PIPE, в PostgreSQL команды NOTIFY и LISTEN, в InterBase/Firebird команды POST_EVENT и EVENT INIT/WAIT...

Удаление дубликатов строк SQL запросом

    Иногда в базах данных встречаются таблицы без первичного ключа. Это позволяет пользователям вносить в подобные таблицы данные содержащие полные дубликаты строк. Что делать, если перед вами стоит задача почистить эту таблицу от дубликатов?

Delphi & PostgreSQL. Фиксим "character with byte sequence 0xcc 0x81 in encoding "UTF8" has no equivalent in encoding "WIN1251""

В логах моей программы появилась странная ошибка:

25.10.2019 10:01:24.523 Thread #5: character with byte sequence 0xcc 0x81 in encoding "UTF8" has no equivalent in encoding "WIN1251"

Первой мыслью было, что Elasticsearch не принял данные, которые я ему передал REST-запросом. Но запустив программу под отладкой я получил эту ошибку при открытии запроса к БД PostgreSQL:

Project XYZ.exe raised exception class EPgError with message 'character with byte sequence 0xcc 0x81 in encoding "UTF8" has no equivalent in encoding "WIN1251"'

Посмотрев в БД данные, я сначала из-за у-умлаут грешил на слово "Zürich". Но сохранив текст в двух вариантах в файл (UTF8 и ANSI) и сравнив их, я увидел, что разница была в "Дадаи́зм" и "Дадаи?зм". Таким образом врагом WIN1251 объявляю букву "и" с ударением!

Враг назначен, т.е. найден, теперь будем решать, что с ним делать.