Обсуждение: plpgsql + dblink() question
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();
Frankie wrote:
> 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'.)
dblink just uses libpq to handle the communication, so you can use the
connect_timeout connection parameter. It defaults to infinite if not
explicitly set. Something like this should set a 5 second timeout:
select * into tmp from dblink(''host=linux dbname=twins connect_timeout=5'',
''select mysleep();'') as (retval text);
See:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/libpq-connect.html
HTH,
Joe
Joe, thanks very much for your reply.
However, the caller (function on Server 1) is still waiting for something to
return endlessly. It seems to me that the connect_timeout option doesn't
work.
I've tried to set connect_timeout to 1, and it also has to wait for the
function
on Server 2 to complete. (Is this a bug?)
Thanks very much for your help.
Frankie Lam
"Joe Conway" <mail@joeconway.com> wrote in message
news:3E449DAB.9050106@joeconway.com...
> Frankie wrote:
> > 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'.)
>
> dblink just uses libpq to handle the communication, so you can use the
> connect_timeout connection parameter. It defaults to infinite if not
> explicitly set. Something like this should set a 5 second timeout:
>
> select * into tmp from dblink(''host=linux dbname=twins
connect_timeout=5'',
> ''select mysleep();'') as (retval text);
>
> See:
>
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/libpq-connect.ht
ml
>
> HTH,
>
> Joe
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
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();
>
>