February 2017 - users-list

Option to suppress status messages when running -f mode
by Anderson, David B 17 Feb '17

17 Feb '17

All, Is there a way to suppress the output of status messages like "3000 affected rows" when using mclient and "-f csv"? I have an sql script where I keep reducing a table and inserting the rows into another table. The final sql command extracts the final result. Would be nice if the status messages could be suppressed completely or sent to stderr (a --quiet option). I could also just run two scripts, or pipe the output through grep to remove the offending lines. Thanks, Dave

3 2

Best way to force table into hot set
by Anderson, David B 13 Feb '17

13 Feb '17

All, Is there a preferred method to move an entire table into the database hot-set? I guess I can have a query which computes across all of the columns in the table, but wondered if there was something better/cleaner. Thanks, Dave

1 0

prevent mitosis from partitioning (filter) function arguments
by Roberto Cornacchia 12 Feb '17

12 Feb '17

I have implemented a custom join as a filter function. By design, it cannot work correctly when the input tables are partitioned by mitosis. How can I prevent mitosis from partitioning its inputs? Roberto

2 6

Severe performance degradation
by Dennis Pallett 10 Feb '17

10 Feb '17

Hi all, We recently upgraded to the Dec2016 release but had to downgrade immediately after experiencing a severe degradation in performance. I have attached 4 graphs of our performance monitoring (of the last 3 days). It should be fairly obvious when we upgraded and when we downgraded again. We're still looking into the root cause but it appears that it has something to do with memory leaks when using aggregations on varchar colums with a high cardinality. Has anyone else experienced the same performance degradation? Is this a known (regression) bug? Best regards, Dennis Pallett

1 1

QueryTimeout Question
by Sebastián Acevedo 09 Feb '17

09 Feb '17

Hi, there is any way to chnage the default querytimeout for a user?... i need to use this to make a restriction for long querys, if i run call sys.settimeout(1) this change is only apllied to the current session and if i login again with the same user the timeout is not longer applied. +---------+-----------------+------+------------------+------+-------+ | user | login | sess | lastcommand | quer | activ | : : : iont : : ytim : e : : : : imeo : : eout : : : : : ut : : : : +=========+=================+======+==================+======+=======+ | monetdb | 2017-02-03 19:1 | 0 | 2017-02-03 19:13 | 1 | true | : : 2:37.000000 : : :28.000000 : : : | monetdb | 2017-02-03 18:0 | 0 | 2017-02-03 18:10 | 0 | false | : : 9:47.000000 : : :18.000000 : : : | monetdb | 2017-02-03 19:1 | 0 | 2017-02-03 19:13 | 0 | false | : : 3:10.000000 : : :12.000000 : : : +---------+-----------------+------+------------------+------+-------+ Thanks Sebastián Acevedo

3 4

Python Aggregate functions
by Anderson, David B 06 Feb '17

06 Feb '17

Mark, I think there is a bug in the implementation of the PYTHON_MAP aggregate functions. The return value of a PYTHON_MAP aggregate function can change if there are other functions in the query. The return value of my function weighted_percentile_0 (definition below) changes if I include the function median() in my query. The results are incorrect if median() isn't in the query, but are correct if median() is in the query. In this test case, the weights (v2) are all 1 and the values (v1) are uniformly sampled integers from [1,10]. Number of rows for each fctr is ~1000, and my function should reduce to min(v1) in this case. Is some information bleeding between threads? Should I submit a bug report? This is on the default branch. Thanks, Dave sql>select fctr,weighted_percentile_0(v1,v2),min(v1) from mini where fctr < 10 group by fctr order by fctr; +------+--------------------------+------+ | fctr | L1 | L2 | +======+==========================+======+ | 1 | 3 | 1 | | 2 | 2 | 1 | | 3 | 2 | 1 | | 4 | 1 | 1 | | 5 | 3 | 1 | | 6 | 3 | 1 | | 7 | 2 | 1 | | 8 | 1 | 1 | | 9 | 2 | 1 | +------+--------------------------+------+ 9 tuples (126.928ms) sql>select fctr,weighted_percentile_0(v1,v2),min(v1),median(v1) from mini where fctr < 10 group by fctr order by fctr; +------+--------------------------+------+------+ | fctr | L1 | L2 | L3 | +======+==========================+======+======+ | 1 | 1 | 1 | 5 | | 2 | 1 | 1 | 5 | | 3 | 1 | 1 | 5 | | 4 | 1 | 1 | 5 | | 5 | 1 | 1 | 5 | | 6 | 1 | 1 | 5 | | 7 | 1 | 1 | 5 | | 8 | 1 | 1 | 5 | | 9 | 1 | 1 | 5 | +------+--------------------------+------+------+ 9 tuples (519.195ms) sql> CREATE AGGREGATE weighted_percentile_0(a DOUBLE, w DOUBLE) RETURNS DOUBLE LANGUAGE PYTHON_MAP { import numpy as np # Standardize and sort based on values in a q = np.array([0]) / 100.0 idx = np.argsort(a) a_sort = a[idx] w_sort = w[idx] # Get the cumulative sum of weights ecdf = np.cumsum(w_sort) # Find the percentile index positions associated with the percentiles p = q * (w_sort.sum() - 1) # Find the bounding indices (both low and high) idx_low = np.searchsorted(ecdf, p, side='right') idx_high = np.searchsorted(ecdf, p + 1, side='right') idx_high[idx_high > ecdf.size - 1] = ecdf.size - 1 # Calculate the weights weights_high = p - np.floor(p) weights_low = 1.0 - weights_high # Extract the low/high indexes and multiply by the corresponding weights x1 = np.take(a_sort, idx_low) * weights_low x2 = np.take(a_sort, idx_high) * weights_high wp = np.add(x1,x2) return(wp[0]) };

3 4

Virtual Column
by imad hajj chahine 01 Feb '17

01 Feb '17

Hi, How far is the feature of supporting virtual column, with the possibility to persist it as a physical column, from the future release of monetdb? This feature is supported in mysql starting the 5.7 version. https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html Thank you.

1 0

SQL grammar
by imad hajj chahine 01 Feb '17

01 Feb '17

Hi, I am interested to know how actually monetdb parse an SQL expression and generate the expression tree, are you using lex/yacc similar grammar tool. I am currently using antlr for some basic parsing tasks, but my objective is to be able to generate a complete expression tree from an SQL expression. Thank you.

1 0

Installing Monetdb in on Ubuntu 16.04.1 LTS via repo
by Yan Grange 01 Feb '17

01 Feb '17

Hi there, I'm trying to install monetdb via the instructions on the website inside a Docker container derived from the Ubuntu 16.04.1 LTS image. I get the following error message: root@ContainerHost:/local# sudo apt-get update Ign:1 http://dev.monetdb.org/downloads/deb xenial InRelease Get:2 http://dev.monetdb.org/downloads/deb xenial Release [3878 B] Get:3 http://dev.monetdb.org/downloads/deb xenial Release.gpg [163 B] Hit:4 http://archive.ubuntu.com/ubuntu xenial InRelease Hit:5 http://archive.ubuntu.com/ubuntu xenial-updates InRelease Hit:6 http://archive.ubuntu.com/ubuntu xenial-security InRelease Fetched 4041 B in 0s (6161 B/s) Reading package lists... Done W: http://dev.monetdb.org/downloads/deb/dists/xenial/Release.gpg: Signature by key 213E64DCD5DDC2C063CC39FD053C3ED40583366F uses weak digest algorithm (SHA1) W: Invalid 'Date' entry in Release file /var/lib/apt/lists/partial/dev.monetdb.org_downloads_deb_dists_xenial_Release E: Failed to fetch http://dev.monetdb.org/downloads/deb/dists/xenial/Release Invalid 'Valid-Until' entry in Release file /var/lib/apt/lists/partial/dev.monetdb.org_downloads_deb_dists_xenial_Release E: Some index files failed to download. They have been ignored, or old ones used instead. I did only find one reference to this in a previous discussion. The advice given was to create a bug report. Since for that I need to have an account and that I can't find an account creation link on the bugzilla page, I decided to use this mailing list. Regards and thanks a lot, Yan Grange

2 1