Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hMQCn-0003Gb-GZ for pgsql-sql@arkaria.postgresql.org; Fri, 03 May 2019 04:53:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hMQCl-0001Y2-Sg for pgsql-sql@arkaria.postgresql.org; Fri, 03 May 2019 04:53:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hMQCl-0001Xv-Gx for pgsql-sql@lists.postgresql.org; Fri, 03 May 2019 04:53:51 +0000 Received: from mail-wr1-x444.google.com ([2a00:1450:4864:20::444]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hMQCi-0000m3-Oo for pgsql-sql@lists.postgresql.org; Fri, 03 May 2019 04:53:51 +0000 Received: by mail-wr1-x444.google.com with SMTP id o4so6111895wra.3 for ; Thu, 02 May 2019 21:53:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=Tu5tLAwo8FwfXlCTxGsYXjnzaJt2FQ92A13tc7mpcLw=; b=GBE1PWMOWoGPJMGnSzoPOl681qHTda9wr+K9HZh35HTVff1QJ4MrnMnsbLyl8g4NGx UILvtu0aejNmBP3JlSxzwiKf0WoTfoAN2zPQbAmJj7V2a77yNXj4jMGiFOdENTjCdYpU s3M5nQiUFnJwkmVugIEbW66/2Jyj4RF4FWzENkbLVghYTnWxWLQdiTFts9mgglwcJBDK Qg4VgcrdVhvuhhOONiUMzV+kToDas5ydbA2XLWGfHdUk2yoKrgSATff/TsonqQaU8Cq6 Rlwi/TsYBo/ei/XSHsdbBhofGmo89C8O/fQIycjRuR5s8yxZZqoOfyyWTdBvTsxc2jUu CkEA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=Tu5tLAwo8FwfXlCTxGsYXjnzaJt2FQ92A13tc7mpcLw=; b=J3xx01NSQrxH+2AmKl5mvI5C+GlVwxFNQIx40LZRT1wJ6cuSouIXO4Bz/7AeCLokp3 FvSW17cKQyH84D1WhY4o2kbI8Q4KDvgPTOaIqMcVY4W31H/880fcseZPgXIZ5cKz/Zi+ R9GgcWMwSpqxsOp6sK219KQb3dwhgtfLIcl/eAtTuUtpMpqMjRsFJtZIlypWU/tZgYQE xFcAk5DhIf0OTe3QDWz01qHEFeN05wKINWS41nJlejoRZeoxOBgPEGbrY9sBizRAPByG RQkFKPl1WJs/luBhNfPeAQoFh+Lrfj4srSBAJIUm4WZe0gHYdUbhld41vanBgd2VOMs+ CxCQ== X-Gm-Message-State: APjAAAWK8vAHyTQdXm2UWUOQ1b9h+kOHxSuMp5vsurzVCgAAKhSLe3kC 5rRwMvBSzYd+IKAIPnAtzPe+u2/B X-Google-Smtp-Source: APXvYqwCJtcJrPAiWtQu3hAtA2gl/kCdM3l4vcMfxPmPWWQC2du0qHEHpGGZTc3WBQpyPVesmk1+hw== X-Received: by 2002:a05:6000:118a:: with SMTP id g10mr5537901wrx.233.1556859227311; Thu, 02 May 2019 21:53:47 -0700 (PDT) Received: from [192.168.1.169] (ip-37-188-244-232.eurotel.cz. [37.188.244.232]) by smtp.gmail.com with ESMTPSA id a184sm1051517wmh.36.2019.05.02.21.53.46 (version=TLS1_3 cipher=AEAD-AES128-GCM-SHA256 bits=128/128); Thu, 02 May 2019 21:53:46 -0700 (PDT) Subject: Re: plpython transforms vs. arrays To: Mark Teper , pgsql-sql@lists.postgresql.org References: From: =?UTF-8?B?SmnFmcOtIEZlamZhcg==?= Message-ID: <2dbe9d47-2a23-274e-ca46-6896d4635845@gmail.com> Date: Fri, 3 May 2019 06:53:43 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.6.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------111E4024D77A4D1574AE596F" Content-Language: cs List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------111E4024D77A4D1574AE596F Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > --------------111E4024D77A4D1574AE596F Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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

--------------111E4024D77A4D1574AE596F--