October 2016 - users-list

Data lost
by Luciano Sasso 17 Oct '16

17 Oct '16

Hi, I am delete rows from table, with "delete from mytable where id=x", and rows deleted no return in select "select * from mytable where id=x", its OK ! Version "Jul2015-SP1" But, when restart database, deleted rows return in select "select * from mytable where id=x". Its bug ?, or i have execute vacuum command, how to execute vacuum command. ? -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | 17 3202-4040 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

2 1

new monetdb version, old farm
by Lynn Carol Johnson 17 Oct '16

17 Oct '16

Hello - My understanding is you should not run a new monetdb version with a dbfarm created from an older version. We have just updated our system from a Jul2015-SP3 version to the latest Jun2016-SP2 version. I have created a new db farm and plan to recreate the tables in the new farm. My question; How do you remove the old dbfarm? Our data is quite large (1.8 T) and I'd like to retrieve this space for other use. While I can start the db against the new farm, I can find no method of removing the old dbfarm to reclaim this space. Thanks - Lynn

5 7

Zero copy optimization in embedded python and loop back queries
by Joseph D'silva 16 Oct '16

16 Oct '16

Hi All, I was wondering if zero copy optimization in embedded Python is applicable even for results returned inside functions that use _conn.execute("select ...") ? Also is there something to look for in explain plan that will indicate if zero copy optimization is used or not ? Thanks -J

1 0

weird behavior when using views with calculated columns and double type
by imad hajj chahine 14 Oct '16

14 Oct '16

Hi, When paginating over a view that contains a calculated double columns. i got a TypeException error and sometimes the server crash. To replicate the error, create a table with 2 double columns col1 and col2, fill it with some test data; after create a view as select col1, col2, col1+col2 as col3 from table. when running select col1, col2, col3 from view limit 100 offset 100 i got the error. Also in related context, when i use 2 quantiles function on the calculated view column i get the same error. select quantile(col3, 0.25), quantile(col3, 0.75) from view. I am using jun2016-sp1 on windows. Thank you.

2 1

Announcement: New Jun2016-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 13 Oct '16

13 Oct '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2016-SP2 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/>. Jun 2016-SP2 bugfix release Build Environment * We now use the CommonCrypto library instead of the OpenSSL library on Darwin. Bug Fixes * 3939: Assert failure on concurrent queries when querying sys.queue * 4019: Casting a timestamp from a string results in NULL * 4025: expressions in the WHERE clause that evaluates incorrectly * 4038: After upgrade from 11.21.19, jdbc couldn't list tables for non sys users * 4044: Server crash when trying to delete a table has been added to a merge table with "cascade" at the end * 4049: Wrong results for queries with "OR" and "LEFT JOIN" * 4052: Infinite loop in rel_select * 4054: copy into file wrongly exports functions * 4058: Server crashes with a particular conditional query * 4059: Geom functions only visible by user monetdb * 4060: BAT leak in some aggregate queries * 4062: Error: SELECT: no such binary operator 1800 790 9186'like(varchar,varchar)' when used in query running in other schema than sys * 4064: Assertion: column not found * 4067: Relevant column name not printed when a CSV parsing error occurs * 4070: Extra condition in join predicate of explicit join produces wrong MAL code * 4074: Cannot use prepared statements when caching disabled * 6065: CTE with row number and union fails within MAL

1 0

EXPLAIN output *almost* re-parsable
by Roberto Cornacchia 10 Oct '16

10 Oct '16

Almost for fun, I did this little experiment: take the output of an EXPLAIN from sql, and execute it in MAL (mclient -l mal). I found only *1* detail which doesn't parse, here it is in a minimal example: * Got the explain of a trivial query: sql>\f raw sql>explain select name from sys.functions; % .explain # table_name % mal # name % clob # type % 94 # length function user.s4_1():void; X_32:void := querylog.define("explain select name from sys.functions;","default_pipe",21); X_16 := bat.new(nil:oid,nil:str); X_24 := bat.append(X_16,"sys.functions"); X_19 := bat.new(nil:oid,nil:str); X_26 := bat.append(X_19,"name"); X_20 := bat.new(nil:oid,nil:str); X_27 := bat.append(X_20,"varchar"); X_21 := bat.new(nil:oid,nil:int); X_29 := bat.append(X_21,256); X_23 := bat.new(nil:oid,nil:int); X_31 := bat.append(X_23,0); X_1 := sql.mvc(); C_2:bat[:oid] := sql.tid(X_1,"sys","functions"); X_5:bat[:str] := sql.bind(X_1,"sys","functions","name",0); (C_8,r1_8) := sql.bind(X_1,"sys","functions","name",2); X_11:bat[:str] := sql.bind(X_1,"sys","functions","name",1); X_13 := sql.delta(X_5,C_8,r1_8,X_11); X_14 := algebra.projection(C_2,X_13); sql.resultSet(X_24,X_26,X_27,X_29,X_31,X_14); end user.s4_1; # optimizer.mitosis() # optimizer.dataflow() * Opened a MAL session, initialized sql, and pasted the function above: mal>sql.init(); mal>function user.s4_1():void; mal> X_32:void := querylog.define("explain select name from sys.functions;","default_pipe",21); mal> X_16 := bat.new(nil:oid,nil:str); mal> X_24 := bat.append(X_16,"sys.functions"); mal> X_19 := bat.new(nil:oid,nil:str); mal> X_26 := bat.append(X_19,"name"); mal> X_20 := bat.new(nil:oid,nil:str); mal> X_27 := bat.append(X_20,"varchar"); mal> X_21 := bat.new(nil:oid,nil:int); mal> X_29 := bat.append(X_21,256); mal> X_23 := bat.new(nil:oid,nil:int); mal> X_31 := bat.append(X_23,0); mal> X_1 := sql.mvc(); mal> C_2:bat[:oid] := sql.tid(X_1,"sys","functions"); mal> X_5:bat[:str] := sql.bind(X_1,"sys","functions","name",0); mal> (C_8,*r1_8*) := sql.bind(X_1,"sys","functions","name",2); mal> X_11:bat[:str] := sql.bind(X_1,"sys","functions","name",1); mal> X_13 := sql.delta(X_5,C_8,r1_8,X_11); mal> X_14 := algebra.projection(C_2,X_13); mal> sql.resultSet(X_24,X_26,X_27,X_29,X_31,X_14); mal>end user.s4_1; MAPI = (monetdb) /tmp/.s.monetdb.54500 QUERY = end user.s4_1; ERROR = !TypeException:user.s4_1[17]:'sql.delta' undefined in: X_13:any := sql.delta(X_5:bat[:str],C_8:bat[:oid],*r1_8:bat[:any]*,X_11:bat[:str]); mal> The error is easy to find: sql.delta() expects r1_8 to be of type :bat[:str], but it gets :bat[:any] instead. * If I fix it manually: mal>function user.s4_1():void; mal> X_32:void := querylog.define("explain select name from sys.functions;","default_pipe",21); mal> X_16 := bat.new(nil:oid,nil:str); mal> X_24 := bat.append(X_16,"sys.functions"); mal> X_19 := bat.new(nil:oid,nil:str); mal> X_26 := bat.append(X_19,"name"); mal> X_20 := bat.new(nil:oid,nil:str); mal> X_27 := bat.append(X_20,"varchar"); mal> X_21 := bat.new(nil:oid,nil:int); mal> X_29 := bat.append(X_21,256); mal> X_23 := bat.new(nil:oid,nil:int); mal> X_31 := bat.append(X_23,0); mal> X_1 := sql.mvc(); mal> C_2:bat[:oid] := sql.tid(X_1,"sys","functions"); mal> X_5:bat[:str] := sql.bind(X_1,"sys","functions","name",0); mal> (C_8,*r1_8:bat[:str]*) := sql.bind(X_1,"sys","functions","name",2); mal> X_11:bat[:str] := sql.bind(X_1,"sys","functions","name",1); mal> X_13 := sql.delta(X_5,C_8,r1_8,X_11); mal> X_14 := algebra.projection(C_2,X_13); mal> sql.resultSet(X_24,X_26,X_27,X_29,X_31,X_14); mal>end user.s4_1; mal> Yay, it works. ** The reason I write this email:* - I suspect that the output of EXPLAIN is not meant to necessarily be re-parsable, but just to be informative - Nevertheless, it really is almost re-parsable. I tried a much larger query, (2000+ lines of MAL), and this was the only detail. It looks like fixing that small detail could be worth it. - Also, I wonder if this is, in fact, a bug. Binds to access types 0 and 1 do get the right type information. Why not access type 2?

2 2

More info about the MAL plan cache
by Roberto Cornacchia 07 Oct '16

07 Oct '16

Hi there, I have a couple of questions about the plan cache. 1) I know it caches MAL a user-functions for a given "SQL query". What does "SQL query" really means? The unparsed SQL script? The parse tree? The relational algebra tree? The normalized/optimized algebra tree? 2) What are the conditions for the cache to be used? Does it kick in only for some types of queries? I noticed some rather large queries (which take 300-400ms just to be translated from SQL to MAL) are not being cached (or so it seems - how can I check?) 3) Hypothetically speaking, would it be possible to interleave SQL and MAL queries? I am well aware that using MAL directly is not recommended - I only would like to know about feasibility. My typical use case is: START TRANSACTION; a few INSERT INTO "parameters"; SELECT * FROM my_big_view; -- my_big_view uses table "parameters" ROLLBACK; -- values inserted into "parameters" are flushed View "my_big_view" can be a rather large query. It takes an awful lot of time just to get a MAL plan for it. Suppose I have already a MAL plan for it, at client side. I could start a MAL session and run it. I guess though, the main problem is that it wouldn't be in the same transaction as the preceding (sql) INSERT INTO, so it wouldn't get the values inserted into "parameters". Would the only possibility be to move also the INSERT INTO into MAL? So skip SQL altogether? Thanks for you help, Roberto

1 1

slowdown after delete
by Stefano Fioravanzo 03 Oct '16

03 Oct '16

Hello, In my db I have a table with ~300million rows on which I have to perform ~1k aggregation queries periodically. I have noticed that if I delete even a small amount of rows from the table I have a greatly reduced performance compared to when no delete has been made. Specifically, with no delete the 1k queries complete in about 2 hours, if I delete a few records the task completes in about 10-12 hours. Inserting new rows in the table does not present the issue. Moreover if I dump and restore the table after the delete, everything’s back to normal and the queries execute fast. So the issue seems to be related to the fact that monet does not actually delete the records from the bat file, right? Are you aware of the issue? Is there some kind of vacuum operation available? If not, what is the fastest way to dump or copy and restore the table; or is there any other trick that may solve the issue? Any suggestion would be much appreciated, Thank you Stefano

2 1

Select with 5K+ columns kills server
by Pascal Heus 03 Oct '16

03 Oct '16

Hi: I have a MonetDB 5 server v11.23.7 "Jun2016-SP1 instance hosting a table with over 5600 columns (for the US General Social Survey) If we run a simple query like SELECT * FROM gss7214_v4 limit 2 we get the first two records as expected. However, the same query with all the column names explicitly stated like select "year","id","wrkstat","hrs1","hrs2","evwork","occ","prestige","wrkslf",<...thousands of additional columns - see attached...> from "gss7214_v4" WHERE "year"=1972 LIMIT 2 OFFSET 0; seem to kill the server. The process keeps running but become non-responsive. Now this is on a AWS EC2 t2.small instance with just ~2Gb or memory. Maybe more memory may solve this issue (?). But in any case concerned that the server hangs without warning or error message in the log. Any potential workaround? Suggestion most appreciated best *P

3 2