CODEWITHSUNDEEP
postgresqlconnectiondblink
Savke
Savke

Reputation: 131

How to overcome problem with dblink when one server is unavailable

I create a view like this 

 CREATE view  zaliha as
    SELECT * FROM public.dblink
('meteor','SELECT z.skladiste,ar.sifra,z.velicina,z.kolicina,z.rezervacija,z.kolicina - z.rezervacija
 as raspolozivo
 from zaliha z, artikli ar
 where ar.id=z.artikal
 and (kolicina <> 0 or kolicina <> 0)') 
AS DATA(skladiste CHARACTER VARYING, sifra CHARACTER VARYING, velicina CHARACTER VARYING, kolicina NUMERIC,
        rezervacija NUMERIC, raspolozivo NUMERIC)

        union

SELECT * FROM public.dblink
('dorcol','SELECT z.skladiste,ar.sifra,z.velicina,z.kolicina,z.rezervacija,z.kolicina - z.rezervacija
 as raspolozivo
 from zaliha z, artikli ar
 where ar.id=z.artikal
 and (kolicina <> 0 or kolicina <> 0)') 
AS DATA(skladiste CHARACTER VARYING, sifra CHARACTER VARYING, velicina CHARACTER VARYING, kolicina NUMERIC,
        rezervacija NUMERIC, raspolozivo NUMERIC)

        union

SELECT * FROM public.dblink
('uzice','SELECT z.skladiste,ar.sifra,z.velicina,z.kolicina,z.rezervacija,z.kolicina - z.rezervacija
 as raspolozivo
 from zaliha z, artikli ar
 where ar.id=z.artikal
 and (kolicina <> 0 or kolicina <> 0)') 
AS DATA(skladiste CHARACTER VARYING, sifra CHARACTER VARYING, velicina CHARACTER VARYING, kolicina NUMERIC,
        rezervacija NUMERIC, raspolozivo NUMERIC)

In fact this is union of 3 same select, but from different servers. What I need, is that I get result of all availables servers, and if there is error with 1 servers, that result to exclude. For example, server meteor and dorcol are available, and server uzice is not, I wanna this view result only of meteor and dorcol, and exclude error with uzice.

ERROR when one server is not available is:

ERROR:  could not establish connection

    DETAIL:  could not connect to server: Connection timed out
        Is the server running on host "x.x.x.x" and accepting
        TCP/IP connections on port 5432?

Is that possible?

Upvotes: 0

Views: 245

Answers (1)

jjanes
jjanes

Reputation: 44363

You can't do this with a view, but you can do it with a set returning function.

I've changed it to use pgbench_branches table for simplicity.

CREATE OR REPLACE FUNCTION public.fail_tolerant()                                                                                                        
 RETURNS SETOF pgbench_branches
 LANGUAGE plpgsql
AS $function$ 
declare 
connections text[]='{host=127.0.0.1,host=192.168.0.bad}';
conn text;  
begin 
  foreach conn in array connections loop 
    begin
      return query SELECT * FROM public.dblink (conn,'SELECT * from pgbench_branches') AS DATA(bid integer, bbalance integer, filler character(88));
    EXCEPTION when others then          
       RAISE NOTICE 'exception caught';
    END;
  end loop;
end $function$;

This doesn't do the de-duplication feature of UNION, so you would want to add that separately:

select distinct * from fail_tolerant();

You would probably want to do better logging than what I do here. If any connection attempts gets black-holed, rather than accepted or rejected immediately, this could take a long time to complete. So you might want to set a connect_timeout in each connection string.

Upvotes: 1

Related Questions