November 2014 - users-list

sys.queue()
by Guillaume Bassot 06 Nov '14

06 Nov '14

Hello, On release JAN2014, select * from sys.queue(); is returning all running process. On release OCT2014 the same command is returning SELECT: no such operator 'queue' Do i miss a new way to get process list ? Could you please tell me how to do ? Thanks Guillaume BASSOT Web Developer +33 (0)1 55 48 02 82 gbassot(a)inbox.fr 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79

5 6

Improving performance of bulk loading data
by Dennis Pallett 06 Nov '14

06 Nov '14

Hi all, We're experiencing some problems during bulk loading of new data into our MonetDB database. We use COPY INTO to load several CSV files every day but this loading takes (too) long but more importantly the database is completely unresponsive with regards to any incoming queries therefore completely disabling our production environment. I'm currently looking into improving this proces and the hardware of our MonetDB server is obviously one of the starting points. Does anyone know which specs should be upgraded? Would adding more core's/CPU's have the most positive effect? Or perhaps more memory? Any advice or insight would be greatly appreciated! Best regards, Dennis Pallett

3 4

Can't use ORDER BY in subqueries
by Kotschenreuther, Leo 06 Nov '14

06 Nov '14

Hi all, I tried to use some more complex queries, they contained subqueries. I will shortly desrcibe the situation: I have two tables (e.g. test and foo) and want to join them. Furthermore I want to preselect a subset of one of the two tables by adding some conditions (e.g. order the records by attribute c and only choosing the biggest one). A query could look like the this: sql>select * from test join (select * from foo order by c limit 1); The result I receive is the following: syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * from test join (select * from foo order” On the other hand, I can run the subquery without the main query and I receive a valid result. Is there a possibility to use subqueries with an ORDER BY statement without separating them into two independent queries? Regards Leo

7 16

Several problems with spatial features and query execution time measuring
by Kotschenreuther, Leo 03 Nov '14

03 Nov '14

Hi all, I’m facing several problems with the spacial extension of Monetdb. Here are the basics about my environment: I am using MonetDB v11.17.21 (Jan2014-SP3) on a Suse Linux Enterprise Server 11 (x86_64, Patchlevel 2). I installed MonetDB by compiling from source as there is no Release for SLES available. 1) First of all, I had a glance at the documentation about the spatial functions [1]. I did need some time to realise that every function that is described with the prefix “ST_” in the documentation works without the prefix. Could somebody please change the documentation so that it is according to the sql functions? 2) I would like to use the “Intersects” function that says whether two geometries intersect or not intersect. Apparently my Monetdb-Server doesn’t know the function although he knows other ones like the Equals function. Let’s create two tables with Geometries, fill them with one Polygon each and then check whether the two Polygons intersect: sql>create table one (idone INT, shape Polygon); sql>create table two (idtwo INT, figure Polygon); sql>insert into one values (0, GeomFromText('Polygon((0 0, 1 0, 1 1, 0 1, 0 0))', 4326)); sql>insert into two values (0, GeomFromText('Polygon((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)); sql>select Intersects(shape, figure) from one join two ON idone = idtwo; As result of the last query I receive the following: SELECT: no such binary operator 'intersects(polygon,polygon)’ I tried this on the sql console using mclient, I tried the same inside my python script using the mounted.sql API. Both times I received the same message. 3) I would like to measure the query execution time for all my queries. I found [2] where it is described how to measure the execution time. Following the steps and running the queries from the sql console, everything works fine. When I want to do the same inside my python script, I get an error on running the following statement: select id, query, avg(run) from sys.querylog_history group by id,query; This is the traceback, I skipped the parts of the trace that belong to my own code. The first line is the place where I try to access the sys.querylog_history table. Traceback (most recent call last): ... File "/Users/leokotschenreuther/EPIC/GIS benchmark/db-benchmark/db/monetdb_db.py", line 109, in runQueries self.cursor.execute("select id, query, avg(run) from sys.querylog_history group by id,query") File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 170, in execute self.__store_result(block) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 397, in __store_result values = self.__parse_tuple(line) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 440, in __parse_tuple self.description)]) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/pythonize.py", line 139, in convert raise ProgrammingError("type %s is not supported" % type_code) monetdb.exceptions.ProgrammingError: type oid is not supported Does somebody know whether there is a chance to do the time measuring in python as well? I know that I could just use the time module in python but I want to measure the database server execution time. Thanks for reading and helping! Regards Leo [1]: https://www.monetdb.org/Documentation/Extensions/GIS/SpatialFunctions <https://www.monetdb.org/Documentation/Extensions/GIS/SpatialFunctions> [2]: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew <https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew>

2 1

"select * from sys.queue" not working in Oct2014
by Robert Koch 03 Nov '14

03 Nov '14

Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014) Database: MonetDB v11.19.3 (Oct2014), 'mapi:monetdb://server:50000/anonym' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * from sys.queue; SELECT: no such operator 'queue' sql>select * from sys.queue(); SELECT: no such operator 'queue' sql>

1 0