- users-list - monetdb.org

Loading timestamps via Python loader functions
by Daniel Glöckner 26 Aug '20

26 Aug '20

Hi, there seems to be a limitation for Python-based loader functions wrt timestamps. Looking at the implementation in sql/backends/monet5/UDF/pyapi3/convert_loops.h suggests that timestamps are indeed not covered. Both of the the 2 following examples fail with "Failed conversion: MALException:pyapi3.eval:PY000!Unrecognized type. Could not convert to NPY_UNICODE.". CREATE LOADER array_loader() LANGUAGE PYTHON { from datetime import datetime _emit.emit( { 'a': [1,2,3], 'b': [datetime.utcnow(), datetime.utcnow(), datetime.utcnow()], 'c': ['1', '2', '3']}) }; CREATE LOADER array_loader() LANGUAGE PYTHON { import numpy as np from datetime import datetime dt = datetime.utcnow() _emit.emit( { 'a': [1,2,3], 'b': np.array([dt, dt, dt], dtype=np.datetime64), 'c': ['1', '2', '3']}) }; Is anyone aware of a workaround? An obvious workaround is to insert the timestamps as strings and convert them via SQL but that's much less efficient. Kind regards, Daniel

1 0

result properties in pcre.c
by Roberto Cornacchia 25 Aug '20

25 Aug '20

Hi, I was looking at pcre.c for some inspiration and found something suspicious. At the end of both pcre_likeselect() and re_likeselect(): if (bn && !msg) { BATsetcount(bn, BATcount(bn)); /* set some properties */ bn->tsorted = true; bn->trevsorted = bn->batCount <= 1; bn->tkey = true; bn->tseqbase = bn->batCount == 0 ? 0 : bn->batCount == 1 ? * (oid *) Tloc(bn, 0) : oid_nil; } Which I read as: if everything went well, then the result is sorted and key. But I miss why it should be sorted and key. Roberto

2 2

Re: Re: MonetDB on IBM Power architecture
by Daniel Glöckner 20 Aug '20

20 Aug '20

Hi, This looks very promising so far. Building on Power 8 was easy, just following the official guide from the wiki. https://www.monetdb.org/wiki/MonetDB:Building_from_sources So far I’ve only loaded larger amounts of data and the timings look good. Will perform more tests over the next days but overall this looks like absolutely usable for production. Kind regards, Daniel

1 0

JDBC Driver
by Walter Weinmann 19 Aug '20

19 Aug '20

Including the JDBC driver with gradle implementation 'monetdb:monetdb-java-lite:2.38' gives me: FAILURE: Build failed with an exception. * Where: Build file 'D:\SoftDevelopment\Projects\Konnexions\db_seeder\build.gradle' line: 66 * What went wrong: Could not determine the dependencies of task ':fatJar'. > Could not resolve all files for configuration ':runtimeClasspath'. > Could not find monetdb:monetdb-jdbc-new:2.37-SNAPSHOT. Searched in the following locations: - https://jcenter.bintray.com/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/maven-me… - https://jcenter.bintray.com/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/monetdb-… - https://repo.maven.apache.org/maven2/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT… - https://repo.maven.apache.org/maven2/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT… - https://jitpack.io/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/maven-metadata.xml - https://jitpack.io/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/monetdb-jdbc-new-… - http://maven.cubrid.org/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/maven-metada… - http://maven.cubrid.org/monetdb/monetdb-jdbc-new/2.37-SNAPSHOT/monetdb-jdbc… Required by: project : > monetdb:monetdb-java-lite:2.38 * Try: Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output. Run with --scan to get full insights.

3 2

Re: Re: MonetDB on IBM Power architecture
by Daniel Glöckner 19 Aug '20

19 Aug '20

Hi, Thanks a lot for confirming this. We’ll perform some basic tests on our Power 8 system to see if we’re able to build and run it. Kind regards, Daniel

1 0

MonetDB on IBM Power architecture
by Daniel Glöckner 19 Aug '20

19 Aug '20

Hi folks, I was wondering if one could run MonetDB on IBM Power architecture (Power 8 and later). I guess there's no official build for this? Did anyone try this out? Kind regards, Daniel

2 1

java.sql.BatchUpdateException: Error(s) occurred while executing the batch, see next SQLExceptions for details
by Walter Weinmann 18 Aug '20

18 Aug '20

*Without addBatch / executeBatch my program runs without problems:* [main] Start [main] tickerSymbolExtern='monetdb' [main] Start MonetDB [main] database table COUNTRY - 200 rows in total [main] database table TIMEZONE - 11 rows in total [main] database table COUNTRY_STATE - 500 rows so far [main] database table COUNTRY_STATE - 600 rows in total [main] database table CITY - 500 rows so far [main] database table CITY - 1000 rows so far [main] database table CITY - 1500 rows so far [main] database table CITY - 1800 rows in total [main] database table COMPANY - 500 rows so far [main] database table COMPANY - 1000 rows so far [main] database table COMPANY - 1500 rows so far [main] database table COMPANY - 2000 rows so far [main] database table COMPANY - 2500 rows so far [main] database table COMPANY - 3000 rows so far [main] database table COMPANY - 3500 rows so far [main] database table COMPANY - 4000 rows so far [main] database table COMPANY - 4500 rows so far [main] database table COMPANY - 5000 rows so far [main] database table COMPANY - 5400 rows in total [main] duration in seconds: 52 [main] End MonetDB [main] End *With addBatch / executeBatch and a batch size of 1000 I get the following error:* [main] Start [main] tickerSymbolExtern='monetdb' [main] Start MonetDB [main] database table COUNTRY - 200 rows in total [main] database table TIMEZONE - 11 rows in total [main] database table COUNTRY_STATE - 500 rows so far [main] database table COUNTRY_STATE - 600 rows in total [main] database table CITY - 500 rows so far [main] database table CITY - 1000 rows so far [main] database table CITY - 1500 rows so far java.sql.BatchUpdateException: Error(s) occurred while executing the batch, see next SQLExceptions for details at nl.cwi.monetdb.jdbc.MonetStatement.executeBatch(Unknown Source) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createDataInsert(AbstractJdbcSeeder.java:501) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createData(AbstractJdbcSeeder.java:404) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createData(AbstractJdbcSeeder.java:362) at ch.konnexions.db_seeder.DatabaseSeeder.main(DatabaseSeeder.java:141) Processing of the script was aborted, error code=1 *Both times the connection runs with autoCommit.*

3 2

java.sql.SQLException: DROP USER: 'kxn_user' owns a schema
by Walter Weinmann 18 Aug '20

18 Aug '20

*The following SQL statements are executed successfully:* SELECT count(*) FROM sys.users WHERE name = 'kxn_user' DROP SCHEMA IF EXISTS kxn_schema CASCADE CREATE USER kxn_user WITH UNENCRYPTED PASSWORD 'monetdb' NAME 'Dbseeder User' SCHEMA sys CREATE SCHEMA kxn_schema AUTHORIZATION kxn_user ALTER USER kxn_user SET SCHEMA kxn_schema CREATE TABLE COUNTRY ( PK_COUNTRY_ID BIGINT NOT NULL PRIMARY KEY, COUNTRY_MAP BLOB, CREATED TIMESTAMP NOT NULL, ISO3166 VARCHAR(50), MODIFIED TIMESTAMP, NAME VARCHAR(100) NOT NULL UNIQUE ) *The following SQL statement produces an error message:* DROP USER kxn_user java.sql.SQLException: DROP USER: 'kxn_user' owns a schema at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(Unknown Source) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(Unknown Source) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(Unknown Source) at nl.cwi.monetdb.jdbc.MonetStatement.execute(Unknown Source) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.dropUser(AbstractJdbcSeeder.java:814) at ch.konnexions.db_seeder.jdbc.monetdb.MonetdbSeeder.setupDatabase(MonetdbSeeder.java:103) at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createData(AbstractJdbcSeeder.java:359) at ch.konnexions.db_seeder.DatabaseSeeder.main(DatabaseSeeder.java:141) Processing of the script was aborted, error code=1 *How can I delete the user in this constellation without errors ?*

2 1

New Elixir driver + some questions and feedback
by Karl Seguin 02 Aug '20

02 Aug '20

I've been working on a driver for Elixir (https://github.com/karlseguin/monet) It's immature, but in a decent enough state for our prototyping. Overall, I've been very pleased and excited about MonetDB. I wanted to share some observations from the perspective of both a newcomer and having developed a driver. First though, a couple questions: 1. I understand what a Q_BLOCK is, but I don't understand under what circumstances it happens. 2. Aside from the initial challenge response, it seems like all messages from the server are _always_ in response to a query. This is different than, say, PostgreSQL which can push data to signal some change, outside of the typical request->response flow. Correct? As for my feedback: 1. The first and, by far, single biggest issue is with the text-base protocol. I'm sure this has been discussed before, and I doubt that I can add any new insight. But it was tedious to implement and error prone. We were initially considering storing large blobs in MonetDB (for a secondary purpose), but I'm now rethinking that idea because of the performance for serializing and deserializing the data. The rest of these issues are in no particular order. 2. I'm pretty sure if you send an invalid x command, the server goes haywire. The simplest way I found to reproduce this is to change the 's' to 'x' in the official Node client (https://github.com/MonetDB/monetdb-nodejs/blob/19f24ec16db70a26d0b11e51d77b…) and connect. The CPU load spikes and the logs are filled with: "createExceptionInternal: !ERROR: SQLException:SQLparser:42000!Unrecognized X command: SET TIME ZONE INTERVAL '-480' MINUTE;"" (I'm running: 17ab9b680ec55b342c08d034f2c5659a073cc74a) 3. Maybe this is naive, but I don't understand why this works: insert into x (time_col) values ('10:30:50') but this doesn't: exec 7('10:30:50') I was hoping I could ignore the response from a "prepare" (except for getting the id from the first line) and just encode based on the argument types, but no. I need the type AND precision so that I can do "time(3) '...'" or "blob '....'" etc. 4. Point 3 is made marginally worse because: prepare select 1 - ? returns 2 rows. I look for the suffix "NULL,\tNULL,\tNULL\t]" to tell which are "real" placeholders. I'm not sure if that's correct. 5. I was surprised to see dates with 1, 2 and 3 digit years. Making it 4 digits would make it ISO8601 compliant. 6. I was caught off guard by the fact that ab error can show up as part of what seems like a healthy response. Initially, I was just looking for a response that stars with !. But while testing, a concurrent create table failed and I got "&3 X Y\n!CODE!ERR\n". Is there a rule that can help me parse these efficiently? (like they'll always be on the first or 2nd line), or can they really show up anywhere? 7. Some of the drivers appear to be in bad shape. The node driver gets confused by my point 4 above and fails to handle it. It also doesn't support time or blob values, and tries to JSON the built-in Date object. I couldn't get the go driver to work at all (nil referencse trying to read rows). The ruby driver has no support for prepared statements and I'm pretty sure it doesn't return the correct value for: db.query("select '\\\\'") 8. While I'm probably wrong, I can't help but think that merging the functionalities of the monetdbd and monetdb binaries would streamline the experience for newcomers. Please don't interpret these as unkind criticisms of (or unhappiness with) MonetDB. Again, overall, very happy.

2 1

Creation of a remote table overhead
by Ioannis Foufoulas 28 Jul '20

28 Jul '20

Hi, I am trying to develop a Python client which sends queries to different Monetdb servers and transfers the results between them using the remote tables functionalities. I’ve seen than my main overhead is execution of CREATE REMOTE TABLE statement. It requires 70-90msec so in cases with many transfers and fast and small queries this makes a difference. After looking at some older versions I’ve seen that the creation of a remote table was very fast before August 2018 (this is when authentication added). Is there any hint on this? Could the. authentication strategy change in someway so that it runs faster or is it possible to select among different authentication methods for the remote tables? Best, Yannis

4 7