Обсуждение: 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/
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomaz Luiz Santos Sent: Thursday, April 25, 2013 10:22 AM To: pgsql-admin@postgresql.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 accessibleby 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 shouldsend 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
Isn't that basically what replication is supposed to do? RobR, quite possibly revealing his ignorance about replication.
No. Here it's the master pushing change to the slave, rather than the slave pulling it (as you'd normally expect), hence the inner fragility of the model. Moreover, replication is about having "exactly the same thing" in two places, while this could be an audit log he keeps in its historical completeness on a remote box, while pruning the local table on regular basis, so not exactly a replication.
Bèrto
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
On 25 April 2013 16:17, Rob Richardson <RDRichardson@rad-con.com> wrote:
Isn't that basically what replication is supposed to do?
RobR, quite possibly revealing his ignorance about replication.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.