#sql-server #postgresql #triggers #msdtc
Вопрос:
Я пытаюсь переместить данные с MS SQL Server (2014) на PostgreSQL (12.6) с помощью драйвера odbc psqlodbc(12_02_0000-x64) и Postgresql в качестве связанного сервера в SSMS на моей локальной машине.
Цель состоит в том, чтобы переместить данные, вставленные в таблицу SQL Server, в таблицу PGSQL с помощью триггера вставки в таблице sql server. Но я всегда получаю ошибку координатора распределенных транзакций MS:
Msg 8522, Уровень 18, Состояние 1, Строка 1 Координатор распределенных транзакций Microsoft (MS DTC) остановил эту транзакцию.
Простая вставка в .. select отлично работает:
INSERT INTO [POSTGRESQL].[MyPgDb].[MyPgSchema].[MyPgTable](Id, StartTime, EndTime, TestMessage)
(SELECT [Id], [StartTime], [EndTime], [TestMessage] FROM [MySqlDb].[dbo].[MySqlTable]);
Это триггер, который я использую:
USE [MySqlDb]
GO
/****** Object: Trigger [dbo].[TriggerInsertMsg] Script Date: 02.07.2021 09:15:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TriggerInsertMsg]
ON [MySqlDb].[dbo].[MySqlTable]
AFTER INSERT
AS
BEGIN TRAN
SET NOCOUNT ON
-- SET XACT_ABORT ON??
INSERT INTO [POSTGRESQL].[MyPgDb].[MyPgSchema].[MyPgTable](Id, StartTime, EndTime, TestMessage)
(SELECT [inserted].Id, [inserted].StartTime, [inserted].EndTime, [inserted].TestMessage FROM INSERTED)
COMMIT;
Я попытался выполнить следующие действия без какого-либо успеха:
- Использование
BEGIN TRAN.. COMMIT;
вместоBEGIN..END
в триггере - Запуск службы MSDTC
- Включение MSDTC в брандмауэре
- Включение транзакций XA в dcomcnfg
- с помощью
SET XACT_ABORT ON
Триггер отлично работает, когда я заменяю связанный сервер pg другой таблицей SQL. Нужно ли мне настраивать некоторую конфигурацию PostgreSQL, чтобы разрешить транзакции, поступающие с SQL server и/или MS DTC? Где я могу найти ссылку на ошибку (Msg 8522, Уровень 18, Состояние 1, Строка 1)?
Комментарии:
1. Честно говоря, я бы не стал делать этого в а
TRIGGER
. ЕслиTRIGGER
произойдет сбой по какой-либо причине, тоINSERT
также произойдет и то, что, как говорится в ошибке,INSERT
удаленный экземпляр будет находиться в той же транзакции, и если экземпляр не сможет, следовательно, создать распределенную транзакцию, она должна завершиться сбоем; именно это, по-видимому, происходит здесь.2. Вы, скорее всего, откажетесь, возможно, включив столбец в свои данные, чтобы указать, что он был «скопирован», а затем процесс, который регулярно «копирует» ожидающие данные с экземпляра SQL Server на экземпляр PostgreSQL, а затем помечает данные в SQL Server как «скопированные». В противном случае, если задержка недопустима, вы, вероятно, захотите изучить решения для репликации или зеркального отображения.
3. Транзакция BEGIN TRAN/COMMIT в триггере избыточна, поскольку триггеры всегда выполняются в контексте транзакции оператора, запустившего триггер. Если вам действительно не нужны распределенные транзакции, измените параметр связанный сервер:
EXEC master.dbo.sp_serveroption @server=N'POSTGRESQL', @optname=N'remote proc transaction promotion', @optvalue=N'false';
4. По соображениям производительности вы также можете захотеть добавить
IF (NOT EXISTS (SELECT 1 FROM inserted)) RETURN;
в верхней части триггера5. Вы должны начать с НАЧАЛА РАСПРЕДЕЛЕННОЙ ТРАНЗАКЦИИ.