- users-list - monetdb.org

[MonetDB-users] MonetDB as server side environment
by bryan rasmussen 18 May '08

18 May '08

Hi, I'm thinking about moving some applications I have running in eXist to MonetDB, just to gauge performance and possibilities. These applications take parameters from the querystring, is there an example of accessing the querystring in a MonetDB Xquery XQL file when it runs? Best Regards, Bryan Rasmussen

3 2

[MonetDB-users] MonetDB V 4.22 installation on Mac OS X 10.5.2 and Mac OS X 10.4.0.
by Irini Fundulaki 16 May '08

16 May '08

Hi, I have the following problems with MonetDB XQuery Release Feb 2008, V. 4.22 on Mac OS X 10.5.2 and Mac OS X 10.4.0. 1) Mac OS X 10.5.2: a) I was not able to install MonetDB from sources. I downloaded MonetDB-Feb2008-SuperBall.bz2 and followed the instructions in http://monetdb.cwi.nl/projects/monetdb//MonetDB/Documentation/Linux-Install…. I successfully installed MonetDB, clients and MonetDB4-server, but when installing MonetDB4-XQuery I had the following error: Undefined symbols: "_embedded_mil", referenced from: _main in prog.o ld: symbol(s) not found collect2: ld returned 1 exit status make[5]: *** [Mbeddedxq] Error 1 make[4]: *** [all] Error 2 make[3]: *** [all-recursive] Error 1 make[2]: *** [all] Error 2 make[1]: *** [all-recursive] Error 1 make: *** [all] Error 2 b) by calling monetdb-install.sh. MonetDB was installed with no problems. Nevertheless, mclient with a simple query (e.g., <example>hello</example) stalled. Mserver stalled when executing xmldb_print(); 2) Mac OS X 10.4.0 a) I was able to install MonetDB XQuery Release Feb 2008, V. 4.22 using monetdb-install.sh. Mserver did not stall with xmldb_print(). mclient for the same query exhibited different behaviors: that is, sometimes it stalled, and others not (again, the same simple query as before). I had downloaded MonetDB on September 2007, and installed it on Mac OS X 10.4.0. I had no problem with that version. Thanks for looking into it, Irini

1 0

[MonetDB-users] multi-query optimization?
by Chinyi Woo 16 May '08

16 May '08

Hello, I have a set of xqueres, which need to be run against a xml doc. In my xqueries, there are many common subexpressions. May I ask you whether MonetDB does any multi-query optimization, or the current version of MonetDB runs them sequentially? Thanks Chinyi

2 1

[MonetDB-users] question about composite uniqueness constraints
by Drew Sky 15 May '08

15 May '08

Hi, I have a question about creating composite uniqueness constraints in monetdb. Say I create the following table called test_property with 3 columns: subject, p1, p2. I want uniqueness contraints for each pair of (subject, p1) and (subject, p2). I try to do this with the following: sql>create table test_property(subject integer, p1 integer, p2 integer, unique(subject, p1), unique(subject, p2)); sql>\d test_property CREATE TABLE "sys"."test_property" ( "subject" int, "p1" int, "p2" int, CONSTRAINT "test_property_subject_p2_unique" UNIQUE ("subject"), CONSTRAINT "test_property_subject_p1_unique" UNIQUE ("subject", "p2", "p1") ); But somehow, I end up with a constraint on "subject" by itself and one on ("subject", p2, p1). Is this a bug in monetdb or am I missing something? I ran the exact same commands in postgres and I got my expected results (2 uniqueness contraints on (subject, p1) and (subject, p2)). I tried also creating the table first, then adding columns and the uniqueness constraint after using ALTER TABLE, but I got the same results. Does anyone see something I'm doing wrong? Thanks Andrew

3 2

[MonetDB-users] Space measurement and data compression in monetdb
by Iftikharn 15 May '08

15 May '08

I have two queries regarding monetdb: Is it possible to measure that how much space/memory each table has occupied? and Is is possible to compress the data (by using a command...)? Regards, Iftikhar -- View this message in context: http://www.nabble.com/Space-measurement-and-data-compression-in-monetdb-tp1… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] broken links on prepared queries
by Chinyi Woo 14 May '08

14 May '08

Hello I have a set of xqueries and want to convert all of them into prepared queries for better performance. I want to see several concrete examples to know how to do this. However, the link at http://monetdb.cwi.nl/XQuery/Version4/Documentation/PreparedQueries/q.xq and http://monetdb.cwi.nl/XQuery/Version4/Documentation/PreparedQueries/m.xq are broken. Could anyone please fix it? thanks Chinyi

2 1

[MonetDB-users] How to measure the performance of a SQL query
by Allan12 14 May '08

14 May '08

How could we measure the performance of a SQL query. I have a table with 100,000 rows and if I peform a SQL query on it, what statistical data I could get. -- View this message in context: http://www.nabble.com/How-to-measure-the-performance-of-a-SQL-query-tp17227… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] SQL Query performance
by Allan12 14 May '08

14 May '08

How could we measure the performance of a SQL query. I have a table with 100,000 rows and if I peform a SQL query on it, what statistical data I could get in addition to time. -- View this message in context: http://www.nabble.com/SQL-Query-performance-tp17227222p17227222.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] How could I run a set of XQuery in a batch?
by Chinyi Woo 14 May '08

14 May '08

Hello, everyone May I ask you how to run a set of XQueries in a batch? I am using MonetDB v4.22.0 on Redhat Linux. I have a set of XQueries. I want to run them against a single xml document and collect the running time. Currently I am only able to submit one XQuery at a time at the mclient commandline. Is there anyway for me to send all my XQueries in a batch and get the total running time? I also have another question. Does MonetDB handle multi-query optimization? I mean if more than one query have common subexpressions, will MonetDB evaluate them just once? Thanks in advance. Chinyi

2 1

[MonetDB-users] A sanity check on MondetDB/XQuery performance
by Nils Grimsmo 13 May '08

13 May '08

Using the "super-ball" from February. I am running some queries, and just want to check if my performance is sane. Are there more indexes I can ask to get built to increase performance? I don't ask for an accurate verification, just a hint of whether or not we are within an order of magnitude of what seems right :) $ Mserver --dbinit="module(pathfinder);" --set mapi_port=60000 $ echo 'pf:add-doc("path/dblp.xml", "dblp.xml")' | mclient --lx -p60000 --time Timer 73068.138 msec $ echo 'pf:documents()' | mclient -lx -p60000 <document updatable="false" url="path/dblp.xml" collection="dblp.xml">dblp.xml</document> $ cat dblp_q7.xq count(doc("dblp.xml")/dblp/*[author="Michael Stonebraker" and author="Hector Garcia-Molina" and year > 1950 ]/title) $ mclient -lx -p 60000 --time < dblp_q7.xq 4 Timer 15512.186 msec (Repeated multiple times for warm-up) My dblp.xml is 441 MB. Mserver uses 1.9 of 3.4 GB memory. $ cat /proc/cpuinfo <snip> model name : AMD Athlon(tm) 64 Processor 3500+ cpu MHz : 2210.763 cache size : 512 KB The dbfarm resides on a WD740GD (http://techreport.com/articles.x/6390) Klem fra Nils

3 3

[MonetDB-users] mclient command line history question
by Drew Sky 12 May '08

12 May '08

Hello, I am having trouble using the command line history feature of mclient. After I run a query at the command line, I can't re-use the same query by pressing the up-arrow. From documentation, it seems like I should be able to do this. Is this a feature I should have compiled in, when building from source code? When I press the up-arrow after a query I get sql>^[[A I've tried adding the --history and --interactive options but it doesn't help. I've also tried changing the terminal setting to other modes (I'm using linux) but this doesn't help either. Can anyone help me out? Thanks, Andrew ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

2 1

[MonetDB-users] php5 and windows
by Michiel Kamermans 08 May '08

08 May '08

Hi all, I've been hearing interesting things about MonetDB so I figured I'd see how it performed with the data that my website uses (several million cross-referencing records of Japanese/English dictionary data) by installing it for use with PHP5... but I ran into a problem come actual PHP interfacing. Which steps must be taken to get PHP5 to properly interface with MonetDB on a windows machine? The online documentation (http://homepages.cwi.nl/~mk/MonetDB/The-PHP-Library.html) has a wonderful, if slightly useless, "TODO" for the windows instruction section, and the self-help approach of simply placing the php_monetdb.dll file in PHP's extensions directory and adding an "extension=php_monetdb.dll" to the PHP.ini file leads to PHP throwing up a terminal error when it then tries to run. Any help is appreciated. - Mike Kamermans

1 0

[MonetDB-users] Limit statements
by Henrik Vendelbo 07 May '08

07 May '08

The section on sorting and limit 3.1.7.2 Select lists, distinct, sorting, limit Is very brief and doesn't seem to describe limit/sort, does this mean that LIMIT/OFFSET is supported or that ROW_NUMBER should be used? Henrik

2 1

[MonetDB-users] MonetDB/XQuery not coming back
by Richard Eckart 06 May '08

06 May '08

Hi there, I am looking again into MonetDB/XQuery. A colleague of mine ran a little test on MonetDB/XQuery and made the experience that when starting the server and importing some documents, a query is possible. But when killing the server (running as deamon - killed with CTRL-C), restarting it the same query never comes back. He tried this under FreeBSD and I am experiencing the same problem now on Mac OS X 10.4.11. I turned on debug logging (level 255) but did not see anything that would help me. The only thing I really did see were lines like this while the client was hanging and the CPU running at 100% load. # batstamp(-1418) = -292 # batstamp(1663) = -84581 # batstamp(1662) = -84582 # batstamp(1662) = -84583 # batstamp(-1659) = -84584 # batstamp(1662) = -84585 # batstamp(1449) = 0 # batstamp(1633) = 0 Killing the client the server would still be running producing these lines with the numbers keeping on sinking (getting more negative). After killing the server (CTRL-C) and restarting it I can browse the database using the web interface, but whenever I run the query it goes bye-bye. The query that ran successfully directly after import but not after restart is this: > declare namespace leveler="urn:xmlns:sfb441:leveler"; > > let $lexical := > pf:documents()[@collection eq 'tuebadz' and > fn:starts-with(., 'Corpus-Lexical')]/fn:string(text > ()) > > let $verbs := ( > for $doc in $lexical > return fn:doc($doc)//tok[fn:starts-with(fn:string(pos/ > @leveler:text), "V")] > ) > > return element results { > attribute matches { count($verbs) }, > for $verb in $verbs/fn:string(orth) > order by $verb > return element verb { $verb } > } If I slightly modify the query removing /fn:string(orth), the query runs well. I played around a little and whenever I try to access the "orth" element in any way it goes bye-bye. The "tok" elements have another child called "pos" which I can access just fine. The difference is that "orth" contains a text node while "pos" is an empty tag. Example: > <tok id="s_7_n_15"> > <orth>seien</orth> > <pos xmlns:leveler="urn:xmlns:sfb441:leveler" leveler:text="VAFIN" > func="HD" /> > </tok> Do you have any ideas how to fix/handle this problem? Thank you very much. Cheers, Richard

1 0

[MonetDB-users] Tutorial for database scripting language MIL?
by jz＠uva 06 May '08

06 May '08

Hi all, Does someone have a tutorial for the database scripting language MIL? I am currently checking pftijah.mx, but I am wondering whether there is a better option than trying to reverse engineer things. Thanks, junte

3 2

[MonetDB-users] MySQLError ?
by Henrik Vendelbo 06 May '08

06 May '08

I was having a look at the __init__.py module header for MonetSQLdb, and saw the following: __all__ = [ 'BINARY', 'Binary', 'Connect', 'Connection', 'DATE', 'Date', 'Time', 'Timestamp', 'DateFromTicks', 'TimeFromTicks', 'TimestampFromTicks', 'DataError', 'DatabaseError', 'Error', 'FIELD_TYPE', 'IntegrityError', 'InterfaceError', 'InternalError', 'MySQLError', 'NULL', 'NUMBER', 'NotSupportedError', 'DBAPISet', What is the reference to MySQLError doing in the code? I suppose it is just ignored by the interpreter but surely it is a mistake. Warning, OperationalError, and ProgrammingError are not exported by the module. Henrik

2 1

[MonetDB-users] Fwd: [ monetdb-Bugs-1957794 ] How to debug on Windows OS?
by Lefteris 05 May '08

05 May '08

Please read carefully the following comments to avoid opening a third bug with the same question. thank you ---------- Forwarded message ---------- From: SourceForge.net <noreply(a)sourceforge.net> Date: Mon, May 5, 2008 at 10:30 AM Subject: [ monetdb-Bugs-1957794 ] How to debug on Windows OS? To: noreply(a)sourceforge.net Bugs item #1957794, was opened at 2008-05-05 09:50 Message generated for change (Comment added) made by lsidir You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1957794&group_… Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: None Group: None >Status: Closed >Resolution: Rejected Priority: 5 Private: No Submitted By: lxwandy (lxwandy) Assigned to: Nobody/Anonymous (nobody) Summary: How to debug on Windows OS? Initial Comment: Hi.. I want to know about the method of debugging MonetDB on Windows OS. After compiering MonetDB use "namke" , how can I debug it. Is special debug tool needed? ---------------------------------------------------------------------- >Comment By: Lefteris Sidirourgos (lsidir) Date: 2008-05-05 10:30 Message: Logged In: YES user_id=1856546 Originator: NO Hi, this question will be answered if you post it to the monetdb-user mailing list. See http://monetdb.cwi.nl/projects/monetdb/Development/MailChannels/index.html about our mailing lists. This is the bugtracker, a tool to submit bugs rather than questions. ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1957794&group_…

1 0

[MonetDB-users] Django Backend: SQL column types
by Henrik Vendelbo 04 May '08

04 May '08

Hi guys, I am jumping straight into the deep end with Monet. My first task is to implement a backend for Django written in Django. I need to map the field types to SQL column types, and that is for the most part easy, yet a few questions: 1) Is 'integer' accepted, or do I have to use 'int' 2) How do I specify a floating point with specified max_digits and decimal_places? I didn't see support for numeric in the docs. 3) How do I specify unsigned integers? unsigned or check expression? Is there a text field, or should I use varchar/clob ? These are my definitions as they stand so far: DATA_TYPES = { 'AutoField': 'serial', 'BooleanField': 'boolean', 'CharField': 'varchar(%(maxlength)s)', 'CommaSeparatedIntegerField': 'varchar(%(maxlength)s)', 'DateField': 'date', 'DateTimeField': 'timestamp', 'FileField': 'varchar(100)', 'FilePathField': 'varchar(100)', 'FloatField': 'numeric(%(max_digits)s, %(decimal_places)s)', 'ImageField': 'varchar(100)', 'IntegerField': 'int', 'IPAddressField': 'char(15)', 'ManyToManyField': None, 'NullBooleanField': 'bool', 'OneToOneField': 'int', 'PhoneNumberField': 'varchar(20)', 'PositiveIntegerField': 'integer unsigned', # TODO how should this be handled??? 'PositiveSmallIntegerField': 'smallint unsigned', # TODO how should this be handled??? 'SlugField': 'varchar(%(maxlength)s)', 'SmallIntegerField': 'smallint', 'TextField': 'text', 'TimeField': 'time', 'USStateField': 'varchar(2)', } Thanks, Henrik

3 2

[MonetDB-users] Performance issues with queries in SQL
by Nils Grimsmo 01 May '08

01 May '08

I have tables with around 12 million rows and large primary keys. I find the performance for various queries to be rather strange. * Why is Q2 (see below) so much slower than Q1? It is actually faster for me to use Q1 and do the rest of the matching myself. * Why is Q3 so immensely much faster than Q1? I understand that the first part of the primary key is selective in Q3. Do I understand right if Q1 scans until "type" matches, then scans until "name" matches, then scans until "value" matches? No indexes are put on disk, right? Then the matching for Q3 should be scan until "value" matches, which shouldn't be more than at most three times faster, right (if three columns are read instead of one for Q1)? I do Q1 twice to look for cash effects, but none are seen... Could some columns from the table "value" be cached and kept, without giving room for those from "type_name_value"? * Q4 has the same performance as Q1, as expected. * Why isn't Q5 faster than Q6? 82% of the rows have type=3. Fewer should be scanned to find that "!!foo" doesn't exist than for "~~foo", as "!" has a low ASCII value, while "~" is high. (See COUNT queries at the bottom.) Klem fra Nils Q1: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' Q2: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' AND doc = 1 AND nenc = '1.1.1.1' AND pos = 4 Q3: SELECT * FROM value WHERE value = 'Jurgen Annevelink' AND type = 3 AND name = 'author' Q4: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = '__bar' Q5: SELECT * FROM type_name_value WHERE type = 3 AND name = '!!foo' AND value = '__bar' Q6: SELECT * FROM type_name_value WHERE type = 3 AND name = '~~foo' AND value = '__bar' Q7: SELECT * FROM type_name_value WHERE type = 3000 AND name = '__foo' AND value = '__bar' See runs below. Number of rows SELECT COUNT(type) FROM type_name_value % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 12165109 ] Query time: 0.066192 SELECT COUNT(type) FROM value % nilsgri.value # table_name % count_no_nil_type # name % int # type % 8 # length [ 12165109 ] Query time: 0.058111 The PRIMARY KEY of type_name_value is (type,name,value,doc,nenc,pos) The PRIMARY KEY of value is (value,doc,nenc,pos) Q1: Search 3 first, match 3 first. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 12, 8, 3, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.42.2.1", 1071, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 107, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 56, true ] Query time: 1.643438 Q1: Repeat to check for disk cache effects. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 12, 8, 3, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.42.2.1", 1071, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 107, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 56, true ] Query time: 1.615900 Q2: Search entire, match entire. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' AND doc = 1 AND nenc = '1.1.1.1' AND pos = 4 % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 7, 1, 1, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] Query time: 11.073827 Q3: Use table value instead. SELECT * FROM value WHERE value = 'Jurgen Annevelink' AND type = 3 AND name = 'author' % nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value # table_name % value, doc, nenc, pos, type, name, pathid, complete # name % clob, int, varchar, int, int, varchar, int, boolean # type % 17, 1, 12, 8, 1, 6, 3, 5 # length [ "Jurgen Annevelink", 1, "1.1.1.1", 4, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.42.2.1", 1071, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 3, "author", 107, true ] [ "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 3, "author", 56, true ] Query time: 0.000477 Q4: Search 3 first, match 2 first. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 1.617259 Q5: Search 3 first, match 1 first. '!' early in alphabet. SELECT * FROM type_name_value WHERE type = 3 AND name = '!!foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.530727 Q6: Search 3 first, match 1 first. '~' late in alphabet. SELECT * FROM type_name_value WHERE type = 3 AND name = '~~foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.534592 Q7: Search 3 first, match 0 first. SELECT * FROM type_name_value WHERE type = 3000 AND name = '__foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.000407 Number of rows with type and name mathcing. SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name = 'author' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 7 # length [ 2730959 ] Query time: 0.683863 Number of rows with type mathcing. SELECT COUNT(type) FROM type_name_value WHERE type = 3 % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 10020569 ] Query time: 0.032573 SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name < '!!foo' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 1 # length [ 0 ] Query time: 0.161186 SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name < '~~foo' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 10020569 ] Query time: 0.341732 Number of rows with type mathcing. real 0m18.372s user 0m0.005s sys 0m0.002s

2 3

[MonetDB-users] How to set the memory limit for monetDB.
by Kang Zhang 30 Apr '08

30 Apr '08

I am using the latest version of monetDB from the web-site(the super source tarball). My machine is a X86-64 machine with a red-hat installed on it. When I insert data into monetDB, it always requires memory from the system. Then it will be crashed with running out of memory. Is there a way to set the limit for memory used by monetDB ? I've read the docs on the web site and can't get what I need from that. Thank you. -- ------------------------------------------------------------ Kang Zhang Computer Science Dept. Shanghai Jiao Tong University

3 7

[MonetDB-users] Problems with parameters to mapi_param*()
by Nils Grimsmo 28 Apr '08

28 Apr '08

I am having some problems with the functions mapi_param*(). 1. It seems mapi_param() really wants a char* cast to a char**. (See the inner workings of mapi_param() vs. mapi_param_string().) 2. If mapi_param(), mapi_param_string() and mapi_param_type() get a char*, the value used in the query does not change as the char* is updated. Using mapi_bind_var( , , MAPI_INT, int*) and mapi_bind_var( , , MAPI_VARCHAR, char**) works as expected, while mapi_param_type( , , MAPI_INT, MAPI_INT, int*) and mapi_param_type( , , MAPI_VARCHAR, MAPI_VARCHAR, char**) do not. Is there something wrong with interface, or am I just using it wrong? Here is how I have tried using the functions: #include <cassert> #include <cstdio> #include <cstdlib> #include <cstring> #include <Mapi.h> using namespace std; Mapi dbh; void check(MapiHdl hdl, int ret = MOK, bool fatal = true) { if (ret != MOK || hdl == NULL || mapi_error(dbh)) { if (hdl) { mapi_explain_result(hdl,stderr); } else { mapi_explain(dbh,stderr); } if (fatal) assert(0);//exit(-1); } } void paramInt(MapiHdl hdl, int fldnr, int* val) { int ret = mapi_param_type(hdl, fldnr, MAPI_INT, MAPI_INT, val); check(hdl, ret); } void paramStr(MapiHdl hdl, int fldnr, char** val) { // int ret = mapi_param(hdl, fldnr, val); /* Case 1: * QUERY = INSERT INTO t VALUES(1, p`) * ERROR = !SELECT: identifier 'p`' unknown * Cast to (void*) in call to mapi_param_type() in mapi_param(). */ int ret = mapi_param(hdl, fldnr, (char**)*val); /* Case 2: * Works, sort of. * Seems it wants a (char*) really. * But 'foo' is inserted the second time instead of bar. */ // int ret = mapi_param_string(hdl, fldnr, MAPI_VARCHAR, *val, NULL); /* 'foo' twice. */ // int ret = mapi_param_string(hdl, fldnr, MAPI_VARCHAR, (char*)val, NULL); /* As Case 1. */ // int ret = mapi_param_type(hdl, fldnr, MAPI_VARCHAR, MAPI_VARCHAR, val); /* As Case 1. */ // int ret = mapi_param_type(hdl, fldnr, MAPI_VARCHAR, MAPI_VARCHAR, *val); /* 'foo' twice. */ check(hdl, ret); } int main() { dbh = mapi_connect("localhost", 50000, "nilsgri", "nilsgri", "sql", "proto"); MapiHdl hdl = mapi_query(dbh, "DROP TABLE t"); check(hdl, MOK, false); mapi_close_handle(hdl); hdl = mapi_query(dbh, "CREATE TABLE t (a INT, b VARCHAR(255))"); check(hdl); mapi_close_handle(hdl); hdl = mapi_prepare(dbh, "INSERT INTO t VALUES(?, ?)"); check(hdl); int a = 1; paramInt(hdl, 0, &a); char* b1 = strdup("'foo'"); char* b = b1; paramStr(hdl, 1, &b); int ret = mapi_execute(hdl); check(hdl, ret); a = 2; char* b2 = strdup("'bar'"); b = b2; // paramStr(hdl, 1, &b); // I would like not having to do this. ret = mapi_execute(hdl); check(hdl, ret); mapi_close_handle(hdl); hdl = mapi_quick_query(dbh, "SELECT * FROM t", stderr); check(hdl, ret); mapi_close_handle(hdl); mapi_disconnect(dbh); mapi_destroy(dbh); free(b1); free(b2); }

1 0

[MonetDB-users] Is it a bug? (insert into ... select command)
by Iftikharn 24 Apr '08

24 Apr '08

Hi I need to insert some null values into a table via a procedure. I created the table and did insert two rows. One with (insert into) command and second with (insert into ..select) command. Both commands work well and I could see two rows added in result. When I used the same (insert into.. select) command in a procedure. It behaves in a strange fashion. It shows that a row has been affected, but does not show the newly added row. If the rows are counted, it gives the correct count which is (3) in this case but still I could not see the last row which is added via a procedure. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' create sequence "ff" as integer start with 1; CREATE TABLE t1(timeid INT DEFAULT NEXT VALUE FOR "ff" PRIMARY KEY, a INT, b INT, c INT, d INT, e INT, f VARCHAR(20), g VARCHAR(20), h INT, i VARCHAR(10), j VARCHAR(10), k VARCHAR(10), l INT, m INT, n VARCHAR(10), o VARCHAR(10), p VARCHAR(10), q VARCHAR(15), r INT ); ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' And then insert a row ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' insert into t1(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r) values(1, 1, 1, 1, 1, 'first row', 'first row',1, 'first row', 'first row', 'first row',1,1, 'first row', 'first row', 'first row', 'first row', 1); select * from t1;sql>sql>sql>sql>more>more>more>more>more>more>more>more>more>more>more>more>more>more>more>more>more>more>more>sql> sql>Rows affected 1 sql> +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ | time |a |b |c |d |e |f |g |h |i |j |k |l |m |n |o |p |q |r | : id | | | | | | | | | | | | | | | | | | | +======+==+==+==+==+==+=====+=====+==+=====+======+======+==+==+======+======+======+======+==+ | 1 |1 |1 |1 |1 |1 |firs |firs |1 |firs |first |first |1 |1 |first |first |first |first |1 | : | | | | | |t |t | |t |row |row | | |row |row |row |row | | : | | | | | |row |row | |row | | | | | | | | | | +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Every thing is fine so far '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' sql>insert into t1(a, b, c, d, e, f, g, h) select a, b, c, d, e, f, g, h from t1 where timeid = 1; Rows affected 1 sql>select * from t1; +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ | time |a |b |c |d |e |f |g |h |i |j |k |l |m |n |o |p |q |r | : id | | | | | | | | | | | | | | | | | | | +======+==+==+==+==+==+=====+=====+==+=====+======+======+==+==+======+======+======+======+==+ | 1 |1 |1 |1 |1 |1 |firs |firs |1 |firs |first |first |1 |1 |first |first |first |first |1 | : | | | | | |t |t | |t |row |row | | |row |row |row |row | | : | | | | | |row |row | |row | | | | | | | | | | | 2 |1 |1 |1 |1 |1 |firs |firs |1 |null |null |null |n |n |null |null |null |null |n | : | | | | | |t |t | | | | |u |u | | | | |u | : | | | | | |row |row | | | | |l |l | | | | |l | : | | | | | | | | | | | |l |l | | | | |l | +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' inserted another row and it works fine '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' sql>create procedure tt() begin insert into t1(a, b, c, d, e, f, g, h) select a, b, c, d, e, f, g, h from t1 where timeid = 1; end; call tt(); select * from t1; more>more>more> sql>Rows affected 1 sql>+------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ | time |a |b |c |d |e |f |g |h |i |j |k |l |m |n |o |p |q |r | : id | | | | | | | | | | | | | | | | | | | +======+==+==+==+==+==+=====+=====+==+=====+======+======+==+==+======+======+======+======+==+ | 1 |1 |1 |1 |1 |1 |firs |firs |1 |firs |first |first |1 |1 |first |first |first |first |1 | : | | | | | |t |t | |t |row |row | | |row |row |row |row | | : | | | | | |row |row | |row | | | | | | | | | | | 2 |1 |1 |1 |1 |1 |firs |firs |1 |null |null |null |n |n |null |null |null |null |n | : | | | | | |t |t | | | | |u |u | | | | |u | : | | | | | |row |row | | | | |l |l | | | | |l | : | | | | | | | | | | | |l |l | | | | |l | +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' The problem starts now: when I try to insert the row via a procedure it shows that row effected 1 but does not show the row. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' sql>select count(*) from t1; +--------+ | count_ | +========+ | 3 | +--------+ sql>select timeid from t1; +--------+ | timeid | +========+ | 1 | | 2 | | 3 | +--------+ sql>select * from t1; +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ | time |a |b |c |d |e |f |g |h |i |j |k |l |m |n |o |p |q |r | : id | | | | | | | | | | | | | | | | | | | +======+==+==+==+==+==+=====+=====+==+=====+======+======+==+==+======+======+======+======+==+ | 1 |1 |1 |1 |1 |1 |firs |firs |1 |firs |first |first |1 |1 |first |first |first |first |1 | : | | | | | |t |t | |t |row |row | | |row |row |row |row | | : | | | | | |row |row | |row | | | | | | | | | | | 2 |1 |1 |1 |1 |1 |firs |firs |1 |null |null |null |n |n |null |null |null |null |n | : | | | | | |t |t | | | | |u |u | | | | |u | : | | | | | |row |row | | | | |l |l | | | | |l | : | | | | | | | | | | | |l |l | | | | |l | +------+--+--+--+--+--+-----+-----+--+-----+------+------+--+--+------+------+------+------+--+ sql> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' I could not see the 3rd row *********************************** Regards, Nadeem -- View this message in context: http://www.nabble.com/Is-it-a-bug--%28insert-into-...-select-command%29-tp1… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] MServer crashing: Random Segmentation fault
by jz＠uva 24 Apr '08

24 Apr '08

Hi all, I am using MD4 with XQuery + PF/Tijah module. I have a problem that my server sometimes/frequently crashes. I could not detect a trigger, as it occurs randomly and at any time. It gives me this error: 26747 Segmentation fault Mserver --dbinit="module(pathfinder);" but it gives me any number before it. I am not sure where I can locate the MD error log (if it exists), and how it is triggered. Help? Thanks, junte

4 9

[MonetDB-users] MALException:opt.remap:no implementation present
by McKennirey.Matthew 24 Apr '08

24 Apr '08

Our application includes multiple services that independently connect to the database. When each service connects it reads the database metadata to discover the column names, many services may start at about the same time. We are getting the following error: java.sql.SQLException: TypeException:user.s7172_2[262]:'io.stdout' undefined in: _416:any MALException:opt.remap:no implementation present at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2058) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:1808) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:371) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:340) at nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:404) at nl.cwi.monetdb.jdbc.MonetDatabaseMetaData.getTypeInfo(MonetDatabaseMetaData.java:2454) # check status of database /monetdb status name state uptime crashes BUSINESS running 1d 1h 40m 38s 3 2008-04-22 11:23:59 # try a query using mclient running on the same machine as the database ./mclient -dBUSINESS -umonetdb -Pmonetdb -lsql sql>select * from tables; !TypeException:user.s5301_3[130]:'io.stdout' undefined in: _193:any sql>select * from schemas; !TypeException:user.s5302_3[40]:'io.stdout' undefined in: _63:any sql>select * from columns; !TypeException:user.s5303_3[146]:'io.stdout' undefined in: _210:any sql>\q; When we stop and restart the merovingian demon, the problem goes away, but may reoccur the next time we try and start multiple services in short succession. Environment MonetDB MSG merovingian[12454]: starting database 'BUSINESS', up min/avg/max: 121/43165/92925, crash average: 0.00 0.30 0.10 (7-4=3) MSG BUSINESS[12459]: executing '/monetdb/bin/mserver5' for database 'BUSINESS' MSG BUSINESS[12459]: # MonetDB server v5.5.0, based on kernel v1.23.0 MSG BUSINESS[12459]: # Serving database 'BUSINESS' MSG BUSINESS[12459]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked JDBC Driver 1.7 (Canephora_p2 20080418 based on MCL v1.3)

3 4

Re: [MonetDB-users] very slow query execution
by ewilde 22 Apr '08

22 Apr '08

hi, first: about that bug, i'll defiantly file it. second: when i migrated this table from mssql to monetdb5 I intentionally inserted it in that order (recid,ft,val). i used COPY INTO while supplying a file that i prepared in advanced with such a sorting. when i first decided to test monetdb5 i did it because i understood it should perform better than row-oriented DBMSs (like mssql), especially in CRM-like applications. so... i now understand that monetsdb5 is actually being expected to perform BAD in that specific situation i described before (in my previous message). from my experience in building CRM applications, that kind of "left join" according to unique number (recid) is very common in CRM applications. am i missing something here?? Thanks again, ewilde.

2 1