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