dblink can't update a table on the same databa

Question

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.

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.

First table:

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
);

Second table:

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")
);

Trigger:

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

Trigger function:

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;

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

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

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.

Answer 1

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

• dblink runs in a separate (quasi "autonomous") transaction.
• Effects of dblink commands cannot be rolled back.

And most importantly for you:

• Since it runs in a separate transaction it cannot see any changes of the calling transaction that have not been committed, yet.