X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DF23A474E5C
	for <pgsql-sql@postgresql.org>; Sat,  8 Feb 2003 00:42:37 -0500 (EST)
Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id 77E9D475A97
	for <pgsql-sql@postgresql.org>; Sat,  8 Feb 2003 00:42:27 -0500 (EST)
Received: by news.hub.org (Postfix, from userid 8)
	id 9E88143A923; Sat,  8 Feb 2003 00:41:51 -0500 (EST)
From: "Frankie" <frankie@ucr.com.hk>
X-Newsgroups: comp.databases.postgresql.sql
Subject: plpgsql + dblink() question
Date: Sat, 8 Feb 2003 13:41:56 +0800
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 82
Message-ID: <b225au$o4g$1@news.hub.org>
X-Complaints-To: usenet@news.hub.org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/165
X-Sequence-Number: 11563

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