Обсуждение: plpgsql + dblink() question
I have a problem with (plpgsql + dblink) function call to another postgresql database server. The case is when I call a function (from Server 1 at psql prompt) that will call dblink to do some operation on another server(it will take certain time), i.e. Server 2, and meanwhile I just unplug the network cable to Server 2. The consequence is that the function will never return except I plug the cable into it again, moreover I cannot even cancel the query and stop the postgresql server (have to 'kill -9'.) My question is, for such case, why doesn't the statement_timeout set on server 1 work? I expect it will prompt " ..... query cancelled .....'' as usual when the statement_timeout expires. (I have set the statement_timeout to 10 seconds and it works fine except in the case mentioned above.) ---------------------------------------------------------------------------- -------------------------------------------------------- More Description to My Problem ---------------------------------------------------------------------------- -------------------------------------------------------- Having the following 2 plpgsql functions installed on both servers (Their database is identical) Server 1 Host Name: linux OS: Redhat Linux 7.2 Postgresql: 7.3.1 (statement_timeout=10seconds) Server 2 Host Name: linux2 OS: Redhat Linux 7.2 Postgresql: 7.3.1 (statement_timeout=10seconds) ---------------------------------------------------------------------------- -------------------------------------------------------- create or replace function test() returns int4 as ' ---------------------------------------------------------------------------- -------------------------------------------------------- declare tmp record; begin -- it just cannot return from the dblink statement on next line select * into tmp from dblink(''host=linux dbname=twins'', ''select mysleep();'') as (retval text); if tmp.retval=''-1'' then return -1; end if; return 1; end; ' language 'plpgsql'; ---------------------------------------------------------------------------- -------------------------------------------------------- create or replace function mysleep() returns text as ' ---------------------------------------------------------------------------- -------------------------------------------------------- declare sec int4; begin sec = 200000 * 15; -- it takes about 15 seconds for the servers to count while sec > 0 loop sec := sec - 1; end loop; return ''OK''; end; ' language 'plpgsql'; ---------------------------------------------------------------------------- -------------------------------------------------------- Under PSQL PROMPT of SERVER 1: twins=# select test();
Frankie wrote: > The case is when I call a function (from Server 1 at psql prompt) that will > call dblink to do some operation > on another server(it will take certain time), i.e. Server 2, and meanwhile I > just unplug the network cable to Server 2. > The consequence is that the function will never return except I plug the > cable into it again, moreover I cannot even cancel > the query and stop the postgresql server (have to 'kill -9'.) dblink just uses libpq to handle the communication, so you can use the connect_timeout connection parameter. It defaults to infinite if not explicitly set. Something like this should set a 5 second timeout: select * into tmp from dblink(''host=linux dbname=twins connect_timeout=5'', ''select mysleep();'') as (retval text); See: http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/libpq-connect.html HTH, Joe
Joe, thanks very much for your reply. However, the caller (function on Server 1) is still waiting for something to return endlessly. It seems to me that the connect_timeout option doesn't work. I've tried to set connect_timeout to 1, and it also has to wait for the function on Server 2 to complete. (Is this a bug?) Thanks very much for your help. Frankie Lam "Joe Conway" <mail@joeconway.com> wrote in message news:3E449DAB.9050106@joeconway.com... > Frankie wrote: > > The case is when I call a function (from Server 1 at psql prompt) that will > > call dblink to do some operation > > on another server(it will take certain time), i.e. Server 2, and meanwhile I > > just unplug the network cable to Server 2. > > The consequence is that the function will never return except I plug the > > cable into it again, moreover I cannot even cancel > > the query and stop the postgresql server (have to 'kill -9'.) > > dblink just uses libpq to handle the communication, so you can use the > connect_timeout connection parameter. It defaults to infinite if not > explicitly set. Something like this should set a 5 second timeout: > > select * into tmp from dblink(''host=linux dbname=twins connect_timeout=5'', > ''select mysleep();'') as (retval text); > > See: > http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/libpq-connect.ht ml > > HTH, > > Joe > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
oh, is this a blocking connection issue of libPQ? (PQexec waits for the command to be completed, and it just won't return?) "Frankie" <frankie@ucr.com.hk> wrote in message news:b225au$o4g$1@news.hub.org... > I have a problem with (plpgsql + dblink) function call to another postgresql > database server. > > The case is when I call a function (from Server 1 at psql prompt) that will > call dblink to do some operation > on another server(it will take certain time), i.e. Server 2, and meanwhile I > just unplug the network cable to Server 2. > The consequence is that the function will never return except I plug the > cable into it again, moreover I cannot even cancel > the query and stop the postgresql server (have to 'kill -9'.) > > My question is, for such case, why doesn't the statement_timeout set on > server 1 work? > I expect it will prompt " ..... query cancelled .....'' as usual when the > statement_timeout expires. > (I have set the statement_timeout to 10 seconds and it works fine except in > the case mentioned above.) > > -------------------------------------------------------------------------- -- > -------------------------------------------------------- > More Description to My Problem > -------------------------------------------------------------------------- -- > -------------------------------------------------------- > Having the following 2 plpgsql functions installed on both servers (Their > database is identical) > > Server 1 > Host Name: linux > OS: Redhat Linux 7.2 > Postgresql: 7.3.1 (statement_timeout=10seconds) > > Server 2 > Host Name: linux2 > OS: Redhat Linux 7.2 > Postgresql: 7.3.1 (statement_timeout=10seconds) > > -------------------------------------------------------------------------- -- > -------------------------------------------------------- > create or replace function test() returns int4 as ' > -------------------------------------------------------------------------- -- > -------------------------------------------------------- > declare > tmp record; > begin > > -- it just cannot return from the dblink statement on next line > select * into tmp from dblink(''host=linux dbname=twins'', ''select > mysleep();'') as (retval text); > > if tmp.retval=''-1'' then > return -1; > end if; > > return 1; > end; > ' language 'plpgsql'; > -------------------------------------------------------------------------- -- > -------------------------------------------------------- > create or replace function mysleep() returns text as ' > -------------------------------------------------------------------------- -- > -------------------------------------------------------- > declare > sec int4; > begin > > sec = 200000 * 15; -- it takes about 15 seconds for the servers to count > > while sec > 0 loop > sec := sec - 1; > end loop; > > return ''OK''; > end; > ' language 'plpgsql'; > -------------------------------------------------------------------------- -- > -------------------------------------------------------- > > Under PSQL PROMPT of SERVER 1: > > twins=# select test(); > >