August 2019 - users-list

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

Re: Alter question about MonetDB
by Ying Zhang 06 Aug '19

06 Aug '19

Hai, This question should have been sent to the users mailing list, so I added that e-mail address. I can confirm this problem with an Apr2019 refreshed yesterday. Please see the 1st attachment. Also, after having restarted both mclients, I can still only query “tbl5” using the mclient on the left side. Please see the 2nd attachment. Can you please fire a bug report for this? Thanks! Jennie > On 6 Aug 2019, at 08:55, 角 <3089454366(a)qq.com> wrote: > > Hi,All: > I am a MonetDB user from China. I have some troubles when I was using MonetDB. I use mclient and enter this sql : "alter table t1 rename to t2；", while this mclient is alive,I use anthor mclient to connect the same database as the former and I enter this sql : "select * from t2" then I get an error : no such table. So how can I use alter command to make all mclients take effect? > _______________________________________________ > developers-list mailing list > developers-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/developers-list

1 0

Question on missing BAT file
by Sharma, Sreejith 06 Aug '19

06 Aug '19

All, Is there a way to figure out which BAT files constitutes a specific table in MonetDB? I know the location in sys.storage represents it, but I see one of the tail file is showing missing which is not available neither in sys.storage not in DB folder. We get this error during migration from older version (MonetDB 11.27.3) to MonetDB 11.33.3 version. 2019-07-31 10:43:54 MSG TSV_PROD_DB_A[161617]: !ERROR: BBPcheckbats: cannot stat file /monet_data01/DBFARM_TSV/TSV_PROD_DB_A/bat/10/45/104502.tail Regards, Sreejith

2 2

Fwd: Re: Managing Access to Monetdb Schemas
by ferreira 02 Aug '19

02 Aug '19

-------- Forwarded Message -------- Subject: Re: Managing Access to Monetdb Schemas Date: Fri, 2 Aug 2019 11:06:21 +0200 From: ferreira <pedro.ferreira(a)monetdbsolutions.com> To: Nobody Noone <nobody.noone.nobody.me(a)gmail.com> Hello! I found it strange why we don't support GRANT/REVOKE privileges statements on schemas. I looked into the SQL standard, and there is no mention of it. You can use roles to limit schema access for each user. Here you have an example: CREATE ROLE my_role; CREATE SCHEMA my_schema AUTHORIZATION my_role; CREATE USER my_user with password 'p1' name 'User with role' schema "my_schema"; GRANT my_role to my_user; You can add more fine grained access to each table with table privileges as you mentioned. As for specific rights such as DDL, we don't support it as of now. You can create a feature request on Bugzilla, but as it is not paid, it will have low priority. Best regards, Pedro On 7/31/19 4:40 PM, Nobody Noone wrote: > New to Monetdb, From a DBA perspective I’m having trouble > understanding how to manage access rights to schemas and the tables > therein. My department has many senior analysts who should have admin > rights to the tables within the schema (DML - select, insert, update, > delete, trunc, etc; DDL create table, drop table, create seq, etc). > There are also some users that need select only in a given schema. How > do I manage this without having to grant access per table? Is that > where roles come into play? Say super users get privilege_code_id= 79 > (SELECT,INSERT,UPDATE,DELETE,TRUNCATE) whereas some users would be > assigned privilege_code_id= 1 (SELECT). What about DDL rights? I am > not seeing those in the roles? I do see the ability when the schema is > created with CREATE SCHEMA "ABC" AUTHORIZATION "xyz_user". What I > don’t want to do is have to run a query that searches all tables in a > schema, grant admin DDL to them. > Thanks! > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list

1 0

Cast a string column with empty strings to int
by Ying Zhang 01 Aug '19

01 Aug '19

Hai, I have e.g. the following string column containing either numbers or empty strings: CREATE TABLE tst (s string); INSERT INTO tst VALUES ('1234'), (''), (‘4567'); I’d like to cast ’s' to an INT column. The following query works: sql>SELECT CAST(s AS INT) FROM (SELECT CASE WHEN s='' THEN NULL ELSE s END AS s FROM tst) AS tmp; +------+ | L4 | +======+ | 1234 | | null | | 4567 | +------+ 3 tuples But the following query doesn’t: sql>SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed. sql>SELECT CASE WHEN s='' THEN CAST(NULL as INT) ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed. This is probably because batcalc.int is applied on the whole “s” column as shown by the query plan: sql>EXPLAIN SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; +--------------------------------------------------------------------------------------------------------------------+ | mal | +====================================================================================================================+ … | C_7:bat[:oid] := sql.tid(X_6:int, "sys":str, "tst":str); | | X_10:bat[:str] := sql.bind(X_6:int, "sys":str, "tst":str, "s":str, 0:int); | | X_19:bat[:str] := algebra.projection(C_7:bat[:oid], X_10:bat[:str]); | | X_21:bat[:bit] := batcalc.==(X_19:bat[:str], "":str); | | X_24:bat[:bit] := batcalc.isnil(X_21:bat[:bit]); | | X_30:bat[:bit] := batcalc.ifthenelse(X_24:bat[:bit], false:bit, X_21:bat[:bit]); | | X_35:bat[:int] := batcalc.int(X_19:bat[:str]); | | X_36:bat[:int] := batcalc.ifthenelse(X_30:bat[:bit], nil:int, X_35:bat[:int]); | … Is my query wrong or the plan? Thanks, Jennie

2 2

Question with wildcarded expressions for LIKE clause
by Gotwisner, Dave 01 Aug '19

01 Aug '19

I am trying to do some constraints off the system tables to select a unique pattern of tables. We have tables like the following (I can't give the actual table names): * Config (a single record exists here, no merge tables use it) * Data_0000 (a small number of records exist in each of these) * Data_0001 * Data_0002 * Data_0003 * Data (this is a merge table over the last 4) * Test_table (a small number of records exist here, no merge tables use it) * Main__yyyy_mm_dd__hh__0000 (millions of records, yyyy, mm, dd, hh are all numbers, we have one set of tables per hour, and keep significantly over 1 weeks' worth of data) * Main__yyyy_mm_dd__hh__0001 * Main__yyyy_mm_dd__hh__0002 * Main__yyyy_mm_dd__hh__0003 * Main__yyyy_mm_dd__hh (this is a merge table over the last 4) If I do a select * from sys.tables where schema_id in (select id from schemas where name = 'frodo') and type in (select table_type_id from table_types where table_type_name = 'TABLE') I get the right thing - all non merge tables. I then want to further break it down into the tables that end in __[0-9][0-9][0-9][0-9] and those that don't (two separate queries for two separate purposes). Consider frodo the name of our schema. Adding and name like 'data\_\_____' appears to work. This should give me the 4 data_* tables. Adding and name like '%\_\_____' gives me all non-merge tables, including config and test_table. I'm assuming % is the wild card for 0 or more, and _ is the wild card for 1. This matches some documentation I have found. The \ are required because I need to explicitly look for 2 of the underscore character. Am I doing this correctly? Are these the right wildcarding characters? MS SQLServer gives a # as a match for a single digit, is there an equivalent? Or, is this just a bug in MonetDB? Thanks, Dave

3 5