X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5E72B4753A1 for ; Mon, 10 Feb 2003 02:46:53 -0500 (EST) Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id EA111474E5C for ; Mon, 10 Feb 2003 02:46:52 -0500 (EST) Received: by news.hub.org (Postfix, from userid 8) id D322C43A666; Mon, 10 Feb 2003 02:46:52 -0500 (EST) From: "Frankie Lam" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: plpgsql + dblink() question Date: Mon, 10 Feb 2003 15:47:12 +0800 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 100 Message-ID: References: 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/188 X-Sequence-Number: 11586 oh, is this a blocking connection issue of libPQ? (PQexec waits for the command to be completed, and it just won't return?) "Frankie" wrote in message news:b225au$o4g$1@news.hub.org... > 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(); > >