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

[Kuczynski, Rafal (LNG-POL)]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120820/6c436cdc/attachment-0001.html>