agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRe: plpython transforms vs. arrays
3+ messages / 3 participants
[nested] [flat]
* Re: plpython transforms vs. arrays
@ 2019-05-03 02:56 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2019-05-03 02:56 UTC (permalink / raw)
To: Mark Teper <mark.teper@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
Mark Teper <mark.teper@gmail.com> writes:
> 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?
Yeah, see PLy_input_setup_func and PLy_output_setup_func, which both
say
* Choose conversion method. Note that transform functions are checked
* for composite and scalar types, but not for arrays or domains. This is
* somewhat historical, but we'd have a problem allowing them on domains,
* since we drill down through all levels of a domain nest without looking
* at the intermediate levels at all.
At least for arrays, it might be sufficient to switch the order of the
array-lookup and transform-lookup cases to fix this. I don't think
anyone's felt motivated to look into that, up to now.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpython transforms vs. arrays
@ 2019-05-03 04:53 ` Jiří Fejfar <jurafejfar@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jiří Fejfar @ 2019-05-03 04:53 UTC (permalink / raw)
To: Mark Teper <mark.teper@gmail.com>; pgsql-sql@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpython transforms vs. arrays
@ 2019-05-06 09:32 Mark Teper <mark.teper@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mark Teper @ 2019-05-06 09:32 UTC (permalink / raw)
To: Stefan <ssonnenberg@ophardt.com>; pgsql-sql@lists.postgresql.org
Hi Stefan,
> is the Python code running inside the PostgreSQL instance?
Yes, it is using the PL Python language so it runs in the PostgreSQL
instance. With some help from Tom, I've been able to make the change
needed to allow this transform.
I need to do some more testing, but my preliminary results for the arrays
I'm interested in show transforming Postgres to Numpy is about 5x faster
and transforming Numpy back to Postgres about 2x faster.
Regards,
Mark
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-05-06 09:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-05-03 02:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-05-03 04:53 ` Jiří Fejfar <jurafejfar@gmail.com>
2019-05-06 09:32 Re: plpython transforms vs. arrays Mark Teper <mark.teper@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox