Re: DBLink

From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Thomaz Luiz Santos
Sent: Thursday, April 25, 2013 10:22 AM
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] DBLink

hello! pgAdmins :D

I am trying to use the dblink in a trigger, however when the computer (source) that is running the triggers are not accessible by ethernet to the target computer, the dblink returns me an error and the trigger is not executed as planned, it is terminated and input records in the table are not written.

my idea is that the insert is done in the table on the local computer if the local computer has access to ethernet it should send the data to another computer using the dblink, but do not have access ethernet writes in the local table.

thank you.

INSERT INTO teste(id, valor) VALUES (1,'valor'); -- On Ethernet, connected OK
INSERT INTO teste(id, valor) VALUES (2,'valor'); -- Off Ethernet, disconnected ERROR.

Error:

ERRO:  could not establish connection
DETAIL:  não pôde conectar ao servidor: No route to host (0x00002751/10065)
O servidor está executando na máquina "192.168.102.23" e aceitando
conexões TCP/IP na porta 5432?

CONTEXT:  comando SQL "SELECT (select count(*) from dblink_exec('host=192.168.102.23 port=5432 dbname=teste password=admin ', '' || $1 || '', false))"
PL/pgSQL function "senddatato" line 6 at atribuição
comando SQL "SELECT (SELECT SendDataTo(SQL))"
PL/pgSQL function "teste_after_insert" line 8 at PERFORM

********** Error **********

ERRO: could not establish connection
SQL state: 08001
Context: comando SQL "SELECT (select count(*) from dblink_exec('host=192.168.102.23 port=5432 dbname=teste password=admin ', '' || $1 || '', false))"
PL/pgSQL function "senddatato" line 6 at atribuição
comando SQL "SELECT (SELECT SendDataTo(SQL))"
PL/pgSQL function "teste_after_insert" line 8 at PERFORM

Code: 

CREATE DATABASE teste
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'Portuguese, Brazil'
       LC_CTYPE = 'Portuguese, Brazil'
       CONNECTION LIMIT = -1;

CREATE TABLE teste
(
  id bigint NOT NULL,
  valor text,
  CONSTRAINT teste_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE teste
  OWNER TO postgres;

CREATE OR REPLACE FUNCTION senddatato(sql text)
  RETURNS integer AS
$BODY$
DECLARE 
    ServerON int;

BEGIN    
    ServerON := (select count(*) from dblink_exec('host=192.168.102.23 port=5432 dbname=teste password=admin ', '' || $1 || '', false)); 
    return ServerON;   
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION senddatatoserver(text)
  OWNER TO postgres;

CREATE OR REPLACE FUNCTION teste_after_insert()
  RETURNS trigger AS
$BODY$
DECLARE 
SQL text;
BEGIN 
    
    SQL := 'insert into teste(id,valor) values (' || NEW.id || ',' || '''' || NEW.valor || '''' || ')';
    IF (true) THEN
         PERFORM(SELECT SendDataTo(SQL));  
         RETURN NEW;
    END IF;

    RETURN NEW;    
    RAISE NOTICE 'gravado local!';
    
 END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION teste_after_insert()
  OWNER TO postgres;

-- 
------------------------------
Thomaz Luiz Santos
Linux User: #359356
http://thomaz.santos.googlepages.com/

Did you try to intercept this error with exception handler inside senddatato(...) function?
See PG docs:

http://www.postgresql.org/docs/9.2/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Regards,
Igor Neyman