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