Обсуждение: Getting "insufficient data left in message" on copy with binary

Hello,

I'm running 8.4 on Linux/Centos.  I am doing a "copy (select ....) to
'/absolute/path/to/file.dat' with binary". That works fine.  But when I load
that file into a table...

  copy mytable (id, mlid, parent_mlid, author_id, date_id, time_id,
content_type_id, provider_id,
    is_duplicate, is_spam, language_code, profanity, tonality, sentiment,
created_time, updated_at)
  from '/absolute/path/to/file.dat' with binary;

  ERROR:  insufficient data left in message
  CONTEXT:  COPY mytable, line 1, column provider_id

The same data works fine without the "with binary".  Also, the column it's
complaining about, provider_id, is a NOT NULL column, and the data is
definitely there -- i.e. not a NULL in data file.

I have searched for this message and mostly I see issues related to JDBC
drivers, so that doesn't appear relevant.  And they all talk about nul bytes
(0x00), but again, how can that be relevant when I'm in binary mode?  Seems
like it should understand null bytes here, if that's what this is about.

Anybody seen this?

Thanks,
Gordon

--
View this message in context:
http://www.nabble.com/Getting-%22insufficient-data-left-in-message%22-on-copy-with-binary-tp25282935p25282935.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Gordon Shannon <gordo169@gmail.com> writes:
> I'm running 8.4 on Linux/Centos.  I am doing a "copy (select ....) to
> '/absolute/path/to/file.dat' with binary". That works fine.  But when I load
> that file into a table...

>   copy mytable (id, mlid, parent_mlid, author_id, date_id, time_id,
> content_type_id, provider_id,
>     is_duplicate, is_spam, language_code, profanity, tonality, sentiment,
> created_time, updated_at)
>   from '/absolute/path/to/file.dat' with binary;

>   ERROR:  insufficient data left in message
>   CONTEXT:  COPY mytable, line 1, column provider_id

> Anybody seen this?

No.  Can you extract a self-contained test case?

            regards, tom lane

Tom Lane-2 wrote:
>
> Gordon Shannon <gordo169@gmail.com> writes:
>
>>   ERROR:  insufficient data left in message
>>   CONTEXT:  COPY mytable, line 1, column provider_id
>
>> Anybody seen this?
>
> No.  Can you extract a self-contained test case?
>


Got it.  The problem was a combination of 2 mis-matched data types.
Consider this test case:

----------------begin-------------------

drop table if exists bar;
drop table if exists foo;

create table foo (
system_id smallint,
credibility real not null
);

insert into foo ( system_id, credibility) values (1,1);

copy foo to '/tmp/repeat.dat' with binary;

create table bar (
system_id int,
credibility numeric(10,9) not null
);

copy bar from '/tmp/repeat.dat' with binary;

copy bar from '/var/lib/pgsql/backups/repeat.dat' with binary;
psql:repeat:19: ERROR:  insufficient data left in message
CONTEXT:  COPY bar, line 1, column system_id

--------------end-----------------

It's interesting to note that I get this error only when there are 2 bad
fields.
If I fix only the numeric field, I get "incorrect binary data format" on the
int field.
If I fix only the smallint field, I get "invalid length in external
"numeric" value on the real field.

So, my fault, and the fix is obvious.  But it does seem like a less than
ideal error message.

Also, maybe a sentence like this would be helpful on the COPY page: "Be
careful that the data types match from 'copy to' to 'copy from'.  There is
no implicit conversion done in binary mode..." or some such?

Cheers

--
View this message in context:
http://www.nabble.com/Getting-%22insufficient-data-left-in-message%22-on-copy-with-binary-tp25282935p25287583.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Gordon Shannon <gordo169@gmail.com> writes:
> Got it.  The problem was a combination of 2 mis-matched data types.
> ...
> So, my fault, and the fix is obvious.  But it does seem like a less than
> ideal error message.

The binary format is sufficiently non-redundant that it's hard for the
code to know just what is wrong.  In this case the first data column
was slurping up bits that belonged to the second column, leaving not
enough data for the second column when it came time to read that.

> Also, maybe a sentence like this would be helpful on the COPY page: "Be
> careful that the data types match from 'copy to' to 'copy from'.  There is
> no implicit conversion done in binary mode..." or some such?

I added this text:

***************
*** 300,305 ****
--- 300,309 ----
      somewhat faster than the normal text mode, but a binary-format
      file is less portable across machine architectures and
      <productname>PostgreSQL</productname> versions.
+     Also, the binary format is very data type specific; for example
+     it will not work to output binary data from a <type>smallint</> column
+     and read it into an <type>integer</> column, even though that would work
+     fine in text format.
     </para>

     <para>

            regards, tom lane