Debugging failed record inserts into kdb+

Don't expect a lot of help if there is an type mismatch error with an insert into kdb+ database. All Q returns is the infamous `type error; which doesn't give you any indication on its cause. This error is due to a type mismatch between the data and the columns it is trying to write to. For instance it is expecting a String and it receives a boolean.

Here's what you usually get. Let's say I have a orders table like this and try to insert the first record into it.

q)orders:(time:`time$(); sym:`$(); client:`$(); side:`$();price:`real$(); qty:`int$());
q)`orders insert (`CLIENT1; "USDEUR"; `A; 1.56; 56.6);
`type

This sucks as it doesn't tell you where the type mismatches are and forced to manually check every column type against the element's type; which could take a while if there are many columns to check. If a production issue occurred relating to this issue, I decided to write a Q function which provides a list of columns that have a type mismatch and if it its good, it returns `good.

inspect:{ [host;port;table;data]
t:"bxhijefcsmdzuvt"!1 4 5 6 7 8 9 10 11 13 14 15 17 18 19h;
h:hopen `$":",host,":",string port;
m:h"meta ",table;
vt:abs type each data;
ct:abs t[exec t from value m];
r:(exec c from key m) where vt {x<>y}' ct;
$[0 < count r; r;`good]}

This can now be used to find why the insert is failing.

q) insert["host"; 8888; "orders"; (`CLIENT1; "USDEUR"; `A; 1.56; 56.6)]
`sym`qty

q) insert["host"; 8888; "orders"; (`CLIENT1; `USDEUR; `A; 1.56; 56)]
`good

There you go; it has computed which columns have the type mismatch and under further scrutiny, one can indeed see a blatant type mismatch between symbol->string and `int->`real for sym and qty respectively.

Constituent clarification

I noticed that there was some confusion between two colleagues' queries between the use of time.ss and time.second so I thought I would blog about it briefly.

q)t:09:00:02.000
q)t.ss
2
q)t.second
09:00:02

select count i by time.ss from ... will count the number of records that have identical number of seconds where select count i by time.second from ... will group records by its timestamp ignoring milliseconds.