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

From Frankie
Subject plpgsql + dblink() question
Date
Msg-id b225au$o4g$1@news.hub.org
Whole thread Raw
Responses Re: plpgsql + dblink() question  (Joe Conway <mail@joeconway.com>)
List pgsql-sql
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();




pgsql-sql by date:

Previous
From: Luis Magaña
Date:
Subject: Re: Start and End Day of a Week
Next
From: "John Cavacas"
Date:
Subject: query help/sugestions