BUG #4443: Dblink wrong output on trigger - Mailing list pgsql-bugs

From Jose Manuel Mira
Subject BUG #4443: Dblink wrong output on trigger
Date
Msg-id 200809300729.m8U7T868088117@wwwmaster.postgresql.org
Whole thread Raw
Responses Re: BUG #4443: Dblink wrong output on trigger
List pgsql-bugs
The following bug has been logged online:

Bug reference:      4443
Logged by:          Jose Manuel Mira
Email address:      labsig.iug@ua.es
PostgreSQL version: PostgreSQL 8.3.
Operating system:   Ubuntu 8.04 AMD64
Description:        Dblink wrong output on trigger
Details:

I've got this plpgsql function to test if there are any open connection with
dblink.

CREATE OR REPLACE FUNCTION comprueba_conexiones()
  RETURNS bool AS
$BODY$
DECLARE
    cuenta integer;
    resultado bool;
BEGIN
   SELECT count(dblink_get_connections[1])::int INTO cuenta FROM
dblink_get_connections();
   IF cuenta = 0 then
      resultado := FALSE;
   ELSE
      IF cuenta > 0 then
        resultado := TRUE;
      END IF;
   END IF;
    RETURN resultado;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;


If there are any open connection the function returns true, else return
false. We tested successfully this function. The result is the expected.

We make a trigger to insert a row into table in other database.

CREATE OR REPLACE FUNCTION actualiza_estancia()
  RETURNS trigger AS
$BODY$DECLARE
         sentencia text;
    conn text;
    conexiones bool;
BEGIN
    --Comprobar conexiones
    conn := 'dbname=XXX user=XXX password=XXX host=localhost';
    select comprueba_conexiones() into conexiones;

        RAISE NOTICE 'Connection state %', conexiones;
    IF conexiones = TRUE then
        RAISE NOTICE 'Open connection %. now we close it',conexiones;
     PERFORM dblink_disconnect('conexion');

    END IF;
    RAISE NOTICE 'New connection %',conexiones;
    PERFORM dblink_connect('conexion',conn);
-- NEW.sentencia is a true sql sentence
    PERFORM dblink_exec ('conexion', NEW.sentencia);
    PERFORM dblink_disconnect('conexion');
    RAISE NOTICE 'Row insert successfully';
RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION actualiza_estancia() OWNER TO postgres;



drop trigger ejecuta_actualizar_estancia on expediente_est;

CREATE TRIGGER ejecuta_actualizar_estancia
 AFTER INSERT
 ON expediente_est
 FOR EACH ROW
 EXECUTE PROCEDURE actualiza_estancia();


If we open manually a connection (with this sentence:
select dblink_connect('conexion','dbname=XXX user=XXX password=XXX
host=localhost') ) the trigger must show a notice that there are open
connections and proceed to close it. The trigger never evaluates this
condition and proceeds to insert a row, not taking into account that there
are open connections with the same name.

pgsql-bugs by date:

Previous
From: "Hemantha"
Date:
Subject: BUG #4441: Error in postgres Installing
Next
From: tomas@tuxteam.de
Date:
Subject: Re: BUG #4441: Error in postgres Installing