Re: plpython transforms vs. arrays

Dear Mark,

I am also looking around to find way how to do effectively (in
parallel?) computations (with potentially large and sometimes sparse)
matrices directly in PostgreSQL. Some time ago I have found this
experimental extension https://github.com/PandaPost/panda_post which
"allow you to represent Python NumPy/Pandas objects in Postgres". There
is also http://madlib.apache.org/ but is seems too much "heavyweight"
for my use-case.

Now I do not have much time to spent with this, but I hope in late
summer it will be better. I am looking forward what will be your
conclusions.

Good luck, Jiří.

On 5/2/19 7:51 PM, Mark Teper wrote:
>
> Hi,
>
> I'm trying to build some numerical processing algorithms on Postgres
> Array data types.  Using PL/Python I can get access to the numpy
> libraries but the performance is not great.  A guess is that there is
> a lot of overhead going from Postgres -> Python List -> Numpy and back
> again.
>
> I'd like to test if that's the issue, and potentially fix by creating
> a C extension to convert directly from Postgres Array types to Numpy
> Array types.  I _think_ I have the C side somewhat working, but I
> can't get Postgres to use the transform.
>
> What I have:
>
> ---
>
> CREATE FUNCTION arr_to_np(val internal) RETURNS internal LANGUAGE C AS
> 'MODULE_PATHNAME', 'arr_to_np';
>
> CREATE FUNCTION np_to_arr(val internal) RETURNS real[] LANGUAGE C AS
> 'MODULE_PATHNAME', 'np_to_arr';
>
> CREATE TRANSFORM FOR real[] LANGUAGE plpythonu (
>
>     FROM SQL WITH FUNCTION arr_to_np(internal),
>
>     TO SQL WITH FUNCTION np_to_arr(internal)
>
> );
>
> CREATE FUNCTION fn (a integer[]) RETURNS integer
>
>     TRANSFORM FOR TYPE real[]
>
>      AS $$  return a $$ LANGUAGE plpythonu;
>
> ----
>
> The problem is this produces an error that transforms for type "real"
> doesn't work.  It doesn't seem to allow for transforms on array's as
> opposed to underlying types. Is it possible to tell it to apply the
> transform to the array?
>
> Thanks,
>
> Mark
>