Is this a known issue that you are working on? If so please give me the bug id where I would know when its fixed.
The case is as follows:
I have a table with 32 columns.
It has a unique key on 3 of them.
I know that it's recommended that you do bulk inserts from csv for performance. But in my case I am running live ETLs for putting data into monetdb. These ETLs are generating sql insert scripts.(inserts/updates/ddls)
I use JDBC and c3p0 connection pooling for executing these sqls.
I have a failover mechanism where the scripts are executed on multiple DBs and I try
to keep their states consistent.
One common consistency check would be a count*.
I am running the latest stable installation from repo on Ubuntu 10.04 64 bit.
Observation:
After running 9100 queries the count* on one DB was 9100 but in the other was 9200.
I have diagnostics in place to do a select count* after running a batch of 100 queries in a single JDBC transaction.
I observed that the count* was consistent until 9000 queries were executed. It failed at 9100.
I did a manual check to see if duplicate records exist with same unique key values and they did.
Following is the query I used for the same:
select ad_group_id, count(*) from ad_groups group by ad_group_id having count(ad_group_id)>1 and client_id=X and account_id=Y;
My constraint is as follows: CONSTRAINT "ad_groups_client_id_account_id_ad_group_id_unique" UNIQUE ("client_id", "account_id", "ad_group_id")
I found exactly 100
tuples with group_by_count* = 2 and the ad_group_id(unique key) values for these exactly matched queries 8901 to 9000.
It means when I inserted 9001 to 9100 the previous batch went into the store again with it and somehow surpassed the unique key constraint check.
Please note that I am converting a batch of 100 insert queries into a single insert query and running this single bulk insert in a single JDBC transaction
to reduce network traffic.
This same issue recurred at 21500 only this time it happened for 300 records with same unique key value being duplicated.
And their unique key values were exactly matching the ones in the last 3 batches
Ad_group_id s 21103-21202, 21203-21302, 21303-21402 existed twice. 21403-21502 existed exactly once.
REQUEST:
Can anyone tell me if I can enable query logs -
something like what mysql has.
I will try re-installing monetdb and see if this problem goes away. :)
Merry Christmas and Warm Regards,
Tapomay