dblink在UPDATE触发器之后无法更新同一数据库上的表 [英] dblink can't update a table on the same database in an after UPDATE trigger

问题描述

我正在使用slony复制数据库，并尝试创建一个触发器，该触发器将在对表进行INSERT操作之后触发.

I am working on a database replicated using slony and trying to create a trigger which will be triggered after an INSERT operation on a table.

在此触发器中，我尝试使用dblink更新THE SAME数据库的另一个表. 但是我收到一个错误消息，当我尝试更新第二张表时，我刚刚插入到第一张表中的值不存在.
我使用dblink是因为如果我使用常规的UPDATE语句更新第二个表，则不会触发slony同步.

In this trigger I am trying to update another table of THE SAME database using dblink. But I am getting an error saying that the value I just inserted in the first table does not exist when I am trying to update the second table.
I am using dblink because if I update the second table with a regular UPDATE statement, slony synchronization is not triggered.

第一张桌子:

CREATE TABLE "COFFRETS"
(
  "NUM_SERIE" character varying NOT NULL,
  "DATE_CREATION" timestamp without time zone NOT NULL DEFAULT now(),
  "DATE_DERNIERE_MODIF" timestamp without time zone NOT NULL DEFAULT now(),
  "NOMENCLATURE" integer NOT NULL,
  "COMMANDES_DETAILS_ID" integer,
  "DEBLOCAGES_ID" integer,
  "ETAT" integer NOT NULL DEFAULT 1,
  "EXPEDITIONS_ID" bigint,
  "STOCKISTE_EXPE_ID" integer,
  "STOCKISTE_RCPT_ID" integer,
  "SITE_ID" integer,
  "FACTURES_ID" integer,
  "CMDDETECH_ID" integer,
  "FACTURE_AVE_ID" integer,
  "SHIPPING_ID" integer,
  "SYNCHRONISE" boolean DEFAULT false,
  CONSTRAINT "COFFRETS_pkey" PRIMARY KEY ("NUM_SERIE"),
  CONSTRAINT "FK_SHIPPING" FOREIGN KEY ("SHIPPING_ID")
      REFERENCES "SHIPPING" ("ID") MATCH SIMPLE
);

第二张表:

CREATE TABLE "SHIPPING"
(
  "DATE_AJOUT" timestamp without time zone NOT NULL,
  "DATE_DERNIERE_MODIF" timestamp without time zone NOT NULL,
  "ORDRE_PRODUCTION" text,
  "AIRE_APPRO" text,
  "DATE_ENVOI" timestamp without time zone,
  "DATE_LIVRAISON" timestamp without time zone,
  "REF_CARRIER" text,
  "QTE" numeric,
  "NUM_CONTRAT" text,
  "COMMENTAIRES" text,
  "ID" serial NOT NULL,
  "POSTE_TRAVAIL" text,
  "POSTE_CONTRAT" integer,
  CONSTRAINT "Pkey_ID_SHIPPING" PRIMARY KEY ("ID")
);

触发:

CREATE TRIGGER test
  AFTER INSERT
  ON "SHIPPING"
  FOR EACH ROW
  EXECUTE PROCEDURE "AffectationShipping"();

触发功能:

CREATE OR REPLACE FUNCTION "AffectationShipping"()
  RETURNS trigger AS
$BODY$DECLARE   
    coffretNumSerie text;
    message text;
    num_site    integer;
    txt text;

BEGIN   

    RAISE NOTICE '-----------------------------------------------------------------------------'; 
    RAISE NOTICE '-                     AffectationShipping                                   -'; 
    RAISE NOTICE '-----------------------------------------------------------------------------'; 

    --lecture du numéro de site
    num_site=0;
    SELECT "Value" INTO num_site FROM "APPLICATION_PARAMETERS" WHERE "Name" = 'SITE_ID';

    --Récupération du coffret concerné. un seul coffret car on a un shipping par coffret chez aquasnap
    SELECT  "COFFRETS"."NUM_SERIE" INTO coffretNumSerie
    FROM    "COFFRETS" INNER JOIN "DEBLOCAGES" ON 
            "COFFRETS"."DEBLOCAGES_ID" = "DEBLOCAGES"."ID" 
    WHERE   "COFFRETS"."SHIPPING_ID" IS NULL AND 
            "DEBLOCAGES"."NumOrdreProduction" = NEW."ORDRE_PRODUCTION"
    LIMIT 1;

    IF coffretNumSerie != '' THEN
    RAISE NOTICE 'ID = %', NEW."ID";
    RAISE NOTICE 'param = %', (SELECT parametre_get('Chaine_connexion_bdd_Atelier')::text);
    RAISE NOTICE 'Statement = %', ('UPDATE "COFFRETS" SET "SHIPPING_ID" = ' || NEW."ID" || '  WHERE "NUM_SERIE" = ''' ||coffretNumSerie ||''';');
        PERFORM dblink_exec((SELECT parametre_get('Chaine_connexion_bdd_Atelier'))::text, ('UPDATE "COFFRETS" SET "SHIPPING_ID" = ' || NEW."ID" || '  WHERE "NUM_SERIE" = ''' ||coffretNumSerie ||''';'));
        RAISE NOTICE 'Affectation du shipping Num.[%], ordre de production Num.[%] au coffret Num.[%].',  NEW."ID" ,NEW."ORDRE_PRODUCTION",coffretNumSerie;
        --Log d'un message d'information
        message = 'Affectation du shipping Num.['|| NEW."ID" ||'], ordre de production Num.['|| NEW."ORDRE_PRODUCTION" ||'] au coffret Num.['|| coffretNumSerie ||'].';
        --enregistrement de l'information
        INSERT INTO "ERRORS_LOG" ("DATE","MESSAGE","ERROR_TYPES","LOCALIZATION", "TYPE_MESSAGE_ID", "SITE_ID" )
        VALUES (now(),message,'Information' ,'Trigger associations coffrets - Shipping : AffectationShipping',4,num_site);
    ELSE --LogErreur

        RAISE NOTICE 'Aucun coffret correspondant au shipping Num.[%], ordre de production Num.[%].' , NEW."ID" ,NEW."ORDRE_PRODUCTION" ;

        --composition du message d'erreur
        message = 'Aucun coffret correspondant au shipping Num.['|| NEW."ID" ||'], ordre de production Num.[' || NEW."ORDRE_PRODUCTION" || '].';
        --enregistrement de l'erreur de type "Gestion COFFRETS"     

        INSERT INTO "ERRORS_LOG" ("DATE","MESSAGE","ERROR_TYPES","LOCALIZATION", "TYPE_MESSAGE_ID", "SITE_ID" )
        VALUES (now(),message,'Erreur' ,'Trigger associations coffrets - Shipping : AffectationShipping',3,num_site);

    END IF; 

    RAISE NOTICE '-----------------------------------------------------------------------------'; 
    RAISE NOTICE '-Fin                      AffectationShipping                               -'; 
    RAISE NOTICE '-----------------------------------------------------------------------------'; 

    RETURN NEW;
END;$BODY$
  LANGUAGE plpgsql;

对于错误消息，我不知道如何使PostgreSQL用英语打印消息.

As for the error message, I do not know how to make PostgreSQL print messages in English.

基本上说SHIPPING_ID外键在运送表中不存在.

Basically it says that the SHIPPING_ID foreign key does not exist in the shipping table.

奇怪的是，当我尝试不使用dblink_exec进行更新时，它工作正常.但是正如我之前所说，我需要通过dblink进行此更新，以确保slony会注意到该更新.

The weird thing is that when I try to do the update without using dblink_exec it works fine. But as I previously said, I need to to this update through dblink to make sure slony will notice the update.

推荐答案

通过在单独连接中访问目标数据库来进行操作.这会带来一些内在的后果:

dblink operates by accessing the target database in a separate connection. This has a few inherent consequences:

• dblink在单独的(准自治")事务中运行.
• dblink命令的效果无法回滚.

对您来说最重要的是:

• 由于它在单独的事务中运行，因此无法看到尚未提交的调用事务的任何更改.

这篇关于dblink在UPDATE触发器之后无法更新同一数据库上的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！