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

Kuczynski, Rafal (LNG-POL) Rafal.Kuczynski at lexisnexis.pl
Mon Aug 20 22:45:09 JST 2012


What is your version of postgresql?
Did you try to send the statement directly to postgresql backend? This could tell us that the problem is with pgpool or just the statement.

Regards,
Rafal

________________________________
From: Nick Maludy [mailto:nmaludy at gmail.com]
Sent: Monday, August 20, 2012 2:57 PM
To: Kuczynski, Rafal (LNG-POL)
Cc: pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 906] Re: Problem executing COPY WITH CSV in load balancing mode

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<mailto: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> [mailto: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<mailto: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<mailto: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/edca5f4a/attachment-0001.html>


More information about the pgpool-general mailing list