Error at dynamic generated copy...

On 08/12/2016 07:11 AM, Edmundo Robles wrote:

Hi!
I hope you could help me...
I tried to generate the next copy instruction in a function:

copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin
DELIMITER as '|' ;
12060157|John|Doe|33
\.

** The commands to generate the copy are:
CREATE OR REPLACE FUNCTION insert_tablefoo(id integer, name varchar,
lastname varchar, age integer)

... declarations ...

qry=format('copy tablefoo (id_foo, foo_name, foo_lastname, foo_age)
FROM stdin DELIMITER as '||chr(39) || '|' || chr(39) || ';' || chr(13)
||'%s|%s|%s|%s' || chr(13) || '\.',id,name,lastname,age);

execute(qry);

... more declarations
end <- end function, returns an integer.

** then, i call the function:
select insert_tablefoo(12321,'John','Doe',33);

**and get the next error:

ERROR: syntax error at or near "12321"
LINE 2: 12321|John|Doe|33

********** Error **********

after many tests, the problem is concatenate the newline, because if
the instruction generated is copied to a file and insert manually the
newline the query works well.

i tried with chr(10), '\n', '\r', split the qry string ( execute
qry_copy || E'\\n' || qry_data || E'\\n\\.')
but always get the same error :(

ERROR: syntax error at or near "12321"
SQL state: 42601

** By the way, i tried to replace an insert with copy because the
insert takes more than 3 minutes to insert a single record.

The above seems to be the real problem.

Can you describe more what you are doing when you INSERT?

Regards and thanks in advance.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/12/2016 07:11 AM, Edmundo Robles wrote:

Hi!
I hope you could help me...
I tried to generate the next copy instruction in a function:

copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin
DELIMITER as '|' ;
12060157|John|Doe|33
\.

** The commands to generate the copy are:
CREATE OR REPLACE FUNCTION insert_tablefoo(id integer, name varchar,
lastname varchar, age integer)

... declarations ...

qry=format('copy tablefoo (id_foo, foo_name, foo_lastname, foo_age)
FROM stdin DELIMITER as '||chr(39) || '|' || chr(39) || ';' || chr(13)
||'%s|%s|%s|%s' || chr(13) || '\.',id,name,lastname,age);

The secondary problem.

Could not the above be written as:

copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin
DELIMITER as '|' ;
id|name|lastname|age
\.

execute(qry);

... more declarations
end <- end function, returns an integer.

** then, i call the function:
select insert_tablefoo(12321,'John','Doe',33);

**and get the next error:

ERROR: syntax error at or near "12321"
LINE 2: 12321|John|Doe|33

********** Error **********

after many tests, the problem is concatenate the newline, because if
the instruction generated is copied to a file and insert manually the
newline the query works well.

i tried with chr(10), '\n', '\r', split the qry string ( execute
qry_copy || E'\\n' || qry_data || E'\\n\\.')
but always get the same error :(

ERROR: syntax error at or near "12321"
SQL state: 42601

** By the way, i tried to replace an insert with copy because the
insert takes more than 3 minutes to insert a single record.

Regards and thanks in advance.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian, your'e right, the real problem is the slow insert, I have many
devices reporting to the server and saving their state each minute so
there is a moment where i reach the limit of connections and the monitor
device send a exception and crash.

The table grows a lot, current have more than 13,000,000 records, plus
have many indexes, that the reason why is slow to insert That's why i
tried to use copy.

On Fri, Aug 12, 2016 at 9:43 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 08/12/2016 08:15 AM, Edmundo Robles wrote:

Adrian, your'e right, the real problem is the slow insert, I have
many devices reporting to the server and saving their state each
minute so there is a moment where i reach the limit of connections and
the monitor device send a exception and crash.

The table grows a lot, current have more than 13,000,000 records, plus
have many indexes, that the reason why is slow to insert That's why i
tried to use copy.

So are all the other INSERTs this slow also?

Using COPY to 'game' the system does not look like a long term solution
to me. Lack of system resources will eventually prevail and then what
happens?

On Fri, Aug 12, 2016 at 9:43 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 08/12/2016 07:11 AM, Edmundo Robles wrote:

Hi!
I hope you could help me...
I tried to generate the next copy instruction in a function:

copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin
DELIMITER as '|' ;
12060157|John|Doe|33
\.

** The commands to generate the copy are:
CREATE OR REPLACE FUNCTION insert_tablefoo(id integer, name
varchar,
lastname varchar, age integer)

... declarations ...

qry=format('copy tablefoo (id_foo, foo_name, foo_lastname,
foo_age)
FROM stdin DELIMITER as '||chr(39) || '|' || chr(39) || ';' ||
chr(13)
||'%s|%s|%s|%s' || chr(13) || '\.',id,name,lastname,age);

execute(qry);

... more declarations
end <- end function, returns an integer.

** then, i call the function:
select insert_tablefoo(12321,'John','Doe',33);

**and get the next error:

ERROR: syntax error at or near "12321"
LINE 2: 12321|John|Doe|33

********** Error **********

after many tests, the problem is concatenate the newline,
because if
the instruction generated is copied to a file and insert
manually the
newline the query works well.

i tried with chr(10), '\n', '\r', split the qry string ( execute
qry_copy || E'\\n' || qry_data || E'\\n\\.')
but always get the same error :(

ERROR: syntax error at or near "12321"
SQL state: 42601

** By the way, i tried to replace an insert with copy
because the
insert takes more than 3 minutes to insert a single record.

The above seems to be the real problem.

Can you describe more what you are doing when you INSERT?

Regards and thanks in advance.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/12/2016 08:15 AM, Edmundo Robles wrote:

Adrian, your'e right, the real problem is the slow insert, I have
many devices reporting to the server and saving their state each
minute so there is a moment where i reach the limit of connections and
the monitor device send a exception and crash.

The table grows a lot, current have more than 13,000,000 records, plus
have many indexes, that the reason why is slow to insert That's why i
tried to use copy.

My COPY example turned out to be a bust, so ignore. Sorry.

On Fri, Aug 12, 2016 at 9:43 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 08/12/2016 07:11 AM, Edmundo Robles wrote:

Hi!
I hope you could help me...
I tried to generate the next copy instruction in a function:

copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin
DELIMITER as '|' ;
12060157|John|Doe|33
\.

** The commands to generate the copy are:
CREATE OR REPLACE FUNCTION insert_tablefoo(id integer, name
varchar,
lastname varchar, age integer)

... declarations ...

qry=format('copy tablefoo (id_foo, foo_name, foo_lastname,
foo_age)
FROM stdin DELIMITER as '||chr(39) || '|' || chr(39) || ';' ||
chr(13)
||'%s|%s|%s|%s' || chr(13) || '\.',id,name,lastname,age);

execute(qry);

... more declarations
end <- end function, returns an integer.

** then, i call the function:
select insert_tablefoo(12321,'John','Doe',33);

**and get the next error:

ERROR: syntax error at or near "12321"
LINE 2: 12321|John|Doe|33

********** Error **********

after many tests, the problem is concatenate the newline,
because if
the instruction generated is copied to a file and insert
manually the
newline the query works well.

i tried with chr(10), '\n', '\r', split the qry string ( execute
qry_copy || E'\\n' || qry_data || E'\\n\\.')
but always get the same error :(

ERROR: syntax error at or near "12321"
SQL state: 42601

** By the way, i tried to replace an insert with copy
because the
insert takes more than 3 minutes to insert a single record.

The above seems to be the real problem.

Can you describe more what you are doing when you INSERT?

Regards and thanks in advance.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

don't worry. I still working in this copy, how can i do to insert
newlines to generate the copy dynamically and avoid the syntax error.

On Fri, Aug 12, 2016 at 11:11 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 08/12/2016 09:34 AM, Edmundo Robles wrote:

don't worry. I still working in this copy, how can i do to insert
newlines to generate the copy dynamically and avoid the syntax error.

I think there is a bigger problem. From one of attempts:

ERROR: cannot COPY to/from client in PL/pgSQL

On Fri, Aug 12, 2016 at 11:11 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 08/12/2016 08:15 AM, Edmundo Robles wrote:

Adrian, your'e right, the real problem is the slow insert, I have
many devices reporting to the server and saving their state
each
minute so there is a moment where i reach the limit of
connections and
the monitor device send a exception and crash.

The table grows a lot, current have more than 13,000,000
records, plus
have many indexes, that the reason why is slow to insert
That's why i
tried to use copy.

My COPY example turned out to be a bust, so ignore. Sorry.

On Fri, Aug 12, 2016 at 9:43 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>> wrote:

On 08/12/2016 07:11 AM, Edmundo Robles wrote:

Hi!
I hope you could help me...
I tried to generate the next copy instruction in a
function:

copy tablefoo (id_foo, foo_name, foo_lastname, foo_age)
FROM stdin
DELIMITER as '|' ;
12060157|John|Doe|33
\.

** The commands to generate the copy are:
CREATE OR REPLACE FUNCTION insert_tablefoo(id
integer, name
varchar,
lastname varchar, age integer)

... declarations ...

qry=format('copy tablefoo (id_foo, foo_name,
foo_lastname,
foo_age)
FROM stdin DELIMITER as '||chr(39) || '|' || chr(39)
|| ';' ||
chr(13)
||'%s|%s|%s|%s' || chr(13) || '\.',id,name,lastname,age);

execute(qry);

... more declarations
end <- end function, returns an integer.

** then, i call the function:
select insert_tablefoo(12321,'John','Doe',33);

**and get the next error:

ERROR: syntax error at or near "12321"
LINE 2: 12321|John|Doe|33

********** Error **********

after many tests, the problem is concatenate the newline,
because if
the instruction generated is copied to a file and insert
manually the
newline the query works well.

i tried with chr(10), '\n', '\r', split the qry
string ( execute
qry_copy || E'\\n' || qry_data || E'\\n\\.')
but always get the same error :(

ERROR: syntax error at or near "12321"
SQL state: 42601

** By the way, i tried to replace an insert with copy
because the
insert takes more than 3 minutes to insert a single record.

The above seems to be the real problem.

Can you describe more what you are doing when you INSERT?

Regards and thanks in advance.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general