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
>