Блог

Главная - Вопросы по программированию - Данные SQL Server в PostgreSQL при вставке триггера sql

Данные SQL Server в PostgreSQL при вставке триггера sql

#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. Вы должны начать с НАЧАЛА РАСПРЕДЕЛЕННОЙ ТРАНЗАКЦИИ.

Метки: