Postgres Pro
Downloads
Home   >   mailing lists

Thread: Dblink and ISDN

Dblink and ISDN

From
"Darko Prenosil"
Date:
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 !

Re: Dblink and ISDN

From
Joe Conway
Date:
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

Re: Dblink and ISDN

From
Joe Conway
Date:
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

Re: Dblink and ISDN

From
"Rod Taylor"
Date:
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)
>

Re: Dblink and ISDN

From
Joe Conway
Date:
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