Обсуждение: Does PLPythonU support COPY table FROM string?
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
^
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?
----
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
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);
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';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.
2015-08-28 17:15 GMT+02:00 Adrian Klaver <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
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@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>>: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
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
Unless I miss something, cursor won't help here - it is to retrieve data, not to insert it.
I just need a missing feature of `plpy` to do COPY (actually doable, but from file).2015-08-29 1:34 GMT+02:00 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