May 2009 - users-list

[MonetDB-users] porting a financial time series database from mysql (how to represent datetime fields)
by Tom H 08 May '09

08 May '09

Hi monetDb collective, I am porting a test database over from a MySQL schema, and it currently looks like this; CREATE TABLE `MSFT` ( `date` datetime default NULL, `o` decimal(10,0) default NULL, `h` decimal(10,0) default NULL, `l` decimal(10,0) default NULL, `c` decimal(10,0) default NULL, `v` decimal(10,0) default NULL, `a` decimal(10,0) default NULL, `period` varchar(12) default 'daily' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1) Whats the equivalent monetDB method for representing the datetime of the start of the OHLC period? 2) As lots of queries are going to pull all the data from a particular symbol table to do things like calculate moving average, should I even be using datetime as the time index, should I use an integer value instead? Many thanks, Tom

1 1

[MonetDB-users] Trigger oddities
by Bram Van Dam 08 May '09

08 May '09

Hi, I'm experiencing some strange trigger behaviour. Not sure if I'm simply missing something, or if I've run into a bug. create table t1 (id serial, toggle boolean); create table t2 (id serial, ref bigint); insert into t1 (toggle) values (false); insert into t1 (toggle) values (false); create trigger updateMe after update on t1 referencing new row new_row for each row when new_row.toggle = true insert into t2 (ref) values (new_row.id); update t1 set toggle = true where id = 1; After this update statement, I would expect to see *1* row in t2. Instead, I see two rows: +----+-----+ | id | ref | +====+=====+ | 1 | 1 | | 2 | 2 | +----+-----+ The "when" clause seems to be ignored entirely -- I can change to "when 1 = 0" and it'll still work. If I change the trigger to create trigger updateMe after update on t1 referencing new row new_row for each row insert into t2 (ref) values (new_row.id); something equally odd happens. Instead of doing an insert for each UPDATED row, it inserts a row for each row present in t1. This seems a bit odd to me, but again, I could just be misunderstand the way this is supposed to work. Documentation on triggers is a bit sparse I'm afraid... Any help on this issue would be more than welcome! - Bram

2 2

[MonetDB-users] clarity on creating users/reseting passwords
by Tom H 08 May '09

08 May '09

Hi, I need to reset some passwords on a monetDB5 installation, and I am not clear on how to do it. Currently I can't login at all. Is there a way to enter single-user mode, or something so I can reset the monetDB user password? Also, I really don't understand the difference between running monet as mserver5 and Merovingian. Basically I want to run the server from an init.d script, which should I use? At the moment I am running the server like so; [root@localhost ~]# mserver5 --dbinit="include sql;" # MonetDB server v5.10.4, based on kernel v1.28.4 # Serving database 'demo', using 2 threads # Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs dynamically linked # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see /etc/monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/SQL module v2.28.4 loaded > Can I reset the password using MAL commands from the prompt? Thanks, Tom

2 1

[MonetDB-users] Primary-Foreign keys
by Alex Bo. 07 May '09

07 May '09

Hi, I wanted to ask what I should expect in terms of performance out of primary-foreign keys. Do join queries that have primary keys defined on their tables return faster? Are such joins materialized in the database? If not - is there any other way to control materialization of joins? Thanks. -- View this message in context: http://www.nabble.com/Primary-Foreign-keys-tp23428537p23428537.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] copy into from a file hangs
by Venu Alatzeth 06 May '09

06 May '09

Hi, I am evaluating MonetDB server v5.8.4, based on kernel v1.26.4 on FreeBSD 6.2 Loading data into a table from a file seem to hang, with mserver using high CPU. The file has only 10 records. If I use stdin instead of a file name the data can be loaded. Here is the debug output. openRead(nil) does not seem right. shouldn't it be openRead('/tmp/head.txt') ? sql>debug copy 10 records into "log_0" from '/tmp/head.txt' using delimiters '|'; mdb># mdb.start(); mdb>n mdb># user.s0_2(); mdb>n mdb># _2 := streams.openRead(nil); mdb>n What am I doing wrong? Thanks for your help, -VG

2 1

[MonetDB-users] Cannot install MonetDB with monetdb-install.sh
by Stefan Klinger 06 May '09

06 May '09

Hello, I'm having problems installing a current (--nightly=current) version of MonetDB: Running monetdb-install.sh provied at [1] displays a lot of HTTP 404 error messages. Then it successfully downloads a 0Byte MonetDB4-XQuery.tar.gz which it cannot unpack. The logfile is attached. Tested on openSUSE 10.2 and Debian GNU/Linux squeeze/sid. Just ask if you need more details. Thank you very much, Stefan ____________________ [1] http://monetdb.cwi.nl/projects/monetdb/Assets/monetdb-install.sh -- Stefan Klinger o/klettern /\/ bis zum send plaintext only - max size 32kB - no spam \ Abfallen http://stefan-klinger.de

2 1

[MonetDB-users] Query failure with 'recycle' in optimizer
by Alex Bo. 06 May '09

06 May '09

Hi, I have a query that runs without any problems when the default optimizer settings are used. But once I add 'recycle' to the existing optimizer modules, the query doesn't return. Mserver seems to be stuck in an infinite loop with 50% CPU usage. I am running Monet compiled from CVS on Windows. This is the query: select sum(t1."AddressID"), "hour"(t1."ModifiedDate") as aa from "schema1"."CustomerAddress" as t1 inner join "schema1"."CustomerAddress"as t2 on t1."CustomerID" = t2."CustomerID" group by aa This is what I get from 'select opimizer': inline,remap,evaluate,costModel,coercions,emptySet,mitosis,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,garbageCollector,dataflow,history,multiplex,recycle Thanks. -- View this message in context: http://www.nabble.com/Query-failure-with-%27recycle%27-in-optimizer-tp23389… Sent from the monetdb-users mailing list archive at Nabble.com.

5 5