- developers-list - monetdb.org

[Monetdb-developers] How to create a binary load files with string values?
by Mark Kerzner 07 Oct '11

07 Oct '11

Hi, I've tried quite a few things (creating the files/strings with 0 at the end, with 0 and \n at the end, fixed length, etc.) but I still get an error sql>copy into sku from ('/home/mark/MonetDbData/myfile); SQLException:sql:failed to attach file /home/mark/MonetDbData/myfile I know that I can load integer values, because that works, so the problem must be in how I am formatting strings for MonetDB binary load. Are there any examples or any dumps to look at? Thank you, Mark

3 4

[Monetdb-developers] Announcement: New Aug2011-SP1 bugfix release of MonetDB suite
by Fabian Groffen 30 Sep '11

30 Sep '11

The MonetDB team at CWI/MonetDB BV is pleased to announce the Aug2011-SP1 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/>. Aug 2011-SP1 bugfix release SQL * Fixed a bug when using default values for interval columns. Bug 2877. * A bug was fixed where deleted rows weren't properly accounted for in all operations. This was bug 2882. * A bug was fixed which caused an update to an internal table to happen too soon. The bug could be observed on a multicore system with a query INSERT INTO t (SELECT * FROM t) when the table t is "large enough". This was bug 2883. Client Package * Fixed Perl DBD rowcount for larger results, bug #2889 * Perl: We now distinguish properly between TABLE and GLOBAL TEMPORARY (the latter are recognized by being in the "tmp" schema). * Perl: fixed a bunch of syntax errors. This fixes bug 2884. With thanks to Rémy Chibois. * Perl: Fixed DBD::monetdb table_info and tabletype_info. This fixes bug 2885. With thanks to Rémy Chibois. * mclient: fix display of varchar columns with only NULL values. * Fixed a bug in mclient/msqldump where an internal error occurred during dump when there are GLOBAL TEMPORARY tables. MonetDB5 Server * Fixed a problem where MAL variables weren't properly cleared before reuse of the data strucutre. This problem could cause the data flow scheduler to generate dependencies between instructions that didn't actually exist, which in turn could cause circular dependencies among instructions with deadlock as a result. Bugs 2865 and 2888. Bug Fixes * 2865: mserver5 --set gdk_nr_threads=2 --forcemito: deadlock during first SQL client connect on virgin (empty) DB * 2877: assertion failed when creating table with default interval * 2879: A SELECT query fails to produce some results * 2882: Delete of some records don't work on Monetdb * 2883: insert into t (select * from t) bogus in multithreaded execution * 2884: Mapi.pm and GetInfo.pm syntax fixes for array usage * 2885: DBD::monetdb table_info error * 2887: Division by zero error, when should not divide at 0 * 2888: test/null/null_in_case.sql hangs server * 2889: Mapi::getBlock is erasing query count on second call

1 0

Re: [Monetdb-developers] MonetDB: default - The sample module for the monetdb5 server.
by Lefteris 27 Sep '11

27 Sep '11

Roberto, we were also puzzled with which syntax to adopt. The first issue is that the syntax is also dictating in a way implementation (although that sounds not correct:)). A syntax that has a order by rand() implies that you are attaching random numbers to the rows and then sort on that. This sorting is very coslty and it is the worst case since you have random order to begin with. Something like that would cost NlogN where N is the size of rows. Moreover, that syntax implies that you are sampling the base table (or some intermediate result anyway) and then "continue" the query evaluation. however sampling is not a very kind operator and cannot by easily pushed down: a sample of a join is not equal with the join of 2 samples. These are in my view the implications (or benefits!) of such a syntax. So MonetDB could as well support the syntax you propose and should! On the other hand, the syntax SAMPLE we just introduce, is applied solely on the final result set of the query, after or query evaluation is done. The algorithm has a complexity of S, where S is the size of the sample (as opposed to NlogN of the previous). Also , the sample operator has the same semantics of limits, it is an operator that shows a limited (random) portion of the entire (usually computed) result. that means that all joins, predicated etc. have been evaluated before a sample is taken. Of course we would love to have that functionality in sub-queries too, hence the workaround. What is really missing now is a function where the table definition is a parameter, indeed! With this 2 views in mind, I would suggest that both syntax are necessary to exist! Each of them serving a different purpose (and different underline implementation eventually). the other solution would be to allow SAMPLE is subqueries and "depart" from the SQL spirit. What do you think? lefteris On Mon, Sep 26, 2011 at 10:41 AM, Roberto Cornacchia < roberto.cornacchia(a)gmail.com> wrote: > Lefteris, I would like to add something about the syntax (and semantics) of > this feature. > See below > > >> +/* >> + * @- Uniform Sampling. >> + * >> + * A new SQL operator has been added to support sampling the result of a >> query. >> + * The syntax for sampling is: >> + * SELECT ... FROM ... WHERE ... SAMPLE s >> + * >> + * where s if is an integer greater than 1, it defines the number of rows >> to be >> + * in the sample. If s is a double between [0.0,1.0] the it refers to the >> + * percentage of the result to be sampled. That is if s=0.3 then the >> sample >> + * will be 30% the size of the query result. >> + * >> > > It has been discussed already whether this choice or an explicit PERCENT is > more intuitive. > Personally, I don't care much about that. > > >> + * SAMPLE is been treated as LIMIT, ORDER BY, etc., that means that it >> can only >> + * be in the outer most SELECT clause, i.e., SAMPLE cannot appear in a >> + * subquery. > > > This is much more interesting to me. > I see that SAMPLE fits in the same semantics as LIMIT. In fact, you can > think of it as a special LIMIT. > What I don't like about it, is that it then shares the same limitations > (which are absurd, as the whole SQL often is, from my point of view), which > is what you mention above about the subquery. > You suggest a workaround here: > > However, if this is needed, then one may define a function, for >> + * example >> + * >> + * CREATE FUNCTION mysample () >> + * RETURNS TABLE(col a,...) >> + * BEGIN >> + * RETURN >> + * SELECT a,... >> + * FROM name_table >> + * SAMPLE 100; >> + * end; >> + * >> + * and then use function mysample() for example to populate a new table >> with >> + * the sample. E.g., >> + * >> + * INSERT INTO sample_table (SELECT * FROM mysample()); >> + * >> + * > > > To me, writing a function with an hard-coded table name in it is... weird! > Like writing a function that can only do 2+2. > (This could be much better if table identifiers were allowed as function > parameters, a feature that I'd love to see implemented!). > > To this, I much prefer a solution based on the window functions RANK() > OVER() and ROW_NUMBER() OVER() which luckily are implemented in MonetDB, > and the function rand(). > > Your example above becomes a regular query that has no nesting-issue: > > SELECT a,b,c > FROM ( > SELECT a,b,c, ROW_NUMBER() OVER(ORDER BY rand()) as my_rank > FROM name_table > ) as x > where x.my_rank <= 100; > > Replace ROW_NUMBER() with RANK() to allow duplicates in the random sample. > > I'm not comparing the efficiency of the two solutions here, your solution > could well be more efficient. > I'm only comparing them from a usability point of view. > > I noticed that my own usage of the LIMIT clause is becoming less and less > frequent (in favour of the syntax I report here) because of the nesting > issue. > What I am wondering is, how much useful is to introduce a new syntax for > the sampling that has this (important, in my view) limitation? How often > would you need a sample in the outermost select clause only? > > Cheers, > Roberto > > > _______________________________________________ > Checkin-list mailing list > Checkin-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/checkin-list > >

2 1

[Monetdb-developers] MacPorts Portfiles
by Rémy Chibois 20 Sep '11

20 Sep '11

Hi all, You'll find attached an archive containing two portfiles for compiling and installing MonetDB using the MacPorts system. Can you please have a look at it ? In order to use these portfiles, please refer to: http://guide.macports.org/#development.local-repositories If this is of interest for someone, do you want me to submit this port to the MacPorts Project ? Regards, -- Rémy Chibois

2 3

[Monetdb-developers] Announcement: New Apr2011-SP2 bugfix release of MonetDB suite
by Sjoerd Mullender 16 Sep '11

16 Sep '11

The MonetDB team at CWI/MonetDB BV is pleased to announce the Aug2011 feature release of the MonetDB suite of programs. The default width for OID columns in the database is now 64 bits for all 64-bit architecture. In releases up to this release, the default width on 64-bit Windows was still 32 bits. This means that with the current release it is possible to have tables with more than about 2 million rows even on Windows (as long as you use a 64-bit build). More information (including release notes) on this release is available at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. -- Sjoerd Mullender

1 0

[Monetdb-developers] Announcement: New Apr2011-SP2 bugfix release of MonetDB suite
by Sjoerd Mullender 29 Jul '11

29 Jul '11

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2011-SP2 bug fix release of the MonetDB suite of programs. This is the first release with support for Fedora 15. Fedora 13 is no longer supported. One important bug fix has to do with columns with string (char/varchar) values. There was a bug that could cause exponential growth of the file holding the data for the column. This bug has been fixed. If you have a database that is much larger than you would expect for the amount of data, this might be the cause. The remedy is to dump and restore that database (or just the affected tables). Apart from using up too much space, this bug didn't have any other consequences. More information (including release notes) on this release is available at <http://www.monetdb.org/OldReleaseNotes/Apr2011>. As usual, the download location is <http://dev.monetdb.org/downloads/>. -- Sjoerd Mullender

1 0

[Monetdb-developers] New Aug2011 stable branch created
by Sjoerd Mullender 11 Jul '11

11 Jul '11

I have created a new stable branch for the next feature release. This branch was created in order to stabilize the code before the feature release. >From now on *only* bug fixes may go to this new stable branch. I reserve the right to undo any changes that do not comply with this rule. I also reserve the right to admit changes that are not strictly bug fixes. The name for the branch is Aug2011. So in each of your stable checkout directories you can do hg pull hg update -rAug2011 to update to this new branch. -- Sjoerd Mullender

1 0

Re: [Monetdb-developers] Tuple reconstruction algorithm
by Stefan Manegold 04 Jul '11

04 Jul '11

Hi Tim, [I prefer to keep the mailing list involved to share the discussion; I hope you don't mind ... ;-) ] On Mon, Jul 04, 2011 at 07:30:36PM +0200, Tim Speier wrote: > Hello Stefan, > > thanks for your fast reply. > > I am especially interested in tuple reconstruction mechanisms in > column-oriented databases. So I'm not a user. I see; please also consider reading our papers (cf., http://repository.cwi.nl/search/searchrepository.php?stheme=INS1) in particular http://repository.cwi.nl/search/fullrecord.php?publnr=14290 http://repository.cwi.nl/search/fullrecord.php?publnr=11117 and/or these PhD these: http://repository.cwi.nl/search/fullrecord.php?publnr=14832 http://repository.cwi.nl/search/fullrecord.php?publnr=14301 http://repository.cwi.nl/search/fullrecord.php?publnr=16706 Basically, "even" in main memory, sequential access is fast, while random access is slow. > > > Further, for any set of BATs the represent the individual columns of a > > relational table, the BATs' "heads" (first/left column) are aligned and > > consist of OIDs that form a dense (i.e., increment == 1) ascending sequence > > of integer numbers starting at a given offset (usually 0). Thus, these OIDs > > can indeed be seen as tuple-/record- identifiers. Be aware, though, that > > these OIDs are only used internally and not available as tuple-/record- > > identifiers in SQL. > > Given their denseness properly, these OIDs are usually not materialized > > (read: physically stored) but rather computed on the fly, and they > > effectively indicate (module offset) the physical position of the respective > > record's attribute values in all BATs involved. > > I am sorry, but it is not yet clear to me. Let me create an example: > A relation R with two attributes X and Y. It consists of the following > tuples: > > +---+-----+ > | X | Y | > +---+-----+ > | A | 300 | > +---+-----+ > | B | 100 | > +---+-----+ > | C | 400 | > +---+-----+ > > Now let's create two BATs: each record gets its own OID and, my naive > approach, map each attribute values to a BAT: > > BAT X BAT Y > +---+---+ +---+-----+ > |OID|VAL| |OID|VAL | > +---+---+ +---+-----+ > | 0 | A | | 0 | 300 | > +---+---+ +---+-----+ > | 1 | B | | 1 | 100 | > +---+---+ +---+-----+ > | 2 | C | | 2 | 400 | > +---+---+ +---+-----+ > > Now I could reconstruct each tuple by its OID and the index. But I would > loose some advantages of the column-oriented databases. Like which? I guess you mean having each column sorted individually? > A more sophisticated approach would be to store values in ascending > order like this: > > BAT X BAT Y > +---+---+ +---+-----+ > |OID|VAL| |OID|VAL | > +---+---+ +---+-----+ > | 0 | A | | 1 | 100 | > +---+---+ +---+-----+ > | 1 | B | | 0 | 300 | > +---+---+ +---+-----+ > | 2 | C | | 2 | 400 | > +---+---+ +---+-----+ > > But how can I now reconstruct the tuples efficient now? > Is there an additional index structure (as you said in > your foregoing email). ? In the first case, tuple reconstruction is cheap (almost "for free"), as columns are physically aligned, and hence, highly efficient in-order positional lookup can be exploited for tuple reconstruction. Obviously, with keeping the columns physically aligned does not allow to order them individually according to their values. Thus without further indeces, value-lookups using binary search are only possible for on sorted column; for all other columns, some secondary index structure would be required. MonetDB only used (automatically) hash-indeces for single-value (point-)lookups, but resorts to (highly optimized) sequential scans for any range lookups on non-sorted columns. In the second case, value-lookups can be done (reasonably) efficient using binary search on each column, however tuple-reconstruction (i.e., OID lookups) can no longer be done with simple highly efficient in-order positional lookups. In that case, MonetDB would (automatically on-the-fly) build a hash-index on the non-ordered (non-dense) OID columns. In general, there is no such thing as a "free lunch" ... ;-) Hope this helps you further --- don't hesitate to keep asking in case you have more questions ... Gruß, Stefan > > I'm very sorry for my innocent questions. > Tim -- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

3 2

[Monetdb-developers] Tuple reconstruction algorithm
by Tim Speier 04 Jul '11

04 Jul '11

Dear all, I'm trying to understand the tuple reconstruction algorithm in MonetDB. Maybe someone can give me a short overview to understand it or the place in documentation where I can do some research. As far as I understood, the BAT consists of object-ids and values. Is the object-identifier the same as the record- or tuple-identifier? If it so, is there a special mechanism to position a specific oid in a BAT or must it be scanned to find a oid? And if not, how can I determine the corresponding values in different BATs to reconstruct a tuple? Or am I totally wrong and there is an obvious way to reconstruct tuples from different BATs? Thanks in advance, Tim

2 1

[Monetdb-developers] PhD opening at CWI - Adaptive indexing techniques for modern database systems
by Stratos Idreos 23 Jun '11

23 Jun '11

Subject: Adaptive indexing techniques for modern database systems. The Centrum Wiskunde & Informatica (CWI) has a full time vacancy for a talented PhD candidate in the Database Architectures group (http://www.cwi.nl/en/research-groups/Database-Architectures) The position is in the broad area of adaptive indexing techniques for modern database systems. Adaptive indexing is a major challenge in database research; it tries to completely alleviate the need for indexing and tuning steps leading to database systems that require no physical design set-up and can cope immediately with any workload. This leads to database systems that are instantly usable and thus more applicable to numerous new applications that now consider databases too slow, too complex and too old. This position will focus specifically on adaptive indexing for extremely large databases where traditional indexing represents a major bottleneck. The Database Architectures group is one of the world-wide leading groups in system-oriented database research, e.g., it received the 10-year best paper award at VLDB 2009 for their pioneering research on hardware-aware database technology and the column-store model and the 2011 ACM SIGMOD Jim Gray Dissertation award for the database cracking work. The group has a unique methodology that combines fundamental database research with active participation of all group members in database engineering, centred around the open-source analytical database system MonetDB (http://www.monetdb.org/) An internship at one of the partnering database research labs world-wide is considered part of the PhD track. REQUIREMENTS The PhD candidates must have a master degree in computer science (or a 5 year diploma), (specialisation databases, distributed systems, software engineering) mathematics, or related fields. Preferable qualifications for candidates include proven research talent, system programming skills (C/C++), practical experience with using and implementing database systems, and software development in a team. Candidates are expected to have an excellent command of English, and good academic writing and presentation skills. CENTRUM WISKUNDE & INFORMATICA (CWI) Founded in 1946, the Centrum Wiskunde & Informatica (CWI) is the national research institute for mathematics and computer science in the Netherlands. It is located at the Science Park Amsterdam and is part of the “Netherlands Organisation for Scientific Research” (NWO). The institute is internationally focused and renowned for its high quality research, a substantial fraction of which is carried out in the framework of (inter)national programs. Over 160 researchers conduct pioneering research and share their acquired knowledge with society. More than 30 researchers are employed as professors at universities. The institute has generated twenty-one spin-off companies. CWI maintains excellent relations with industry and the academic world, both in the Netherlands as well as abroad. After their research careers at CWI, an increasing number of young staff members find employment in these sectors or in spin-off companies that are based on research results from CWI. Of course, library and computing facilities are first-rate. CWI's non-scientific services to its personnel include career planning, training and courses, assistance in finding housing, and tailor-made solutions to problems that may occasionally arise. TERMS AND CONDITIONS The terms of employment are in accordance with the Dutch Collective Labour Agreement for Research Institutes ("CAO-onderzoeksinstellingen"). Moreover CWI offers attractive working conditions, including flexible scheduling and help with housing for expat employees. The gross monthly salary, for an employee on a full time basis, is €2,037 during the first year and increases to €2,610 over a four year period. CWI also offers excellent and flexible terms of employment, including an employee pension fund. INFORMATION Additional information can be obtained from: dr. Stratos Idreos, email stratos.idreos(a)cwi.nl, telephone +31(0)20 592 4169. or dr. Stefan Manegold, email stefan.manegold(a)cwi.nl, telephone +31(0)20 592 4212, or prof. dr. Martin Kersten, email martin.kersten(a)cwi.nl, telephone +31(0)20 592 4066. APPLICATION Please send your application before 31 July 2011 to: pd(a)cwi.nl. Applications should include a detailed CV, a motivation letter, a list of your M.SC. courses and grades, a copy of your master thesis, and preferably a list of publications.

1 0