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

Nick Maludy nmaludy at gmail.com
Mon Aug 20 21:56:30 JST 2012 

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

More information about the pgpool-general mailing list