Thread: BUG #11121: Error not properly reported when calling PQputCopyData with a column missing

The following bug has been logged on the website:

Bug reference:      11121
Logged by:          Jay Carlson
Email address:      menotyou@gmail.com
PostgreSQL version: 9.3.4
Operating system:   Windows 7 (64 Bit)
Description:

The short version: After successfully calling calling PQexec to COPY_IN from
STDIN, I noticed calling PQputCopyData with a buffer that does not contain
all of the required columns and an incorrectly formatted column, an error is
not generated and PQputCopyEnd will not leave PGRES_COPY_IN mode.  An error
for this situation is only ennunciated after PQputCopyData is called again
AFTER calling PQputCopyEnd.

THE DETAILS:
Here is the format of the status_data table:
++ test_id: integer
++ device_id: integer
++ time_stamp: timestamp with time zone
++ mem_map_detail_id: integer
++ value: text

First I'm successfully calling PQexec with "COPY status_data (test_id,
device_id, time_stamp, mem_map_detail_id, value) FROM STDIN"

----------------------------------
PQputCopyData 1: (No error expected)
If I call PQputCopyData with the following the result is 1 (Success):
"1\t3\t07/25/2014\t48811\tHey1\n"

At this point after calling PQgetResult:
PQresultStatus returns 4 (PGRES_COPY_IN)
PQerrorMessage returns no error.

----------------------------------
PQputCopyData 2: (Expected Error)
If I call PQputCopyData with the following (missing the tab before "Hey2"),
the result is 1 (Success):
"1\t3\t07/25/2014\t48811Hey2\n"

At this point after calling PQgetResult:
PQresultStatus returns 4 (PGRES_COPY_IN)
PQerrorMessage returns no error.

**An error should have been generated here because we are calling
PQputCopyData with the incorrect number of columns AND an incorrect format
for one of the columns.

----------------------------------
PQputCopyData 3: (No error expected)
If I call PQputCopyData with the following the result is 1 (Success):
"1\t3\t07/25/2014\t48811\tHey3\n"

At this point after calling PQgetResult:
PQresultStatus returns 4 (PGRES_COPY_IN)
PQerrorMessage returns no error.

** There is STILL no error generated!

----------------------------------
If I then call PQputCopyEnd it returns 1 (Success) and an error message =
(null).

At this point after calling PQgetResult:
PQresultStatus returns 4 (PGRES_COPY_IN)

So far no error has been returned at all but PQputCopyEnd did not leave the
PGRES_COPY_IN state.

----------------------------------
PQputCopyData 4:
Now, AFTER CALLING PQputCopyEnd, if I call PQputCopyData with the following
valid buffer string I FINALLY see an error even though the string I'm
sending is kosher:
"1\t3\t07/25/2014\t48811\tHey4\n"

At this point after calling PQgetResult:
PQresultStatus returns 7 (PGRES_FATAL_ERROR)
PQerrorMessage returns:
'ERROR:  invalid input syntax for integer: "48811Hey2"
CONTEXT:  COPY status_data, line 2, column mem_map_detail_id: "48811Hey2"'

menotyou wrote
> The following bug has been logged on the website:
>
> Bug reference:      11121
> Logged by:          Jay Carlson
> Email address:

> menotyou@

> PostgreSQL version: 9.3.4
> Operating system:   Windows 7 (64 Bit)
> Description:
>
> The short version: After successfully calling calling PQexec to COPY_IN
> from
> STDIN, I noticed calling PQputCopyData with a buffer that does not contain
> all of the required columns and an incorrectly formatted column, an error
> is
> not generated and PQputCopyEnd will not leave PGRES_COPY_IN mode.  An
> error
> for this situation is only ennunciated after PQputCopyData is called again
> AFTER calling PQputCopyEnd.
>
> THE DETAILS:
> Here is the format of the status_data table:
> ++ test_id: integer
> ++ device_id: integer
> ++ time_stamp: timestamp with time zone
> ++ mem_map_detail_id: integer
> ++ value: text
>
> First I'm successfully calling PQexec with "COPY status_data (test_id,
> device_id, time_stamp, mem_map_detail_id, value) FROM STDIN"
>
> ----------------------------------
> PQputCopyData 1: (No error expected)
> If I call PQputCopyData with the following the result is 1 (Success):
> "1\t3\t07/25/2014\t48811\tHey1\n"
>
> At this point after calling PQgetResult:
> PQresultStatus returns 4 (PGRES_COPY_IN)
> PQerrorMessage returns no error.
>
> ----------------------------------
> PQputCopyData 2: (Expected Error)
> If I call PQputCopyData with the following (missing the tab before
> "Hey2"),
> the result is 1 (Success):
> "1\t3\t07/25/2014\t48811Hey2\n"
>
> At this point after calling PQgetResult:
> PQresultStatus returns 4 (PGRES_COPY_IN)
> PQerrorMessage returns no error.
>
> **An error should have been generated here because we are calling
> PQputCopyData with the incorrect number of columns AND an incorrect format
> for one of the columns.

This is not a bug but improper usage of the API.  In short, both
PQputCopyData and PQputCopyEnd are strictly concerned with data transfer
success/failure.  The data is not guaranteed to be parsed until you attempt
to finish the copy command by executing PQgetResult.

See this earlier discussion for more context and thoughts:

http://postgresql.1045698.n5.nabble.com/PQputCopyData-dont-signal-error-td4302340.html#a5798077

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-11121-Error-not-properly-reported-when-calling-PQputCopyData-with-a-column-missing-tp5813713p5813724.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.