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.klaver@xxxxxxxxxxx>:
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@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>:adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
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
adrian.klaver@xxxxxxxxxxx
