- developers-list - monetdb.org

[Monetdb-developers] Announcement: New bug fix release of MonetDB suite
by Sjoerd Mullender 26 Mar '09

26 Mar '09

The MonetDB team at CWI is pleased to announce the Feb2009-SP1 bug fix release of the MonetDB suite of programs. This is a bug fix release of last month's Feb2009 release. More information on this release can be found at <http://monetdb.cwi.nl/projects/monetdb/Development/Releases/Feb2009/#Feb200…>. The sources and binaries are available at <http://monetdb.cwi.nl/projects/monetdb/Download/>. -- Sjoerd Mullender

1 0

[Monetdb-developers] do_not_use_posix_fadvise
by Stefan de Konink 23 Mar '09

23 Mar '09

Hi, What is the rationale to enable or disable fadvise explicitly? do_not_use_posix_fadvise = uname(&ubuf) == 0 && strcmp(ubuf.sysname, "Linux") == 0 && strncmp(ubuf.release, "2.4", 3) <= 0; Answers as "it doesn't work" will not satisfy my hunger to this answer; because I do see a completely different performance upon manual changing this variable. Stefan

1 0

Re: [Monetdb-developers] [Monetdb-pf-checkins] pathfinder/compiler/algebra algopt.c, Feb2009, 1.46.2.4, 1.46.2.5
by Stefan Manegold 20 Mar '09

20 Mar '09

On Fri, Mar 20, 2009 at 09:58:04AM +0000, Bastian Brodbeck wrote: > Update of /cvsroot/monetdb/pathfinder/compiler/algebra > In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv13909/compiler/algebra > > Modified Files: > Tag: Feb2009 ^^^^^^^ > algopt.c > Log Message: > adding new optimization -Y ^^^^^^^^^^^^^^^^^^^^^^^^^^ is this a bug fix or a new feature? If the latter (as I suspect), please undo this checking on the Feb2009 branch and redo it on the development trunk (HEAD), using only CVS commands, as, e.g., discribed on page 15 of http://homepages.cwi.nl/~manegold/Presentations/MonetDBdevTTT.pdf (pages 8 - 16 might be interesting) Thanks! Stefan > this optimization pushes projection up in the tree to minimize them. > the goal is to simplify the tree for other optimizations > > > U algopt.c > Index: algopt.c > =================================================================== > RCS file: /cvsroot/monetdb/pathfinder/compiler/algebra/algopt.c,v > retrieving revision 1.46.2.4 > retrieving revision 1.46.2.5 > diff -u -d -r1.46.2.4 -r1.46.2.5 > --- algopt.c 6 Feb 2009 14:48:45 -0000 1.46.2.4 > +++ algopt.c 20 Mar 2009 09:58:02 -0000 1.46.2.5 > @@ -179,6 +179,19 @@ > PFlog (" MonetDB specific optimizations: %s", > PFtimer_str (tm)); > break; > + > + case 'Y': > + /*REMOVE_PROXIES("MonetDB specific optimization")*/ > + > + tm = PFtimer_start (); > + > + root = PFalgopt_projection (root); > + > + tm = PFtimer_stop (tm); > + if (timing) > + PFlog (" project optimizations: %s", > + PFtimer_str (tm)); > + break; > > case 'E': > MAP_UNQ_NAMES("common subexpression elimination") > > > ------------------------------------------------------------------------------ > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through debugging. > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > Monetdb-pf-checkins mailing list > Monetdb-pf-checkins(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-pf-checkins > > -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

2 2

Re: [Monetdb-developers] [Monetdb-pf-checkins] pathfinder/compiler/algebra/opt Makefile.ag, Feb2009, 1.19.2.2, 1.19.2.3
by Sjoerd Mullender 20 Mar '09

20 Mar '09

This sounds like a new feature which is not allowed in the Stable branch. Bastian Brodbeck wrote: > Update of /cvsroot/monetdb/pathfinder/compiler/algebra/opt > In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv13909/compiler/algebra/opt > > Modified Files: > Tag: Feb2009 > Makefile.ag > Log Message: > adding new optimization -Y > this optimization pushes projection up in the tree to minimize them. > the goal is to simplify the tree for other optimizations > > > U Makefile.ag > Index: Makefile.ag > =================================================================== > RCS file: /cvsroot/monetdb/pathfinder/compiler/algebra/opt/Makefile.ag,v > retrieving revision 1.19.2.2 > retrieving revision 1.19.2.3 > diff -u -d -r1.19.2.2 -r1.19.2.3 > --- Makefile.ag 6 Feb 2009 14:47:47 -0000 1.19.2.2 > +++ Makefile.ag 20 Mar 2009 09:58:02 -0000 1.19.2.3 > @@ -47,6 +47,7 @@ > opt_key.c \ > opt_monetxq.c \ > opt_mvd.c \ > + opt_projection.c \ > opt_req_node.c \ > opt_reqval.c \ > opt_rank.c \ > > > ------------------------------------------------------------------------------ > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through debugging. > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > Monetdb-pf-checkins mailing list > Monetdb-pf-checkins(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-pf-checkins -- Sjoerd Mullender

1 0

Re: [Monetdb-developers] [Monetdb-pf-checkins] pathfinder/compiler/mil milprint.c, , 1.93, 1.94
by Stefan Manegold 13 Mar '09

13 Mar '09

Jan F (and Jan R), or should my fix (below) rather have looked like this: Index: pathfinder/compiler/mil/milprint.c =================================================================== RCS file: /cvsroot/monetdb/pathfinder/compiler/mil/milprint.c,v retrieving revision 1.93 diff -u -r1.93 milprint.c --- pathfinder/compiler/mil/milprint.c 10 Mar 2009 12:20:44 -0000 1.93 +++ pathfinder/compiler/mil/milprint.c 13 Mar 2009 08:39:16 -0000 @@ -429,7 +429,7 @@ , [m_egsum] = "{sum}" , [m_prod] = "prod" , [m_gprod] = "{prod}" - , [m_gprod] = "{prod}" + , [m_egprod] = "{prod}" , [m_bat] = "bat" , [m_catch] = "CATCH" , [m_error] = "ERROR" ? Thanks! Stefan On Fri, Mar 13, 2009 at 08:37:07AM +0000, Stefan Manegold wrote: > Update of /cvsroot/monetdb/pathfinder/compiler/mil > In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv12904/pathfinder/compiler/mil > > Modified Files: > milprint.c > Log Message: > > fixing compilation error > > .../pathfinder/compiler/mil/milprint.c:433: error: initialized field overwritten > .../pathfinder/compiler/mil/milprint.c:433: error: (near initialization for ‘ID[111]’) > > there is no need to initialize the same field twice with the same value > > > U milprint.c > Index: milprint.c > =================================================================== > RCS file: /cvsroot/monetdb/pathfinder/compiler/mil/milprint.c,v > retrieving revision 1.93 > retrieving revision 1.94 > diff -u -d -r1.93 -r1.94 > --- milprint.c 10 Mar 2009 12:20:44 -0000 1.93 > +++ milprint.c 13 Mar 2009 08:37:05 -0000 1.94 > @@ -429,7 +429,6 @@ > , [m_egsum] = "{sum}" > , [m_prod] = "prod" > , [m_gprod] = "{prod}" > - , [m_gprod] = "{prod}" > , [m_bat] = "bat" > , [m_catch] = "CATCH" > , [m_error] = "ERROR" > > > ------------------------------------------------------------------------------ > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through debugging. > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > Monetdb-pf-checkins mailing list > Monetdb-pf-checkins(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-pf-checkins -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

1 1

[Monetdb-developers] SourceForge unplanned downtime
by Sjoerd Mullender 11 Mar '09

11 Mar '09

We can't use CVS at the moment. According to the SourceForge site status page: "2009-03-10: Service CVS unplanned downtime March 10th, 2009 CVS repositories that begin with the letters (a e h i m o r w z) are experiencing unplanned downtime since 2009-03-10 18:00 UTC. SourceForge.net staff are actively working to resolve the issue and anticipate having the service returned sometime in the next 2 days." -- Sjoerd Mullender

1 0

Re: [Monetdb-developers] [MonetDB-users] too deep recursion
by Jan Rittinger 10 Mar '09

10 Mar '09

On Mar 9, 2009, at 23:35, Martin Kersten wrote: > > Please run it also against the HEAD, because most of the problems > may have been resolved there. > > Jan Rittinger wrote: >> Hi Martin and others, >> I just tested what part the Pathfinder code generation plays and >> generated MIL code for the Aug2008 (0.24), the Nov2008, and the >> Feb2009 release branches. I ran all queries using the newest stable >> version (Feb2009) on Mac OS X. >> The observations are: >> * The problem with gdk_heap.mx, mmap, and Mac OS X still resides >> (all queries run in 10 seconds instead of 2 seconds)---Peter knows >> what I'm talking about. >> * Like Nils reported the queries are getting slower. >> * The main performance decrease in my scenario is the document >> loading. >> * The problem does not stem from Pathfinder's MIL code generation. >> For more details see the attached file... >> ------------------------------------------------------------------------ >> BTW: For todays' head version the results are even worse... ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Attached you find a bundle with the queries and the test results. (I'm using a Macbook Pro 2.6GHz Intel Core 2 Duo with 4GB of RAM running Mac OS X 10.5.6.) >> >> Jan >> On Mar 9, 2009, at 18:08, Martin Kersten wrote: >>> For all interested. Indeed there are performance differences >>> between the various releases. Some can be traced back to >>> functional enhancements, others are a result from internal >>> administrative activities. >>> >>> Recent experiments with the TPC-H scale-factor 2 on Feb 2009 >>> branch show a performance degradation compared to Aug 2008, >>> as reported on the website. >>> >>> It appears that some low-level actions related to allocation >>> of BATs and their management in memory-scarce situations are >>> debet to this situation. >>> >>> Solutions are integrated with the HEAD, and may (depending >>> on our resources) be back propagated into a bugfix release >>> of the Feb 2009 version. >>> >>> Nils Grimsmo wrote: >>>> On Wed, Mar 04, 2009 at 11:08:40PM +0100, Jan Rittinger wrote: >>>>> Hi Nils, >>>>> >>>>> I just ran your queries with the latest (not yet announced) >>>>> Feb2009 >>>>> release (http://monetdb.cwi.nl/downloads/sources/Feb2009/) and >>>>> received an answer in 1.5 (Q1) and 2.5 (Q2) seconds. If you >>>>> still have >>>>> problems with the new version, then please let us know. >>>> >>>> Thank you for your answer, Jan. Feb2009 is indeed faster than >>>> Nov2008, >>>> but on my computer it is still slower than Aug2008. I also see >>>> some >>>> strange and unfavorable performance characteristics on subsequent >>>> queries >>>> for Nov2008 and Feb2009 (see below). >>>> >>>> >>>> Aug2008: >>>> # MonetDB Server v4.24.0 >>>> # based on GDK v1.24.0 >>>> # PF/Tijah module v0.5.0 loaded. http://dbappl.cs.utwente.nl/ >>>> pftijah >>>> # MonetDB/XQuery module v0.24.0 loaded (default back-end is >>>> 'algebra') >>>> >>>> Nov2008-SP2: >>>> # MonetDB Server v4.26.4 >>>> # based on GDK v1.26.4 >>>> # PF/Tijah module v0.9.0 loaded. http://dbappl.cs.utwente.nl/ >>>> pftijah >>>> # MonetDB/XQuery module v0.26.4 loaded (default back-end is >>>> 'algebra') >>>> >>>> Feb2009: >>>> # MonetDB Server v4.28.0 >>>> # Based on GDK v1.28.0 >>>> # PF/Tijah module v0.9.0 loaded. http://dbappl.cs.utwente.nl/ >>>> pftijah >>>> # MonetDB/XQuery module v0.28.0 loaded (default back-end is >>>> 'algebra') >>>> >>>> >>>> I run the queries multiple times in different scenarios. >>>> >>>> A - Have just indexed the document, first run. >>>> B - Second run (subsequent have similar timing). >>>> C - Restart the server (Mserver), then first run. >>>> D - Second run (subsequent have similar timing). >>>> >>>> >>>> Query Q0: >>>> Aug2008 Nov2008 Feb2009 >>>> A 1101 3687 1760 >>>> B 1031 4510 3015 >>>> C 1350 5216 3390 >>>> D 1035 12620 9533 >>>> >>>> >>>> Query Q1: >>>> Aug2008 Nov2008 Feb2009 >>>> A 2161 15119 3013 >>>> B 2099 19292 4072 >>>> C 2526 18523 4567 >>>> D 2117 42555 10602 >>>> >>>> >>>> This seems very strange to me. The timings make sense for >>>> Aug2008, where >>>> the query is slightly slower right after restarting the server >>>> (C). For >>>> Nov2008 and Feb2009, the second (and subsequent) runs are slower >>>> than the >>>> first. How can this be? It can make sense for the first run after >>>> restarting the server (C) to be slower (reading stuff from disk >>>> etc.), but >>>> why is the second (D) terribly slower? If I just keep running >>>> the query, >>>> the timings are similar to D. >>>> >>>> Note: If I start mixing Q0 and Q1 after step D, they are both as >>>> slow as >>>> in step D. >>>> >>>> >>>> I hope this feedback is helpful. Is there something strange with >>>> my >>>> setup, or is this a "bug"? (My timings in step (A) seem similar >>>> to Jan's >>>> timings). >>>> >>>> >>>> If I want to compare MonetDB/XQuery to other implementations in a >>>> scientific paper, I typically want to warm up the system, then >>>> run the >>>> query multiple times to get an average timing. It is kind of >>>> inconvenient >>>> not to be able to close down Mserver between experiments... >>>> >>>> >>>>> P.S.: The E-Mail subject seems slightly off topic here :) >>>> >>>> Yes, thought I'd avoid touching the mouse to copy the email >>>> address. Cut >>>> away In-Reply-To:, but forgot to change Subject:... >>>> >>>> >>>> Thank you for your assistance! >>>> >>>> >>>> Klem fra Nils >>>> >>>>> On Mar 4, 2009, at 16:30, Nils Grimsmo wrote: >>>>> >>>>>> Hi, I just upgraded from the Augst to the Noveber super-ball, >>>>>> and the >>>>>> performance has worsened badly. >>>>>> >>>>>> Example queries on dblp.xml (441 MB): >>>>>> >>>>>> Q0: count(/dblp//author[text()="Michael Stonebraker"]) >>>>>> Q1: count(/dblp/*/author[text()="Michael Stonebraker"]) >>>>>> >>>>>> Query time in milliseconds: >>>>>> >>>>>> August November >>>>>> Q0 1100 4867 >>>>>> Q1 3993 17999 >>>>>> >>>>>> I have compiled with --enable-optimise both times. I query with: >>>>>> >>>>>> mclient --language=xquery --algebra --time < $QUERYFILE >>>>>> >>>>>> Is this performance degradation expected? If so, why? >>>>>> >>>>>> >>>>>> BTW: Is there any way of finding how much disk space a >>>>>> collection >>>>>> uses? >>>>>> >>>>>> >>>>>> Thank you for contributing free software! >>>>>> >>>>>> >>>>>> Klem fra Nils -- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen http://www-db.informatik.uni-tuebingen.de/team/rittinger

1 0

Re: [Monetdb-developers] [MonetDB-users] too deep recursion
by Jan Rittinger 09 Mar '09

09 Mar '09

Hi Martin and others, I just tested what part the Pathfinder code generation plays and generated MIL code for the Aug2008 (0.24), the Nov2008, and the Feb2009 release branches. I ran all queries using the newest stable version (Feb2009) on Mac OS X. The observations are: * The problem with gdk_heap.mx, mmap, and Mac OS X still resides (all queries run in 10 seconds instead of 2 seconds)---Peter knows what I'm talking about. * Like Nils reported the queries are getting slower. * The main performance decrease in my scenario is the document loading. * The problem does not stem from Pathfinder's MIL code generation. For more details see the attached file... BTW: For todays' head version the results are even worse... Jan On Mar 9, 2009, at 18:08, Martin Kersten wrote: > For all interested. Indeed there are performance differences > between the various releases. Some can be traced back to > functional enhancements, others are a result from internal > administrative activities. > > Recent experiments with the TPC-H scale-factor 2 on Feb 2009 > branch show a performance degradation compared to Aug 2008, > as reported on the website. > > It appears that some low-level actions related to allocation > of BATs and their management in memory-scarce situations are > debet to this situation. > > Solutions are integrated with the HEAD, and may (depending > on our resources) be back propagated into a bugfix release > of the Feb 2009 version. > > Nils Grimsmo wrote: >> On Wed, Mar 04, 2009 at 11:08:40PM +0100, Jan Rittinger wrote: >>> Hi Nils, >>> >>> I just ran your queries with the latest (not yet announced) Feb2009 >>> release (http://monetdb.cwi.nl/downloads/sources/Feb2009/) and >>> received an answer in 1.5 (Q1) and 2.5 (Q2) seconds. If you still >>> have >>> problems with the new version, then please let us know. >> >> Thank you for your answer, Jan. Feb2009 is indeed faster than >> Nov2008, >> but on my computer it is still slower than Aug2008. I also see some >> strange and unfavorable performance characteristics on subsequent >> queries >> for Nov2008 and Feb2009 (see below). >> >> >> Aug2008: >> # MonetDB Server v4.24.0 >> # based on GDK v1.24.0 >> # PF/Tijah module v0.5.0 loaded. http://dbappl.cs.utwente.nl/pftijah >> # MonetDB/XQuery module v0.24.0 loaded (default back-end is >> 'algebra') >> >> Nov2008-SP2: >> # MonetDB Server v4.26.4 >> # based on GDK v1.26.4 >> # PF/Tijah module v0.9.0 loaded. http://dbappl.cs.utwente.nl/pftijah >> # MonetDB/XQuery module v0.26.4 loaded (default back-end is >> 'algebra') >> >> Feb2009: >> # MonetDB Server v4.28.0 >> # Based on GDK v1.28.0 >> # PF/Tijah module v0.9.0 loaded. http://dbappl.cs.utwente.nl/pftijah >> # MonetDB/XQuery module v0.28.0 loaded (default back-end is >> 'algebra') >> >> >> I run the queries multiple times in different scenarios. >> >> A - Have just indexed the document, first run. >> B - Second run (subsequent have similar timing). >> C - Restart the server (Mserver), then first run. >> D - Second run (subsequent have similar timing). >> >> >> Query Q0: >> Aug2008 Nov2008 Feb2009 >> A 1101 3687 1760 >> B 1031 4510 3015 >> C 1350 5216 3390 >> D 1035 12620 9533 >> >> >> Query Q1: >> Aug2008 Nov2008 Feb2009 >> A 2161 15119 3013 >> B 2099 19292 4072 >> C 2526 18523 4567 >> D 2117 42555 10602 >> >> >> This seems very strange to me. The timings make sense for Aug2008, >> where >> the query is slightly slower right after restarting the server >> (C). For >> Nov2008 and Feb2009, the second (and subsequent) runs are slower >> than the >> first. How can this be? It can make sense for the first run after >> restarting the server (C) to be slower (reading stuff from disk >> etc.), but >> why is the second (D) terribly slower? If I just keep running the >> query, >> the timings are similar to D. >> >> Note: If I start mixing Q0 and Q1 after step D, they are both as >> slow as >> in step D. >> >> >> I hope this feedback is helpful. Is there something strange with my >> setup, or is this a "bug"? (My timings in step (A) seem similar to >> Jan's >> timings). >> >> >> If I want to compare MonetDB/XQuery to other implementations in a >> scientific paper, I typically want to warm up the system, then run >> the >> query multiple times to get an average timing. It is kind of >> inconvenient >> not to be able to close down Mserver between experiments... >> >> >>> P.S.: The E-Mail subject seems slightly off topic here :) >> >> Yes, thought I'd avoid touching the mouse to copy the email >> address. Cut >> away In-Reply-To:, but forgot to change Subject:... >> >> >> Thank you for your assistance! >> >> >> Klem fra Nils >> >>> On Mar 4, 2009, at 16:30, Nils Grimsmo wrote: >>> >>>> Hi, I just upgraded from the Augst to the Noveber super-ball, and >>>> the >>>> performance has worsened badly. >>>> >>>> Example queries on dblp.xml (441 MB): >>>> >>>> Q0: count(/dblp//author[text()="Michael Stonebraker"]) >>>> Q1: count(/dblp/*/author[text()="Michael Stonebraker"]) >>>> >>>> Query time in milliseconds: >>>> >>>> August November >>>> Q0 1100 4867 >>>> Q1 3993 17999 >>>> >>>> I have compiled with --enable-optimise both times. I query with: >>>> >>>> mclient --language=xquery --algebra --time < $QUERYFILE >>>> >>>> Is this performance degradation expected? If so, why? >>>> >>>> >>>> BTW: Is there any way of finding how much disk space a collection >>>> uses? >>>> >>>> >>>> Thank you for contributing free software! >>>> >>>> >>>> Klem fra Nils -- Jan Rittinger Lehrstuhl Datenbanken und Informationssysteme Wilhelm-Schickard-Institut für Informatik Eberhard-Karls-Universität Tübingen http://www-db.informatik.uni-tuebingen.de/team/rittinger

1 0

[Monetdb-developers] Possible bug in MonetDB and JDBC driver in sequence management
by Cimballi 09 Mar '09

09 Mar '09

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

2 1

Re: [Monetdb-developers] [Monetdb-sql-checkins] sql/src/backends/monet5 sql.mx, , 1.321, 1.322 sql_scenario.mx, , 1.337, 1.338
by Fabian Groffen 07 Mar '09

07 Mar '09

On 07-03-2009 21:41:21 +0000, Stefan de Konink wrote: > This code adds the last_id variable to MonetDB5/SQL to implement 'in SQL' > access to the last updated sequence, in a similar fashion as the @@IDENTITY > found in MSSQL. For future compatibility a procedure like get_last_row_id() > found in other databases, this function would have to return the variable > found. The variable is updated when m->last_id is updated. > > The code was added to have last_id support within the ODBC interface; > required for phpBB compatibility. Weird, ODBC already knows it, and more reliable, via the response of Q_UPDATE. I think it was already implemented in ODBC even? It's available in mapilib, using mapi_get_last_id.

2 4