pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql + dblink() question
4+ messages / 3 participants
[nested] [flat]
* plpgsql + dblink() question
@ 2003-02-08 05:41 Frankie <frankie@ucr.com.hk>
0 siblings, 2 replies; 4+ messages in thread
From: Frankie @ 2003-02-08 05:41 UTC (permalink / raw)
To: 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();
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql + dblink() question
@ 2003-02-08 06:03 Joe Conway <mail@joeconway.com>
parent: Frankie <frankie@ucr.com.hk>
1 sibling, 1 reply; 4+ messages in thread
From: Joe Conway @ 2003-02-08 06:03 UTC (permalink / raw)
To: Frankie <frankie@ucr.com.hk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql + dblink() question
@ 2003-02-10 03:39 Frankie Lam <frankie@ucr.com.hk>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 4+ messages in thread
From: Frankie Lam @ 2003-02-10 03:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql + dblink() question
@ 2003-02-10 07:47 Frankie Lam <frankie@ucr.com.hk>
parent: Frankie <frankie@ucr.com.hk>
1 sibling, 0 replies; 4+ messages in thread
From: Frankie Lam @ 2003-02-10 07:47 UTC (permalink / raw)
To: pgsql-sql
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();
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-02-10 07:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-08 05:41 plpgsql + dblink() question Frankie <frankie@ucr.com.hk>
2003-02-08 06:03 ` Joe Conway <mail@joeconway.com>
2003-02-10 03:39 ` Frankie Lam <frankie@ucr.com.hk>
2003-02-10 07:47 ` Frankie Lam <frankie@ucr.com.hk>
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