- users-list - monetdb.org

Announcement: New Apr2019-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 02 Sep '19

02 Sep '19

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2019-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <https://dev.monetdb.org/downloads/>. Apr 2019-SP1 bugfix release (11.33.11) Build Environment * Removed restriction on using combinations of --enable-assert, --enable-debug, and --enable-optimize. --enable-debug adds a -g (or -even -g3) option, --enable-debug=gdb adds a -ggdb3 flag (for GCC); --enable-optimize adds a bunch of optimization flags; --disable-debug (or --enable-debug=no) removes any -g flags; --disable-optimize removes any -O flags; --enable-optimize=auto leaves the optimization flags untouched. MonetDB Common * We now look at the limits imposed by cgroups and the setrlimit system call to initialize some internal values related to how much (virtual) memory we think is available. SQL Frontend * Many fixes for using the server in a highly concurrent setting with many clients doing read-only queries mixed with clients doing updating queries. * Changed the internal representation of RANGE MINVALUE and RANGE MAXVALUE in MERGE TABLE partitions. Before, the limits were represented by the smallest and largest value of the domain of the column, now these are represented by a NULL value. This has the added benefit that if you use TO RANGE MAXVALUE, the largest value of the domain is included in the partition (before it wasn't). Also, these new limits now work for any ordered type, so including VARCHAR. Bug Fixes * 6576: Sqlitelogictest aritmetic expressions with negative numbers handling * 6697: Duplicate expressions not eliminated with long CASE statement * 6701: When changing the schema name of a table, referencing rows from sys.columns, sys.keys and more tables are not removed * 6703: SQL optimizer enters loop and goes into stack overflow * 6706: prepare doesn't recognize merge statement * 6712: Where clause with cast ignores sub-select * 6713: COPY INTO FROM 'file.xz' does not work * 6714: Assertion failure in rel_select.c for correlated subquery in aggregation query (with group by and having and order by and limit) * 6715: Assertion failure in rel_bin.c for MERGE INTO command * 6716: COPY INTO does not load UTF8 encoded text * 6718: Can't copy table into another table with constraints (mkey.bulk_rotate_xor_hash) * 6720: Compilation issues on gcc 9.1.1 [-Werror=stringop-truncation] * 6721: Possibly incorrect call to pcre_exec * 6725: Prepare statement on non-existing function crashes * 6728: SELECT DISTINCT not removing duplicates * 6729: Creating a table with duplicate column name should not be possible * 6730: sys.stop or sys.pause a INSERT query triggers "BATproject: does not match always" * 6736: Unexpected query result with merge tables and view after upgrade * 6738: issue with window functions and hugeint type coercion * 6741: report no such table when execute “alter table tb1 rename to tb2” according to using mapi * 6744: CREATE SEQUENCE seq; fails * 6748: MonetDB "forgets" columns after ALTER TABLE statements * 6749: mserver5 restart aborts/segfaults after dropping column with constraint * 6751: ODBC driver(11.33.3) Seg Faults when "fn ucase" is used in SQL

1 0

Crash - !mvc_init: unable to create system tables
by Nobody Noone 30 Aug '19

30 Aug '19

Hi, Would anyone have any advice on recovering. I was running a bunch of COPY SELECT * FROM x INTO '/y.z.csv' USING DELIMITERS ',' , '\n' , '"'; The log said there was already an existing file in the dir. The db was not responding so I stopped it. Then tried to start it and I get; !mvc_init: unable to create system tables #main thread:!ERROR:SQLException:SQLinit:42000!Catalogue initialization failed Fatal error during initialization: SQLException:SQLinit:42000!Catalogue initialization failed # MonetDB 5 server v11.33.3 (Apr2019) database 'y' (53342) has exited with exit status 1 ERR control[33899]: (local): failed to fork mserver: database 'y' has crashed after starting, manual intervention needed, check monetdbd's logfile (merovingian.log) for details Any advice on how to recover? Thanks:)

1 0

Performance issue related to dropping (empty) tables
by Gotwisner, Dave 27 Aug '19

27 Aug '19

I am seeing a performance issue related to schema upgrades. This is from a C++ program that uses the MAPI library. We try and keep 30 days worth of data, imported at a fairly high data rate with lots of columns. We use multiple threads to ingest data (bulk insert), and the number of threads is stored in a configuration table. For 20 days worth of data, this works out to around 9000 tables (plus merge tables). We have roughly 6 sets of tables per hour, each of which has 4 tables and a merge table connecting them. Because this is a schema change, we wrap the entire upgrade in a transaction. For the purpose of the test, we had no data in the tables, but only had empty tables. If we change our setting from 4 parallel tables to 2, we end up dropping 9000 tables. About half of the "drop table" commands take 0 seconds. The rest take anywhere between 4 and 12 seconds each. Here is a plot of the time per delete, y access is seconds, x is the statement. The X access is NOT time based: [cid:image001.png@01D5568E.A5389030] Any idea on how we can reduce the time? The total time to delete 9000 tables was 480 minutes. Thanks, Dave

2 4

memory control on large results
by Daniel Zvinca 15 Aug '19

15 Aug '19

Hello, I plan to use the embedded library (custom build) for a project that will likely address very large tables. While the control over statements is handled to the end user, I need an advice how to control memory usage on fetching huge amount of data. Specifically, I already skipped the default "monetdb_result_fetch" and implemented a compressed storage (which optional can be stored on disk), but I wasn't able to see a way of releasing the associated memory for already processed columns (persistent or transient bat). Unless the bat contents is shared between different res_col's, I would like to be able to release that memory to the system before proceeding to the next column. Any suggestion? Thank you, Dan

1 0

Embedded Python Initialization Issue
by Sharma, Sreejith 14 Aug '19

14 Aug '19

All, I am using an Python based UDF and have enabled embedpy=true for DB with Python version 2.7.5. This works fine with CentOS 7, but the same doesn't wok on Red Hat Ent Linux version. Is there any dependency on this? Is it due to OS version? Am getting below error though Python path is proper in Red Hat OS version server. Embedded Python is enabled but an error was thrown during initialization. Can anyone provide some help on this? Both uses same version of MonetDB server version. MonetDB server on which function works fine [user@lnx1535 ~]$ cat /etc/os-release NAME="CentOS Linux" VERSION="7 (Core)" ID="centos" ID_LIKE="rhel fedora" VERSION_ID="7" PRETTY_NAME="CentOS Linux 7 (Core)" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:centos:centos:7" HOME_URL="https://www.centos.org/" BUG_REPORT_URL="https://bugs.centos.org/" CENTOS_MANTISBT_PROJECT="CentOS-7" CENTOS_MANTISBT_PROJECT_VERSION="7" REDHAT_SUPPORT_PRODUCT="centos" REDHAT_SUPPORT_PRODUCT_VERSION="7" CENTOS_MANTISBT_PROJECT="CentOS-7" CENTOS_MANTISBT_PROJECT_VERSION="7" REDHAT_SUPPORT_PRODUCT="centos" REDHAT_SUPPORT_PRODUCT_VERSION="7" MonetDB server on Azure where function is not working [user@alcazimdbd01 ~]$ cat /etc/os-release NAME="Red Hat Enterprise Linux Server" VERSION="7.5 (Maipo)" ID="rhel" ID_LIKE="fedora" VARIANT="Server" VARIANT_ID="server" VERSION_ID="7.5" PRETTY_NAME="Red Hat Enterprise Linux" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:redhat:enterprise_linux:7.5:GA:server" HOME_URL="https://www.redhat.com/" BUG_REPORT_URL="https://bugzilla.redhat.com/" REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 7" REDHAT_BUGZILLA_PRODUCT_VERSION=7.5 REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux" REDHAT_SUPPORT_PRODUCT_VERSION="7.5" Regards, Sreejith

2 1

Type of integers used to calculate a SUM
by Jan, Di-An 13 Aug '19

13 Aug '19

With hugeint enabled, it seems that the SUM of INT (32-bit) is done using HUGEINT (128-bit) while the SUM of BIGINT (64-bit) is done using BIGINT (64-bit). That seems weird. Is SUM supposed to use the largest type or use the same type as the input or something else? Database: MonetDB v11.33.3 (Apr2019) sql>CREATE TABLE test (bi BIGINT, i INT); sql>INSERT INTO test VALUES (0x7000000000000000, 0x70000000); sql>INSERT INTO test VALUES (0x7000000000000000, 0x70000000); sql>INSERT INTO test VALUES (1, 3); sql>INSERT INTO test VALUES (2, 4); sql>SELECT SUM(i) FROM test; -- larger than an INT -- +------------+ | L3 | +============+ | 3758096391 | +------------+ 1 tuple sql>SELECT SUM(bi) FROM test; -- larger than a BIGINT -- overflow in calculation. sql>EXPLAIN SELECT SUM(i) FROM test; +------------------------------------------------------------------------------------------------------+ | function user.s10_22():void; | | X_1:void := querylog.define("explain select sum(i) from test;":str, "default_pipe":str, 12:int); | | barrier X_74:bit := language.dataflow(); | | X_4:int := sql.mvc(); | | C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "test":str); | | X_8:bat[:int] := sql.bind(X_4:int, "sys":str, "test":str, "i":str, 0:int); | | X_17:bat[:int] := algebra.projection(C_5:bat[:oid], X_8:bat[:int]); | | X_18:hge := aggr.sum(X_17:bat[:int]); | | exit X_74:bit; | | sql.resultSet("sys.L3":str, "L3":str, "hugeint":str, 128:int, 0:int, 7:int, X_18:hge); | | end user.s10_22; | +------------------------------------------------------------------------------------------------------+ sql>EXPLAIN SELECT SUM(bi) FROM test; +-------------------------------------------------------------------------------------------------------+ | function user.s12_22():void; | | X_1:void := querylog.define("explain select sum(bi) from test;":str, "default_pipe":str, 12:int); | | barrier X_74:bit := language.dataflow(); | | X_4:int := sql.mvc(); | | C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "test":str); | | X_8:bat[:lng] := sql.bind(X_4:int, "sys":str, "test":str, "bi":str, 0:int); | | X_17:bat[:lng] := algebra.projection(C_5:bat[:oid], X_8:bat[:lng]); | | X_18:lng := aggr.sum(X_17:bat[:lng]); | | exit X_74:bit; | | sql.resultSet("sys.L3":str, "L3":str, "bigint":str, 64:int, 0:int, 7:int, X_18:lng); | | end user.s12_22; | +-------------------------------------------------------------------------------------------------------+ sql>EXPLAIN SELECT SUM(CAST(i AS HUGEINT)) FROM test; +-----------------------------------------------------------------------------------------------------------------------+ | function user.s20_22():void; | | X_1:void := querylog.define("explain select sum(cast(i as hugeint)) from test;":str, "default_pipe":str, 13:int); | | barrier X_75:bit := language.dataflow(); | | X_4:int := sql.mvc(); | | C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "test":str); | | X_8:bat[:int] := sql.bind(X_4:int, "sys":str, "test":str, "i":str, 0:int); | | X_17:bat[:int] := algebra.projection(C_5:bat[:oid], X_8:bat[:int]); | | X_18:bat[:hge] := batcalc.hge(X_17:bat[:int]); | | X_19:hge := aggr.sum(X_18:bat[:hge]); | | exit X_75:bit; | | sql.resultSet("sys.L4":str, "L4":str, "hugeint":str, 128:int, 0:int, 7:int, X_19:hge); | | end user.s20_22; | +-----------------------------------------------------------------------------------------------------------------------+ sql>EXPLAIN SELECT SUM(CAST(bi AS BIGINT)) FROM test; +-----------------------------------------------------------------------------------------------------------------------+ | function user.s8_22():void; | | X_1:void := querylog.define("explain select sum(cast(bi as bigint)) from test;":str, "default_pipe":str, 12:int); | | barrier X_74:bit := language.dataflow(); | | X_4:int := sql.mvc(); | | C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "test":str); | | X_8:bat[:lng] := sql.bind(X_4:int, "sys":str, "test":str, "bi":str, 0:int); | | X_17:bat[:lng] := algebra.projection(C_5:bat[:oid], X_8:bat[:lng]); | | X_18:lng := aggr.sum(X_17:bat[:lng]); | | exit X_74:bit; | | sql.resultSet("sys.L4":str, "L4":str, "bigint":str, 64:int, 0:int, 7:int, X_18:lng); | +-----------------------------------------------------------------------------------------------------------------------+ sql>EXPLAIN SELECT SUM(CAST(i AS INT)) FROM test; +-----------------------------------------------------------------------------------------------------------+ | function user.s10_22():void; | | X_1:void := querylog.define("select sum(cast(i as int)) from test;":str, "default_pipe":str, 12:int); | | barrier X_74:bit := language.dataflow(); | | X_4:int := sql.mvc(); | | C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "test":str); | | X_8:bat[:int] := sql.bind(X_4:int, "sys":str, "test":str, "i":str, 0:int); | | X_17:bat[:int] := algebra.projection(C_5:bat[:oid], X_8:bat[:int]); | | X_18:hge := aggr.sum(X_17:bat[:int]); | | exit X_74:bit; | | sql.resultSet("sys.L4":str, "L4":str, "hugeint":str, 128:int, 0:int, 7:int, X_18:hge); | | end user.s10_22; | +-----------------------------------------------------------------------------------------------------------+ sql>SELECT SUM(CAST(i AS INT)) FROM test; +------------+ | L4 | +============+ | 3758096391 | +------------+ 1 tuple Also, the cast in SUM(CAST(bi AS BIGINT)) seems to be optimized out while SUM(CAST(i AS HUGEINT)) has one more BAT operation than SUM(i) even though they seem to do the same thing, and the cast in SUM(CAST(i AS INT)) seems to have no effect. Di-an Jan

1 0

Books about Monetdb?
by Nobody Noone 09 Aug '19

09 Aug '19

Hi, Are there any books published about Monetdb? This listserv, monetdb.org and stackoverflow.com seem to be all I can find. Thanks!

2 1

understand how monetdb choose the best query plan
by DEMBELE Simon Pierre 08 Aug '19

08 Aug '19

Hello, J'm new in the use of monetdb, i want to understand how monetdb choose the best query plan. Is the model cost is used for that or what. Please if someone can clarify me. Thank yours time. -- Simon

2 2

Questions on upgrading tables based upon the system tables.
by Gotwisner, Dave 08 Aug '19

08 Aug '19

I am writing code to migrate from one version of our schema to another (such as if we add tables or add columns). The from version is whatever is physically being used, and the new version is compiled into data structures in our code. I can successfully create or remove tables, and add and drop columns. We have multiple tables that we then bind to a merge table, so the schemas must be exact. Our tables have a primary key. For the table and column based changes, querying sys.schemas, sys.tables, and sys.columns gives us everything we need. I can also figure out how to add the primary key on the new table (via alter table commands). However, I can't figure out how to find the set of columns in the primary key, to be able to build up the primary key. Here is the table definition for our tables that go into the merge table (%04d goes from 0000 to the number of threads we send bulk data with): CREATE TABLE if not exists data_availability__%04d ( interval_start BIGINT, source INT, PRIMARY KEY(interval_start, source) ); alter table data_availability add table data_availability__%04d; Looking at system tables gives the following information (before adding this table): sql>select * from sys.tables where name = 'data_availability'; +------+-------------------+------+------+------+-------+------+-------+-------+ | id | name | sche | quer | type | syste | comm | acces | tempo | : : : ma_i : y : : m : it_a : s : rary : : : : d : : : : ctio : : : : : : : : : : n : : : +======+===================+======+======+======+=======+======+=======+=======+ | 8889 | data_availability | 8800 | null | 3 | false | 0 | 0 | 0 | +------+-------------------+------+------+------+-------+------+-------+-------+ 1 tuple sql>select * from sys.keys where table_id = 8889; +------+----------+------+----------------------------------------------+------+--------+ | id | table_id | type | name | rkey | action | +======+==========+======+==============================================+======+========+ | 8887 | 8889 | 0 | data_availability_interval_start_source_pkey | -1 | -1 | +------+----------+------+----------------------------------------------+------+--------+ 1 tuple sql>select * from sys.key_types; +-------------+---------------+ | key_type_id | key_type_name | +=============+===============+ | 0 | Primary Key | | 1 | Unique Key | | 2 | Foreign Key | +-------------+---------------+ 3 tuples sql>select * from sys.idxs where table_id = 8889; +------+----------+------+----------------------------------------------+ | id | table_id | type | name | +======+==========+======+==============================================+ | 8888 | 8889 | 0 | data_availability_interval_start_source_pkey | +------+----------+------+----------------------------------------------+ 1 tuple sql>select * from sys.index_types; +---------------+-----------------------+ | index_type_id | index_type_name | +===============+=======================+ | 0 | Hash | | 1 | Join | | 2 | Order preserving hash | | 3 | No-index | | 4 | Imprint | | 5 | Ordered | +---------------+-----------------------+ 6 tuples sql>select * from sys.columns where table_id = 8889; +------+----------------+--------+-------------+------------+----------+---------+-------+--------+---------+ | id | name | type | type_digits | type_scale | table_id | default | null | number | storage | +======+================+========+=============+============+==========+=========+=======+========+=========+ | 8885 | interval_start | bigint | 64 | 0 | 8889 | null | false | 0 | null | | 8886 | source | int | 32 | 0 | 8889 | null | false | 1 | null | +------+----------------+--------+-------------+------------+----------+---------+-------+--------+---------+ 2 tuples The above data is for the merge table that we will bind the (new) table to. I can get the name and id of the primary key (which is also an index). I could not find a table that gives a column -> key binding. Is there such a table? If not, how do I get this information from MonetDB? Thanks, Dave

2 1

Question on querying two specific columns from sys.columns
by Gotwisner, Dave 07 Aug '19

07 Aug '19

I am trying to query the default and null columns from sys.columns. Both of these are sql key words, so queries either don't work or return the wrong value. Doing a "select default from ss.columns;" returns "syntax error, unexpected DEFAULT in: "select default"" error. Doing a "select null from sys.columns;" returns null for all records, even though it is a Boolean column with true/false values. I am currently working around this by doing "select *" and pulling out the relevant fields, but, I want to protect against a future release possibly changing the column ordering or changing the number of columns in this table (without also requiring an update to our code). Is there a way to escape the column names in a way that gives me what I want? Thanks, Dave

2 1