- users-list - monetdb.org

MonetDB Mar2018 Release - Connection terminates abruptly with no error codes/messages
by Murthy, Gautham 18 Jul '18

18 Jul '18

Hello, MonetDB release - MonetDB Mar2018 We are facing issues when trying to run queries concurrently on the newer version of MonetDB. When 2 or more sessions (create/drop tables) are executed concurrently, sessions get terminated without any error messages. Do we need to explicitly enable error feedback? Does hitting concurrency abruptly terminates the session/connection ? [monetadmin@lnx1490 ~]$ /monet_binaries/MonetDB-11.27.13_PY/bin/mclient mapi:monetdb://lnx1490.ch3.prod.i.com:50105/… [View More]

1 0

MonetDB Crashed - Version Jul2017-SP4 - GDKmmap(327680) fails, try to free up space memory in use=48376596752, virtual memory in use=102304729360
by Murthy, Gautham 12 Jul '18

12 Jul '18

Hello All, We had a crash reported on our MonetDB database, Below are the error messages. Can you share any insights on the below reported issue. There were no memory/cpu crunch during the crash, System had ample resource available. MonetDB Version - Jul2017-SP4 2018-07-03 06:33:40 ERR TSV_PROD_DB_A315293<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315293>: #GDKmmap(327680) fails, try to free up space memory in use=48376596752,virtual memory in use=102304729360<https:… [View More]

1 0

Memory management, 8 years later
by Roberto Cornacchia 28 Jun '18

28 Jun '18

Hi all, 8 yeas after this interesting thread about using ulimit to limit the data segment size ( https://www.monetdb.org/pipermail/users-list/2010-March/004007.html) a few things have changed and I find myself a bit lost again about memory management. At some point (can't remember when) mserver5 stopped respecting ulimit. No matter how I change the limits, it always sees all available physical ram. Does any other mechanism exist? The issue I'm facing all the time, and for which I find very … [View More]

2 2

Troublesome Query
by Doherty, Adam 23 Jun '18

23 Jun '18

Hi all: I'm trying to figure how to calculate the time delta between two events. My events have a timestamp, event source, event type, and an event state and event id. So an event looks like - event_date_time event_source event_type event_state event_id 2018-06-22 09:05:07.000000 MODULE123 TYPEABC ACK 24567890 2018-06-22 11:32:14.000000 MODULE123 TYPEABC UNACK 24812266 This makes up a unique "event", start to finish. However I have hundreds or thousands of entries between and I want be … [View More]

2 1

Performance issues when streaming records from table.
by Manuel Pagliai 20 Jun '18

20 Jun '18

We are interested in adding support to import records from Monetdb tables in the ETL we are developing. We have performance issues when executing queries like: select col0, col1, ... coln from <table> on tables with a large number of records. What I am concerned about is the time spent upfront, before the first record is retrieved: once records become available we can start processing them. For example, on my machine, executing a executing a select * from <table> query on a 68 … [View More]

1 0

Announcement: New Mar2018-SP1 Bugfix release of MonetDB suite
by Panagiotis Koutsourakis 20 Jun '18

20 Jun '18

The MonetDB team at CWI/MonetDB BV is pleased to announce the Mar2018-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <https://dev.monetdb.org/downloads/>. Mar 2018-SP1 bugfix release (11.29.7) Build Environment * On Linux and … [View More]Unix, try not to link to libraries multiple times: that only causes the code (and worse, the variables in those libraries) to be included multiple times. On Windows, we do need to link to libraries multiple times (once for each DLL we create if the DLL references anything from the library) but that doesn't cause the linked library to be loaded multiple times. SQL Frontend * Corrected the definition of view: sys.ids. Bug Fixes * 6562: Sqlitelogictest crash on group by query with not in operator * 6565: Sqlitelogictest crash on complex select query with coalesce call * 6566: Sqlitelogictest unavailable calc.- MAL operations * 6568: Sqlitelogictest crash on complex case query * 6569: Sqlitelogictest select query with not between cause with wrong results * 6570: Sqlitelogictest select coalesce undefined calc * 6572: ordered index Error in optimizer garbageCollector * 6573: Sqlitelogictest crash on complex select query with case statement * 6574: server crashed could not find ordered index while creating a table * 6576: Sqlitelogictest aritmetic expressions with negative numbers handling * 6577: creating temp table kills performance of the original query * 6578: One two-tuple insert gives different results than two single inserts * 6581: Join condition errors. * 6583: Fixed size string concatenation with integer results in fixed size string of size 0 * 6584: SELECT FROM REMOTE TABLE WHERE IS NOT NULL produces wrong result * 6585: Nested Merge tables cause an infinite loop in rel_optimizer * 6587: Sqlitelogictest crash on complex case statement * 6589: Sqlitelogictest crash on complex on complex expression * 6594: Sqlitelogictest crash on complex case statement * 6595: Remote decimal division triggers assertion / returns wrong answer * 6598: Python 3.4 not supported (due to usage of Py_DecodeLocale) * 6600: Sqlitelogictest queries fail to execute * 6601: "where is null" clause on remote table causes problem with next query * 6602: Sqlitelogictest wrong results in IN query * 6603: Sqlitelogictest: Aggregation query with distinct clause produces duplicated rows [View Less]

1 0

pymonetdb or Python UDF
by Doherty, Adam 17 Jun '18

17 Jun '18

Hello all: This is more of curiosity but I have data in my events table currently trapped in a nvarchar field. I am wondering if I can build a table and do execute a select into with a python UDF pulling data from the existing fields plus the values of my nvarchar field. My string data has the following format - "key1=value, key2=val, key3=value"... I want to break out the data first by splitting on the , and then the = Keys would be the columns in the table. I can do this with a python … [View More]

3 3

joinable generate_series with arguments from a table
by Stefan de Konink 14 Jun '18

14 Jun '18

Goodmorning, At this moment there is no functional compatibility between the PostgreSQL and MonetDB with respect to the generate_series function. The range in PostgreSQL is inclusive, in MonetDB exclusive. More importantly generate_series can't be used with argument that is joined from a different table, MonetDB just seems to support a subquery, hence joining that back without a primary is impossible. PostgreSQL; select generate_series(a, b), a from (select 1 as a, 2 as b) as a; … [View More]

4 4

Re: Combine a table-returning function with the source table
by Mark Clements 13 Jun '18

13 Jun '18

Stefan, This is a good question. For the specific example, you are quite correct. However, the motivation for the question came from using the MonetDB-rmath extension and embedded R: often it is more efficient to calculate a number of values (e.g. the confidence intervals and p-values) together and then return all of the values as a table. This probably also reflects that I am a statistician and an old R user. The recent article by Raasveldt suggested that analysis objects from Python could … [View More]be stored natively rather than being serialised BLOBs. This could be further extended to storing R objects natively. Members of the objects could then be extracted as they are needed. Admittedly, this would lose many relational advantages. My suggestion is halfway, where R and Python can be used for analytics, with simple relational storage of the results. Actually, is there any way to optimise a lateral call using BATs? -- Mark On 13 Jun 2018 17:43, Stefan Manegold <Stefan.Manegold(a)cwi.nl> wrote: Hi Mark, just out of curiosity, (in the given case/example) what's wrong with a simple select *, mean-1.96*sd as lci, mean+1.96*sd as uci from test; or create or replace function ci(se double) returns double begin return 1.96*se; end; select *, mean-ci(sd) as lci, mean+ci(sd) as uci from test; in case you really want/need to avoid the "redundant" occurrence or "1.96" ... Thanks! Best, Stefan ----- On Jun 13, 2018, at 5:30 PM, Mark Clements mark.clements(a)ki.se wrote: > Jennie, > > Thank you for replying to this inquiry - it's appreciated. > > Your solution gives the right result - but it requires changing the > function call and will not generalise easily to other column types or > other number of covariates. If we had another table such as: > > drop table test; > create table test as select 1 as id, 2 as sex, cast(3 as double) as > mean, cast(4.0 as double) as sd; > -- and repeating the function > create or replace function cis(mu double, se double) returns table(lci > double, uci double) > begin return select mu-1.96*se, mu+1.96*se; > end; > > we can get all of the test table combined with the confidence interval by: > > -- row_number() > select * from > (select *, row_number() over() as rid from test) as t1 > natural join > (select *, row_number() over() as rid from cis((select mean,sd from > test))) as t2; > -- or using lateral > select * from test, lateral cis(test.mean,test.sd); > > The row_number solution is fast, and the lateral solution is simple. I > was hoping for: > > select *, cis(mean,test).* from test; > > Again, thank you for your help. > > -- Mark > > On 06/08/2018 01:53 PM, Ying Zhang wrote: >> Hai Mark, >> >> I’m just wonder if this gives you wat you want: >> >> create function cis(mu double, se double) >> returns table(mu_out double, lci double, uci double) >> begin >> return select mu, mu-1.96*se, mu+1.96*se; >> end; >> >> Regards, >> Jennie > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/<http://www.CWI.nl/~manegold/> | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list [View Less]

1 0

Combine a table-returning function with the source table
by Mark Clements 13 Jun '18

13 Jun '18

For using the MonetDB-rmath extension, I was interested in using table-returning functions to return multiple values. Is there a fast and elegant way to combine the results with the source table in SQL? To make this concrete, consider some test data and a function to calculate a 95% confidence interval from a mean and standard error: drop table test; create table test as select cast(value as double) as value from generate_series(0,5); drop function cis(double,double); create function cis(mu … [View More]

3 4