plpgsql + dblink() question

From: "Frankie" <frankie(at)ucr(dot)com(dot)hk>
To: pgsql-sql(at)postgresql(dot)org
Subject: plpgsql + dblink() question
Date: 2003-02-08 05:41:56
Message-ID: b225au$o4g$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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();

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message John Cavacas 2003-02-08 05:50:20 query help/sugestions
Previous Message Luis Magaña 2003-02-07 23:52:39 Re: [SQL] Start and End Day of a Week