Re: pg_terminate_backend

Since I have a stuck backend without client again, I'll have to kill -SIGTERM a backend. Fortunately, I do
have console access to that machine and it's not win32 but a decent OS. For other cases I'd really really really
appreciate if that function would make it into 8.2.

utils/adt/misc.c says:

#*ifdef* NOT_USED

//* Disabled in 8.0 due to reliability concerns; FIXME someday *//
Datum
*pg_terminate_backend*(PG_FUNCTION_ARGS)

Well, AFAIR there were no more issues raised about code paths that don't clean up correctly, so can we please
remove that comment and make the function live finally?

Regards,
Andreas

Andreas Pflug wrote:

>Since I have a stuck backend without client again, I'll have to kill -SIGTERM a backend. Fortunately, I do
>have console access to that machine and it's not win32 but a decent OS.
>
>

You do know that on Windows you can use pg_ctl to send a pseudo SIGTERM
to a backend, don't you?

cheers

andrew

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> utils/adt/misc.c says:
> //* Disabled in 8.0 due to reliability concerns; FIXME someday *//
> Datum
> *pg_terminate_backend*(PG_FUNCTION_ARGS)

> Well, AFAIR there were no more issues raised about code paths that don't clean up correctly, so can we please
> remove that comment and make the function live finally?

No, you have that backwards. The burden of proof is on those who want
it to show that it's now safe. The situation is not different than it
was before, except that we can now actually point to a specific bug that
did exist, whereas the original concern was just an unfocused one that
the code path hadn't been adequately exercised. That concern is now
even more pressing than it was.

regards, tom lane

Andrew Dunstan wrote:
>
>
> Andreas Pflug wrote:
>
>> Since I have a stuck backend without client again, I'll have to kill
>> -SIGTERM a backend. Fortunately, I do have console access to that
>> machine and it's not win32 but a decent OS.
>>
>>
>
> You do know that on Windows you can use pg_ctl to send a pseudo
> SIGTERM to a backend, don't you?
The main issue still is that console access id required, on any OS.

Regards,
Andreas

Tom Lane wrote:
> Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
>
>> utils/adt/misc.c says:
>> //* Disabled in 8.0 due to reliability concerns; FIXME someday *//
>> Datum
>> *pg_terminate_backend*(PG_FUNCTION_ARGS)
>>
>
>
>> Well, AFAIR there were no more issues raised about code paths that don't clean up correctly, so can we please
>> remove that comment and make the function live finally?
>>
>
> No, you have that backwards. The burden of proof is on those who want
> it to show that it's now safe. The situation is not different than it
> was before, except that we can now actually point to a specific bug that
> did exist, whereas the original concern was just an unfocused one that
> the code path hadn't been adequately exercised. That concern is now
> even more pressing than it was.
>

If the backend's stuck, I'll have to SIGTERM it, whether there's
pg_terminate_backend or not. Ultimately, if resources should remain
locked, there's no chance except restarting the whole server anyway.
SIGTERM gives me a fair chance (>90%) that it will work without restart.

The persistent refusal of supporting the function makes it more painful
to execute, but not less necessary.

Regards,
Andreas

Tom Lane wrote:
> Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> > utils/adt/misc.c says:
> > //* Disabled in 8.0 due to reliability concerns; FIXME someday *//
> > Datum
> > *pg_terminate_backend*(PG_FUNCTION_ARGS)
>
> > Well, AFAIR there were no more issues raised about code paths that don't clean up correctly, so can we please
> > remove that comment and make the function live finally?
>
> No, you have that backwards. The burden of proof is on those who want
> it to show that it's now safe. The situation is not different than it
> was before, except that we can now actually point to a specific bug that
> did exist, whereas the original concern was just an unfocused one that
> the code path hadn't been adequately exercised. That concern is now
> even more pressing than it was.

I am not sure how you prove the non-existance of a bug. Ideas?

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Tom Lane wrote:
>> No, you have that backwards. The burden of proof is on those who want
>> it to show that it's now safe. The situation is not different than it
>> was before, except that we can now actually point to a specific bug that
>> did exist, whereas the original concern was just an unfocused one that
>> the code path hadn't been adequately exercised. That concern is now
>> even more pressing than it was.

> I am not sure how you prove the non-existance of a bug. Ideas?

What I'm looking for is some concentrated testing. The fact that some
people once in a while SIGTERM a backend doesn't give me any confidence
in it.

regards, tom lane

> What I'm looking for is some concentrated testing. The fact that some
> people once in a while SIGTERM a backend doesn't give me any confidence
> in it.

Now wait a minute, is there some risk of lockup if I kill a backend ?
Cause I do that relatively often (say 20 times a day, when some web
users time out but their query keeps running). Should I rather not do it
?

Thanks,
Csaba.

Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:
> Now wait a minute, is there some risk of lockup if I kill a backend ?
> Cause I do that relatively often (say 20 times a day, when some web
> users time out but their query keeps running). Should I rather not do it
> ?

statement_timeout is your friend.

regards, tom lane

You didn't answer the original question: is killing SIGTERM a backend
known/suspected to be dangerous ? And if yes, what's the risk (pointers
to discussions would be nice too).

> statement_timeout is your friend.

I know, but unfortunately I can't use it. I did try to use
statement_timeout and it worked out quite bad (due to our usage
scenario).

Some of the web requests which time out on the web should still go
through... and we have activities which should not observe statement
timeout at all, i.e. they must finish however long that takes.

I know it would be possible to use a different user with it's own
statement timeout for those requests, but that means we have to rewrite
a lot of code which is not possible immediately, and our admins would
resist to add even more configuration (additional users=additional
connection pool+caches and all to be configured). We also can fix the
queries so no timeout happens in the first place, but that will take us
even more time.

Cheers,
Csaba.

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> Tom Lane wrote:
>> No, you have that backwards. The burden of proof is on those who want
>> it to show that it's now safe.

> If the backend's stuck, I'll have to SIGTERM it, whether there's
> pg_terminate_backend or not.

"Stuck?" You have not shown us a case where SIGTERM rather than SIGINT
is necessary or appropriate. It seems to me the above is assuming the
existence of unknown backend bugs, exactly the same thing you think
I shouldn't be assuming ...

regards, tom lane

On Thu, 2006-08-03 at 18:10, Csaba Nagy wrote:
> You didn't answer the original question: is killing SIGTERM a backend
^^^^^^^^^^^^^^^
Nevermind, I don't do that. I do 'kill backend_pid' without specifying
the signal, and I'm sufficiently unfamiliar with the unix signal names
to have confused them. Is a plain "kill" still dangerous ?

Thanks,
Csaba.

> "Stuck?" You have not shown us a case where SIGTERM rather than SIGINT
> is necessary or appropriate. It seems to me the above is assuming the
> existence of unknown backend bugs, exactly the same thing you think
> I shouldn't be assuming ...

I do know a case where a plain kill will seem to be stucked: on vacuum
of a big table. I guess when it starts an index's cleanup scan it will
insist to finish it before stopping. I'm not sure if that's the cause,
but I have seen delays of 30 minutes for killing a vacuum... it's true
that finally it always did die... but it's also true that I have 'kill
-9'-ed it before because I thought it's stucked.

Cheers,
Csaba.

Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:
> I do know a case where a plain kill will seem to be stucked: on vacuum
> of a big table. I guess when it starts an index's cleanup scan it will
> insist to finish it before stopping.

We've fixed a few cases of missing CHECK_FOR_INTERRUPTS lately, and will
fix more if you can point them out. Note though that SIGTERM is just as
vulnerable to that as SIGINT.

regards, tom lane

Tom Lane wrote:
> Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
>
>> Tom Lane wrote:
>>
>>> No, you have that backwards. The burden of proof is on those who want
>>> it to show that it's now safe.
>>>
>
>
>> If the backend's stuck, I'll have to SIGTERM it, whether there's
>> pg_terminate_backend or not.
>>
>
> "Stuck?" You have not shown us a case where SIGTERM rather than SIGINT
> is necessary or appropriate.
Last night, I had a long-running query I launched from pgAdmin. It was
happily running and completing on the server (took about 2 hours), and
the backend went back to <IDLE>. pgAdmin didn't get back a response,
assuming the query was still running. Apparently, the VPN router had
interrupted the connection silently without notifying either side of the
tcp connection. Since the backend is <IDLE>, there's no query to cancel
and SIGINT won't help. So "Stuck" for me means a backend *not*
responding to SIGINT.
BTW, there's another scenario where SIGINT won't help. Imagine an app
running wild hammering the server with queries regardless of query
cancels (maybe some retry mechanism). You'd like to interrupt that
connection, i.e. get rid of the backend.

Regards,
Andreas

Csaba Nagy wrote:
> On Thu, 2006-08-03 at 18:10, Csaba Nagy wrote:
>
>> You didn't answer the original question: is killing SIGTERM a backend
>>
> ^^^^^^^^^^^^^^^
> Nevermind, I don't do that. I do 'kill backend_pid' without specifying
> the signal, and I'm sufficiently unfamiliar with the unix signal names
> to have confused them. Is a plain "kill" still dangerous ?
>
SIGTERM is the default kill parameter, so you do exactly what I'm
talking about.

Regards,
Andreas

Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:
> On Thu, 2006-08-03 at 18:10, Csaba Nagy wrote:
>> You didn't answer the original question: is killing SIGTERM a backend
> ^^^^^^^^^^^^^^^
> Nevermind, I don't do that. I do 'kill backend_pid' without specifying
> the signal,

"man kill" says the default is SIGTERM.

regards, tom lane

Bruce Momjian wrote:
>
>
> I am not sure how you prove the non-existance of a bug. Ideas?
>
Would be worth at least the Nobel prize :-)

Regards,
Andreas

> "man kill" says the default is SIGTERM.

OK, so that means I do use it... is it known to be dangerous ? I thought
till now that it is safe to use. What about "select pg_cancel_backend()"
?

Thanks,
Csaba.

Csaba Nagy wrote:
>> "man kill" says the default is SIGTERM.
>>
>
> OK, so that means I do use it... is it known to be dangerous ? I thought
> till now that it is safe to use.
Apparently you never suffered any problems from that; neither did I.

> What about "select pg_cancel_backend()"
>

That's the function wrapper around kill -SIGINT, which is probably the
way you could safely stop your queries most of the time.

Regards,
Andreas

> I am not sure how you prove the non-existance of a bug. Ideas?

I do that by deleting all of my code (usually by accident :-)

No code, no bugs!

-- Korry

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Tom Lane wrote:
> >> No, you have that backwards. The burden of proof is on those who want
> >> it to show that it's now safe. The situation is not different than it
> >> was before, except that we can now actually point to a specific bug that
> >> did exist, whereas the original concern was just an unfocused one that
> >> the code path hadn't been adequately exercised. That concern is now
> >> even more pressing than it was.
>
> > I am not sure how you prove the non-existance of a bug. Ideas?
>
> What I'm looking for is some concentrated testing. The fact that some
> people once in a while SIGTERM a backend doesn't give me any confidence
> in it.

OK, here is an opportunity for someone to run tests to get this into
8.2. The code already exists in CVS, but we need testing to enable it.
I would think running a huge workload and killing it over and over again
would be a good test.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Tom Lane wrote:
>> What I'm looking for is some concentrated testing. The fact that some
>> people once in a while SIGTERM a backend doesn't give me any confidence
>> in it.

> OK, here is an opportunity for someone to run tests to get this into
> 8.2. The code already exists in CVS, but we need testing to enable it.
> I would think running a huge workload and killing it over and over again
> would be a good test.

Big multiprocess workload and you kill individual processes at random
while letting the rest run. It probably needs to be something that
stresses more of the code than pgbench would, too. (For instance,
it'd be a good idea if some of the workload involved having a few 2PC
transactions getting prepared and then either committed or rolled
back ... SIGTERM during a COMMIT PREPARED strikes me as the sort of
corner case that's probably never been exercised.)

regards, tom lane

Thanks. Good plan.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Tom Lane wrote:
> >> What I'm looking for is some concentrated testing. The fact that some
> >> people once in a while SIGTERM a backend doesn't give me any confidence
> >> in it.
>
> > OK, here is an opportunity for someone to run tests to get this into
> > 8.2. The code already exists in CVS, but we need testing to enable it.
> > I would think running a huge workload and killing it over and over again
> > would be a good test.
>
> Big multiprocess workload and you kill individual processes at random
> while letting the rest run. It probably needs to be something that
> stresses more of the code than pgbench would, too. (For instance,
> it'd be a good idea if some of the workload involved having a few 2PC
> transactions getting prepared and then either committed or rolled
> back ... SIGTERM during a COMMIT PREPARED strikes me as the sort of
> corner case that's probably never been exercised.)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

> >> Since I have a stuck backend without client again, I'll have to
> kill
> >> -SIGTERM a backend. Fortunately, I do have console access to
> that
> >> machine and it's not win32 but a decent OS.
> >>
> >>
> >
> > You do know that on Windows you can use pg_ctl to send a pseudo
> > SIGTERM to a backend, don't you?
> The main issue still is that console access id required, on any OS.

Yeah.
Though for the Windows case only, we could easily enough make it
possible to run pg_ctl kill remotely, since we use a named pipe. Does
this seem like a good or bad idea?

//Magnus

Magnus Hagander wrote:
>>>> Since I have a stuck backend without client again, I'll have to
>>>>
>> kill
>>
>>>> -SIGTERM a backend. Fortunately, I do have console access to
>>>>
>> that
>>
>>>> machine and it's not win32 but a decent OS.
>>>>
>>>>
>>>>
>>> You do know that on Windows you can use pg_ctl to send a pseudo
>>> SIGTERM to a backend, don't you?
>>>
>> The main issue still is that console access id required, on any OS.
>>
>
> Yeah.
> Though for the Windows case only, we could easily enough make it
> possible to run pg_ctl kill remotely, since we use a named pipe. Does
> this seem like a good or bad idea?
>

Not too helpful. How to kill a win32 backend from a linux workstation?
Additionally, NP requires an authenticated RPC connection. I you're not
allowed to access the console, you probably haven't got sufficient
access permissions to NP as well, or you'd need extra policy tweaking or
so. Nightmarish, just to avoid the easy and intuitive way.

Regards,
Andreas

"Magnus Hagander" <mha(at)sollentuna(dot)net> writes:
> Though for the Windows case only, we could easily enough make it
> possible to run pg_ctl kill remotely, since we use a named pipe. Does
> this seem like a good or bad idea?

Seems like we'd be opening a can of security worms :-(

regards, tom lane

> > Though for the Windows case only, we could easily enough make it
> > possible to run pg_ctl kill remotely, since we use a named pipe.
> Does
> > this seem like a good or bad idea?
>
> Seems like we'd be opening a can of security worms :-(

Not really, standard windows ACL already applies to everything, so you
need to be an admin on the machine to make it work.

Anyhoo, I don't really see the gain in it, which also seems to be what
others think, so let's just drop that idea.

//Magnus