- users-list - monetdb.org

MEDIAN(double) not found
by Radovan Bičiště 08 Aug '14

08 Aug '14

Hello, We use MonetDB Jan2014-SP2. When I try MEDIAN function it simply says: MEDIAN: no such operator 'median(double)' [SQL State=22000] Am I missing something? I tried to execute section of 39_analytics.sql containing the MEDIAN function. It says MEDIAN function is already there. Thank you for any tips. Radovan Test Case: I took the sql from bug 2454: select median(id) from sys.tables; result: An error occurred when executing the SQL command: select median(id) from sys.tables MEDIAN: … [View More]

2 2

Connection Reset(mserver still alive?)
by Ali Salem 06 Aug '14

06 Aug '14

Hello, I have recently started using MonetDB for a BI project, and it's doing pretty well. However, I receive a connection reset error each time I run an ETL process (I use Pentaho PDI) to populate my dimensional schema. This error is reported by the MonetDB JDBC driver *Connection reset(mserver still alive?)* This usually happens after a random amount of time, 30 min, 45 min, 1 hour, etc. Any pointers related to this would be very helpful. Specs ------- Ubuntu 12.04 8 GB Ram 500 GB Space … [View More]

4 6

SIGSEGV during log flush
by Richard Hughes 01 Aug '14

01 Aug '14

Hello, Can I persuade a developer to help me out with bug 3422 [https://www.monetdb.org/bugzilla/show_bug.cgi?id=3422#c4]? I'm amassing an unhealthy collection of core dumps now (about one a day), and the latest is from Jan2014SP3 which I upgraded to last night. FWIW I'm no longer sure that I am looking at the same SIGSEGV as the one that the original bug reporter was talking about - of course I can create a new Bugzilla entry if it helps. This is our staging platform so I have the ability to … [View More]

1 0

Adding conditions slows query 1000x
by Dennis Pallett 31 Jul '14

31 Jul '14

Hi all, When I run the following query the results are computed extremely fast (within 5 ms): SELECT id_str, len,coordinates_x,coordinates_y FROM uk_neogeo_sorted WHERE coordinates_x >= 0.0 AND coordinates_x <= 22.499999996867977 LIMIT 100; However if I add additional conditions to the query so that it becomes the following: SELECT id_str,len,coordinates_x,coordinates_y FROM uk_neogeo_sorted WHERE coordinates_x >= 0.0 AND coordinates_x <= 22.499999996867977 AND coordinates_y &… [View More]

5 15

Announcement: New Jan2014-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 31 Jul '14

31 Jul '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014-SP3 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/>. Jan 2014-SP3 bugfix release Build Environment * Fix configure to … [View More]

1 0

High amount of VIRT and high amount of writing
by amihay gonen 29 Jul '14

29 Jul '14

Hi all , we are trying to compare oracle vs monetdb. the query we are try to run is : select count( client_code_new ) as s from ( SELECT t.client_code_new, t.bank, t.branch_new, t.ACCOUNT, t.instrument_ident, MAX (t.system_code) FROM transactions t left outer join ( SELECT c.linked_open_transaction_new as linked_open_transaction_new FROM calculated_taxs c GROUP BY c.linked_open_transaction_new) c_temp on t.transaction_no = c_temp.… [View More]linked_open_transaction_new join clients_list cl on cl.client_code = t.client_code_new AND cl.branch = t.branch_new WHERE t.opg_cls = 'O' AND (t.close_trans_disregard_balance_new) =0 and new_system_code = 1 GROUP BY t.client_code_new, t.bank, t.branch_new, t.ACCOUNT, t.instrument_ident) a ; tables sizes are: transactions = 103M rows calculated_taxs= 79M rows The query is taking 1min to run. we've notice that during the query run the VIRT column (from top utility) is become very high 10G , and during the we see extensive write to disk . why does monet is do so much writing ? thanks, amihay bellow is output of minimal_pipe optimizer +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +============================================================================================================================================================+ | function user.s4_3{autoCommit=true}(A0:str,A1:int,A2:int):void; | | X_5 := sql.mvc(); | | X_6:bat[:oid,:oid] := sql.tid(X_5,"sys","clients_list"); | | X_9 := sql.bind(X_5,"sys","clients_list","client_code",0); | | (X_12,r1_12) := sql.bind(X_5,"sys","clients_list","client_code",2); | | X_15 := sql.bind(X_5,"sys","clients_list","client_code",1); | | X_17 := sql.delta(X_9,X_12,r1_12,X_15); | | X_18 := algebra.leftfetchjoin(X_6,X_17); | | X_19 := batcalc.int(X_18); | | X_20:bat[:oid,:wrd] := batcalc.hash(X_19); | | X_23 := calc.int(22); | | X_25 := sql.bind(X_5,"sys","clients_list","branch",0); | | (X_27,r1_27) := sql.bind(X_5,"sys","clients_list","branch",2); | | X_29 := sql.bind(X_5,"sys","clients_list","branch",1); | | X_30 := sql.delta(X_25,X_27,r1_27,X_29); | | X_31 := algebra.leftfetchjoin(X_6,X_30); | | X_32:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_20,X_23,X_31); | | X_33:bat[:oid,:int] := bat.new(nil:oid,nil:int); | | X_36:bat[:oid,:oid] := sql.tid(X_5,"sys","transactions"); | | X_38 := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",0); | | (X_40,r1_40) := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",2); | | X_43 := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",1); | | X_44 := sql.delta(X_38,X_40,r1_40,X_43); | | X_45 := algebra.leftfetchjoin(X_36,X_44); | | X_46 := A1; | | X_47 := sql.bind(X_5,"sys","transactions","new_system_code",0); | | (X_50,r1_50) := sql.bind(X_5,"sys","transactions","new_system_code",2); | | X_53 := sql.bind(X_5,"sys","transactions","new_system_code",1); | | X_55 := sql.delta(X_47,X_50,r1_50,X_53); | | X_56 := algebra.leftfetchjoin(X_36,X_55); | | X_57 := A2; | | X_58 := sql.bind(X_5,"sys","transactions","opg_cls",0); | | (X_60,r1_60) := sql.bind(X_5,"sys","transactions","opg_cls",2); | | X_62 := sql.bind(X_5,"sys","transactions","opg_cls",1); | | X_63 := sql.delta(X_58,X_60,r1_60,X_62); | | X_64 := algebra.leftfetchjoin(X_36,X_63); | | X_65 := A0; | | X_66 := algebra.subselect(X_64,X_65,X_65,true,true,false); | | X_69 := algebra.subselect(X_56,X_66,X_57,X_57,true,true,false); | | X_70 := algebra.subselect(X_45,X_69,X_46,X_46,true,true,false); | | X_71 := sql.bind(X_5,"sys","transactions","transaction_no",0); | | (X_74,r1_74) := sql.bind(X_5,"sys","transactions","transaction_no",2); | | X_77 := sql.bind(X_5,"sys","transactions","transaction_no",1); | | X_78 := sql.delta(X_71,X_74,r1_74,X_77); | | X_79 := algebra.leftfetchjoin(X_36,X_78); | | X_80 := algebra.leftfetchjoin(X_70,X_79); | | X_81:bat[:oid,:oid] := sql.tid(X_5,"sys","calculated_taxs"); | | X_83 := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",0); | | (X_86,r1_86) := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",2); | | X_89 := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",1); | | X_91 := sql.delta(X_83,X_86,r1_86,X_89); | | X_92 := algebra.leftfetchjoin(X_81,X_91); | | (X_93,r1_93,r2_93) := group.subgroupdone(X_92); | | X_96 := algebra.leftfetchjoin(r1_93,X_92); | | X_97 := batcalc.lng(X_96); | | (X_98,r1_98) := algebra.join(X_80,X_97); | | X_100 := sql.bind(X_5,"sys","transactions","client_code_new",0); | | (X_102,r1_102) := sql.bind(X_5,"sys","transactions","client_code_new",2); | | X_104 := sql.bind(X_5,"sys","transactions","client_code_new",1); | | X_106 := sql.delta(X_100,X_102,r1_102,X_104); | | X_107 := algebra.leftfetchjoin(X_36,X_106); | | X_108 := algebra.leftfetchjoin(X_70,X_107); | | X_109 := algebra.leftfetchjoin(X_98,X_108); | | X_110 := bat.append(X_33,X_109,true); | | X_112 := bat.mirror(X_80); | | X_113 := algebra.tdiff(X_112,X_98); | | X_114 := algebra.leftfetchjoin(X_113,X_108); | | X_115 := bat.append(X_110,X_114,true); | | X_116:bat[:oid,:wrd] := batcalc.hash(X_115); | | X_119 := calc.int(22); | | X_121:bat[:oid,:int] := bat.new(nil:oid,nil:int); | | X_124 := sql.bind(X_5,"sys","transactions","branch_new",0); | | (X_128,r1_128) := sql.bind(X_5,"sys","transactions","branch_new",2); | | X_131 := sql.bind(X_5,"sys","transactions","branch_new",1); | | X_133 := sql.delta(X_124,X_128,r1_128,X_131); | | X_134 := algebra.leftfetchjoin(X_36,X_133); | | X_135 := algebra.leftfetchjoin(X_70,X_134); | | X_136 := algebra.leftfetchjoin(X_98,X_135); | | X_137 := bat.append(X_121,X_136,true); | | X_138 := algebra.leftfetchjoin(X_113,X_135); | | X_139 := bat.append(X_137,X_138,true); | | X_140:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_116,X_119,X_139); | | (X_141,r1_141) := algebra.join(X_32,X_140); | | X_143 := algebra.leftfetchjoin(X_141,X_19); | | X_144 := algebra.leftfetchjoin(r1_141,X_115); | | X_145:bat[:oid,:bit] := batcalc.==(X_143,X_144); | | X_147 := calc.bit(true); | | X_149 := algebra.subselect(X_145,X_147,X_147,true,true,false); | | X_151 := algebra.leftfetchjoin(X_149,X_141); | | X_152 := algebra.leftfetchjoin(X_151,X_31); | | X_153 := algebra.leftfetchjoin(X_149,r1_141); | | X_154 := algebra.leftfetchjoin(X_153,X_139); | | X_155:bat[:oid,:bit] := batcalc.==(X_152,X_154); | | X_157 := calc.bit(true); | | X_158 := algebra.subselect(X_155,X_157,X_157,true,true,false); | | X_159 := algebra.leftfetchjoin(X_158,X_153); | | X_160:bat[:oid,:dbl] := bat.new(nil:oid,nil:dbl); | | X_163 := sql.bind(X_5,"sys","transactions","bank",0); | | (X_168,r1_168) := sql.bind(X_5,"sys","transactions","bank",2); | | X_171 := sql.bind(X_5,"sys","transactions","bank",1); | | X_173 := sql.delta(X_163,X_168,r1_168,X_171); | | X_174 := algebra.leftfetchjoin(X_36,X_173); | | X_175 := algebra.leftfetchjoin(X_70,X_174); | | X_176 := algebra.leftfetchjoin(X_98,X_175); | | X_177 := bat.append(X_160,X_176,true); | | X_178 := algebra.leftfetchjoin(X_113,X_175); | | X_179 := bat.append(X_177,X_178,true); | | X_180 := algebra.leftfetchjoin(X_159,X_179); | | X_181:bat[:oid,:str] := bat.new(nil:oid,nil:str); | | X_183 := sql.bind(X_5,"sys","transactions","account",0); | | (X_185,r1_185) := sql.bind(X_5,"sys","transactions","account",2); | | X_187 := sql.bind(X_5,"sys","transactions","account",1); | | X_188 := sql.delta(X_183,X_185,r1_185,X_187); | | X_189 := algebra.leftfetchjoin(X_36,X_188); | | X_190 := algebra.leftfetchjoin(X_70,X_189); | | X_191 := algebra.leftfetchjoin(X_98,X_190); | | X_192 := bat.append(X_181,X_191,true); | | X_194 := algebra.leftfetchjoin(X_113,X_190); | | X_195 := bat.append(X_192,X_194,true); | | X_196 := algebra.leftfetchjoin(X_159,X_195); | | X_197:bat[:oid,:str] := bat.new(nil:oid,nil:str); | | X_199 := sql.bind(X_5,"sys","transactions","instrument_ident",0); | | (X_204,r1_204) := sql.bind(X_5,"sys","transactions","instrument_ident",2); | | X_207 := sql.bind(X_5,"sys","transactions","instrument_ident",1); | | X_209 := sql.delta(X_199,X_204,r1_204,X_207); | | X_210 := algebra.leftfetchjoin(X_36,X_209); | | X_211 := algebra.leftfetchjoin(X_70,X_210); | | X_212 := algebra.leftfetchjoin(X_98,X_211); | | X_213 := bat.append(X_197,X_212,true); | | X_214 := algebra.leftfetchjoin(X_113,X_211); | | X_215 := bat.append(X_213,X_214,true); | | X_216 := algebra.leftfetchjoin(X_159,X_215); | | X_217 := algebra.leftfetchjoin(X_159,X_115); | | X_218 := algebra.leftfetchjoin(X_159,X_139); | | (X_219,r1_219,r2_219) := group.subgroup(X_218); | | (X_222,r1_222,r2_222) := group.subgroup(X_217,X_219); | | (X_225,r1_225,r2_225) := group.subgroup(X_216,X_222); | | (X_228,r1_228,r2_228) := group.subgroup(X_196,X_225); | | (X_231,r1_231,r2_231) := group.subgroupdone(X_180,X_228); | | X_234 := algebra.leftfetchjoin(r1_231,X_217); | | X_235 := algebra.selectNotNil(X_234); | | X_236 := aggr.count(X_235); | | sql.exportValue(1,".L2","s","wrd",64,0,6,X_236,""); | | end s4_3; | | # querylog.define("explain \nselect count( client_code_new ) as s from \n(\n select t.client_code_new, t.bank, t.branch_new, t.account, t. | : instrument_ident, max (t.system_code)\n from transactions t left outer join \n ( select c.linked_open_transaction_new as linked_open_transa : : ction_new from calculated_taxs c group by c.linked_open_transaction_new) c_temp\n on t.transaction_no = c_temp.linked_open_transaction_new \njoin cli : : ents_list cl on cl.client_code = t.client_code_new and cl.branch = t.branch_new\n where t.opg_cls = \\'O\\' and (t.close_trans_disregard_balance_new) = : : 0 and new_system_code = 1 \n group by t.client_code_new, t.bank, t.branch_new, t.account, t.instrument_ident) a : : ;","minimal_pipe") : +------------------------------------------------------------------------------------------------------------------------------------------------------------+ [View Less]

2 1

monetdb is slow to backup
by Pierre-Adrien Coustillas 29 Jul '14

29 Jul '14

Hello, I'm used to save monetdb using msqldump : msqldump --database=base --table=table > backup.sql This command takes 30 minutes to backup 6 GB , but 4 minutes to restore. How can I increase backup speed ? Thanks Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

3 5

GDKerror when using COPY INTO sql commands
by Mahmut Uludag 25 Jul '14

25 Jul '14

Hi, We update our MonetDB databases using COPY INTO commands, we recently have started to get errors similar to the following error. GDKerror:!ERROR: GDKload: cannot read: name=13/52/135274, ext=theap, 56932 bytes missing. !OS: No such file or directory GDKload: cannot read: name=13/56/135610, ext=theap, 906 bytes missing. !OS: No such file or directory Deleting and recreating the tables and the schema haven't helped. If we create a new table with a table name not used in the past then … [View More]

2 3

Developing a C-based UDF
by Dennis Pallett 18 Jul '14

18 Jul '14

Hi all, I'm developing a C-based user defined function at the moment (actually porting an existing Postgres extension) and I was wondering what kinds of tools I should use for this and how I should actually do the development. At the moment I'm using a simple text editor to edit the relevant files (i.e. the c and mal files) and then completely re-compile MonetDB to test my changes. I'm not entirely happy about this workflow though, so is there an easier way? Perhaps with Visual Studio (I'… [View More]

3 3

python3-monetdb cursor.scroll questions
by Chris Richards 17 Jul '14

17 Jul '14

Howdy. I've recently started using monetdb and was testing out the python3-monetdb module with the 'quick start' voc dataset. I'm not grokking the purpose of cursor.scroll() if it's working as intended. I am getting weird (to me) results, which I'm including below. Running 11.17.17-20140514 of the server, client, and python module under Ubuntu 14.04 LTS. My example starts at this point in the quick start guide (but in python): sql>SELECT type, COUNT(*) AS total I loaded this snippet into … [View More]the python3 interpreter: from monetdb import sql def p(c): print('.rowcnt={}, .__offset={}, .rownumber={}'.format ( c.rowcount, c._Cursor__offset, c.rownumber)) print ('.__rows={}'.format (c._Cursor__rows)) db = sql.connect(database='voc', user='voc', password='voc') cursor = db.cursor() # Set the arraysize small so it traverses result sets, # though it doesn't matter for the problem I'm seeing. cursor.arraysize = 2 cursor.execute("SELECT type, COUNT(*) AS total FROM onboard_people GROUP BY type ORDER BY type;") Then proceeded with this interactive session. Consecutive fetchone()'s seem to work properly; it traverses to the second result set. Where I get confused is when I .scroll(): >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=0 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('craftsmen', 2349) >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=1 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('impotenten', 938) >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=2 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('passengers', 2813) >>> p(cursor) .rowcnt=6, .__offset=2, .rownumber=3 .__rows=[('passengers', 2813), ('seafarers', 4468)] # Here's where I scroll back to the beginning. The # offset changes, but the rownumber doesn't. >>> cursor.scroll (0, mode='absolute') >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=3 .__rows=[('craftsmen', 2349), ('impotenten', 938), ('passengers', 2813), ('seafarers', 4468), ('soldiers', 4177)] # My expectation was that this would be ('craftsmen', 2349), # but it continues as if I didn't scroll. >>> cursor.fetchone() ('seafarers', 4468) # I try to back to the last item with no luck, it instead # marches forward to the next item as if I didn't scroll. >>> cursor.scroll (-1, mode='relative') >>> p(cursor) .rowcnt=6, .__offset=3, .rownumber=4 .__rows=[('seafarers', 4468), ('soldiers', 4177), ('total', 2467)] >>> cursor.fetchone() ('soldiers', 4177) Am I using the .scroll() wrong? Is this a bug? Thanks, Chris [View Less]

1 0