- users-list - monetdb.org

MonetDB Blob Inserts
by Brian Hood 07 Dec '17

07 Dec '17

Hi Guys, I don't seem to be able to INSERT into a blob field is there any information how to achieve this. Regards, Brian Hood

3 6

What is better JSON or TEXT field in terms of performance ???
by Ariel Abadi 07 Dec '17

07 Dec '17

Hi All! Does anybody has worked with json fields? We are planning to use json fields with very large text on it (the hierarchy of our json would be plain (only one level), we are doing some tests over 7MM records, it took like 1m30s to retrieve the information. In this case to get the information we used the function json.filter(name_of_field, '$..xxxx') We have also tested using a TEXT field (and using some string functions) we parsed the information inside the field and we found that sometimes it was quicker it took from 1m30s to 35s. I dont know exactly why the difference on that delta. We need to implement the processes, and need to decide how to move forward.... Does anybody can help me on the decision ??? We know and is a fact, that Monet can beat any DB in terms of performance, and that is why we decided to use it, but now Im worried about this type of queries (against json/text fields) and how long this queries would take if we increase the amount of records.... What would you suggest ?? Thanks in advance! Rgds Ariel

3 3

mserver5 crash on startup in Ubuntu Xenial
by Manuel Pagliai 06 Dec '17

06 Dec '17

Hello, I get this error when I try to start monetdb on Ubuntu Xenial 16.04.03 mserver5 visokio # MonetDB 5 server v11.27.9 "Jul2017-SP2" # Serving database 'demo', using 12 threads # Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers # Found 7.702 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved # Visit https://www.monetdb.org/ for further information !SyntaxException:parseError:include sqlcatalog; !SyntaxException:parseError:^LoaderException:loadLibrary:Loading error failed to open library sql (from within file '/usr/lib/monetdb5/lib_sql.so'): /usr/lib/monetdb5/lib_sql.so: undefined symbol: mnstr_writeFlt !SyntaxException:parseError:include sqlcatalog; !SyntaxException:parseError: ^';' expected Segmentation fault (core dumped) any ideas ? Thanks, Best regards, *Manuel Pagliai* Visokio Ltd www.visokio.com +44 20 7351 4646

1 0

Issue with SUM(DISTINCT) in 11.27.9
by Sharma, Sreejith 05 Dec '17

05 Dec '17

All, We have noticed when SQL pattern has SUM(DISTINCT... ) there is a degradation of SQL timings when compared to 11.27.5 build. Anyone is aware of this? Regards, Sreejith

1 0

Re: MAL query "sql.resultSet"
by dennis.fassl 01 Dec '17

01 Dec '17

Hi all, can you help me with the below query? Is there an other way to return query results using the jdbc interface (without using sql.resultSet)? Thanks in advance! Best regards,Dennis -------- Ursprüngliche Nachricht --------Von: Dennis Fassl <dennis.fassl(a)uni-konstanz.de> Datum: 25.11.17 16:55 (GMT+01:00) An: users-list(a)monetdb.org Betreff: MAL query "sql.resultSet" Hi all, I am trying to execute the MAL query below. But the command "sql.resultSet" is terminating mclient. When using the JDBC interface I get "SQLException: Connection to server lost! (mserver still alive?)". Do you have an idea why this is happening? Thanks and regards, Dennis sql.init(); table_names := bat.new(nil:str); bat.append(table_names, "t0"); bat.append(table_names, "t1"); table_attrs := bat.new(nil:str); bat.append(table_attrs, "int_col"); bat.append(table_attrs, "str_col"); table_types := bat.new(nil:str); bat.append(table_types, "int"); bat.append(table_types, "str"); table_lens := bat.new(nil:int); bat.append(table_lens, 1); bat.append(table_lens, 1); table_scales := bat.new(nil:int); bat.append(table_scales, 0); bat.append(table_scales, 0); ints := bat.new(nil:int); bat.append(ints, 42); bat.append(ints, 23); strs := bat.new(nil:str); bat.append(strs, "foo"); bat.append(strs, "bar"); sql.resultSet(table_names, table_attrs, table_types, table_lens, table_scales, ints, strs); _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

4 6

MAL query "sql.resultSet"
by Dennis Fassl 25 Nov '17

25 Nov '17

Hi all, I am trying to execute the MAL query below. But the command "sql.resultSet" is terminating mclient. When using the JDBC interface I get "SQLException: Connection to server lost! (mserver still alive?)". Do you have an idea why this is happening? Thanks and regards, Dennis sql.init(); table_names := bat.new(nil:str); bat.append(table_names, "t0"); bat.append(table_names, "t1"); table_attrs := bat.new(nil:str); bat.append(table_attrs, "int_col"); bat.append(table_attrs, "str_col"); table_types := bat.new(nil:str); bat.append(table_types, "int"); bat.append(table_types, "str"); table_lens := bat.new(nil:int); bat.append(table_lens, 1); bat.append(table_lens, 1); table_scales := bat.new(nil:int); bat.append(table_scales, 0); bat.append(table_scales, 0); ints := bat.new(nil:int); bat.append(ints, 42); bat.append(ints, 23); strs := bat.new(nil:str); bat.append(strs, "foo"); bat.append(strs, "bar"); sql.resultSet(table_names, table_attrs, table_types, table_lens, table_scales, ints, strs);

1 0

Number of instance
by Sharma, Sreejith 23 Nov '17

23 Nov '17

All, May be a dump question, but curious to know. What is the maximum number of MonetDB instance anyone has setup in a single server? And what level of concurrency has been tested? Regards, Sreejith

1 0

MonetDB - Data Compression, is it possible?
by Sharma, Sreejith 23 Nov '17

23 Nov '17

All, Is there any data compression methods available in MonetDB? What I understand from I read is, by default MonetDB doesn't have a compression methods to avoid the overhead of uncompression while loading the files into memory mapped files. Is that true? If there is a compression mechanism (alike QUERY HIGH Compression in Oracle) what is it in here? Regards, Sreejith

2 2

algebra.join heapextend crash
by Anthony Damico 22 Nov '17

22 Nov '17

hi, i'm using monetdblite-r 0.5.0 on a confidential dataset. my syntax worked on monetdblite 0.3.1 on the same machine ftruncate: Invalid argument Error in .local(conn, statement, ...) : Unable to execute statement 'CREATE TABLE ccaed123_b_svcmon_xwalk AS ( SELECT a.enrolid , a.yr , a.dataty...'. Server says 'MALException:algebra.join:GDK reported error. !ERROR: GDKextendf: c !ERROR: HEAPextend: failed to extend to 2890530816 for 02\57\25766.tail: GDKmre map() failed '. it's a local drive, the hard disk isn't close to full and the machine has 700gb of ram, but the table it's trying to create might be bigger than that. dunno if related to https://www.monetdb.org/bugzilla/show_bug.cgi?id=3791 thanks

1 0

Patch for backreferences in PCRE replace
by Roberto Cornacchia 16 Nov '17

16 Nov '17

Hi, Following up on this thread ( https://www.monetdb.org/pipermail/users-list/2016-April/009107.html) from last year, the issue was that the pcre-based regex replacement did not implement back references in the replacement string. In need for this feature, I so far used the pcre implementation via R function. It works well, but using R just for this is a bit odd, especially because R-core has a pretty dumb dependency on the whole TeX distribution, and building MonetDB docker images that need to include R and TeX just because of regex isn't a neat option. I have also tried to use both re and regex packages via python, but this is very slow compared to pcre. So I finally decided to put a few hours on the MonetDB implementation and added support for back references. They can be indicated with both \1 and $1 syntax. Back-reference 0 is the whole match. Out-of-bound back-references are empty strings. While at it: - pcre.replace replaces all matches, so I made a variant pcre.replacefirst to replace only the first match. - the bat implementation was ignored because of a missing "module batpcre" in pcre.mal, it always resorted to manifold on the string version. Fixed that. Also, I'm not sure why there don't seem to exist SQL functions declared for this. I use the following: CREATE FUNCTION pcre_replace(s string, pattern string, repl string, flags string) RETURNS string EXTERNAL NAME pcre."replace"; CREATE FUNCTION pcre_replacefirst(s string, pattern string, repl string, flags string) RETURNS string EXTERNAL NAME pcre."replace_first"; Perhaps they could come by default? Please feel free to use and modify the patch (Jul2017) in attachment if you think it's useful. I've tested it quite a bit, no issues found. Here are some examples: sql>select name, pcre_replace(name, '([a-z]+)_([a-z]+)', '[\\1]-[\\2]', '') as replaced from sys.functions limit 2; +-------------+---------------------------------------------+ | name | replaced | +=============+=============================================+ | mbr_overlap | [mbr]-[overlap] | | mbr_overlap | [mbr]-[overlap] | +-------------+---------------------------------------------+ 2 tuples (4.650ms) sql>select name, pcre_replacefirst(name, '([a-z])', '[\\1]', '') as replaced from sys.functions limit 2; +-------------+---------------------------------------+ | name | replaced | +=============+=======================================+ | mbr_overlap | [m]br_overlap | | mbr_overlap | [m]br_overlap | +-------------+---------------------------------------+ 2 tuples (4.728ms) sql>select name, pcre_replace(name, '([a-z])', '[\\1]', '') as replaced from sys.functions limit 2; +-------------+-----------------------------------------------------------------------------------------------------+ | name | replaced | +=============+=====================================================================================================+ | mbr_overlap | [m][b][r]_[o][v][e][r][l][a][p] | | mbr_overlap | [m][b][r]_[o][v][e][r][l][a][p] | +-------------+-----------------------------------------------------------------------------------------------------+ 2 tuples (10.360ms)

1 1