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

Nick Maludy nmaludy at gmail.com
Mon Aug 20 22:31:04 JST 2012 

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/35b00655/attachment-0001.html>

More information about the pgpool-general mailing list