Skip site navigation (1) Skip section navigation (2)

BUG #4443: Dblink wrong output on trigger

From: "Jose Manuel Mira" <labsig(dot)iug(at)ua(dot)es>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4443: Dblink wrong output on trigger
Date: 2008-09-30 07:29:08
Message-ID: 200809300729.m8U7T868088117@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4443
Logged by:          Jose Manuel Mira
Email address:      labsig(dot)iug(at)ua(dot)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.

Responses

pgsql-bugs by date

Next:From: tomasDate: 2008-09-30 08:44:19
Subject: Re: BUG #4441: Error in postgres Installing
Previous:From: HemanthaDate: 2008-09-30 01:28:05
Subject: BUG #4441: Error in postgres Installing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group