Thread: Does PLPythonU support COPY table FROM string?

On 08/28/2015 05:58 AM, Dominik Czarnota wrote:
> Hello,
>
> Is there any possibility to make COPY from list of records in PLPythonU?
>
> I've tried to simply call `plpy.execute(query)` with such query:
>
> COPY table (field1, field2, field3) FROM STDIN DELIMITER',';
> val1,val2,val3
> \.
>
> But it fails with not so explicit error:
> ProgrammingError: (psycopg2.ProgrammingError) spiexceptions.SyntaxError:
> syntax error at or near "val1"
> LINE 2: val1,val2,val3
>          ^

The above is a psycopg2 error, so where are you running this and how?

>
> However the same thing works in psql.
>
> After some googling I found out that it doesn't work inside sql
> functions because there is no STDIN (
> http://www.postgresql.org/message-id/000501c5acc9$83747aa0$0501a8c0@plexus
> ).
>
>
> I've got working solution which was saving tmp file and then calling
> `COPY table FROM 'path'` but that's not cool.
>
> Did I miss something in plpythonu api? If not, where can I post "feature
> request" for that?
> Or can you point me place where I could dig into the code and implement it?
>
>
> ----
> Dominik Czarnota


--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/28/2015 08:32 AM, Dominik Czarnota wrote:
> I am launching it from postgres plpythonu function (postgres version:
> PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
> 4.9.2-10) 4.9.2, 64-bit).
>
> The error can be reproduced using the SQL below:
>
> DROP TABLE IF EXISTS test;
> CREATE TABLE test(field1 integer, field2 integer);
>
> CREATE OR REPLACE FUNCTION example() RETURNS VOID AS
> $$
>      plpy.execute("COPY test (field1, field2) FROM STDIN
> DELIMITER',';\n1,2\n\\.")
> $$ LANGUAGE 'plpythonu';
>
> select example();
>
>
> Maybe there is a way to get into the underlying psycopg2 driver and call
> some method that would do COPY FROM, but I can't find anything related
> to it in the docs.

plpythonu does not use psycopg2. When I run your test code on 9.4.4 I get:

aklaver@test=> select example();
ERROR:  spiexceptions.SyntaxError: syntax error at or near "1"
LINE 2: 1,2
         ^
QUERY:  COPY test (field1, field2) FROM STDIN DELIMITER',';
1,2
\.
CONTEXT:  Traceback (most recent call last):
   PL/Python function "example", line 2, in <module>
     plpy.execute("COPY test (field1, field2) FROM STDIN
DELIMITER',';\n1,2\n\\.")
PL/Python function "example"


It is a similar error, but it is not coming from psycopg2, so I am still
not sure how you got the error below?

>
> 2015-08-28 17:15 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>
>     On 08/28/2015 05:58 AM, Dominik Czarnota wrote:
>
>         Hello,
>
>         Is there any possibility to make COPY from list of records in
>         PLPythonU?
>
>         I've tried to simply call `plpy.execute(query)` with such query:
>
>         COPY table (field1, field2, field3) FROM STDIN DELIMITER',';
>         val1,val2,val3
>         \.
>
>         But it fails with not so explicit error:
>         ProgrammingError: (psycopg2.ProgrammingError)
>         spiexceptions.SyntaxError:
>         syntax error at or near "val1"
>         LINE 2: val1,val2,val3
>                   ^
>
>
>     The above is a psycopg2 error, so where are you running this and how?
>
>
>
>         However the same thing works in psql.
>
>         After some googling I found out that it doesn't work inside sql
>         functions because there is no STDIN (
>         http://www.postgresql.org/message-id/000501c5acc9$83747aa0$0501a8c0@plexus
>         ).
>
>
>         I've got working solution which was saving tmp file and then calling
>         `COPY table FROM 'path'` but that's not cool.
>
>         Did I miss something in plpythonu api? If not, where can I post
>         "feature
>         request" for that?
>         Or can you point me place where I could dig into the code and
>         implement it?
>
>
>         ----
>         Dominik Czarnota
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/28/2015 08:32 AM, Dominik Czarnota wrote:
> I am launching it from postgres plpythonu function (postgres version:
> PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
> 4.9.2-10) 4.9.2, 64-bit).
>
> The error can be reproduced using the SQL below:
>
> DROP TABLE IF EXISTS test;
> CREATE TABLE test(field1 integer, field2 integer);
>
> CREATE OR REPLACE FUNCTION example() RETURNS VOID AS
> $$
>      plpy.execute("COPY test (field1, field2) FROM STDIN
> DELIMITER',';\n1,2\n\\.")
> $$ LANGUAGE 'plpythonu';
>
> select example();
>
>
> Maybe there is a way to get into the underlying psycopg2 driver and call
> some method that would do COPY FROM, but I can't find anything related
> to it in the docs.

The only thing I can think of is using io.BytesIO() to create an in
memory file and then reading from that. I have done it using psycopg2,
but not in plpythonu so I would mark this untested.

Otherwise, could you explain more where the list is coming from and it's
size as there may be other ways to attack this.

>
> 2015-08-28 17:15 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>
>     On 08/28/2015 05:58 AM, Dominik Czarnota wrote:
>
>         Hello,
>
>         Is there any possibility to make COPY from list of records in
>         PLPythonU?
>
>         I've tried to simply call `plpy.execute(query)` with such query:
>
>         COPY table (field1, field2, field3) FROM STDIN DELIMITER',';
>         val1,val2,val3
>         \.
>
>         But it fails with not so explicit error:
>         ProgrammingError: (psycopg2.ProgrammingError)
>         spiexceptions.SyntaxError:
>         syntax error at or near "val1"
>         LINE 2: val1,val2,val3
>                   ^
>
>
>     The above is a psycopg2 error, so where are you running this and how?
>
>
>
>         However the same thing works in psql.
>
>         After some googling I found out that it doesn't work inside sql
>         functions because there is no STDIN (
>         http://www.postgresql.org/message-id/000501c5acc9$83747aa0$0501a8c0@plexus
>         ).
>
>
>         I've got working solution which was saving tmp file and then calling
>         `COPY table FROM 'path'` but that's not cool.
>
>         Did I miss something in plpythonu api? If not, where can I post
>         "feature
>         request" for that?
>         Or can you point me place where I could dig into the code and
>         implement it?
>
>
>         ----
>         Dominik Czarnota
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/28/2015 10:12 AM, Dominik Czarnota wrote:
> Oh... I have copied stacktrace generated from Python script which
> connects to the db using psycopg2 driver, so that's where this
> misleading psycopg2 error came from...
>
> About the list origin - I have to calculate a lot of stuff for each
> stock on stock exchange market. Each calculation requires quotes from
> the database - so to reduce io and not to fetch them everytime from
> Python, I've created a plpythonu function that calculates everything for
> each stock.
> As the results also have to stay in the database, I need to use the
> fastest possible method to insert a lot of data.

This means moving the results to another table?

Have you looked at plpy.cursor here:

http://www.postgresql.org/docs/9.4/interactive/plpython-database.html
>
> Another problem for those calculations would be parallelizing them
> somehow (I hope postgres clustering will do the work), but that's
> totally unrelated to the problem we are discussing now.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com