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