Re: Error at dynamic generated copy... - Mailing list 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.

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