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/