- users-list - monetdb.org

Pathological behaviour when building large hash index
by Will Muldrew 06 Dec '13

06 Dec '13

Hi I've been investigating very slow queries on my 350M row dataset when selecting for equality on a non-unique string id. e.g. select count(*) from t_order where orderId = 'FOO'; This query takes ~4.5 hours to build a thash index. Subsequent repeats hit the cache and are sub milli. select count(*) from t_order where time > '1970-01-01' and orderId = 'FOO' This second one is consistently finished in a few seconds or less, and doesn't create any indexes. The first query is a bit of a show-stopper. It spends a huge about of time in BAThash (gdk_search.c), and incurs a lot of page faults while reading and writing at random across a multi GB mmaped thash file. Additionally these page faults are hampered by the OS trying to write dirty pages out in the background. I've got plenty of RAM overall (130GB), but the 'active' page proportion for the thash file seems to be stuck at about 25% - giving me a 0.75 probability of a miss. * Is there some way to make my OS less keen to evict pages? (swappiness = 0 already) I should have plenty of room to have the whole thash file resident. Additionally, allowing it to sit on dirty pages longer would reduce the total write IO. * Is there some way to simply prevent large thash creation? For many applications I'd rather have slower consistent queries, then incur a massively slow query following a restart (not great in a prod environment!) * Is there some way to generate the hash index with a more sympathetic algo that doesn't degrade so steeply. e.g some hash + sort. (I have literally no experience with this!) -Will Muldrew

2 12

Re: Pathological behaviour when building large hash index
by Roberto Cornacchia 06 Dec '13

06 Dec '13

Sorry for the quick intrusion, I just urged to say this is one of the most interesting threads of the year ;-) On Fri, Dec 6, 2013 at 10:06 AM, Roberto Cornacchia <roberto(a)spinque.com>wrote: > Sorry for the quick intrusion, I just urged to say this is one of the most > interesting threads of the year ;-) > > > > > > On Thu, Dec 5, 2013 at 8:57 PM, Will Muldrew <will.muldrew(a)gmail.com>wrote: > >> No index! >> On 5 Dec 2013 17:05, "Stefan Manegold" <Stefan.Manegold(a)cwi.nl> wrote: >> >>> hm, that's with the index in place? >>> >>> If so: >>> good for our optimizer, >>> but unfortunately not so good for you ... >>> >>> If not: >>> - good for you! >>> - impressive for our "execution engine" (scan) >>> (- "embarrassing" for our optimizer ... well, it's a minor detail ...) >>> >>> ;-) >>> >>> Stefan >>> >>> ----- Original Message ----- >>> > Sorry! >>> > >>> > sql>select count(*) from t_order2 where orderId between 'AAAAAA' and >>> > 'AAAAAA'; >>> > +------+ >>> > | L1 | >>> > +======+ >>> > | 0 | >>> > +------+ >>> > 1 tuple (596.202ms) >>> > >>> > >>> > >>> > On 5 December 2013 16:49, Stefan Manegold < Stefan.Manegold(a)cwi.nl > >>> wrote: >>> > >>> > >>> > Hi Will, >>> > >>> > thank for reminding me: the optimizer still sees your query as point >>> query >>> > and thus prevents parallelization in order to build/use the hash index. >>> > >>> > Maybe you can "fool" the optimizer (and I hope noone else is reading >>> what I'm >>> > writing now ... ;-)) by "disguising" the point predicate as range >>> predicate, >>> > say, "where orderId between 'FOO' and 'FOO'" --- in fact, I rather >>> hope (for >>> > us) >>> > that is will not work ... >>> > >>> > Stefan >>> > >>> > ----- Original Message ----- >>> > > Hi Stefan >>> > > >>> > > I've added that hash = FALSE patch, and now my simple query returns >>> > > reliably >>> > > in 6 seconds. It's still a lot slower than the indexed one >>> (obviously), and >>> > > 12x slower than the one with the time > '1970-01-01' optimiser nudge >>> (which >>> > > causes parallelism), but it'll do for now. >>> > > >>> > > Thanks >>> > > >>> > > -Will >>> > > >>> > > >>> > > On 5 December 2013 16:32, Will Muldrew < will.muldrew(a)gmail.com > >>> wrote: >>> > > >>> > > >>> > > >>> > > Hi Stefan >>> > > >>> > > Here are my version details: >>> > > >>> > > MonetDB-11.15.17 >>> > > CentOS release 6.3 >>> > > kernel 3.6.2 >>> > > >>> > > I think I might proceed with a patched version with the hash >>> disabled. >>> > > That's >>> > > totally fine for me right now, though obviously it'd be nice to be >>> on the >>> > > trunk! >>> > > >>> > > W.r.t. a less random access hash generation. This is all very >>> hand-wavey >>> > > but >>> > > how about: 1) generate a sequence of (hash,oid) pairs to some file or >>> > > mmapped area. 2). use some well-known "external sort" algo to sort >>> these >>> > > pairs by hash (e.g. split, qsort, merge). 3). build your final hash >>> by >>> > > iterating through this sorted datastructure. >>> > > >>> > > Your memory writing would then be sequential and you'd get good page >>> hit >>> > > characteristics. You just need to choose a decent sort algo - >>> ideally which >>> > > works transparently over memmapped data. >>> > > >>> > > What do you think? I might even have a crack at it myself :) >>> > > >>> > > -Will >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > On 5 December 2013 15:54, Stefan Manegold < Stefan.Manegold(a)cwi.nl> wrote: >>> > > >>> > > >>> > > Hi Will, >>> > > >>> > > could you please also share with us which version of MonetDB you are >>> using, >>> > > and OS you are running? >>> > > >>> > > Pleass find more comments & questions inlined below. >>> > > >>> > > ----- Original Message ----- >>> > > > Hi >>> > > > >>> > > > I've been investigating very slow queries on my 350M row dataset >>> when >>> > > > selecting for equality on a non-unique string id. e.g. >>> > > > >>> > > > select count(*) from t_order where orderId = 'FOO'; >>> > > >>> > > Is your orderId column unique, or are there duplicate values? >>> > > >>> > > > This query takes ~4.5 hours to build a thash index. Subsequent >>> repeats >>> > > > hit >>> > > > the cache and are sub milli. >>> > > > >>> > > > select count(*) from t_order where time > '1970-01-01' and orderId >>> = >>> > > > 'FOO' >>> > > > >>> > > > This second one is consistently finished in a few seconds or less, >>> and >>> > > > doesn't create any indexes. >>> > > > >>> > > > The first query is a bit of a show-stopper. It spends a huge about >>> of >>> > > > time >>> > > > in >>> > > > BAThash (gdk_search.c), and incurs a lot of page faults while >>> reading and >>> > > > writing at random across a multi GB mmaped thash file. >>> Additionally these >>> > > > page faults are hampered by the OS trying to write dirty pages out >>> in the >>> > > > background. I've got plenty of RAM overall (130GB), but the >>> 'active' page >>> > > > proportion for the thash file seems to be stuck at about 25% - >>> giving me >>> > > > a >>> > > > 0.75 probability of a miss. >>> > > > >>> > > > * Is there some way to make my OS less keen to evict pages? >>> (swappiness = >>> > > > 0 >>> > > > already) I should have plenty of room to have the whole thash file >>> > > > resident. >>> > > > Additionally, allowing it to sit on dirty pages longer would >>> reduce the >>> > > > total write IO. >>> > > >>> > > In fact, if there is sufficient memory in total, there would be no >>> need to >>> > > flush >>> > > dirty (non-persistent) pages at all, but we have not yet found a >>> good way >>> > > to >>> > > hit >>> > > this to the OS --- madvice() did not have much/any useful efficet >>> for us on >>> > > Linux. >>> > > >>> > > Maybe some of the issues dicsussed here play a role: >>> > > >>> http://engineering.linkedin.com/performance/optimizing-linux-memory-managem… >>> > > ? >>> > > >>> > > While we have not yet encountered the NUMA problem reported there, >>> > > we have encountered the transparent huge page problems, and usually >>> > > disable them as also suggested in the above posting. >>> > > >>> > > > * Is there some way to simply prevent large thash creation? For >>> many >>> > > > applications I'd rather have slower consistent queries, then incur >>> a >>> > > > massively slow query following a restart (not great in a prod >>> > > > environment!) >>> > > >>> > > Not easily right now. >>> > > However, you can either add the "fake" time predicate as you did >>> above, >>> > > which (in this case) triggers MonetDB's optimizer to push the time >>> > > predicate >>> > > (either because it's in timestamp (int) rather then varchar >>> (string), or >>> > > because the time column happens to be ordered) below the orderId >>> predicate >>> > > and this avoid the hash build, >>> > > or change the following line(s) in gdk/gdk_select.c (in case you >>> built >>> > > MonetDB yourself from source): >>> > > >>> > > hash = b->batPersistence == PERSISTENT && >>> > > (size_t) ATOMsize(b->ttype) > sizeof(BUN) / 4 && >>> > > BATcount(b) * (ATOMsize(b->ttype) + 2 * sizeof(BUN)) < >>> GDK_mem_maxsize / 2; >>> > > -> >>> > > hash = FALSE; >>> > > >>> > > We'll consider whether we can provide a better solution in a future >>> > > release. >>> > > >>> > > > * Is there some way to generate the hash index with a more >>> sympathetic >>> > > > algo >>> > > > that doesn't degrade so steeply. e.g some hash + sort. (I have >>> literally >>> > > > no >>> > > > experience with this!) >>> > > >>> > > We'll also look into this. However, building a hash index inherently >>> incurs >>> > > random access, so chances are slim ... >>> > > >>> > > Best, >>> > > Stefan >>> > > >>> > > > -Will Muldrew >>> > > > >>> > > > >>> > > > >>> > > > >>> > > > >>> > > > >>> > > > _______________________________________________ >>> > > > users-list mailing list >>> > > > users-list(a)monetdb.org >>> > > > https://www.monetdb.org/mailman/listinfo/users-list >>> > > > >>> > > >>> > > -- >>> > > | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | >>> > > | www.CWI.nl/~manegold/ | Science Park 123 (L321) | >>> > > | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | >>> > > >>> > > _______________________________________________ >>> > > users-list mailing list >>> > > users-list(a)monetdb.org >>> > > https://www.monetdb.org/mailman/listinfo/users-list >>> > > >>> > > >>> > > >>> > > _______________________________________________ >>> > > users-list mailing list >>> > > users-list(a)monetdb.org >>> > > https://www.monetdb.org/mailman/listinfo/users-list >>> > > >>> > >>> > -- >>> > | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | >>> > | www.CWI.nl/~manegold/ | Science Park 123 (L321) | >>> > | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | >>> > >>> > _______________________________________________ >>> > users-list mailing list >>> > users-list(a)monetdb.org >>> > https://www.monetdb.org/mailman/listinfo/users-list >>> > >>> > >>> > _______________________________________________ >>> > users-list mailing list >>> > users-list(a)monetdb.org >>> > https://www.monetdb.org/mailman/listinfo/users-list >>> > >>> >>> -- >>> | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | >>> | www.CWI.nl/~manegold/ | Science Park 123 (L321) | >>> | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | >>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> https://www.monetdb.org/mailman/listinfo/users-list >>> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> >> >

1 0

Mantente en contacto conmigo a través de LinkedIn
by Edgar Mejia silva 03 Dec '13

03 Dec '13

LinkedIn ------------ Communication: Me gustaría añadirte a mi red profesional en LinkedIn. -Edgar Edgar Mejia silva Ingeniero consultor de sistemas de gestion y BI (Business intelligent) en ITFusion Colombia Confirma que conoces a Edgar Mejia silva: https://www.linkedin.com/e/-id7iby-hor6db4w-59/isd/18454372725/QZ9x6cIx/?hs… -- Estás recibiendo invitaciones a conectar. Haz clic para darte de baja: http://www.linkedin.com/e/-id7iby-hor6db4w-59/d3mz0XtXuHhwR56ylf8k7AOIRLhmN… (c) 2012 LinkedIn Corporation. 2029 Stierlin Ct, Mountain View, CA 94043, EE.UU.

1 0

Announcement: New Feb2013-SP6 Bugfix release of MonetDB suite
by Sjoerd Mullender 03 Dec '13

03 Dec '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP6 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Feb 2013-SP6 bugfix release MonetDB5 Server * Fixed a possible buffer overflow in the COPY INTO code. * Fixed a problem that when the server is using all available threads for a query, it was not possible to attach another client and have it execute even the smallest query. This is fixed by creating extra threads for each client entering the fray at the cost of having more threads that execute queries. But at least there is guaranteed progress for all clients (modulo the operating system scheduler). See bug #3258. Merovingian * monetdbd(1) now refuses to startup if it cannot read the properties from the dbfarm, bug #3398 Client Package * mclient: Fixed a bug where the -H option only worked if the readline history file already existed. Now we properly create and use the history file. * ODBC: Fixed interpretation SQL_C_SLONG/SQL_C_ULONG/SQL_C_LONG to refer to a 32 bit integer always (i.e. "int" on 64 bit architectures despite the name and the Microsoft documentation). This seems to be the consensus. * ODBC: Fixed transaction level: MonetDB only supports the highest level (SQL_TXN_SERIALIZABLE), so setting the transaction level can be accepted and ignored. MonetDB Common * Stopped using the deprecated sbrk() system call. * Fixed a problem when reverse sorting a sorted column. * Fixed bugs that deal with problems that could possibly occur when transactions are aborted and the server is restarted. See bug #3243. * A bug was fixed in the handling of grouped aggregates when all values in a group are null. See bug #3388. SQL * Fixed a bug where the server at some point stopped saving compiled queries in the SQL query cache. Bug Fixes * 3243: Segmentation fault (possible data corruption) after clean shutdown * 3258: Scheduling issues * 3368: BAT sortedness info ignored on ORDER BY and TOPN * 3374: UNIQUE constraint does not set tkey property on the corresponding BAT * 3382: Response to PREPARE emtpy if query contains a LIMIT * 3385: Simple query fails with 'identifier not found' * 3387: mclient does not properly double quote schema names when using autofill tab * 3388: case statement in "order by" clause doesn't work when used together with "group by" * 3389: median function with "group by" - SIGSEGV * 3392: ODBC datatype conversion for INTEGER not working properly * 3394: "Cannot find column type" error in temporary tables in functions * 3395: error occurred during a query: "'CASE WHEN" sentence * 3396: Improper UDF expansion * 3397: Error in ODBC-Driver when using Prepared Statements * 3398: Cannot stop monetdbd after erroneously starting an uninitialized dbfarm

1 0

I can integrate monetdb as hadoop?
by Edgar Mejia 02 Dec '13

02 Dec '13

if for example i have 300 millions of rows, i need to use something as hadoop? is posible integrate monetdb as hadoop? if i use only monetdb, and i have 300 millions of rows, how much memory and processor is recommended as minimum? Thanks, EM

2 2

how i do one date diff of on month?
by Edgar Mejia 02 Dec '13

02 Dec '13

i need to know how to subtract one month the current date. For example in postgres i can to do it so: extract('year' from current_date - interval '1 month') . How i can to do this as monetdb? Thanks

2 2

Very different query speeds for two similar queries
by Will Muldrew 02 Dec '13

02 Dec '13

Hi I've been trying out monetdb on a 350M row table (modern linux workstation with 24GB RAM + SSD). This table has a sorted TIMESTAMP column and a second unsorted VARCHAR(6) column which is non-unique (perhaps 50M distinct values fairly randomly distributed). This query is very slow, spends huge amount of time writing, and has very low CPU (intermediate results?): select count(*) from t_order where orderId = 'XXXXXX' This takes hours to complete. However, this query is a lot faster, even though the timestamp clause is redundant (all my data is > '2012-01-01'): select count(*) from t_order where orderId = 'XXXXXX' and time > '1970-01-01'; The first run is slowish (10s of seconds - I guess because we're reading the orderId column fully), however subsequent runs are sub-second. Obviously I could write an app which puts the spurious timestamp clause into the where, but it's still confusing and not ideal. Is there something I can do? The EXPLAINs are: sql>explain select count(*) from t_order where orderId = 'XXXXXX' and time > '1970-01-01'; +-------------------------------------------------------------------------------------------------------------------------+ | mal | +=========================================================================================================================+ | function user.s9_3{autoCommit=true}(A0:str,A1:str):void; | | X_14 := nil:wrd; | | barrier X_263 := language.dataflow(); | | X_4 := sql.mvc(); | | X_68:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,0,16); | | X_132 := X_68; | | X_101:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,0,16); | | X_164 := X_101; | | X_35:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",0,16); | | X_10 := calc.timestamp(A1,7); | | X_180 := algebra.thetasubselect(X_164,X_35,X_10,">"); | | X_197 := algebra.subselect(X_132,X_180,A0,A0,true,true,false); | | X_228 := algebra.leftfetchjoin(X_197,X_164); | | X_246 := aggr.count(X_228); | | X_70:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,1,16); | | X_133 := X_70; | | X_103:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,1,16); | | X_165 := X_103; | | X_37:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",1,16); | | X_181 := algebra.thetasubselect(X_165,X_37,X_10,">"); | | X_199 := algebra.subselect(X_133,X_181,A0,A0,true,true,false); | | X_229 := algebra.leftfetchjoin(X_199,X_165); | | X_247 := aggr.count(X_229); | | X_72:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,2,16); | | X_134 := X_72; | | X_105:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,2,16); | | X_166 := X_105; | | X_39:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",2,16); | | X_182 := algebra.thetasubselect(X_166,X_39,X_10,">"); | | X_201 := algebra.subselect(X_134,X_182,A0,A0,true,true,false); | | X_230 := algebra.leftfetchjoin(X_201,X_166); | | X_248 := aggr.count(X_230); | | X_74:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,3,16); | | X_135 := X_74; | | X_107:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,3,16); | | X_167 := X_107; | | X_41:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",3,16); | | X_183 := algebra.thetasubselect(X_167,X_41,X_10,">"); | | X_203 := algebra.subselect(X_135,X_183,A0,A0,true,true,false); | | X_231 := algebra.leftfetchjoin(X_203,X_167); | | X_249 := aggr.count(X_231); | | X_76:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,4,16); | | X_136 := X_76; | | X_109:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,4,16); | | X_168 := X_109; | | X_43:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",4,16); | | X_184 := algebra.thetasubselect(X_168,X_43,X_10,">"); | | X_205 := algebra.subselect(X_136,X_184,A0,A0,true,true,false); | | X_232 := algebra.leftfetchjoin(X_205,X_168); | | X_250 := aggr.count(X_232); | | X_78:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,5,16); | | X_137 := X_78; | | X_111:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,5,16); | | X_169 := X_111; | | X_45:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",5,16); | | X_185 := algebra.thetasubselect(X_169,X_45,X_10,">"); | | X_207 := algebra.subselect(X_137,X_185,A0,A0,true,true,false); | | X_233 := algebra.leftfetchjoin(X_207,X_169); | | X_251 := aggr.count(X_233); | | X_80:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,6,16); | | X_138 := X_80; | | X_113:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,6,16); | | X_170 := X_113; | | X_47:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",6,16); | | X_186 := algebra.thetasubselect(X_170,X_47,X_10,">"); | | X_209 := algebra.subselect(X_138,X_186,A0,A0,true,true,false); | | X_234 := algebra.leftfetchjoin(X_209,X_170); | | X_252 := aggr.count(X_234); | | X_82:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,7,16); | | X_139 := X_82; | | X_115:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,7,16); | | X_171 := X_115; | | X_49:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",7,16); | | X_187 := algebra.thetasubselect(X_171,X_49,X_10,">"); | | X_211 := algebra.subselect(X_139,X_187,A0,A0,true,true,false); | | X_235 := algebra.leftfetchjoin(X_211,X_171); | | X_253 := aggr.count(X_235); | | X_84:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,8,16); | | X_140 := X_84; | | X_117:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,8,16); | | X_172 := X_117; | | X_51:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",8,16); | | X_188 := algebra.thetasubselect(X_172,X_51,X_10,">"); | | X_213 := algebra.subselect(X_140,X_188,A0,A0,true,true,false); | | X_236 := algebra.leftfetchjoin(X_213,X_172); | | X_254 := aggr.count(X_236); | | X_86:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,9,16); | | X_141 := X_86; | | X_119:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,9,16); | | X_173 := X_119; | | X_53:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",9,16); | | X_189 := algebra.thetasubselect(X_173,X_53,X_10,">"); | | X_215 := algebra.subselect(X_141,X_189,A0,A0,true,true,false); | | X_237 := algebra.leftfetchjoin(X_215,X_173); | | X_255 := aggr.count(X_237); | | X_88:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,10,16); | | X_142 := X_88; | | X_121:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,10,16); | | X_174 := X_121; | | X_55:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",10,16); | | X_190 := algebra.thetasubselect(X_174,X_55,X_10,">"); | | X_217 := algebra.subselect(X_142,X_190,A0,A0,true,true,false); | | X_238 := algebra.leftfetchjoin(X_217,X_174); | | X_256 := aggr.count(X_238); | | X_90:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,11,16); | | X_143 := X_90; | | X_123:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,11,16); | | X_175 := X_123; | | X_57:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",11,16); | | X_191 := algebra.thetasubselect(X_175,X_57,X_10,">"); | | X_219 := algebra.subselect(X_143,X_191,A0,A0,true,true,false); | | X_239 := algebra.leftfetchjoin(X_219,X_175); | | X_257 := aggr.count(X_239); | | X_92:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,12,16); | | X_144 := X_92; | | X_125:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,12,16); | | X_176 := X_125; | | X_59:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",12,16); | | X_192 := algebra.thetasubselect(X_176,X_59,X_10,">"); | | X_221 := algebra.subselect(X_144,X_192,A0,A0,true,true,false); | | X_240 := algebra.leftfetchjoin(X_221,X_176); | | X_258 := aggr.count(X_240); | | X_94:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,13,16); | | X_145 := X_94; | | X_127:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,13,16); | | X_177 := X_127; | | X_61:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",13,16); | | X_193 := algebra.thetasubselect(X_177,X_61,X_10,">"); | | X_223 := algebra.subselect(X_145,X_193,A0,A0,true,true,false); | | X_241 := algebra.leftfetchjoin(X_223,X_177); | | X_259 := aggr.count(X_241); | | X_96:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,14,16); | | X_146 := X_96; | | X_129:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,14,16); | | X_178 := X_129; | | X_63:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",14,16); | | X_194 := algebra.thetasubselect(X_178,X_63,X_10,">"); | | X_225 := algebra.subselect(X_146,X_194,A0,A0,true,true,false); | | X_242 := algebra.leftfetchjoin(X_225,X_178); | | X_260 := aggr.count(X_242); | | X_98:bat[:oid,:str] := sql.bind(X_4,"sys","t_order","orderid",0,15,16); | | X_147 := X_98; | | X_131:bat[:oid,:timestamp] := sql.bind(X_4,"sys","t_order","time",0,15,16); | | X_179 := X_131; | | X_65:bat[:oid,:oid] := sql.tid(X_4,"sys","t_order",15,16); | | X_195 := algebra.thetasubselect(X_179,X_65,X_10,">"); | | X_227 := algebra.subselect(X_147,X_195,A0,A0,true,true,false); | | X_243 := algebra.leftfetchjoin(X_227,X_179); | | X_261 := aggr.count(X_243); | | X_245 := mat.pack(X_246,X_247,X_248,X_249,X_250,X_251,X_252,X_253,X_254,X_255,X_256,X_257,X_258,X_259,X_260,X_261); | | X_262 := algebra.selectNotNil(X_245); | | X_14 := aggr.sum(X_262); | | language.pass(X_164); | | language.pass(X_165); | | language.pass(X_166); | | language.pass(X_167); | | language.pass(X_168); | | language.pass(X_169); | | language.pass(X_170); | | language.pass(X_171); | | language.pass(X_172); | | language.pass(X_173); | | language.pass(X_174); | | language.pass(X_175); | | language.pass(X_176); | | language.pass(X_177); | | language.pass(X_178); | | language.pass(X_179); | | exit X_263; | | sql.exportValue(1,"sys.L1","L1","wrd",64,0,6,X_14,""); | | end s9_3; | +-------------------------------------------------------------------------------------------------------------------------+ 171 tuples (63.183ms) sql>explain select count(*) from t_order where orderId = 'XXXXXX'; +----------------------------------------------------------------+ | mal | +================================================================+ | function user.s10_3{autoCommit=true}(A0:str):void; | | X_3 := sql.mvc(); | | X_7 := sql.bind(X_3,"sys","t_order","orderid",0); | | X_10 := X_7; | | X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","t_order"); | | X_11 := algebra.subselect(X_10,X_4,A0,A0,true,true,false); | | X_13 := algebra.leftfetchjoin(X_11,X_10); | | X_14 := aggr.count(X_13); | | sql.exportValue(1,"sys.L1","L1","wrd",64,0,6,X_14,""); | | end s10_3; | | # optimizer.mitosis() | | # optimizer.dataflow() | +----------------------------------------------------------------+ 12 tuples (0.838ms) sql>select * from storage() where "table" = 't_order'; +--------+---------+-------------------+-----------+----------+-----------+-----------+------------+------------+---------+--------+ | schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted | +========+=========+===================+===========+==========+===========+===========+============+============+=========+========+ | sys | t_order | time | timestamp | 11/1146 | 349989136 | 8 | 2799913088 | 0 | 0 | true | ... | sys | t_order | orderid | varchar | 10/1043 | 349989136 | 6 | 2799913088 | 8240889856 | 0 | false | ... | sys | t_order | idx_orderid | oid | 04/456 | 349989136 | 8 | 2799913088 | 0 | 0 | true | +--------+---------+-------------------+-----------+----------+-----------+-----------+------------+------------+---------+--------+ (some fields edited out...) So the fast one has a much more complicated MAL plan. (Other than this, and a date parsing prob which I've reported, I'm really excited about this software!) -Will Muldrew

3 6

Re: copy into failed to import table
by Colin Foss 30 Nov '13

30 Nov '13

I am using the latest version. MonetDB 5 server v11.15.17 "Feb2013-SP5". The server and client are running under my account. The files and folders were also created with my account. Here is a simpler version which also encounters the "failed to import table" message. CREATE TABLE staging_simple_results( lookup varchar(20) NULL, extra_symbol varchar(20) NULL, measure1 tinyint NULL, base_temp float NULL ) ; Here are the contents of the same input, in a file called D:\junk\simple_import.csv. The file is also attached. one,two,3,4.023456787 green,brown,2,8.3456837 This is the statement I issued for the bulk load. copy into staging_simple_results from ('D:\junk\simple_import.csv') delimiters ',', '\\r\\n' ; On 2013-11-27 09:03, Colin Foss wrote: > > I created a table with 43 columns. The table contains char, > varchar, and tinyint datatypes. I am running on Windows Server > 2008 R2 64bit. The file contains two rows and is comma separated. > The file is BCP output from MSSQL. When I tried to bulk load into > the table from a file with a COPY INTO command I got an unhelpful > message. > > "failed to import table" > > How can I find out why the import failed? Which version of MonetDB are you using? I seem to remember fixing a problem where the actual failure message wasn't propagated to the level where the error was reported.

3 5

"temp" table renamed to "temporary"
by Lee Hachadoorian 28 Nov '13

28 Nov '13

I created a nontemporary table, but named it "temp" (because, after messing around with it, I was going to drop it). The table name on create was changed to "temporary". But the name "temp" still works in some contexts (apparently all SQL) but not others (\d). See session history below. Is this by design? --Lee sql>CREATE TABLE geo_boundaries.temp (id int PRIMARY KEY, name text); operation successful (55.041ms) sql>SELECT * FROM geo_boundaries.temp; +----+------+ | id | name | +====+======+ +----+------+ 0 tuples (1.818ms) sql>INSERT INTO geo_boundaries.temp VALUES (1, 'name'); 1 affected row (16.073ms) sql>SELECT * FROM geo_boundaries.temp; +------+------+ | id | name | +======+======+ | 1 | name | +------+------+ 1 tuple (0.977ms) sql>\d geo_boundaries.temp table geo_boundaries.temp does not exist sql>\d TABLE geo_boundaries.states TABLE geo_boundaries.temporary sql>\d geo_boundaries.temporary CREATE TABLE "geo_boundaries"."temporary" ( "id" INTEGER NOT NULL, "name" CHARACTER LARGE OBJECT, CONSTRAINT "temporary_id_pkey" PRIMARY KEY ("id") ); sql>DROP TABLE geo_boundaries.temp; operation successful (21.538ms) sql>\d TABLE geo_boundaries.states sql> -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://geospatial.commons.gc.cuny.edu http://freecity.commons.gc.cuny.edu

1 0

Table very slow
by Edgar Mejia 27 Nov '13

27 Nov '13

HI, i have one table as 15 millions of rows of all 2013. I all days delete the las 2 months and return to reload them again. Today is table is very slow, one select count takes more that 2 minutes, when it would normally to a few seconds. I migrate this information to a new table and this table if works fine and is very fast. What i need to do for to fix this and how i to fix this table slow? Thansk Edgar M

3 2

copy into failed to import table
by Colin Foss 27 Nov '13

27 Nov '13

I created a table with 43 columns. The table contains char, varchar, and tinyint datatypes. I am running on Windows Server 2008 R2 64bit. The file contains two rows and is comma separated. The file is BCP output from MSSQL. When I tried to bulk load into the table from a file with a COPY INTO command I got an unhelpful message. "failed to import table" How can I find out why the import failed?

2 1

Sum of nulls is 0
by Pedro Salgueiro 26 Nov '13

26 Nov '13

Hi, A few weeks ago we encountered an issue with monetdb when summing nulls, grouped by some column. We managed to create a simple test case which reproduces this scenario: create table test (number int, name varchar(50)); > > insert into test values(1,'jonh'); > insert into test values(2,'mike'); > insert into test values(3,'mary'); > insert into test values(null,'austin'); > insert into test values(null,'jonh'); > insert into test values(null,'mary'); > > select name, sum(number) > from test > group by name; The result of this query is: > austin | 0 > jonh | 1 > mary | 3 > mike | 2 But the expected result is: > austin | null > jonh | 1 > mary | 3 > mike | 2 The version that we are using is Feb2013-SP5. We found a bug report that could be related to this one, but we are not sure: http://bugs.monetdb.org/show_bug.cgi?id=3388 Should we open a new issue for this one? Thank you, Pedro Salgueiro

3 7

MonetDB with OpenCL externsion (Ocelot) query fails trying to cache an empty BAT
by Edward Ching 25 Nov '13

25 Nov '13

I'm running into a problme with Ocelot, the MonetDB with OpenCL extension. I apologize if this is not the right forum to post. I'm seeing a query failure which was caused by MoneDB-OpenCL trying to cache an empty BAT and ended up calling clCreateBuffer with a 0 size buffer and failed. Any select with a where clause would trigger it. Has anyone seen this too? It fails whether I use 32 bit or 64 bit oid, Nvidia or AMD. I'm trying to figure if it's a real issue or if I'm missing anything. Any input greatly appreciated. The database was generated by TPC-H dbgen (details below). The query I used was "select c_custkey, c_name from customer where c_custkey < 2", and here's a trace of the sequence of calls leading to the failure starting from the MAL statement: 1) X_52 := ocelot.thetasubselect(r1_10,A0,"<",0);(see log below) 2) ocl_select.c::ocl_subselect() line 142: ocl_cacheBAT 3) ocl_memmanager.c::ocl_cacheBAT() line 771: allocateBuffer 4) ocl_memmanager.c::allocateBuffer() line 432: clCreateBuffer ===== Source version and environment ===== 1) Ocelot src installed from zip downloaded from monetdb-opencl: msaecker-monetdb-opencl-b513b35eb84a.zip 2) Ocelot configure options: ./configure --prefix=/usr/local --enable-oid32 --enable-optimize --disable-test ing --disable-developer --disable-debug --disable-assert --enable-sql --enable-m onetdb5 --with-opencl=/usr 3) Operating System: Fedora 17 kernel 3.9.10-100 4) Hardware: NVIDIA GTX570 on Intel i7-3820 w/ 16GB mem 5) OpenCL: Nvidia OpenCL that came with CUDA 5.5 SDK 6) Database: generated w/ TPC-H dbgen using size factor s=0.01 ===== operation log follows ===== [root@gen3-2 ocelot]# mclient -ddb001 -lmal mal>ocelot.init(); mal>ocelot.listDevices(); # Device 0: NVIDIA Corporation - GeForce GTX 570 (default device) mal>\q [root@gen3-2 ocelot]# mclient -ddb001 -lsql sql>\d TABLE sys.customer TABLE sys.lineitem TABLE sys.nation TABLE sys.orders TABLE sys.part TABLE sys.partsupp TABLE sys.region TABLE sys.supplier sql>\d customer CREATE TABLE "sys"."customer" ( "c_custkey" INTEGER NOT NULL, "c_name" VARCHAR(25) NOT NULL, "c_address" VARCHAR(40) NOT NULL, "c_nationkey" INTEGER NOT NULL, "c_phone" VARCHAR(15) NOT NULL, "c_acctbal" DECIMAL(15,2) NOT NULL, "c_mktsegment" VARCHAR(10) NOT NULL, "c_comment" VARCHAR(117) NOT NULL, CONSTRAINT "customer_c_custkey_pkey" PRIMARY KEY ("c_custkey"), CONSTRAINT "customer_c_nationkey_fkey" FOREIGN KEY ("c_nationkey") REFERENCES "sys"."nation" ("n_nationkey") ); sql>select count(*) from customer; +------+ | L1 | +======+ | 1500 | +------+ 1 tuple (1.311ms) sql>select c_custkey, c_name from customer where c_custkey < 2; +-----------+--------------------+ | c_custkey | c_name | +===========+====================+ | 1 | Customer#000000001 | +-----------+--------------------+ 1 tuple (1.776ms) sql>set optimizer='cl_pipe'; operation successful (0.981ms) sql>select c_custkey, c_name from customer where c_custkey < 2; invalid/unknown response from server, ignoring output ? #Could not allocate OpenCL buffer of size: 0. Error caching input BAT. sql> sql>explain select c_custkey, c_name from customer where c_custkey < 2; +------------------------------------------------------------------------------+ | mal +==============================================================================+ | function user.s9_1{autoCommit=true}(A0:int):void; | | ocelot.beginQuery(); | | X_3 := sql.mvc(); | | X_7 := sql.bind(X_3,"sys","customer","c_custkey",0); | | X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","customer"); | | X_51 := ocelot.thetasubselect(X_7,X_4,A0,"<",0); | | (X_10,r1_10) := sql.bind(X_3,"sys","customer","c_custkey",2); | | X_52 := ocelot.thetasubselect(r1_10,A0,"<",0); | | X_13 := sql.bind(X_3,"sys","customer","c_custkey",1); | | X_53 := ocelot.thetasubselect(X_13,X_4,A0,"<",0); | | ocelot.sync(X_51); | | ocelot.sync(X_52); | | ocelot.sync(X_53); | | X_15 := sql.subdelta(X_51,X_4,X_10,X_52,X_53); | | X_17 := sql.projectdelta(X_15,X_7,X_10,r1_10,X_13); | | X_18 := sql.bind(X_3,"sys","customer","c_name",0); | | (X_20,r1_23) := sql.bind(X_3,"sys","customer","c_name",2); | | X_22 := sql.bind(X_3,"sys","customer","c_name",1); | | X_23 := sql.projectdelta(X_15,X_18,X_20,r1_23,X_22); | | X_24 := sql.resultSet(2,1,X_17); | | sql.rsColumn(X_24,"sys.customer","c_custkey","int",32,0,X_17); | | sql.rsColumn(X_24,"sys.customer","c_name","varchar",25,0,X_23); | | X_32 := io.stdout(); | | sql.exportResult(X_32,X_24); | | ocelot.endQuery(); | | end s9_1; | | # querylog.define("explain select c_custkey, c_name from customer where c_cu | : stkey < 2;","cl_pipe") : +------------------------------------------------------------------------------+ 27 tuples (1.408ms) sql> =======================================

2 3

monetdbd hangs from Python subprocess
by Christopher Nelson 23 Nov '13

23 Nov '13

Hi, I am writing some automation to control monetdbd programmatically from Python. While the rest of the monetdbd commands work fine, the command: monetdbd start <farm> never returns control to Python. I have tried this on both CPython 2.7 and PyPy 2.0.4. cnelson@hpvm:~/workspace/cql-db-py/cql $ python store.py DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set passphrase=letmein /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set control=yes /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set discovery=yes /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set port=60000 /tmp/test_warehouse' INFO:root:Starting warehouse at '/tmp/test_warehouse' cnelson@hpvm:~/workspace/cql-db-py/cql $ ps a PID TTY STAT TIME COMMAND 31608 pts/3 S 0:00 python store.py 31613 pts/3 Z 0:00 [monetdbd] <defunct> You can see that the process appears to have finished, but never returns control to Python. If I run the 'stop' command from the prompt, everything clears up and finishes: cnelson@hpvm:~/workspace/cql-db-py/cql $ /home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd stop /tmp/test_warehouse cnelson@hpvm:~/workspace/cql-db-py/cql $ DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd start /tmp/test_warehouse' INFO:root:Stopping warehouse at '/tmp/test_warehouse' ERROR:root:Unable to run command: '/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd stop /tmp/test_warehouse' DEBUG:root:unable to open merovingian.pid: No such file or directory ERROR:root:None Traceback (most recent call last): File "store.py", line 198, in <module> w.stop() File "store.py", line 192, in stop self._control_warehouse("stop") File "store.py", line 98, in _control_warehouse raise subprocess.CalledProcessError(p.returncode, cmd, out) subprocess.CalledProcessError: Command '['/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd', 'stop', '/tmp/test_warehouse']' returned non-zero exit status 1 The error here occurs because the next thing that the script will try to do is 'stop' the monetdbd instance. Which of course I already did by hand. The point is that once I stop the monetdbd instance OOB, the original monetdbd where 'start' was called finally does return control to Python. Which indicates that it is probably not a Python problem. Any ideas?

2 1

LIMIT clause in sub-query?
by Dennis Pallett 22 Nov '13

22 Nov '13

Hello all, I'm trying to use a LIMIT clause in a sub-query but MonetDB doesn't seem to be able to handle it. A very simple example query would be: SELECT COUNT(*) AS gt_result FROM ( SELECT * FROM nyc_buildings LIMIT 10 ) AS temp; The result of this query should be 10 and works fine in PostgreSQL. However MonetDB gives the following error: syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION or ')' in: "<original query>" Is this a bug in MonetDB? Any way to work around it? Best regards, Dennis Pallett

2 1

Enum data type?
by Dennis Pallett 22 Nov '13

22 Nov '13

Hello all, Does MonetDB support some kind of enum type, similar to MySQL's enum type (http://dev.mysql.com/doc/refman/5.0/en/enum.html) i.e. a column that only allows a limited set of values? I couldn't find it in the documentation and I'm unable to simulate it with triggers or constraints. Best regards, Dennis Pallett

2 2

CREATE CLUSTERED INDEX fails
by Michael Wittig 20 Nov '13

20 Nov '13

Hi, I am just getting started with monetdb. I have a table like this: CREATE TABLE values ( "date" DATE, "datetime" TIMESTAMP, "origin" VARCHAR(30), "value" REAL ); Normally I want to do queries like SELECT date, AVG(value) FROM values WHERE date>='2013-01-01' AND origin='sensor1' GROUP BY date; date and datetime are sorted so I would assume that an index on origin will be useful. The possible values of origin are limited so I tried: (Version Feb2013-SP5) CREATE CLUSTERED INDEX index_origin ON values (origin); But the result is syntax error, unexpected IDENT, expecting INDEX in "create clustered" What am I missing? Or is there even a better solution for data that looks like this where origin is sorted within a date. date | datetime | origin | value 2013-01-01 | 2013-01-01 10:00:00.000 | sensor1 | 10 2013-01-01 | 2013-01-01 12:00:00.000 | sensor1 | 12 2013-01-01 | 2013-01-01 14:00:00.000 | sensor1 | 15 2013-01-01 | 2013-01-01 16:00:00.000 | sensor1 | 10 2013-01-01 | 2013-01-01 10:00:00.000 | sensor2 |111 2013-01-01 | 2013-01-01 12:00:00.000 | sensor2 | 222 2013-01-01 | 2013-01-01 14:00:00.000 | sensor2 | 333 2013-01-01 | 2013-01-01 16:00:00.000 | sensor2 | 222 2013-01-02 | 2013-01-02 10:00:00.000 | sensor1 | 10 2013-01-02 | 2013-01-02 12:00:00.000 | sensor1 | 12 2013-01-02 | 2013-01-02 14:00:00.000 | sensor1 | 15 2013-01-02 | 2013-01-02 16:00:00.000 | sensor1 | 10 2013-01-02 | 2013-01-02 10:00:00.000 | sensor2 | 111 2013-01-02 | 2013-01-02 12:00:00.000 | sensor2 | 222 2013-01-02 | 2013-01-02 14:00:00.000 | sensor2 | 333 2013-01-02 | 2013-01-02 16:00:00.000 | sensor2 | 222 Regards, Michael

2 2

COPY INTO with foreign key
by Treffer, Arian 20 Nov '13

20 Nov '13

Hello, I want to import data into a table which has a foreign key to itself: CREATE TABLE "Type" ( "id" INT NOT NULL , "name" VARCHAR(255) NOT NULL , "file" VARCHAR(255) NULL , "componentTypeId" INT NULL , PRIMARY KEY ("id") , CONSTRAINT "fk_Type_componentType" FOREIGN KEY ("componentTypeId" ) REFERENCES "Type" ("id" ) ON DELETE NO ACTION ON UPDATE NO ACTION) ; Now, if I try to import some data, eg: 0;a;a;NULL 1;b;b;0 the second line causes an error: sql>COPY INTO "Type" FROM 'C:/data.csv' USING DELIMITERS ';','\n'; INSERT INTO: FOREIGN KEY constraint 'Type.fk_Type_componentType' violated What is the correct way to import the data? Since I have a lot of tables, I would rather not have to drop and re-create all constraints. Best regards, Arian

2 1

User Defined Aggregations in MonetDB?
by Alastair McKinley 20 Nov '13

20 Nov '13

Dear all, I was wondering if it is possible to build a User Defined Aggregation in MonetDB. For example, rather than do SUM(myfunction(a,b)) I would like to do MYAGG(a,b) where MYAGG internally holds a small buffer of a and b values so that they can be calculated in a batch fashion with a high performance batch implementation of the function. Could MYAGG be implemented in C? Best regards, Alastair

3 6

User Defined Functions returning null
by Alastair McKinley 13 Nov '13

13 Nov '13

Hi all, I am trying to evaluate the performance of MonetDB for an analytical workload. I am using MonetDB-11.15.17 built from source on Ubuntu 13.04. I have encountered a problem with user using defined functions in SQL which I have reduced to this test case. I appears that whenever I have over 200,000 rows in the table, the UDF returns null. First create a test data set with the following python: import random with open("test_data.csv","w") as f: for i in range(0,1000000): f.write("%s,%s\n" % (random.random(),random.random())) Then I use the following to load the data, and try using a simple UDF in SQL. CREATE TABLE TEST ( x float, y float ); COPY 200000 RECORDS INTO TEST from '/home/alastair/test_data.csv' USING DELIMITERS ',','\n','"' NULL AS ''; CREATE FUNCTION MY_UDF (x float, y float) RETURNS float BEGIN DECLARE ret float; set ret = LOG(x/y); RETURN ret; END; SELECT 'udf alone',MY_UDF(0.1,0.2); SELECT 'udf from table',MY_UDF(t.x,t.y) FROM TEST as t limit 1; -- this returns NULL with 200000 records DROP FUNCTION MY_UDF; DROP TABLE TEST; The value returned my MY_UDF is null with 200,000 records, changing to 199,999 records in COPY INTO produces the correct result. If I use a UDF built in C, the problem does not exist at all. I feel like I might have missed something silly but I can't think of anything. Best regards, Alastair

3 5

Spatial reference ID not stored/returned correctly?
by Dennis Pallett 12 Nov '13

12 Nov '13

Hello all, I'm working with the GeoSpatial extension of MonetDB and I might have run into a bug with regards to the spatial reference ID of objects. When adding a new object I specify the spatial reference ID but when retrieving the same object the correct spatial reference ID is not returned. The following queries demonstrate the bug: # Create a new spatial table CREATE TABLE trees (id INT, shape POINT); # insert a new Point object in spatial reference system 4326 (WGS 84) INSERT INTO trees VALUES ( 1, PointFromText('POINT (30 10)', 4326) ); # Retrieve the Spatial Reference ID from the just inserted Point object SELECT SRID(shape) FROM trees; I expect the result to be 4326 but instead it is always 0. Is this a bug in MonetDB / GeoSpatial or am I misunderstanding something? Best regards, Dennis Pallett

2 2

How to reclaim disk space used by intermediates
by Ashish Kumar Singh 10 Nov '13

10 Nov '13

Hi , While going through below section @ http://www.monetdb.org/book/export/html/242 : I understand that monetdb can create a lot of intermediates over the period if time, and when memory is limited can keep on consuming hard disk space forever. Is there a way to reclaim this space if I want to? Running out of diskspace One of the features of MonetDB's execution model is that all intermediates are materialized as memory mapped files. A consequence of this approach is that when memory is too small to keep them around, they will be swapped to disk by the operating system. This can be seen as a decaying free space and ultimately a full disk. In turn this (should) lead to a single transaction abort and removing its disk claim. Evidently, filling your disk depends on the number of users and the complexity of their queries. It aids to the cost of running COPY INTO and queries concurrently. If you have limited resources for concurrent access then the monetdb funnel may be an option to serialize the user requests. A single server can have multipled funnels. Bets Regards, Ashish

2 1

TypeException:user.main[2]:'A0' may not be used before being initialized
by Ruben Silva 08 Nov '13

08 Nov '13

Dear MonetDB users, Have you experienced anything like this before? TypeException:user.main[2]:'A0' may not be used before being initialized. Stacktrace follows: java.sql.SQLException: TypeException:user.main[2]:'A0' may not be used before being initialized at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2535) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2284) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349) at nl.cwi.monetdb.jdbc.MonetPreparedStatement.<init>(MonetPreparedStatement.java:118) at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:901) at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:825) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) (...) What do you recommend? Should I open a bug report? Ruben Silva

2 3

Monetdb ODBC datatype conversion problem
by baraa Mohamad 06 Nov '13

06 Nov '13

Dear MonetDB users, I have created a database link from oracle to monetdb using Monetdb ODBC driver, the link works fine but i have a strange results 😓 I have the following configurations: host1: Oracle (ubuntu 64bit)host2: MonetDB (ubntu 64bit)ODBC driver: MonetDB odbc (64bit) using unixODBC driver manager on monetdb i have the following table: emp (id integer , name varchar); test query: select id from emp where id<20; when I execute this query directly on montdb I get the following results (IDs from 1 to 19)ID123..19 BUT when I execute this query from oracle using dblink select "id" from "emp"@monetdb where "id"<20 ; I GET THE FOLLOWING RESULTS: ID-------- 18589934592 21.2885E+10 31.7180E+10 42.1475E+10 52.5770E+10 63.0065E+10 73.4360E+10 83.8655E+10 94.2950E+10 10 I cannot figure out the cause of the problem and how I can solve it (it seems a problem related to datatype conversion using ODBC) Could you please tell me what I should do.... Best regards,Baraa

3 6

Modify column datataype
by baraa Mohamad 06 Nov '13

06 Nov '13

Hello, I would like to know if it is possible to change a column type. I have a table with millions of records and I wand to change the type of the column (id) but I couldn't find how to do that. Thanks in advance for your help, Regards,Baraa

3 3