Alvaro Herrera wrote:
> On Sun, May 01, 2005 at 03:09:37PM +0300,
adnandursun@asrinbilisim.com.tr wrote:
> > Process A start to update / insert some rows in a table
> > and then the connection of process A is lost to PostgreSQL
> > before it sends commit or rollback. Other processes want to
> > update the same rows or SELECT …..FOR UPDATE for the same
> > rows.Now these processes are providing SELECT WAITING… or
> > CANCEL QUERY if statement_timeout was set. Imagine these
> > processes is getting grower. What will we do now ?
> > Restarting backend or finding process A and kill it ?
>
> Well, if process A loses the connection to the client, then the
> transaction will be rolled back and other processes will be able to
> continue.

The problem, as I understand it, is that if you have a long-running
query and the client process disappears, the query keeps running and
holds whatever resources it may have until it finishes. In fact, it
keeps sending data to the client and keeps ignoring the SIGPIPE it gets
(in case of a Unix-domain socket connection).

Now of course this has nothing to do with "high availability" and does
not warrant hijacking a thread about the release schedule, but it may
be worth investigating.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Sun, 1 May 2005, Alvaro Herrera wrote:

> Well, if process A loses the connection to the client, then the
> transaction will be rolled back and other processes will be able to
> continue.

If the other end of a tcp/ip connection just disapears, for example if the
network cable is cut off then in linux it can take up to 2 hours as
default for it to close the connection. Normally if a client application
dies then the client OS cleans up and closes the socket so that the server
knows about it.

There are some settings that one can alter to change the time it waits
before probing and killing the connection, ie tcp_keepalive_time in
/proc/sys/net/ipv4/.

It's documented in "man tcp" that say that it will take 2h11m as default
to kill of such a connection.

Pg could of course also implement some pinging protocl that should be done
every now and then by the client so that the server knows that it is
alive. For now you just have to lower the global settings as the one above
if you want it to handle it better.

--
/Dennis Björklund


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Peter Eisentraut <peter_e@gmx.net> writes:
> The problem, as I understand it, is that if you have a long-running
> query and the client process disappears, the query keeps running and
> holds whatever resources it may have until it finishes.

There is a trivial solution for this: it's called statement_timeout.

If the concern is that a process may block other processes for a long
time, what does it matter whether the client is still connected or not?
It's the long-running command in itself that is the problem. So you
limit the time the command can run.

It might be interesting to think about a transaction_timeout as well,
to bound the time locks can be held. But none of this has anything
to do with "high availability" as I understand the term. It looks
more like a forcing function to make your users fix poorly-written
client software ;-)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On 2005-05-01, Peter Eisentraut <peter_e@gmx.net> wrote:
> The problem, as I understand it, is that if you have a long-running
> query and the client process disappears, the query keeps running and
> holds whatever resources it may have until it finishes. In fact, it
> keeps sending data to the client and keeps ignoring the SIGPIPE it gets
> (in case of a Unix-domain socket connection).

Ignoring the SIGPIPE is exactly the right thing to do.

What's _not_ a good idea is ignoring the EPIPE error from write(), which
seems to currently be reported via ereport(COMMERROR) which doesn't try
and abort the query as far as I can tell.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On Sun, 01 May 2005 11:37:47 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
>Peter Eisentraut <peter_e@gmx.net> writes:
>> The problem, as I understand it, is that if you have a
>long-running
>> query and the client process disappears, the query keeps
>running and
>> holds whatever resources it may have until it finishes.
>
>There is a trivial solution for this: it's called
>statement_timeout.

statement_timeout is not a solution if many processes are
waiting the resource. statement_timeout is providing a
escape mechanism. Imagine tens of processes are waiting,
statement_timeout refuses them but this processes must do
their works.

>If the concern is that a process may block other processes
>for a long
>time, what does it matter whether the client is still
>connected or not?
>It's the long-running command in itself that is the
>problem. So you
>limit the time the command can run.
>
>It might be interesting to think about a
>transaction_timeout as well,
>to bound the time locks can be held. But none of this has
>anything
>to do with "high availability" as I understand the term.
> It looks
>more like a forcing function to make your users fix
>poorly-written
>client software ;-)

Listen Tom, write a client software that releases the
resources / locks that was hold before client power is down
or client connection was lost.

Do we must suggest this solution to ppl that wants to use
PostgreSQL or do we must implement a pinging mechanism to
check whether client is dead or live ?

Best Regards

Adnan DURSUN
ASRIN Biliþim Hiz.Ltd.
Ankara / TURKEY

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Sun, May 01, 2005 at 19:57:37 +0300,
adnandursun@asrinbilisim.com.tr wrote:
>
> Listen Tom, write a client software that releases the
> resources / locks that was hold before client power is down
> or client connection was lost.

If Postgres can tell the connection has been lost then it should roll
back the connection. The problem is that you can't always tell if
a connection has been lost. All you can do is timeout, either when TCP
times out or some other timeout (such as a statment timeout) that you
set.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Sun, 1 May 2005 14:35:37 -0500
Bruno Wolff III <bruno@wolff.to> wrote:
>On Sun, May 01, 2005 at 19:57:37 +0300,
> adnandursun@asrinbilisim.com.tr wrote:
>>
>> Listen Tom, write a client software that releases the
>> resources / locks that was hold before client power is
>down
>> or client connection was lost.
>
>If Postgres can tell the connection has been lost then it
>should roll back the connection.

Yes, but, Can PostgreSQL know which connection is lost or
live or dead ?

>The problem is that you can't always
>tell if a connection has been lost. All you can do is
timeout, either when TCP
>times out or some other timeout (such as a statment
timeout) that you set.

You are right, a timeout parameter must be used for that
on the backend. a client application never find the
previous instance before it crashed. However more than one
connection was able to be established to PostgreSQL
backend..

Statement_timeout is just a escape mechanism for active
transaction. Imagine; you've started a process to update
the rows in a table then your PC power was down but you
have not sent commit or rollback yet..What will happen now
? Example Codes ;

-- Client Side of Codes

1. send statement_timeout = 10;
2. start a transaction;
3. start to update table;
** connection is lost here
4. commit;

Best Regards,

Adnan DURSUN
ASRIN Biliþim Hiz.Ltd.
Ankara / TURKEY

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

After takin a swig o' Arrakan spice grog, adnandursun@asrinbilisim.com.tr belched out:
> On Sun, 1 May 2005 14:35:37 -0500
> Bruno Wolff III <bruno@wolff.to> wrote:
>>On Sun, May 01, 2005 at 19:57:37 +0300,
>> adnandursun@asrinbilisim.com.tr wrote:
>>>
>>> Listen Tom, write a client software that releases the
>>> resources / locks that was hold before client power is
>>down
>>> or client connection was lost.
>>
>>If Postgres can tell the connection has been lost then it
>>should roll back the connection.
>
> Yes, but, Can PostgreSQL know which connection is lost or
> live or dead ?

Certainly, with the "magic connection analysis protocol."

Or not...

In order to that sort of analysis, you need to have some form of
heartbeat monitor on the connection, thereby requiring extra
connections.

It could make sense to attach that kind of extra apparatus to a
connection pool manager like pgpool, but probably not directly to
PostgreSQL itself.

You might want to look into pgpool; that is something that many people
interested in "enterprise usage" of PostgreSQL are looking into...
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://linuxdatabases.info/info/slony.html
"A army's effectiveness depends on its size, training, experience and
morale, and morale is worth more than all the other factors combined."
-- Napoleon Bonaparte

adnandursun@asrinbilisim.com.tr wrote:
> statement_timeout is not a solution if many processes are
> waiting the resource.

Why not?

I think the only problem with using statement_timeout for this purpose
is that the client connection might die during a long-running
transaction at a point when no statement is currently executing. Tom's
suggested transaction_timeout would be a reasonable way to fix this.
Adnan, if you think this is such a significant problem (I can't say that
I agree), I'd encourage you to submit a patch.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Neil Conway wrote:
> adnandursun@asrinbilisim.com.tr wrote:
>
>> statement_timeout is not a solution if many processes are
>> waiting the resource.
>
>
> Why not?
>
> I think the only problem with using statement_timeout for this purpose
> is that the client connection might die during a long-running
> transaction at a point when no statement is currently executing. Tom's
> suggested transaction_timeout would be a reasonable way to fix this.
> Adnan, if you think this is such a significant problem (I can't say that
> I agree), I'd encourage you to submit a patch.

I raised this a while back on -hackers:

http://archives.postgresql.org/pgsql...2/msg00397.php

but did not get much feedback.

Does anyone have comments on that email?

It's a problem that is unlikely to happen in normal operation, but you
do need to deal with it to cover the network failure cases if you have
an otherwise failure-tolerant cluster..

-O

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster