Получение расширения файла в 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 план этого запроса составлялся всегда корректно.

MS SQL Server. Таблицы оптимизированные для памяти и пропажа места на диске

К нам в поддержку обратился клиент с ошибкой "MAT/PIT export/import encountered a failure for memory optimized table or natively compiled stored procedure with object ID 1873539326 in database ID 5. The error code was 0x80030070." в приложении работающем с MS SQL Server.

Так как в тексте ошибки упомянута таблица оптимизированная для памяти, то моя первая мысль была, что на сервере недостаточно памяти или она глюканула. Поиск описания ошибки по коду 0x80030070 вывел на STG_E_MEDIUMFULL "There is insufficient disk space to complete operation", а потом на ERROR_DISK_FULL 112 (0x70) "There is not enough space on the disk". Действительно, как мне потом написали, у клиента "всё починилось добавлением места". Как связаны таблицы оптимизированные для памяти и недостаток места на диске для завершения операции?

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

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

Ошибка TNetHTTPClient "ENetHTTPCertificateException: Server Certificate Invalid or not present"

Есть у меня маленькая написанная на Delphi утилита, которая, используя TNetHTTPClient POST-запросом, забирает с сайта заказчика справочники для тестирования нашей системы. Попытался я скачать новый справочник, а утилита вместо JSON'а с данными выдала мне ошибку "ENetHTTPCertificateException: Server Certificate Invalid or not present":

Я попробовал зайти на страницу сервиса браузером и получил сообщение "Сервер не может подтвердить связь с доменом заказчик.by. Срок действия его сертификата безопасности истек 33 дня назад.". Оказалось, что при его переводе с HTTP на HTTPS, что-то у них пошло не так...

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

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

Уменьшение размера виртуального диска Oracle VM VirtualBox

    На одном из используемых мной компьютеров на жестком диске стало катастрофически не хватать места. Причиной этого были невероятно распухшие динамически распределяемые образы дисков виртуальных машин под управлением Oracle VM VirtualBox. При этом некоторые виртуальные машины использовали только 20-30% от выделенного им места. На них установлены различные версии СУБД, которые съедают много дискового пространства при заливке дампов в тестовые базы данных, но не отдают его обратно при удалении этих баз. Сначала я думал создать новые виртуальные машины, установить на них программное обеспечение и перелить нужные базы... Но мне стало лень делать столько работы, и я стал искать, как заставить VirtualBox вернуть неиспользуемое место операционной системе. Оказалось – это сделать очень просто.

Ошибка ".exe is not a valid Win32 application" на старых версиях Windows

    На прошлой неделе мне сказали, что 32-х битное консольное приложение, написанное на Delphi 11, не запускается под Windows 2003. Я не поверил. Поставил на виртуалку Delphi 11.3, сделал тестовое 32-х битное консольное приложение с одной строкой кода "Writeln('It works!')", попросил админа поднять древнюю виртуалку с Windows Server 2003, запустил на ней свое тестовое приложение и получил ошибку "temp.exe is not a valid Win32 application":

Как так? Ведь разработка десктопных приложений – это же конек Delphi!

Конвертация данных при изменении типа столбца таблицы 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 эту корректировку надо произвести вручную.

MS SQL Server. Управление контекстом безопасности подключения к связанным серверам

    Механизм связанных серверов MS SQL Server позволяет реализовать распределенные базы данных, которые работают с данными в других базах данных. "Связаться" можно с любым источником данных, для которого существует возможность подключения к нему с использованием OLE DB. Есть два шага обеспечения безопасности при подключении к удаленной базе данных связанного сервера:

1. сопоставить имена пользователей локального сервера MS SQL Server с именами пользователей удаленного сервера;
2. указать, как связанный сервер должен обрабатывать подключение пользователей, имена которых не сопоставлены.

Оба эти шага из T-SQL выполняются с помощью процедуры sp_addlinkedsrvlogin. Она создает или обновляет сопоставление между учетными записями пользователей локального и удаленного серверов. Название процедуры намекает на шаг с сопоставлением имен пользователей, но определенные комбинации ее параметров позволяют управлять подключением всех не сопоставленных пользователей.

Проблема с цветом контролов в дизайнере VCL-форм Delphi 11.2

    При одном из первых запусков Delphi 11.2 Alexandria меня ждал неприятный сюрприз – изменение цвета контрола не работает в design-time. Например, бросаем на форму memo, меняем его свойство Color с clWindow на clRed и никого эффекта – цвет memo не изменился. Запускаем проект и видим memo красного цвета. Значит в run-time подобной проблемы нет.

Memo в design-time и run-time

И это оказывается не баг! Причина такого поведения дизайнера форм – постоянное желание разработчиков RAD Studio улучить поддержку High DPI.

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

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

Запуск Delphi и C++Builder без Welcome Page

    Страница приветствия в Delphi и C++Builder 11 Alexandria стала более удобной. Но все равно ее наличие при загрузке IDE нравится не всем программистам. Есть, как минимум, два способа запустить Delphi и C++Builder без страницы приветствия.

MS SQL Server. Получение из файловой системы списка папок и файлов

    Иногда работа с базами данных подкидывает не стандартные задачи. Например, недавно мне в скрипте MS SQL Server понадобилось получить из файловой системы список папок и файлов. Мои попытки сделать это через объект Scripting.FileSystemObject потерпели неудачу. Его метод GetFolder по имени папки возвращает объект Folder, у которого есть свойства SubFolders/Files содержащее списки вложенных папок и файлов. Но проблема в том, что эти списки – это коллекции, элементы которых в VBA можно перебрать в цикле "For Each File in Folder.Files", а из скрипта T-SQL к их элементам можно обратиться только по имени папки/файла (exec sp_OAMethod @objItems, N'Item("FileName.txt")', @objItem out). То есть для получения списка папок и файлов объект Scripting.FileSystemObject не подходит. Поиск в интернете позволил мне сформулировать 4 различные способа решения этой задачи.

Фиксим "There is insufficient system memory in resource pool 'internal' to run this query" при запуске MS SQL Server

    Вчера MS SQL Server преподнес мне сюрприз. При создании новой базы данных он выдал ошибку "There is insufficient system memory in resource pool 'internal' to run this query" и "умер" – сервис SQL Server не запускался, а в ERRORLOG сыпались ошибки:

Msg 701, Level 17, State 130, Server XYZ, Line 1
There is insufficient system memory in resource pool 'internal' to run this query.

Работа с любыми структурами данных через DB-Aware контролы

    DB-Aware контролы в Delphi значительно упрощают жизнь разработчикам GUI-программ работающих с базами данных. Они многое делают сами без написания кода – отображают данные, позволяют пользователям их модифицировать и сохраняют изменения в базу данных. Но, что делать, если данные хранятся не в базе данных, а в массиве, списке, объекте или какой-нибудь другой структуре? Можно воспользоваться "memory table" – потомком TDataSet, который хранит данные в памяти. Скопировать в него данные, отобразить, обработать и скопировать обратно. Вариантов таких "memory table" много: TClientDataSet, TFDMemTable из FireDAC, TkbmMemTable, TVirtualTable из UniDAC, TMemTableEh из EhLib... Но есть способ решить этот вопрос проще, без копирования данных туда-сюда.

Разделение по диагонали ячейки MS Excel с двумя значениями

    Существует много способов разнообразить внешний вид таблицы в MS Excel. Например, иногда таблица может стать более наглядной, если в одной ячейке разместить сразу два значения, разделив ее по диагонали на две части. Одним из вариантов использования такой ячейки является шапка таблицы, в ячейке которой сразу два заголовка – для строк и для столбцов.