Multi-line records are not supported! panic: pq: unterminated CSV quoted field

[binakot]

Got an error on using timescaledb-parallel-copy.

$ timescaledb-parallel-copy -version
timescaledb-parallel-copy 0.3.0 (linux amd64)

$ timescaledb-parallel-copy --db-name postgres --table table --file data.csv
panic: pq: unterminated CSV quoted field

goroutine 21 [running]:
main.processBatches(0xc0000b6370, 0xc0000926c0)
        /go/src/github.com/timescale/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy/main.go:262 +0x8ad
created by main.main
        /go/src/github.com/timescale/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy/main.go:148 +0x1d2

Later I will compile the app from source and try to find the reason and prepare the test data set for reproduction.
The error may be related to the following issues: #19 #24 #31

[binakot]

First of all timescaledb-parallel-copy gives wrong output of inserted rows when CSV has multiline records.
Related to issue #19.

But looks like it works anyway even with wrong result output.

How to reproduce

Table to insert into:

CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  state TEXT,
  time TIMESTAMPTZ,
  date TEXT
)

Content of test file data.csv:

1,failed,2019-07-29 07:43:14.197455,"---
:image:
  :name: ubuntu:16.04
:before_script:
- echo ""Install sofrware""
- apt-get update && apt-get install -y sudo wget
:script:
- errcount=0
"
2,failed,2019-08-29 08:38:31.747244,"---
:image:
  :name: ubuntu:16.04
:before_script:
- echo ""Install sofrware""
- apt-get update && apt-get install -y sudo wget
:script:
- echo ""Tests""
- ""./tests.sh""
"
3,failed,2019-09-29 08:52:44.77306,"---
:image:
  :name: docker:stable
:services:
- :name: docker:stable-dind
:before_script:
- docker info
:script:
- echo ""Tests""
- ""./tests.sh""
"
4,failed,2019-10-29 09:31:14.098283,"---
:image:
  :name: ubuntu:16.04
:services:
- :name: docker:stable-dind
:before_script:
- docker info
:script:
- echo ""Tests""
- ""./tests.sh""
"
5,failed,2019-11-29 09:55:21.588764,"---
:image:
  :name: docker:stable
:services:
- :name: docker:stable-dind
:before_script:
- docker info
:script:
- echo ""Tests""
- ""./tests.sh""
"

$ timescaledb-parallel-copy --table test --file data.csv
COPY 52

52 rows were inserted.
It's not a count of records in CSV, it is a number or rows in file.

But table looks correct:

postgres=# select * from test limit 1;
 id | state  |             time              |                       date                       
----+--------+-------------------------------+--------------------------------------------------
  1 | failed | 2019-07-29 07:43:14.197455+00 | ---                                             
    |        |                               | :image:                                         
    |        |                               |   :name: ubuntu:16.04                           
    |        |                               | :before_script:                                 
    |        |                               | - echo "Install sofrware"                       
    |        |                               | - apt-get update && apt-get install -y sudo wget
    |        |                               | :script:                                        
    |        |                               | - errcount=0                                    
    |        |                               | 
  2 | failed | 2019-08-29 08:38:31.747244+00 | ---                                             
    |        |                               | :image:                                         
    |        |                               |   :name: ubuntu:16.04                           
    |        |                               | :before_script:                                 
    |        |                               | - echo "Install sofrware"                       
    |        |                               | - apt-get update && apt-get install -y sudo wget
    |        |                               | :script:                                        
    |        |                               | - echo "Tests"                                  
    |        |                               | - "./tests.sh"                                  
    |        |                               | 
  3 | failed | 2019-09-29 08:52:44.77306+00  | ---                                             
    |        |                               | :image:                                         
    |        |                               |   :name: docker:stable                          
    |        |                               | :services:                                      
    |        |                               | - :name: docker:stable-dind                     
    |        |                               | :before_script:                                 
    |        |                               | - docker info                                   
    |        |                               | :script:                                        
    |        |                               | - echo "Tests"                                  
    |        |                               | - "./tests.sh"                                  
    |        |                               | 
  4 | failed | 2019-10-29 09:31:14.098283+00 | ---                        
    |        |                               | :image:                    
    |        |                               |   :name: ubuntu:16.04      
    |        |                               | :services:                 
    |        |                               | - :name: docker:stable-dind
    |        |                               | :before_script:            
    |        |                               | - docker info              
    |        |                               | :script:                   
    |        |                               | - echo "Tests"             
    |        |                               | - "./tests.sh"             
    |        |                               | 
  5 | failed | 2019-11-29 09:55:21.588764+00 | ---                        
    |        |                               | :image:                    
    |        |                               |   :name: docker:stable     
    |        |                               | :services:                 
    |        |                               | - :name: docker:stable-dind
    |        |                               | :before_script:            
    |        |                               | - docker info              
    |        |                               | :script:                   
    |        |                               | - echo "Tests"             
    |        |                               | - "./tests.sh"             
    |        |                               | 

(5 rows)

[binakot]

I seem to have found the cause of the error. It's all about the same problem of multi-line records.

Specifying the --batch-size, which is 5000 by default, is not the number of records, but the number of rows, without taking the fact that the data can be on several lines.

How to reproduce

Table to insert into:

CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  state TEXT,
  time TIMESTAMPTZ,
  date TEXT
)

Content of test file data.csv:

1,failed,2019-07-29 07:43:14.197455,"---
:image:
  :name: ubuntu:16.04
:before_script:
- echo ""Install sofrware""
- apt-get update && apt-get install -y sudo wget
:script:
- errcount=0
"

Single worker

--batch-size 1 --workers 1

$ timescaledb-parallel-copy --table test --file data.csv --batch-size 1 --workers 1

>>> batch=&{[1,failed,2019-07-29 07:43:14.197455,"---]}
>>> copyCmd=COPY "public"."test" FROM STDIN WITH DELIMITER ',' CSV
panic: pq: unterminated CSV quoted field

goroutine 19 [running]:
main.processBatches(0xc0000c4350, 0xc0000ca660)
        /home/binakot/repos/github/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy/main.go:263 +0x978
created by main.main
        /home/binakot/repos/github/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy/main.go:148 +0x1bb

--batch-size 2 --workers 1

$ timescaledb-parallel-copy --table test --file data.csv --batch-size 2 --workers 1

>>> batch=&{[1,failed,2019-07-29 07:43:14.197455,"--- :image:]}
>>> copyCmd=COPY "public"."test" FROM STDIN WITH DELIMITER ',' CSV
panic: pq: unterminated CSV quoted field

goroutine 6 [running]:
main.processBatches(0xc00001a460, 0xc00002a720)
        /home/binakot/repos/github/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy/main.go:263 +0x978
created by main.main
        /home/binakot/repos/github/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy/main.go:148 +0x1bb

--batch-size 3 --workers 1

$ timescaledb-parallel-copy --table test --file data.csv --batch-size 3 --workers 1

>>> batch=&{[1,failed,2019-07-29 07:43:14.197455,"--- :image:   :name: ubuntu:16.04]}
>>> copyCmd=COPY "public"."test" FROM STDIN WITH DELIMITER ',' CSV
panic: pq: unterminated CSV quoted field

goroutine 6 [running]:
main.processBatches(0xc00001a460, 0xc00002a720)
        /home/binakot/repos/github/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy/main.go:263 +0x978
created by main.main
        /home/binakot/repos/github/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy/main.go:148 +0x1bb

--batch-size 100 --workers 1

$ timescaledb-parallel-copy --table test --file data.csv --batch-size 100 --workers 1

COPY 9

Two workers

--batch-size 1 --workers 2

$ timescaledb-parallel-copy --table test --file data.csv --batch-size 1 --workers 2

>>> batch=&{[:image:]}
>>> copyCmd=COPY "public"."test" FROM STDIN WITH DELIMITER ',' CSV
panic: pq: invalid input syntax for integer: ":image:"

goroutine 19 [running]:
main.processBatches(0xc0000c4350, 0xc0000ca660)
        /home/binakot/repos/github/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy/main.go:263 +0x978
created by main.main
        /home/binakot/repos/github/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy/main.go:148 +0x1bb

---

Debug output done with code fmt.Printf(">>> batch=%s\r\n>>> copyCmd=%s\r\n", batch, copyCmd) here: https://github.com/timescale/timescaledb-parallel-copy/blob/master/cmd/timescaledb-parallel-copy/main.go#L262

The current implementation is completely dependent on reading the file line by line. We need to think about how to ensure that the data is read by records rather than rows. Because every worker can get to the middle of a multi-line record.

[binakot]

@RobAtticus Hello!

Any ideas how to fix that?
May be is there some hacks to fix multi-line records?
Or is it required additional implementation?

[ryanmjacobs]

Also had this issue. Had to resort to psql -d new_db -c "\COPY conditions FROM old_db.csv CSV"

[mindrunner]

my comment was rubbish. deleting....

using psql is the proper workaround, as mentioned by @ryanmjacobs

[jchampio]

Okay, as of the above commit, multiline record splitting should no longer happen. If you have the ability to test that out and confirm, that'd be fantastic! Please reopen if you find any issues.