COPY FROM LOCAL "Too many columns found"

Jack_Kidwell

Is there a way to inspect the content of a "COPY...FROM LOCAL..." statement? We want to see the corrupt data as seen on the database server.

Adrian_Oprea_1

Can you post a sample of your data ?

Jack_Kidwell

It's a fire hose--about 260,000 rows per second, Most are inserted without an error. We see a few dozen of these errors per minute, so we are looking for ways to investigate.

Adrian_Oprea_1

Is your data enclosed ? by any comma or string ? 
What is you copy cmd look like ?

Daniel_Leybovic

In database Catalog folder should be CopyErrorLogs directory.
# list files and sort by modification time, oldest first

$ ls -lrt

In case of Client-Server load check error_messages table.
Example

CREATE TABLE public.stdin
(
    id int,
    c char(1)
);

daniel@synapse ~ $ vsql -c "copy stdin from local stdin direct"
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|a
>> 2|b
>> c|3
>> 4|d
>> CREATE TABLE public.stdin
(
    id int,
    c char(1)
);

daniel@synapse ~ $ vsql -c "copy stdin from local stdin direct"
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|a
>> 2|b
>> c|3
>> 4|d
>> \.
 Rows Loaded 
-------------
           3
(1 row)

daniel=> \x
Expanded display is on.
daniel=> select * from error_messages where message like 'Invalid%column%' order by event_timestamp desc;
-[ RECORD 1 ]---+---------------------------------------------
event_timestamp | 2014-05-20 21:45:23.745288+03
node_name       | v_dev_node0001
user_id         | 45035996273704962
user_name       | daniel
session_id      | synapse-3205:0x17b3
request_id      | 0
transaction_id  | 45035996273709962
statement_id    | 1
error_level     | ERROR
error_code      | 67788930
message         | Invalid integer format 'c' for column 1 (id)
detail          | 
hint            | 
#46;
 Rows Loaded 
-------------
           3
(1 row)

daniel=> \x
Expanded display is on.
daniel=> select * from error_messages where message like 'Invalid%column%' order by event_timestamp desc;
-[ RECORD 1 ]---+---------------------------------------------
event_timestamp | 2014-05-20 21:45:23.745288+03
node_name       | v_dev_node0001
user_id         | 45035996273704962
user_name       | daniel
session_id      | synapse-3205:0x17b3
request_id      | 0
transaction_id  | 45035996273709962
statement_id    | 1
error_level     | ERROR
error_code      | 67788930
message         | Invalid integer format 'c' for column 1 (id)
detail          | 
hint            | 

Jack_Kidwell

The data is CSV. Each field is numeric.

A code rollback resolved the issue. Fortunately cooperation won the day. But it'd be nice to have evidence to present for making a compelling argument.

Thanks!

Adrian_Oprea_1

Jack Kidwell - way to troubleshoot loaded data.