On 27 juil, 21:49, t...@sss.pgh.pa.us (Tom Lane) wrote:
Robert James srobertja...@gmail.com writes:
Hi. I noticed that when clients (both psql and pgAdmin) disconnect or
cancel, queries are often still running on the server. A few questions:
1) Is there a way to reconnect and get the
On Thu, Aug 06, 2009 at 07:23:41AM -0700, Nicolas wrote:
Now, I'm in a situation where I have many IDLE in transaction. I
spotted them myself. I could kill them, but that wouldn't prevent them
from happening again. In fact, it happens regularily.
Is there a way to get the list of SQL
Right. For this purpose, pgpool sends param packet to client
periodically while waiting for a reply from backend to detect if the
connection to the client is broken. If it's broken, pgool sends cancel
packet to backend not to waste backend machine's CPU cycle.
The downside to this is
On Wed, 2009-07-29 at 14:56 +0100, Greg Stark wrote:
SIGURG might be useful but it would be more complex to use and less
widely useful since it would only work if the client disconnects
gracefully (though it might be worth checking into as an alternative
to our existing query cancel method).
Well SIGPIPE is no help since it would only fire if we tried to write
to the socket anyways.
Right. For this purpose, pgpool sends param packet to client
periodically while waiting for a reply from backend to detect if the
connection to the client is broken. If it's broken, pgool sends cancel
On Thu, Jul 30, 2009 at 8:41 AM, Tatsuo Ishiiis...@postgresql.org wrote:
Well SIGPIPE is no help since it would only fire if we tried to write
to the socket anyways.
Right. For this purpose, pgpool sends param packet to client
periodically while waiting for a reply from backend to detect if
On Thu, Jul 30, 2009 at 7:43 AM, Craig
Ringercr...@postnewspapers.com.au wrote:
On Wed, 2009-07-29 at 14:56 +0100, Greg Stark wrote:
SIGURG might be useful but it would be more complex to use and less
widely useful since it would only work if the client disconnects
gracefully (though it might
On Thu, Jul 30, 2009 at 10:27 AM, Csaba Nagyn...@ecircle-ag.com wrote:
Sorry, I have to disagree here. If there's a spurious network error, you
have usually bigger problems. I prefer to have the connection killed
even if the network recovers
I know this is a popular feeling. But you're
Hi all,
On Thu, 2009-07-30 at 11:02 +0200, Greg Stark wrote:
On Thu, Jul 30, 2009 at 7:43 AM, Craig
Ringercr...@postnewspapers.com.au wrote:
On Wed, 2009-07-29 at 14:56 +0100, Greg Stark wrote:
What does work well is occasionally poking the socket with recv(...,
MSG_DONTWAIT) while doing
On Thu, 2009-07-30 at 11:41 +0200, Greg Stark wrote:
I know this is a popular feeling. But you're throwing away decades of
work in making TCP reliable. You would change feelings quickly if you
ever faced this scenario too. All it takes is some bad memory or a bad
wire and you would be turning
On Thu, Jul 30, 2009 at 10:59 AM, Csaba Nagyn...@ecircle-ag.com wrote:
But if I get bad memory or bad wire I'll get much worse problems
already, and don't tell me it will work more reliably if you don't kill
the connection. It's a lot better to find out sooner that you have those
problems and
Greg Stark wrote:
Right, you'll only get SIGURG if there's actually any urgent data
received. The client would have to actively send such data
periodically. That would make this a portability headache since it
wouldn't just be an add-on which would fail gracefully if it's
unsupported.
It'd
Csaba Nagy wrote:
On Thu, 2009-07-30 at 11:41 +0200, Greg Stark wrote:
I know this is a popular feeling. But you're throwing away decades of
work in making TCP reliable. You would change feelings quickly if you
ever faced this scenario too. All it takes is some bad memory or a bad
wire and you
On Thu, 2009-07-30 at 13:22 +0200, Craig Ringer wrote:
So, barring network breaks (wifi down / out of range, ethernet cable
fell out, etc etc) how is the OP managing to leave backends running
queries? Hard-resetting the machine?
It happened to us when a client box went out of memory and
Csaba Nagy wrote:
A simple ping to the client would have
cleared the fact that the client is not there anymore.
Yep. It'd also stop PostgreSQL working for clients with software
firewalls, since most of them drop ICMP ECHO (ping).
TCP keepalives are designed to do the same thing, but do it
Csaba Nagy wrote:
It happened to us when a client box went out of memory and started
swapping up to the point it was unaccessible even for console login. The
connections of that machine were still live but unusable, as the client
box will never get out of that state until hard resetting...
On Thu, 2009-07-30 at 13:40 +0200, Craig Ringer wrote:
A simple ping to the client would have
cleared the fact that the client is not there anymore.
Yep. It'd also stop PostgreSQL working for clients with software
firewalls, since most of them drop ICMP ECHO (ping).
I wasn't meaning TCP
[just to make things clear, I'm not the one who brought up this
discussion, only that I was also bitten once by zombie connections]
On Thu, 2009-07-30 at 13:29 +0200, Craig Ringer wrote:
Idle? I thought your issue was _active_ queries running, servicing
requests from clients that'd since
On Thu, Jul 30, 2009 at 12:22 PM, Craig
Ringercr...@postnewspapers.com.au wrote:
In fact, I'm not even sure _how_ one goes about exiting without sending an
RST. A quick check shows that when I `kill -9' a process with an open client
socket (ssh, in this case) the OS sends a FIN, and responds
[this is getting off topic]
On Thu, 2009-07-30 at 13:44 +0200, Craig Ringer wrote:
A host with a runaway process hogging memory shouldn't be dying. It
should really be killing off the problem process, or the problem process
should be dying its self after failing to allocate requested memory.
Craig Ringer cr...@postnewspapers.com.au writes:
Greg Stark wrote:
Also it requires the server to
periodically take time out from processing the query to do this.
This aspect I'm not to bothered about. I doubt it'd cost anything
detectable if done a few times a minute - unless it required
On Thu, Jul 30, 2009 at 3:20 PM, Tom Lanet...@sss.pgh.pa.us wrote:
The earlier part of the discussion was focused on getting the kernel
to actively tell us when the connection had dropped. That would be
workable if we found a way to request it, but I think we'd run out of
options :-(
Yeah,
Robert James wrote:
Is there no way to have the OS interrupt the postgres process
when a TCP/IP disconnect happens? Or is the OS also in the
dark that the TCP/IP connection was dropped? I believe that
there is a way to monitor this using TCP/IP keep alives.
Or perhaps Postgres could check
Robert James wrote:
I see - thanks, Tom, for the informative explanation.
In my experience admining high volume servers, I found this to a major
failure pattern: Client tries query which seems to go on forever (either
do to contention or resource exhaustion or some other problem), client
On 2009-07-28, Tom Lane t...@sss.pgh.pa.us wrote:
Robert James srobertja...@gmail.com writes:
Hi. I noticed that when clients (both psql and pgAdmin) disconnect or
cancel, queries are often still running on the server. A few questions:
1) Is there a way to reconnect and get the results?
On Wed, Jul 29, 2009 at 1:58 PM, Jasen Bettsja...@xnet.co.nz wrote:
can't coerce a signal from the network stack? the linux socket(2)
manpage is full of promise (SIGPIPE, SIGURG, SIGIO)
[please don't quote the entire message back, just the part you're responding to]
Well SIGPIPE is no help
On Mon, Jul 27, 2009 at 09:49:04PM -0400, Tom Lane wrote:
It does not spend cycles looking aside to see if the
connection has dropped when it is doing something that doesn't involve
output to the client.
Is this ever an interesting case? It would seem possible for something
to test the client
Greg Stark gsst...@mit.edu writes:
On Wed, Jul 29, 2009 at 1:58 PM, Jasen Bettsja...@xnet.co.nz wrote:
can't coerce a signal from the network stack? the linux socket(2)
manpage is full of promise (SIGPIPE, SIGURG, SIGIO)
SIGIO on the other hand looks like exactly what we would need. I'm not
On Wed, Jul 29, 2009 at 3:17 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Greg Stark gsst...@mit.edu writes:
On Wed, Jul 29, 2009 at 1:58 PM, Jasen Bettsja...@xnet.co.nz wrote:
can't coerce a signal from the network stack? the linux socket(2)
manpage is full of promise (SIGPIPE, SIGURG, SIGIO)
And
Greg Stark gsst...@mit.edu writes:
That is a question. But actually I think sigio might be fairly
portable -- at least the first hit I found was for someone complaining
that it wasn't working on Linux (due to a bug) and this broke their
app which worked everywhere else.
In any case this
I see - thanks, Tom, for the informative explanation.
In my experience admining high volume servers, I found this to a major
failure pattern: Client tries query which seems to go on forever (either do
to contention or resource exhaustion or some other problem), client gives up
/ fails / gets shut
Hi. I noticed that when clients (both psql and pgAdmin) disconnect or
cancel, queries are often still running on the server. A few questions:
1) Is there a way to reconnect and get the results?
2) Is there a way to tell postgres to automatically stop all queries when
the client who queried them
Robert James srobertja...@gmail.com writes:
Hi. I noticed that when clients (both psql and pgAdmin) disconnect or
cancel, queries are often still running on the server. A few questions:
1) Is there a way to reconnect and get the results?
No.
2) Is there a way to tell postgres to
33 matches
Mail list logo
