Re: Does PLPythonU support COPY table FROM string?

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.

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.

2015-08-28 17:59 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>