Re: plpgsql + dblink() question - Mailing list pgsql-sql

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();
>
>