From: Frankie Lam <frankie@ucr.com.hk>
To: pgsql-sql@postgresql.org
Subject: Re: plpgsql + dblink() question
Date: Mon, 10 Feb 2003 15:47:12 +0800
Message-ID: <b27ldc$22ra$1@news.hub.org> (raw)
References: <b225au$o4g$1@news.hub.org>
oh, is this a blocking connection issue of libPQ?
(PQexec waits for the command to be completed, and it just won't return?)
"Frankie" <frankie@ucr.com.hk> 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();
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: frankie@ucr.com.hk
Subject: Re: plpgsql + dblink() question
In-Reply-To: <b27ldc$22ra$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox