Hi,

I'm a recent user of MonetDB. I'm using it to store data from XML files from "http://oasissta.caiso.com/". I parse the XML files and I insert the data into MonetDB using the JDBC driver (1.9). I make a batch insert of each xml data element. The ID column of my table is a sequence I created like this :

CREATE TABLE "oasis_report_data" ( "oasis_report_data_id" INT NOT NULL, "oasis_report_header_id" INT NOT NULL, "data_item" VARCHAR(255) NOT NULL, "resource_name" VARCHAR(255) NOT NULL, "opr_date" DATE NOT NULL, "interval_num" INT NOT NULL, "value" DOUBLE NOT NULL, CONSTRAINT "pk_oasis_report_data" PRIMARY KEY ("oasis_report_data_id"), CONSTRAINT "fk_oasis_report_data_oasis_report_header" FOREIGN KEY ("oasis_report_header_id") REFERENCES "oasis_report_header" ("oasis_report_header_id") );

CREATE SEQUENCE seq_oasis_report_data_id AS INT;

And I'm using iBatis to insert the data like that :

<insert id="insertOasisReportData" parameterClass="oasisReportData"> <selectKey keyProperty="id" resultClass="int" type="pre"> SELECT NEXT VALUE FOR seq_oasis_report_data_id </selectKey> INSERT INTO oasis_report_data (oasis_report_data_id, oasis_report_header_id, data_item, interval_num, opr_date, resource_name, value) VALUES (#id#, #header.id#, #dataItem#, #intervalNum#, #oprDate#, #resourceName#, #value#) </insert>

Here is the bug I found :

When the file is small (up to 2 Mo, around 15 000 items), no problem. If the file is bigger (20 Mo, around 100 000 items), then at a moment the select next value return null. Here is the java.sql log :

2009-03-02 21:14:30,324 | DEBUG | java.sql.Connection | debug | {conn-170420} Connection 2009-03-02 21:14:30,324 | DEBUG | java.sql.Connection | debug | {conn-170420} Preparing Statement: SELECT NEXT VALUE FOR seq_oasis_report_data_id 2009-03-02 21:14:30,334 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170421} Executing Statement: SELECT NEXT VALUE FOR seq_oasis_report_data_id 2009-03-02 21:14:30,334 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170421} Parameters: [] 2009-03-02 21:14:30,334 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170421} Types: [] 2009-03-02 21:14:30,344 | DEBUG | java.sql.ResultSet | debug | {rset-170422} ResultSet 2009-03-02 21:14:30,344 | DEBUG | java.sql.ResultSet | debug | {rset-170422} Header: [next_value_for_sys] 2009-03-02 21:14:30,344 | DEBUG | java.sql.ResultSet | debug | {rset-170422} Result: [16901] 2009-03-02 21:14:30,344 | DEBUG | java.sql.Connection | debug | {conn-170420} Preparing Statement: INSERT INTO oasis_report_data (oasis_report_data_id, oasis_report_header_id, data_item, interval_num, opr_date, resource_name, value) VALUES (?, ?, ?, ?, ?, ?, ?) 2009-03-02 21:14:30,354 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170423} Executing Statement: INSERT INTO oasis_report_data (oasis_report_data_id, oasis_report_header_id, data_item, interval_num, opr_date, resource_name, value) VALUES (?, ?, ?, ?, ?, ?, ?) 2009-03-02 21:14:30,354 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170423} Parameters: [16901, 705, LMP_PRC, 5, 2009-01-26 00:00:00.0, AVOCADO_6_N002, 31.80941] 2009-03-02 21:14:30,354 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170423} Types: [java.lang.Integer, java.lang.Integer, java.lang.String, java.lang.Integer, java.sql.Timestamp, java.lang.String, java.lang.Double] 2009-03-02 21:14:30,364 | DEBUG | java.sql.Connection | debug | {conn-170424} Connection 2009-03-02 21:14:30,364 | DEBUG | java.sql.Connection | debug | {conn-170424} Preparing Statement: SELECT NEXT VALUE FOR seq_oasis_report_data_id 2009-03-02 21:14:30,364 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170425} Executing Statement: SELECT NEXT VALUE FOR seq_oasis_report_data_id 2009-03-02 21:14:30,364 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170425} Parameters: [] 2009-03-02 21:14:30,364 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170425} Types: [] 2009-03-02 21:14:34,500 | DEBUG | java.sql.Connection | debug | {conn-170424} Preparing Statement: INSERT INTO oasis_report_data (oasis_report_data_id, oasis_report_header_id, data_item, interval_num, opr_date, resource_name, value) VALUES (?, ?, ?, ?, ?, ?, ?) 2009-03-02 21:14:34,510 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170426} Executing Statement: INSERT INTO oasis_report_data (oasis_report_data_id, oasis_report_header_id, data_item, interval_num, opr_date, resource_name, value) VALUES (?, ?, ?, ?, ?, ?, ?) 2009-03-02 21:14:34,510 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170426} Parameters: [null, 705, LMP_PRC, 6, 2009-01-26 00:00:00.0, AVOCADO_6_N002, 35.59336] 2009-03-02 21:14:34,510 | DEBUG | java.sql.PreparedStatement | debug | {pstm-170426} Types: [null, java.lang.Integer, java.lang.String, java.lang.Integer, java.sql.Timestamp, java.lang.String, java.lang.Double]

You can see that in the second case there are two lines missing :

2009-03-02 21:14:30,344 | DEBUG | java.sql.ResultSet | debug | {rset-170422} Header: [next_value_for_sys] 2009-03-02 21:14:30,344 | DEBUG | java.sql.ResultSet | debug | {rset-170422} Result: [16901]

And what strange is that each time it appears at a different moment (I mean on a different set of data, using the same file). Maybe on nig stress the sequence generator of MonetDB bugs or maybe the JDBC driver.

HTH,

Cimballi