June 2013 - users-list

User defined aggregation function (SQL)
by Sebastian Dorok 20 Jun '13

20 Jun '13

Hi all, I am evaluating MonetDB in the context of genomic data. Therefore I'm trying to implement a custom aggregation function. To get a feeling for the system I set up a UDF that just returns identical values. Now I'm trying to implement this simple function as aggregate function for groupings but always run into an error when executing this: select grpident(base) from test.bases group by pos; "TypeException:user.s1_1[18]:'udf.subident' undefined in: _30:bat[:any,:str] := udf.subident(_15:bat[:oid,:str], _27:bat[:oid,:oid], r1_27:bat[:oid,:oid], _1:bit) program contains errors" Here is the MAL plan: +-----------------------------------------------------------------+ | mal | +=================================================================+ | function user.s1_1{autoCommit=true}():void; | | X_2 := sql.mvc(); | | X_3:bat[:oid,:oid] := sql.tid(X_2,"test","bases"); | | X_6 := sql.bind(X_2,"test","bases","base",0); | | (X_9,r1_9) := sql.bind(X_2,"test","bases","base",2); | | X_12 := sql.bind(X_2,"test","bases","base",1); | | X_14 := sql.delta(X_6,X_9,r1_9,X_12); | | X_15 := algebra.leftfetchjoin(X_3,X_14); | | calc.str("bases"); | | calc.str("base"); | | X_18 := sql.bind(X_2,"test","bases","pos",0); | | (X_20,r1_20) := sql.bind(X_2,"test","bases","pos",2); | | X_22 := sql.bind(X_2,"test","bases","pos",1); | | X_23 := sql.delta(X_18,X_20,r1_20,X_22); | | X_24 := algebra.leftfetchjoin(X_3,X_23); | | calc.str("bases"); | | calc.str("pos"); | | (X_27,r1_27,r2_27) := group.subgroupdone(X_24); | | X_30:bat[:any,:str] := udf.subident(X_15,X_27,r1_27,true); | | calc.str("bases"); | | calc.str("L1"); | | calc.str("bases"); | | calc.str("L1"); | | X_36 := sql.resultSet(1,1,X_30); | | sql.rsColumn(X_36,"test.bases","L1","clob",1,0,X_30); | | X_40 := io.stdout(); | | sql.exportResult(X_40,X_36); | | end s1_1; | +-----------------------------------------------------------------+ I'm using following setup: MonetDB 5 server v11.15.7 "Feb2013-SP2" (64-bit, 64-bit oids) Found 1.9GiB available memory, 2 available cpu cores Libraries: libpcre: 8.31 2012-07-06 (compiled with 8.31) openssl: OpenSSL 1.0.1c 10 May 2012 (compiled with OpenSSL 1.0.1c 10 May 2012) libxml2: 2.9.0 (compiled with 2.9.0) Here's what my related code looks like: udf.c ##### str GRPident(int *ret, int *bid, int *eid){ ... } udf.h ##### udf_export str GRPident(int *ret, int *bid, int *eid); udf.mal ####### module udf ... command ident(b:bat[:oid,:str], e:bat[:oid,:str]) :bat[:oid,:str] address GRPident comment "identity of groups"; udf.sql ####### create aggregate grpident(src string) returns string external name udf.ident; I already renamed the MAL command to "subident" and changed the SQL expression accordingly without success. Furthermore I already had a look at "batxml.c" as suggested here: http://old.nabble.com/users-list-Digest,-Vol-3,-Issue-30-td34736774.html but don't see what I'm doing wrong. What do I have to do to implement a custom aggregate function? Thanks, Sebastian

2 3

Support for simultaneous queries / read locking
by Mark Hansen 13 Jun '13

13 Jun '13

We are looking to use MonetDb as the backend of a multi-user BI system. Before we get too far down the road with development, I'm wondering how MonetDb will perform with 10, 50, 100, 1000 simultaneous users? We are not concerned about writes - just reads, as we plan to do updates as a nightly batch process when we can tolerate a small window of performance downgrades on the queries. Is there any read-locking that limits concurrent queries from accessing the same data? If so, is it at the column level, or finer grained? Does anyone have experience using MonetDb for handling lots of concurrent queries? Thanks in advance for any info. -- Mark

2 1

Import data - MonetDB
by Raquel Ribeiro 13 Jun '13

13 Jun '13

Hi, I am trying do bulk load like I saw here http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/LoadingBulkData , but I am not having success when doing it. I am trying to bulk load more than 60k registers using the following command: > mclient -d adventurework -s "COPY INTO FactInternetSales FROM STDIN USING DELIMITERS ',', '\n', '\"'" - < C:Users\dir\text.txt After running this command I do not receive any error message but I cannot see the data in the database either. What am I missing here? Am I trying to this in the wrong way? PS: I also tried with a smaller file but in the end I have the same result. I attached my partial data file in case of being necessary and the create of this table. And the system is Windows. Thanks for your attention. Best regards, -- Raquel Ribeiro

3 4

question about concurrent read/write
by Roberto Cornacchia 13 Jun '13

13 Jun '13

Hi there, I'm trying to chase the cause of a problem with concurrent reads/writes, that shows up as either a "COMMIT FAILED" error or an assertion (delta_append_bat: Assertion `!c || ((c)->U->count) == bat->ibase' failed), probably depending timing issues. The scenario is roughly: two threads, one reading, one writing. Sometimes, they need to read/write from/to the same table. I'm aware that this may raise issues, but I'd like to get straight what the real cause is (and possibly what the cause of the assertion is). Imagine two JDBC connections, R (read only) and W (write only), in the following timeline: TS = transaction start TC = transaction commit TR = transaction trollback CO = cursor open C++ = increment cursor (read one row) IN = insert R | W _______________ 1| | TS 2| TS | 3| CO | IN 4| C++ | 5| | TC 6| | TS 6| C++ | IN 7| TR | 8| | TC The question is: is this supposed to fail or to succeed? There are no UPDATE in W, only INSERT INTO (actually COPY INTO). I tried a similar scenario with 2 mclient sessions, continuously reading from R (without cursors) and writing and committing from W. R kept nicely showing no updates, until the ROLLBACK. Then, it showed the updates from W. But I'm not sure whether the scenario above with cursors should behave the same. This is using very up to date Feb2013 branch. I guess the assertion is a bug, and I'd like to file it, but at the moment it's quite hard to reproduce consistently, perhaps it would be easier if I could get a better understanding of the scenario above. Thanks, Roberto

2 1

Building MonetDB on Windows (x64)
by Scott Mathieson 12 Jun '13

12 Jun '13

Hi, I am trying to build the latest sources on Windows (x64) and I'm having problems getting the compilation to work, specifically the code in Mx does not seem to be fully compliant with the Windows VC compiler (the use of __attribute__ in the MxFcnDef.h file to be a case in point although there are lots of other examples of gcc specific syntax in this code). I believe I have followed the instructions in windowsbuild.rst but to no avail. Am I missing something - is Mx.exe required for the build and if so, can it be built with VS2010? Thanks, Scott ________________________________

1 0

JDBC driver does not return stored procedure information.
by Thomas Kellerer 11 Jun '13

11 Jun '13

Hello list, while adding support for MonetDB to my tool "SQL Workbench/J" (http://www.sql-workbench.net) I noticed that the calls to DatabaseMetaData.getProcedures() DatabaseMetaData.getProcedureColumns() will always return an empty ResultSet even if there are stored procedures or functions in the database. Are there any plans to fix this bug? If yes, what will be the timeframe for that? Regards Thomas Kellerer

1 0

Inconsistency in select
by Flavio Coelho 10 Jun '13

10 Jun '13

Dear list, We have found this strange inconsistency when selecting from data. Consider the two following select clauses: in the first one with a "where" filter, we notice a number of missing data on the resulting table (hint: they are not really missing in the database). However if we look to any of the specific lines which appear to have missing data, as done in the second select, we confirm that the data is there (not missing). What is going on? Am I doing anything wrong? Is this a bug of the mclient? the version I am running is this: MonetDB Database Server Toolkit v1.1 (Feb2013-SP2) sql>select * from notas_c where instalacao=412307404; +-------------+-----------+------------+------------+------+--------------+------+------------------------------+------------+-----------+--------------------------------------------------+ | id | instalaca | data_de_cr | data_do_en | tipo | descricao_do | codi | descricao_da_codificacao_da_ | data_fim_d | status_do | descricao_do_status_do_usuario | : : o : iacao_da_n : cerramento : _de_ : _tipo_de_not : fica : nota : a_avaria : _usuario : : : : : ota : _da_nota : nota : a : cao_ : : : : : : : : : : _de_ : : da_n : : : : : : : : : : serv : : ota : : : : : : : : : : ico : : : : : : : +=============+===========+============+============+======+==============+======+==============================+============+===========+==================================================+ | 35004675 | 412307404 | 2007-10-22 | 2011-06-14 | AF | | AF01 | | 2007-10-30 | NPRO ARQU | Laudo Válido Improcedente//Enviado via Interface | | 10009362195 | 412307404 | 2009-10-28 | 2009-10-31 | AL | | AL06 | | 2009-10-31 | VIRE EXEC | Vistoria Reprovada//Executada no Campo | | 10009372721 | 412307404 | 2009-10-29 | 2009-11-17 | AG | | AG22 | | 2009-11-17 | PRO | Procedente | | 10009455607 | 412307404 | 2009-11-10 | 2009-11-17 | AD | | AD05 | | 2009-11-16 | EXEC | Executada no Campo | | 10010449356 | 412307404 | 2010-03-30 | 2010-08-11 | AJ | | AJ11 | | 2010-08-11 | EXEC | Executada no Campo | | 10010832683 | 412307404 | 2010-05-21 | 2010-05-24 | AD | | AD05 | | 2010-05-24 | REJE | Rejeitada | | 10011114747 | 412307404 | 2010-06-29 | 2010-06-30 | AG | | AG44 | Serviço fora do prazo | 2010-06-30 | IMP | Improcedente | | 10011308855 | 412307404 | 2010-07-22 | 2010-07-28 | AD | | AD05 | BO - Manutenção na lente | 2010-07-28 | EXEC | Executada no Campo | | 10011475191 | 412307404 | 2010-08-11 | 2010-08-16 | AB | | AB08 | AB08 Auc c/subst c ramal Tri | 2010-08-16 | EXEC | Executada no Campo | | 10012702114 | 412307404 | 2011-01-05 | 2011-01-10 | A3 | | A375 | Corte MCV exec na rede | 2011-01-07 | EXEC | Executada no Campo | | 10014234776 | 412307404 | 2011-06-14 | 2011-06-15 | A3 | | A375 | Corte MCV exec na rede | 2011-06-15 | EXEC | Executada no Campo | | 10014271409 | 412307404 | 2011-06-15 | 2011-06-16 | A5 | Religação BT | A513 | Religa Normal-Exec Rede | 2011-06-16 | EXEC | Executada no Campo | +-------------+-----------+------------+------------+------+--------------+------+------------------------------+------------+-----------+--------------------------------------------------+ 12 tuples (0.9s) sql>select * from notas_c where id=35004675; +----------+-----------+------------+------------+------+--------------------+------+--------------------------------+------------+-----------+---------------------------------------------+ | id | instalaca | data_de_cr | data_do_en | tipo | descricao_do_tipo_ | codi | descricao_da_codificacao_da_no | data_fim_d | status_do | descricao_do_status_do_usuario | : : o : iacao_da_n : cerramento : _de_ : de_nota : fica : ta : a_avaria : _usuario : : : : : ota : _da_nota : nota : : cao_ : : : : : : : : : : _de_ : : da_n : : : : : : : : : : serv : : ota : : : : : : : : : : ico : : : : : : : +==========+===========+============+============+======+====================+======+================================+============+===========+=============================================+ | 35004675 | 412307404 | 2007-10-22 | 2011-06-14 | AF | VerifTensão Fornec | AF01 | Verif Tensão Fornecimento - BT | 2007-10-30 | NPRO ARQU | Laudo Válido Improcedente//Enviado via Inte | : : : : : : : : : : : rface : +----------+-----------+------------+------------+------+--------------------+------+--------------------------------+------------+-----------+---------------------------------------------+ 1 tuple (325.965ms) -- Flávio Codeço Coelho ================ +55(21) 3799-5567 Professor Escola de Matemática Aplicada Fundação Getulio Vargas Praia de Botafogo, 190 sala 312 Rio de Janeiro - RJ 22250-900 Brasil

4 8

disable foreign key checks
by Gijs Molenaar 10 Jun '13

10 Jun '13

Hi monetdb people, I'm doing some work again on Djonet, implementing missing features. One of the features is to flush the content of a set tables e.g. remove all rows. The problem is that the order the tables are processed is important in the case of foreign key relations. They way this is implemented in other database drivers for django is to temporarily disable foreign key relations (mysql) or to TRUNCATE multiple tables in one statement (postgresql). Is there an (undocumented) way of accomplishing something similar? I prefer not to temporarily alter the table. Thanks, -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

3 4

gdk_heap
by Hassan, Ahmad 06 Jun '13

06 Jun '13

Hi, I have read the gdk_heap code in detail to understand whether MonetDB reuses the same virtual address range over and over again for multiple allocations. From my analyses, I found out the following: 1) HEAPalloc is used for memory allocation. If requested size is under GDK_mmap_minsize then STORE_MEM (GDKmalloc|malloc) is used for allocation Otherwise STORE_MMAP (anonymous mmap) or STORE_PRIV (DISK mmap) is used 2) HEAPfree use HEAPcacheAdd to return the previously allocated virtual address space to the cache whose size is fixed at 5 elements. So this means only these 5 address ranges will be used over time and their size would be extended using HEAPextend 3) Apart from those 5 HEAPcache entries, MonetDB doesn't use any reallocation of same virtual address range for multiple objects but just relies on operating system to handle the allocation and de-allocation through malloc/mmap. Does this mean that MonetDB just keeps only 5 HEAPcache entries to reduce the overhead of mmap calls and manage those virtual addresses by itself for future allocations? [HEAP_CACHE_SIZE = 5] I understand that this is very simplistic analyses of what MonetDB actually does in gdk_heap but any information on the gdk_heap would be really helpful. Thanks. Kind Regards, Ahmad

1 0

which version of monetdb works with cygwin ?
by Kaniska_Mandal＠DELL.com 06 Jun '13

06 Jun '13

Hi, Which linux flavor of MonetDB (Fedora / FreeBSD / Debian) can I run in Cygwin ? If it's not possible to run a linux version of monetdb in Cygwin, then what are the steps to compile monetdb src into Cygwin ? Thanks, Kaniska

2 1