- users-list - monetdb.org

Duplicate and reappearing tables.
by Nicolas Stalder 16 Mar '16

16 Mar '16

Hi all I am running a currently roughly 400G-sized single Monet instance. We load daily CSV files into corresponding tables, and use merge tables to query the data (e.g., feed_20160301, feed_20160302, ... and feed_mt). We have a problem mainly with duplicate tables (same schema + table name, different table_id in sys.tables). My approach is to "DROP TABLE foo" for N - 1 times if there are N copies (is there even a way to SELECT or DROP by table_id?). This seemingly fixes the problem, however after restarting the database, the dropped tables reappear. Similarly, I have some old empty tables, which reappear after being dropped when the database is restarted. We start and stop the database to roll over connections during maintenance. Is this a known issue? Can it be fixed? Not sure how to make this reproducible. Thanks, Nicolas Stalder.

3 2

will monetdb work well if it needs to swap onto a SSD?
by Anthony Damico 15 Mar '16

15 Mar '16

pretty sure that monetdb hits a huge performance wall when you use up all your ram, but i'm curious if anyone has experience with swapping onto a SSD? is the speed-loss nearly equally bad, or is swapping onto a SSD a reasonable choice? thanks all

1 0

Backslash n interpreted as newline.
by Nicolas Stalder 15 Mar '16

15 Mar '16

Hi all I have noticed that when loading tables from CSV files via COPY INTO ... USING DELIMITERS '\t','\n', the ASCII characters '\' followed by 'n' are interpreted as a newline, leading to the loading being aborted due to an incorrect number of columns in this row. This seems to me to be a bug? Also, it would be most helpful if the error message contained a line number (as is the case if there is unicode in the CSV file). Thanks, Nicolas Stalder.

1 0

Debugging: !SELECT: no such operator 'normalize2'
by Willem Ligtenberg 15 Mar '16

15 Mar '16

I have created a function in R for MonetDB. My first function works fine: dbGetQuery(conn, "DROP FUNCTION normalize;") functionDef <- paste( "CREATE FUNCTION normalize(welltype STRING, data_column DOUBLE) RETURNS TABLE (i DOUBLE) LANGUAGE R {", "idx <- which(welltype == 'LC')", "100 * data_column / median(data_column[idx])", "};", sep = "\n") dbGetQuery(conn, functionDef) # example use stmt <- "SELECT barcode, normalize(welltype_code, data_column1) FROM hcs;" dbGetQuery(conn, stmt) # Works! Yay! So I wanted to create a more advanced version: dbGetQuery(conn, "DROP FUNCTION normalize2;") functionDef <- paste( "CREATE FUNCTION normalize2(barcode DOUBLE, welltype STRING, data_column DOUBLE) RETURNS TABLE (i DOUBLE) LANGUAGE R {", "idx <- which(welltype == 'LC')", "100 * data_column / median(data_column[idx])", "};", sep = "\n") dbGetQuery(conn, functionDef) As you see, the only thing that I have changed so far, is the addition of an extra argument to the function. However, this fails: # example use stmt <- "SELECT normalize2(barcode, welltype_code, data_column1) FROM hcs;" dbGetQuery(conn, stmt) I checked, and the types are all fine: barcode = DOUBLE welltype_code = CHARACTER LARGE OBJECT data_column1 = DOUBLE So what is going wrong? Kind regards, Willem

2 3

why "INSERT INTO ... SELECT" is concurrency conflicts with "CREATE TABLE"
by Pierre-Adrien Coustillas 15 Mar '16

15 Mar '16

Hi, Why is that the query INSERT INTO io__selection_top (id_navigateur) SELECT id_navigateur FROM bdm__index_client_cookies_id_navigateur; has a "COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead" whith this query : DROP TABLE pierre; What is the table that a conflict ? SYS.TABLES ? 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 // 09 54 38 70 90

3 2

Monetdb crashes.
by Souri Ratnaparkhi 14 Mar '16

14 Mar '16

Hello, We have a main table with 126 columns and approx. 94000 rows. We are trying to load approx 2500 records from temporary table into main table using query “insert into select” query. The database crashes upon query execution. We get error “*monetdbd: internal error while starting mserver, please refer to the logs*” when to try to connect again. No specific error is created in log file. 1500 records successfully loads from temp table to main table. We also created another main table with same DDL, that table is empty & inserted all the records from temp successfully without any issue. What could be the reason? Is 126 cols an issue? Please help. We are stuck. (btw: We have machine about 200GB of hard disk & 16 GB RAM (14 GB free)) OS* - Ubuntu 14.04.2 LTS (GNU/Linux 3.13.0-55-generic x86_64)* Monetdb version* - MonetDB v11.21.13 (Jul2015-SP2)* Thank you [image: Description: Description: Description: Description: logo] * Souri Ratnaparkhi United Software Associates Pvt. Ltd. Pune, India Mob: 8975677255* * Phone (India Lines): (91) 020-25445550 **www.usain.com* <http://www.usain.com/>

4 10

User Defined Function and regular Function
by Shmagi Kavtaradze 13 Mar '16

13 Mar '16

I read documentation, but have some questions: 1) What is the real difference between UDF and just creating function in sql? I mean the speed of execution. 2) If I create a function, is it implicitly C? I am asking because there is python binding also. 3) If I have already installed Monetdb and want to create UDF, should I recompile whole monetdb? I am not good enough yet to do something like this. I am asking, because I want to take a text type column full of 0s and 1s ("110101010"), represent it as a "int" or "bit" and do bitwise AND against other columns. When I am saying represent as int/bit, I mean not to change ("110101010") into bit, just let C know that it should interpret that number already as int or bit.

2 2

Function
by Shmagi Kavtaradze 12 Mar '16

12 Mar '16

I want to compare two integer columns (filled with 1s and 0s) to each other with bitwise and, then count number of 1s and divide by length of column entries. I wrote a function, but not sure if it is right or how to create in in Monetdb from console. create function BAnd(int a, int b) returns float begin declare count int; int length = floor(log10(abs(a))) + 1; temp = a & b; while (temp != 0) { if (n % 10 == 1) count++; temp /= 10; } return count/length; end;

1 0

Bitwise operations in Monetdb
by Shmagi Kavtaradze 11 Mar '16

11 Mar '16

I am new to Monetdb. I am using Postgresql mainly, but want to check Monetdb performace. In Postres I have column of type bit() filled with 0s and 1s. Then I am comparing each row to all other rows with bitwise AND on that column. Monetdb does not have bit() type so I used text. Any ideas how to do bitwise AND in monetdb and what type of column should I use for this? The query I tried: select a.sentenecid, b.sentenecid, a.sentence AND b.sentence from test a, test b;

4 11

Monitoring JDBC Connections
by Arno Mittelbach 11 Mar '16

11 Mar '16

Hi all, I am looking for a way to monitor open remote connections (i.e., JDBC connections) and possibly to force close them. As far as I can tell, MonetDB restricts the number of simultaneous connections to 64 by default. Via select * from sys.queue I can monitor running queries but I haven't yet figured out a way to list all open connections. Consider e.g. the case that a JDBC connection is for some reason not properly closed. Any hints would be much appreciated. Best regards, Arno

1 0