Dblink and ISDN
We have a request from our customers to link two database servers through the ISDN link.
We found the dblink in the contrib directory, and it works ,but there is one big problem.
I'll try to explain it using the sample from README.dblink:
SAMPLE:
create view myremotetable as
select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1 user=postgres password=postgres'
,'select proname, prosrc from pg_proc') as dblink_p) as t1;
select f1, f2 from myremotetable where f1 like 'bytea%';
When the select is executed:
1. all the data from table pg_proc are retrieved from remote database
2. then where clause is executed against that data (on the local side)
This behaviour is OK if the whole story is happenning on local network, but
in our case data should be send through slow ISDN connection.
Is it possible to write a rule that uses the current SQL expression and sends this expression to the remote database ?
In this case only wanted data would be send through the network.
Thank You in advance !
Darko Prenosil wrote:
SAMPLE:
create view myremotetable as
select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
user=postgres password=postgres'
,'select proname, prosrc from pg_proc') as dblink_p)
as t1;select f1, f2 from myremotetable where f1 like 'bytea%';
You could write the query directly instead of using a view, i.e.
select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
user=postgres password=postgres','select proname, prosrc from pg_proc')
as dblink_p WHERE proname LIKE 'bytea%') as t1;
Is it possible to write a rule that uses the current SQL expression and
sends this expression to the remote database ?In this case only wanted data would be send through the network.
I'm not experienced in using PostgreSQL rules, but I don't see a way to
access the current SQL expression. Hopefully someone more knowledgeable
will chime in here.
Joe
Joe Conway wrote:
Darko Prenosil wrote:
SAMPLE:
create view myremotetable as
select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
user=postgres password=postgres'
,'select proname, prosrc from pg_proc') as
dblink_p) as t1;select f1, f2 from myremotetable where f1 like 'bytea%';
You could write the query directly instead of using a view, i.e.
select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
user=postgres password=postgres','select proname, prosrc from pg_proc')
as dblink_p WHERE proname LIKE 'bytea%') as t1;
Oops, messed up my cut and paste, and forgot to double the quotes around
bytea%. This one I tested ;) to work fine:
select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
user=postgres password=postgres','select proname, prosrc from pg_proc
WHERE proname LIKE ''bytea%''')
as dblink_p) as t1;
Joe
Out of curiousity, what happens if the remove server is unavailable?
----- Original Message -----
From: "Joe Conway" <mail@joeconway.com>
To: "Darko Prenosil" <Darko.Prenosil@finteh.hr>
Cc: "Hackers" <pgsql-hackers@postgresql.org>
Sent: Tuesday, April 02, 2002 12:58 PM
Subject: Re: [HACKERS] Dblink and ISDN
Joe Conway wrote:
Darko Prenosil wrote:
SAMPLE:
create view myremotetable as
select dblink_tok(t1.dblink_p,0) as f1,
dblink_tok(t1.dblink_p,1) as f2
from (select dblink('hostaddr=127.0.0.1 port=5432
dbname=template1
user=postgres password=postgres'
,'select proname, prosrc from pg_proc') as
dblink_p) as t1;select f1, f2 from myremotetable where f1 like 'bytea%';
You could write the query directly instead of using a view, i.e.
select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1)
as f2
from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
user=postgres password=postgres','select proname, prosrc from
pg_proc')
as dblink_p WHERE proname LIKE 'bytea%') as t1;
Oops, messed up my cut and paste, and forgot to double the quotes
around
bytea%. This one I tested ;) to work fine:
select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as
f2
from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1
user=postgres password=postgres','select proname, prosrc from
pg_proc
WHERE proname LIKE ''bytea%''')
as dblink_p) as t1;Joe
---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
Show quoted text
Rod Taylor wrote:
Out of curiousity, what happens if the remove server is unavailable?
I tried it against a bogus IP, and this is what I got:
test=# select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1)
as f2 from (select dblink('hostaddr=123.45.67.8
dbname=template1','select proname, prosrc from pg_proc WHERE proname
LIKE ''bytea%''') as dblink_p) as t1;
ERROR: dblink: connection error: could not connect to server:
Connection timed out
Is the server running on host 123.45.67.8 and accepting
TCP/IP connections on port 5432?
test=#
dblink just uses libpq to make a client connection, and thus inherits
libpq's response.
Joe