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 d
Robert James 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 automatically stop all querie
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 d
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 ch
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
gives
On 2009-07-28, Tom Lane wrote:
> Robert James 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
On Wed, Jul 29, 2009 at 1:58 PM, Jasen Betts 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 since it would onl
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 clie
Greg Stark writes:
> On Wed, Jul 29, 2009 at 1:58 PM, Jasen Betts 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
> sure if it can be set to
On Wed, Jul 29, 2009 at 3:17 PM, Tom Lane wrote:
> Greg Stark writes:
>> On Wed, Jul 29, 2009 at 1:58 PM, Jasen Betts wrote:
>>> can't coerce a signal from the network stack? the linux socket(2)
>>> manpage is full of promise (SIGPIPE, SIGURG, SIGIO)
>
>
> And the other question is how much of wha
Greg Stark 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 would be a feat
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 Ishii 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 the
> connectio
On Thu, Jul 30, 2009 at 7:43 AM, Craig
Ringer 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 be worth checking i
On Thu, Jul 30, 2009 at 10:27 AM, Csaba Nagy 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 throwing away decades
Hi all,
On Thu, 2009-07-30 at 11:02 +0200, Greg Stark wrote:
> On Thu, Jul 30, 2009 at 7:43 AM, Craig
> Ringer 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 other work. Program
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 turni
On Thu, Jul 30, 2009 at 10:59 AM, Csaba Nagy 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 fix them than ha
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 als
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 w
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 st
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
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... which
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 mea
[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 cea
On Thu, Jul 30, 2009 at 12:22 PM, Craig
Ringer 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 to the server's
> FIN,ACK
[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 memor
Craig Ringer 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
> restructuring of qu
On Thu, Jul 30, 2009 at 3:20 PM, Tom Lane 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, everything I'v
> > 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 t
On 27 juil, 21:49, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Robert James 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.
>
>
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 s
33 matches
Mail list logo
