Я дважды неправильно объяснил один баг в ora2pg

У меня было объяснение, почему ora2pg (Perl-инструмент, которым схемы Oracle, MySQL и MSSQL переносят в PostgreSQL) теряет внешние ключи: на файловом входе FK пропадает насовсем, потому что спросить о нём неоткуда. Я в это верил, пока не поднял живую MariaDB и не прогнал через неё ora2pg -m -t TABLE без единой дополнительной настройки. Внешний ключ оказался в выводе. Прямое противоречие тому, что я считал доказанным.

Проверил еще раз, с нуля.

ora2pg-gap-report это статический сканер поверх ora2pg: скармливаешь ему DDL-дамп, он ищет в нём места, где перенос молча теряет часть схемы, ещё до того, как ora2pg вообще запускался на реальных данных. Этот FK как раз одна из его 105 находок.

Сначала подтверждаю проблему в исходном виде

Ровно та же схема, файловый вход, -i schema.sql:

CREATE TABLE orders2 (  id INT NOT NULL,  customer_id INT NOT NULL,  CONSTRAINT fk_orders_customer    FOREIGN KEY (customer_id)    REFERENCES customers (id)    ON DELETE CASCADE
);

ora2pg -m -i schema.sql -t TABLE

CREATE TABLE orders2 (	id integer NOT NULL,	customer_id integer NOT NULL
) ;

Ноль вхождений FOREIGN KEY. Первая версия объяснения была простая: у ora2pg -t в принципе нет типа экспорта под внешние ключи, смотри список сам, там нет ни FKEY, ни CONSTRAINT. Логично звучит. И неверно.

Та же схема, но живым подключением

Поднял MariaDB локально, загрузил ту же таблицу, прогнал ora2pg -m с теми же настройками -t TABLE, только на этот раз через реальный ORACLE_DSN, а не файл:

CREATE TABLE orders2 (	id integer NOT NULL,	customer_id integer NOT NULL
) ;
CREATE INDEX fk_orders_customer ON orders2 (customer_id);
ALTER TABLE orders2 ADD PRIMARY KEY (id);
ALTER TABLE orders2 ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) MATCH SIMPLE ON DELETE CASCADE ON UPDATE RESTRICT;

Внешний ключ есть. Отдельным ALTER TABLE ADD CONSTRAINT, но есть. То есть -t TABLE умеет экспортировать FK, и рассуждение про «нет такого типа экспорта» отвечало не на тот вопрос.

Объяснение, которое продержалось до первой перепроверки

Полез в lib/Ora2Pg/MySQL.pm, строка 607. Вся функция foreignkey это один SQL-запрос:

sub _foreign_key
{    my ($self, $table, $owner) = @_;    ...    my $sql = "SELECT DISTINCT A.COLUMN_NAME, ...                FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS A                INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS B ...";    my $sth = $self->{dbh}->prepare($sql) or $self->logit("FATAL: " . $self->{dbh}->errstr . "\n", 0, 1);    $sth->execute or $self->logit("FATAL: " . $sth->errstr . "\n", 0, 1);    ...
}

$self->{dbh} это живое соединение с базой. Ни в этой функции, ни где-либо ещё в файле нет ни строчки, которая бы вытаскивала FOREIGN KEY (...) REFERENCES ... из текста DDL. Вывод напрашивался сам: внешние ключи это метаданные, которые ora2pg спрашивает только у живого INFORMATION_SCHEMA, при файловом входе спрашивать не у кого, значит там они и теряются. Записал это как объяснение.

Кстати, PRIMARY KEY в том же файловом прогоне выше уцелел, ALTER TABLE orders2 ADD PRIMARY KEY (id); в выводе есть. Он читается из другого места, из построчного разбора колонок, а не из join двух таблиц INFORMATION_SCHEMA, и это наблюдение было верным. Проблема оказалась не в нём.

Объяснение не подтвердилось второй раз

Взялся перепроверять этот же пример еще раз, уже для другого диалекта, и не получилось воспроизвести собственное объяснение. Ни на файле, ни живым подключением, пять разных попыток, и результат не укладывался в схему «файл теряет, живая база нет». Иногда живая база теряла FK точно так же, как файл.

Пересобрал все с нуля, теми же командами, но на этот раз не поленился прогнать оба варианта на одном и том же конфиге ora2pg вместо двух разных. И ora2pg -m -i schema.sql -t TABLE против дефолтного конфига дал ноль строк FOREIGN KEY, ровно как в начале статьи. Добавил в конфиг одну строку, PG_VERSION 16, ничего больше не тронул, и та же самая команда против того же файла выдала внешний ключ. Файл как был файлом, так и остался. Разница была в конфиге, который я не догадался зафиксировать как переменную.

Трассировка вместо чтения

Дальше читать исходники по третьему кругу смысла не было, слишком легко опять убедить себя в чём-то правдоподобном и неверном. Подключил к $self->{partitions_list} временный tie с Carp::cluck на запись, чтобы поймать не код, а момент, когда это значение меняется, с полным стеком вызовов.

Оказалось, createunique_keys() в lib/Ora2Pg.pm, общем файле, а не в MySQL.pm, вызывается для каждой таблицы с PRIMARY KEY или UNIQUE, и внутри есть строчка:

$reftable = $self->{partitions_list}{"\L$table\E"}{refrtable}    if (exists $self->{partitions_list}{"\L$table\E"}{refrtable});

exists на многоуровневом хеше в Perl это известная подстава: сама проверка молча создаёт $self->{partitions_list}{$table} = {}, даже если таблица вообще никогда не была партиционирована, просто потому что промежуточный уровень хеша пришлось материализовать, чтобы заглянуть на следующий. Чуть ниже та же функция обращается к {columns} как к массиву и авто-создаёт его тоже. После первого же вызова createunique_keys() для таблицы в partitions_list навсегда остаётся фантомная запись на неё.

А createforeign_keys(), когда добирается до конкретного FK, спрашивает именно этот хеш, прежде чем вывести ограничение:

next if ($self->{pg_supports_partition}    && exists $self->{partitions_list}{lc($desttable)}    && $self->{pg_version} <= 12);

Фантомная запись отвечает на exists утвердительно, PostgreSQL до 13 не разрешает ссылаться на партиционированную таблицу обычным FK, и вот next срабатывает вхолостую, для совершенно непартиционированной таблицы. Условие целиком зависит от $self->{pg_version}, а он равен 11, если PG_VERSION не прописан в конфиге. То есть ровно то состояние, в котором находится любой конфиг, который никто ещё не трогал.

Проверил на четырех комбинациях, файл и живое подключение к MariaDB, PG_VERSION не задан или 12 против 13:

Вывод читается по диагонали: колонка «вход» не коррелирует ни с чем, колонка PG_VERSION объясняет всё. Не файл против базы, а PG_VERSION против всего остального.

Строка с живой базой и объясняет, почему объяснение про файловый путь не подтвердилось при повторной проверке: с тем же самым дефолтным конфигом живое подключение теряет FK так же, как файл. Файл против живой базы вообще ни при чём. Проверил отдельно и для MSSQL, тем же способом, тот же результат: PG_VERSION не задан, ноль строк FOREIGN KEY, PG_VERSION 16, ограничение на месте.

Проверил ещё раз, чтобы не наступить на те же грабли дважды

FK оказался не про файл, но ещё два похожих примера стоило перепроверить тем же способом: вдруг они тоже на самом деле про PG_VERSION, а не про файл. С PG_VERSION 16 в конфиге результат не изменился ни для одного из них.

AUTO_INCREMENT=1000 в MySQL. Файловый вход:

CREATE TABLE invoices (  id INT PRIMARY KEY AUTO_INCREMENT,  amount DECIMAL(10,2)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4;

CREATE TABLE invoices (	id serial,	amount decimal(10,2)
) ;

Число 1000 нигде не встречается, хотя написано прямо в исходной строке. Живое подключение к той же самой базе:

CREATE TABLE invoices (	id serial,	amount decimal(10,2)
) ;
ALTER SEQUENCE invoices_id_seq RESTART WITH 1000;

Есть. Причина в MySQL.pm та же самая, ещё один запрос напрямую в базу:

my $sql = "SELECT TABLE_NAME, AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES            WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA = '$self->{schema}'            AND AUTO_INCREMENT IS NOT NULL";

Число 1000 лежит прямо в тексте CREATE TABLE, который ora2pg держит в руках. Он его туда даже не смотрит, спрашивает исключительно у живой базы. Вероятная причина в происхождении инструмента: ora2pg изначально писался под Oracle, а там AUTO_INCREMENT=<n> как опция таблицы попросту не существует, это чисто MySQL-синтаксис. Когда добавляли поддержку MySQL, статический разбор под него, похоже, просто не стали писать, раз уже есть живой INFORMATION_SCHEMA.

Третий пример, IDENTITY(1,1) в MSSQL. Живого SQL Server под рукой не было, поэтому проверить так же, живым прогоном, не вышло, но в MSSQL.pm функция getidentities устроена один в один: $self->{dbh}->prepare() на sys.identity_columns, никакого разбора IDENTITY(1,1) из текста колонки.

С этими двумя ничего не поменялось: стартовое значение автоинкремента и identity-свойство столбца действительно существуют для ora2pg только в момент разговора с живой базой, PG_VERSION тут ни при чём, INFORMATION_SCHEMA.TABLES и sys.identity_columns не имеют отношения к партициям вообще. Файл с DDL, даже подготовленный безупречно, для этих двух вещей не источник данных, независимо от диалекта. Foreign key в эту компанию не попадает, и именно поэтому его стоило проверять отдельно, а не по аналогии.

Две боковые проверки, которые ничего не изменили

Раз уж взялся перепроверять, заодно прогнал ещё пару похожих примеров из реестра, других по механизму, но подозрительно похожих по формулировке. Оба подтвердились как были.

COLLATE в MSSQL:

CREATE TABLE cs1 (    id int NOT NULL PRIMARY KEY,    code varchar(20) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
);

CREATE TABLE cs1 (	id integer NOT NULL,	code citext NOT NULL
) ;

CSAS значит регистрозависимое сравнение, а на выходе citext, тип с точностью до наоборот. Похоже на хардкод, но это документированная опция CASE_INSENSITIVE_SEARCH, по умолчанию равная citext для источников MSSQL:

if ($self->{case_insensitive_search} =~ /^citext$/i && $type =~ /^(?:char|varchar|text)/)
{	...	$type = 'citext';
}

Проверяется только базовый тип, COLLATE этого конкретного столбца не смотрит вообще никто. Для MySQL, кстати, эффект другой: COLLATE там просто выбрасывается, без замены на citext. Общее только одно: собственный COLLATE столбца не читает ни один диалект.

Второй пример, SIGNAL/RESIGNAL в MySQL и RAISERROR/THROW в MSSQL, копируется в тело процедуры как есть, в обоих диалектах:

SIGNAL SQLSTATE '45000' MESSAGE_TEXT := 'insufficient funds';

Схема грузится чисто, check_function_bodies = false тело не разбирает заранее, падает только при первом реальном вызове в проде. Здесь файл и живая база вообще не отличаются: источник для тела процедуры всегда один и тот же текст.

Что из этого следует

Изначальная идея была «одна и та же болезнь в разных диалектах». Она не выдержала первой проверки, и следующая версия, «файл не может достучаться до живого каталога», выглядела ничуть не хуже, объясняла ровно те же наблюдения и тоже оказалась неверной. Оба раза объяснение подгонялось под один пройденный тест, а не под условия, которые потом меняются независимо от кода. С PG_VERSION это сработало только потому, что я прогнал те же команды на конфиге, который отличался одной строкой, и результат перестал совпадать с рассказом.

Из трёх вещей две, стартовое значение автоинкремента и identity-столбец, действительно существуют для ora2pg только на живом подключении, и с этим ничего не поделать, кроме как экспортировать оттуда. Третья, foreign key, ломается из-за пары строк Perl-кода, к которым база данных и файл не имеют никакого отношения, и чинится одной строчкой конфига. Снаружи оба выглядели одинаково: FK нет, номер нет, столбец нет. Причины не имели между собой ничего общего.

Реестр всех 105 находок, включая эти три с дважды исправленным объяснением у одной из них, лежит в репозитории вместе с прогонами при разных настройках, где было что показывать. Ссылки на эти прогоны появились в документах уже после обеих проверок, задним числом было бы нечестно оставлять как было.

pip install ora2pg-gap-report
ora2pg-gap-report --dialect mysql path/to/dump.sql
ora2pg-gap-report --dialect mssql path/to/script.sql

github.com/Lunch418/ora2pg-gap-report

Если после этой статьи кто-то прогонит пример сам, а не поверит на слово, будет только правильно. Ровно об этом весь проект.