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.
