[pgpool-general: 911] Re: Problem executing COPY WITH CSV in load balancing mode

Nick Maludy nmaludy at gmail.com
Mon Aug 20 22:42:47 JST 2012 

All,

I'm an idiot, the reason why i couldn't get it to work for the first
element being NULL is that the first element is my distribution column so
it can't be NULL (DUH!).

So, i guess the key for doing COPY with CSV is manually specifying the
DELIMITER, QUOTE, and NULL strings in the command.

-Nick

On Mon, Aug 20, 2012 at 9:31 AM, Nick Maludy <nmaludy at gmail.com> wrote:

> All,
>
> I am now able to get CSV to work if i specify the following command:
> COPY base_time_table FROM STDIN WITH DELIMITER ',' CSV QUOTE '"' NULL AS ''
>
> This works with the following data:
> 0,0,"Boston"
> 1,1,
> 2,,
>
> But does NOT work if the first element is empty/NULL:
> ,1,"Detroit"
> ,2,
> ,,
>
> Again, if i delete my entry from the pgpool_catalog.dist_def table and
> restart pgpool the above data  works with the same command.
>
> -Nick
>
>
> On Mon, Aug 20, 2012 at 8:56 AM, Nick Maludy <nmaludy at gmail.com> wrote:
>
>> Rafal,
>>
>> I tried using the same table but changing my data input to:
>>
>> 0,0,"Boston"
>> 1,1,"Detroit"
>>
>> I still received an error from psql "connection to server was lost" and
>> the following appeared in pgpool.log:
>>
>> 2012-08-20 08:48:46 LOG:   pid 23536: DB node id: 1 backend pid: 2233
>> statement: BEGIN
>> 2012-08-20 08:48:46 DEBUG: pid 23536: do_command: Query: BEGIN
>> 2012-08-20 08:48:46 DEBUG: pid 23536: wait_for_query_response: waiting
>> for backend 1 completing the query
>> 2012-08-20 08:48:46 DEBUG: pid 23536: detect_error: kind: C
>> 2012-08-20 08:48:46 DEBUG: pid 23536: do_command: kind: C
>> 2012-08-20 08:48:46 DEBUG: pid 23536: do_command: kind: Z
>> 2012-08-20 08:48:46 DEBUG: pid 23536: do_command: transaction state: T
>> 2012-08-20 08:48:46 DEBUG: pid 23536: pool_unset_writing_transaction: done
>> 2012-08-20 08:48:46 LOG:   pid 23536: DB node id: 0 backend pid: 2861
>> statement: COPY base_time_table FROM STDIN WITH CSV;
>> 2012-08-20 08:48:46 DEBUG: pid 23536: wait_for_query_response: waiting
>> for backend 0 completing the query
>> 2012-08-20 08:48:46 DEBUG: pid 23536: detect_error: kind: G
>> 2012-08-20 08:48:46 DEBUG: pid 23536: detect_error: kind: G
>> 2012-08-20 08:48:46 DEBUG: pid 23536: detect_error: kind: G
>> 2012-08-20 08:48:46 DEBUG: pid 23536: detect_error: kind: G
>> 2012-08-20 08:48:46 LOG:   pid 23536: DB node id: 1 backend pid: 2233
>> statement: COPY base_time_table FROM STDIN WITH CSV;
>> 2012-08-20 08:48:46 DEBUG: pid 23536: wait_for_query_response: waiting
>> for backend 1 completing the query
>> 2012-08-20 08:48:46 DEBUG: pid 23536: read_kind_from_backend: kind: G
>> from 0 th backend
>> 2012-08-20 08:48:46 DEBUG: pid 23536: read_kind_from_backend: read kind
>> from 0 th backend G NUM_BACKENDS: 2
>> 2012-08-20 08:48:46 DEBUG: pid 23536: read_kind_from_backend: kind: G
>> from 1 th backend
>> 2012-08-20 08:48:46 DEBUG: pid 23536: read_kind_from_backend: read kind
>> from 1 th backend G NUM_BACKENDS: 2
>> 2012-08-20 08:48:46 DEBUG: pid 23536: ProcessBackendResponse: kind from
>> backend: G
>> 2012-08-20 08:48:54 DEBUG: pid 23536: parse_copy_data: divide key value
>> is 1,1,"Boston"
>> 2012-08-20 08:48:54 ERROR: pid 23536: PQexecPrepared failed: ERROR:
>>  invalid input syntax for integer: "1,1,"Boston""
>>
>> 2012-08-20 08:48:54 DEBUG: pid 23536: CopyDataRow: copying id: -1
>> 2012-08-20 08:48:54 ERROR: pid 23536: pool_is_node_to_be_sent: invalid
>> node id:-1
>>
>>
>> If i login to the pgpool database and delete my entry from
>> pgpool_catalog.dist_def for my base_time_table then restart pgpool i can
>> use the same command "COPY base_time_table FROM STDIN WITH CSV" and the
>> same data 0,0,"Boston" things work just fine.
>>
>> -Nick
>>
>> On Mon, Aug 20, 2012 at 5:27 AM, Kuczynski, Rafal (LNG-POL) <
>> Rafal.Kuczynski at lexisnexis.pl> wrote:
>>
>>> ** ** **
>>>
>>> String values must be quoted, afaik the default quote for csv mode is
>>> double-quote (“”). It means that your input values must be like****
>>>
>>> 0,0,”****Boston****”****
>>>
>>> ** **
>>>
>>> You can also specify your own quote character (with csv quote as
>>> ‘yourquote’)****
>>>
>>>  ****
>>>
>>> Regards,****
>>>
>>> Rafal****
>>>
>>>  ****
>>>   ------------------------------
>>>
>>> *From:* pgpool-general-bounces at pgpool.net [mailto:
>>> pgpool-general-bounces at pgpool.net] *On Behalf Of *Nick Maludy
>>> *Sent:* Friday, August 17, 2012 10:16 PM
>>> *To:* **pgpool-general at pgpool.net**
>>> *Subject:* [pgpool-general: 900] Problem executing COPY WITH CSV in
>>> load balancing mode****
>>>
>>> ** **
>>>
>>> Hello,****
>>>
>>> ** **
>>>
>>> I'm current running pgpool-II version 3.2.0 (namameboshi). I'm currently
>>> running in load_balance_mode=on, parallel_mode=on, replicaton_mode=off. The
>>> following command fails when i execute it:****
>>>
>>> ** **
>>>
>>> COPY base_time_table FROM STDIN WITH CSV****
>>>
>>>
>>> *The data i'm trying to pass in on STDIN is formatted like:*****
>>>
>>> 0,0,Boston****
>>>
>>> 1,1,Detroit****
>>>
>>> etc...****
>>>
>>> ** **
>>>
>>> *The error i am getting in /tmp/pgpool.log is:*****
>>>
>>> 2012-08-17 16:08:20 LOG:   pid 9333: DB node id: 0 backend pid: 22780
>>> statement: COPY base_time_table FROM STDIN WITH CSV;****
>>>
>>> 2012-08-17 16:08:20 DEBUG: pid 9333: wait_for_query_response: waiting
>>> for backend 0 completing the query****
>>>
>>> 2012-08-17 16:08:20 DEBUG: pid 9333: detect_error: kind: G****
>>>
>>> 2012-08-17 16:08:20 DEBUG: pid 9333: detect_error: kind: G****
>>>
>>> 2012-08-17 16:08:20 DEBUG: pid 9333: detect_error: kind: G****
>>>
>>> 2012-08-17 16:08:20 DEBUG: pid 9333: detect_error: kind: G****
>>>
>>> 2012-08-17 16:08:20 LOG:   pid 9333: DB node id: 1 backend pid: 23689
>>> statement: COPY base_time_table FROM STDIN WITH CSV;****
>>>
>>> 2012-08-17 16:08:20 DEBUG: pid 9333: wait_for_query_response: waiting
>>> for backend 1 completing the query****
>>>
>>> 2012-08-17 16:08:20 DEBUG: pid 9333: read_kind_from_backend: kind: G
>>> from 0 th backend****
>>>
>>> 2012-08-17 16:08:20 DEBUG: pid 9333: read_kind_from_backend: read kind
>>> from 0 th backend G NUM_BACKENDS: 2****
>>>
>>> 2012-08-17 16:08:20 DEBUG: pid 9333: read_kind_from_backend: kind: G
>>> from 1 th backend****
>>>
>>> 2012-08-17 16:08:20 DEBUG: pid 9333: read_kind_from_backend: read kind
>>> from 1 th backend G NUM_BACKENDS: 2****
>>>
>>> 2012-08-17 16:08:20 DEBUG: pid 9333: ProcessBackendResponse: kind from
>>> backend: G****
>>>
>>> 2012-08-17 16:08:44 DEBUG: pid 9333: *parse_copy_data: divide key value
>>> is 0,0,Boston*****
>>>
>>> 2012-08-17 16:08:44 ERROR: pid 9333: *PQexecPrepared failed: ERROR:
>>>  invalid input syntax for integer: "0,0,Boston"*****
>>>
>>> ** **
>>>
>>> 2012-08-17 16:08:44 DEBUG: pid 9333: CopyDataRow: copying id: -1****
>>>
>>> 2012-08-17 16:08:44 ERROR: pid 9333: pool_is_node_to_be_sent: invalid
>>> node id:-1****
>>>
>>> 2012-08-17 16:08:44 DEBUG: pid 8774: reap_handler called****
>>>
>>> 2012-08-17 16:08:44 DEBUG: pid 8774: reap_handler: call wait3****
>>>
>>> 2012-08-17 16:08:44 DEBUG: pid 8774: child 9333 exits with status 256***
>>> *
>>>
>>> 2012-08-17 16:08:44 DEBUG: pid 8774: fork a new child pid 9345****
>>>
>>> 2012-08-17 16:08:44 DEBUG: pid 8774: reap_handler: normally exited****
>>>
>>> ** **
>>>
>>> *My table has the following structure:*****
>>>
>>> test# \d base_time_table;****
>>>
>>>   Table "public.base_time_table"****
>>>
>>>    Column    |  Type  | Modifiers ****
>>>
>>> -------------+--------+-----------****
>>>
>>>  base_time   | bigint | ****
>>>
>>>  base_id     | bigint | ****
>>>
>>>  base_source | text   | ****
>>>
>>> ** **
>>>
>>> *My pgpool_catalog.dist_def entry is:*****
>>>
>>> pgpool=# select * from  pgpool_catalog.dist_def;****
>>>
>>>  dbname | schema_name |   table_name    | col_name  |
>>>  col_list             |      type_lis****
>>>
>>> t       |   dist_def_func    ****
>>>
>>>
>>> --------+-------------+-----------------+-----------+---------------------------------+--------------
>>> ****
>>>
>>> --------+--------------------****
>>>
>>>  rr     | public      | base_time_table | base_time |
>>> {base_time,base_id,base_source} | {bigint,bigin****
>>>
>>> t,text} | dist_def_base_time****
>>>
>>> ** **
>>>
>>> *My dist_def_base_time function is:*****
>>>
>>> CREATE OR REPLACE FUNCTION dist_def_base_time(anyelement)****
>>>
>>> RETURNS integer AS $$****
>>>
>>>     SELECT CASE WHEN $1 > 5000 THEN 0****
>>>
>>>         ELSE 0****
>>>
>>>     END;****
>>>
>>> $$ LANGUAGE sql;****
>>>
>>> ** **
>>>
>>> ** **
>>>
>>> I noticed that if i change the command to:****
>>>
>>> COPY base_time_table FROM STDIN;****
>>>
>>> ** **
>>>
>>> And use the PostgreSQL Text Format for COPY things work just fine.****
>>>
>>> ** **
>>>
>>> Also, if i remove my entry from pgpool_catalog.dist_def for
>>> base_time_table and restart pgpool the COPY WITH CSV works fine also
>>> (except it sends the data to all nodes).****
>>>
>>> ** **
>>>
>>> ** **
>>>
>>> Any help would be greatly appreciated,****
>>>
>>> Nick****
>>>
>>> _______________________________________________
>>> pgpool-general mailing list
>>> pgpool-general at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>>
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120820/0a88d716/attachment-0001.html>

More information about the pgpool-general mailing list