Re: [HACKERS] signal handling in plpython

[Mario De Frutos Dieguez]

Hi!

Following your ideas I've made a test [here
],
only in plpython and seems to works pretty well. I've to make more tests
and execute the postgres regress too.

This ad-hoc solution could be enough for now, we don't have
shared_preload_libraries
as Heikki pointed, because in the next week we need to be able to interrupt
plpython functions.

But, I would REALLY LOVE to implement a proper solution for this case,
making hooks in Postgres for extensions like Heikki propose or any other
proposal. I'm really enjoying to work in the Postgres internals
and at least I'd like to finish this PATCH.

Any suggestion on what do I need to read, similar cases, advices, etc?

Again thank you very much for your time and you invaluable help.

Mario



2016-10-14 15:22 GMT+02:00 Tom Lane :

> Heikki Linnakangas  writes:
> > On 10/14/2016 04:05 PM, Tom Lane wrote:
> >> I wrote:
> >>> Py_AddPendingCall is safe to call from a signal handler?  That would
> >>> be ... quite remarkable.
>
> > Yes, I believe it is.
>
> > https://github.com/python/cpython/blob/4b71e63b0616aa2a44c9b13675e4c8
> e3c0157481/Python/ceval.c#L422
>
> I don't know whether to laugh or cry, but that code is a joke.  Just
> silently fail if you can't get the lock?
>
> regards, tom lane
>

Re: [HACKERS] signal handling in plpython

[Mario De Frutos Dieguez]

Hi!

Thank you very much for your quick response :)

We're looking for a solution at plpython level. My two proposals are a
quick "workaround" that let us interrupt using custom signal handlers in
the python code at plpython level. But I'm looking for something more solid
and your proposal, I've been doing this for 3 days hehe, looks great and I
would LOVE to hear more about it and if you can't guide me a bit more in
order to fully understand it :)

We've been thinking to make something like the PostGIS handler
<https://github.com/postgis/postgis/blob/98b2cdb872b5b5bd65606f5bce334d2477b2afc5/postgis/postgis_module.c#L128>
and
have multiple signal handlers: one for the Postgres using
StatementCancelHandler and one for the python code. How does it sound?

Thank you again for your time :)

2016-10-14 12:01 GMT+02:00 Heikki Linnakangas <hlinn...@iki.fi>:

> On 10/13/2016 08:57 PM, Mario De Frutos Dieguez wrote:
>
>> I come here asking for some advice/help because we're facing some
>> unexpected behavior when we want to interrupt functions doing CPU
>> intensive
>> operations in plpython.
>>
>> Our problem is that we're not able to interrupt them when they're making
>> CPU intensive operations. For example, when calculating Moran using PySAL,
>> the SIGINT handler of Postgres is not able to cancel it.
>>
>
> Python code isn't interruptible, but any queries you run within a python
> function are. So if you have a loop in your function that you know will run
> for a long time, you could issue a dummy "SELECT 1" query every once in a
> while. However, that doesn't help, if the long loop is in a library
> function that you have no control over, rather than the PL/python function
> itself.
>
> It would be nice to have a solution for this in plpython itself, so that
> the query cancel was turned into a Python exception. Patches for that would
> be welcome. I think you could use Py_AddPendingCall() from PostgreSQL's
> signal handler, to schedule a call to a function that in turn throws a
> Python exception. That'll need some changes to PostgreSQL's normal signal
> handlers, like die() and StatementCancelHandler() in postgres.c, but it
> seems doable.
>
> - Heikki
>
>

[HACKERS] signal handling in plpython

[Mario De Frutos Dieguez]

Hello everyone :).

First of all, I want to introduce me to this list. My name is Mario de
Frutos and I work at CARTO :)

I come here asking for some advice/help because we're facing some
unexpected behavior when we want to interrupt functions doing CPU intensive
operations in plpython.

Our problem is that we're not able to interrupt them when they're making
CPU intensive operations. For example, when calculating Moran using PySAL,
the SIGINT handler of Postgres is not able to cancel it.

I want to show you some possible solutions that I've tried without success:

- If we don't add a custom signal handler, we're not able to interrupt the
function when it's making CPU intensive operations. When the `SIGINT`
signal is launched, the system is not able to interrupt it until the
function ends.
- If we add a custom signal handler for the `SIGINT`, we are able to
interrupt the CPU intensive function but we're not able to interrupt data
fetching operations like `plpy.execute(query)` because we have overridden
the Postgres handler for that signal.
- As a third option I've added a python context manager to wrap, for
testing purposes, the CPU intensive part (Moran function from PySAL):
```
def _signal_handler(signal_code, frame):
plpy.error(INTERRUPTED BY USER!!')


@contextmanager
def interruptible():
try:
signal.signal(signal.SIGINT, _signal_handler)
yield
finally:
# Restore the default behavoiur for the signal
signal.signal(signal.SIGINT, signal.SIG_DFL)
```
  This doesn't work as expected because in the `finally` clause we try to
reset to the default behavior but in Postgres, the behavior for the SIGINT
signal is defined by a [custom handler](
https://github.com/postgres/postgres/blob/master/src/include/tcop/tcopprot.h#L66
).
  If we try to retrieve the old handler using `signal.getsignal` we get a
None object

So after all,going back and forth I came up with two possible solutions:
- [custom code
]
in `plpython` to make us able to reset the default signal handler after
finish the CPU intensive functions. It seems to work but I'm still doing
some tests. This option lets us call it explicitly and add it to the
`finally` part of a decorator/context manager
- Reset the signal handler at the beginning of the `plpy.execute` or alike
functions like [here

].

As an extra ball, we want to implement the SIGALRM part to mimic the
"statement timeout" behavior too

I don't know if there is a better way to implement this, I know we're
pushing/doing things beyond the scope of plpython but any advise is welcome
:)