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 !








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 
!
Tue, 2 Apr 2002 10:25:44 +0200
"Darko Prenosil" <Darko.Prenosil@finteh.hr>
Dblink and ISDN
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


Tue, 02 Apr 2002 09:49:52 -0800
Joe Conway <mail@joeconway.com>
Re: 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


Tue, 02 Apr 2002 09:58:55 -0800
Joe Conway <mail@joeconway.com>
Re: Dblink and ISDN
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)
>


Tue, 2 Apr 2002 13:16:57 -0500
"Rod Taylor" <rbt@zort.ca>
Re: Dblink and ISDN
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


Tue, 02 Apr 2002 11:30:07 -0800
Joe Conway <mail@joeconway.com>
Re: Dblink and ISDN