- users-list - monetdb.org

Announcement: New Aug2018 Feature release of MonetDB suite
by Sjoerd Mullender 31 Aug '18

31 Aug '18

The MonetDB team at CWI/MonetDB BV is pleased to announce the Aug2018 feature 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/>. Aug 2018 feature release (11.31.7) MonetDB5 Server * The lsst module was moved to a separate repository (https://dev.monetdb.org/hg/MonetDB-lsst/) Build Environment * Build the MonetDB-cfitsio RPM and libmonetdb5-server-cfitsio Debian/Ubuntu package. * On Windows, the separate MonetDB5-Geom installer has been incorporated into the main MonetDB5-SQL installer and is therefore no longer available as a separate download. Merovingian * Added a "logrotate" configuration file. See /etc/logrotate.d/monetdbd. * Changed the monetdb profilerstart command to be more robust. If the server or stethoscope crashed before, the pid file is still there, so the next time we try to start stethoscope, it will fail. Now the profilerstart command will check if a stethoscope process with the recorded pid is running. If not, we start stethoscope, assuming that something went wrong before. * Changed the monetdb stop command to try to stop stethoscope before stoping the server. The error conditions that can arrise from attempting to stop stethoscope are: + The database is not running. + The profilerlogpath is not set. + The profiler.pid file does not exist or is inaccessible. + The contents of the profiler.pid are not valid. + Shutdown of stethoscope did not succeed. + Removing the profiler.pid file failed. In all the cases, the attempt to stop the server can continue normally, so we actually ignore any errors that rise from the attempt to stop stethoscope. Client Package * ODBC: Implemented SQL_ATTR_QUERY_TIMEOUT parameter in SQLSetStmtAttr. * ODBC SQLGetInfo now returns a positive numeric value for InfoTypes: SQL_MAX_COLUMN_NAME_LEN, SQL_MAX_DRIVER_CONNECTIONS, SQL_MAX_IDENTIFIER_LEN, SQL_MAX_PROCEDURE_NAME_LEN, SQL_MAX_SCHEMA_NAME_LEN, SQL_MAX_TABLE_NAME_LEN and SQL_MAX_USER_NAME_LEN. * Added a '-f rowcount' option in mclient to repress printing the actual data of a resultset, but only print the number of returned tuples Stream Library * Added support for lz4 compressed files in the stream library MonetDB Common * Hash indexes are now persistent across server restarts. * The macros bunfastapp and tfastins and variants no longer set the dirty flag of the heap they write to. This now needs to be done separately (and preferably outside of the inner loop). * Removed batDirty flag from BAT record. Its function is completely superseded by batDirtydesc and the dirty flags on the various heaps. * Removed "masksize" argument of function BAThash. * A whole bunch of functions that took an int argument that was used as a Boolean (true/false) value now take a value of type bool. The functions BATkeyed, BATordered and BATordered_rev now return a bool instead of an int. * Removed the tdense property: it's function is completely replaced by whether or not tseqbase is equal to oid_nil. Testing Environment * Removed helper programs Mtimeout and MkillUsers: they have long been superseded by timeout handling by Mtest.py itself. SQL Frontend * Removed deprecated table producing system functions: sys.dependencies_columns_on_functions() sys.dependencies_columns_on_indexes() sys.dependencies_columns_on_keys() sys.dependencies_columns_on_triggers() sys.dependencies_columns_on_views() sys.dependencies_functions_on_functions() sys.dependencies_functions_on_triggers() sys.dependencies_keys_on_foreignkeys() sys.dependencies_owners_on_schemas() sys.dependencies_schemas_on_users() sys.dependencies_tables_on_foreignkeys() sys.dependencies_tables_on_functions() sys.dependencies_tables_on_indexes() sys.dependencies_tables_on_triggers() sys.dependencies_tables_on_views() sys.dependencies_views_on_functions() sys.dependencies_views_on_triggers() They are replaced by new system dependency_* views: sys.dependency_args_on_types sys.dependency_columns_on_functions sys.dependency_columns_on_indexes sys.dependency_columns_on_keys sys.dependency_columns_on_procedures sys.dependency_columns_on_triggers sys.dependency_columns_on_types sys.dependency_columns_on_views sys.dependency_functions_on_functions sys.dependency_functions_on_procedures sys.dependency_functions_on_triggers sys.dependency_functions_on_types sys.dependency_functions_on_views sys.dependency_keys_on_foreignkeys sys.dependency_owners_on_schemas sys.dependency_schemas_on_users sys.dependency_tables_on_foreignkeys sys.dependency_tables_on_functions sys.dependency_tables_on_indexes sys.dependency_tables_on_procedures sys.dependency_tables_on_triggers sys.dependency_tables_on_views sys.dependency_views_on_functions sys.dependency_views_on_procedures sys.dependency_views_on_views * Implemented group_concat(X,Y) aggregate function which also concatenates a column of strings X, but using a supplied string Y as the separator. This function is also a SQL extension. * Implemented group_concat(X) aggregate function which concatenates a column of strings using a comma as a separator. This function is not featured in the SQL standard. Bug Fixes * 4020: Importing timestamp with zone from copy into * 6506: Improper performance counters * 6556: Sqlitelogictest division by zero on COALESCE call * 6564: Changes to the Remote Table definition * 6575: Sqlitelogictest crash on groupby query with coalesce call * 6579: Sqlitelogic test infinite loop while compiling SQL query * 6586: Sqlitelogictest crash on complex aggregation query * 6593: Poor performance with like operator and escape clause * 6596: Multicolumn aggregation very slow after ANALYZE when persistent hashes are enabled * 6605: Sqlitelogictest set queries with wrong results * 6606: Misleading parameter name in generate_series function * 6610: Sqlitelogictest algebra.rangejoin undefined * 6611: Cannot compile with GCC 8.1 and --enable-debug=no * 6612: Implement BLOB handling in python UDFs * 6618: dependency column on sequence violated by DROP SEQUENCE * 6621: SELECT FROM REMOTE TABLE WHERE <> returns wrong results * 6624: "Cannot use non GROUP BY column in query results without an aggregate function" when using aggregate function in both HAVING and ORDER BY clauses. * 6625: OR in subselect causes the server to crash with segmentation fault * 6627: stddev_pop inconsistent behaviour * 6628: User cannot insert into own local temporary table * 6629: CREATE TABLE IF NOT EXISTS returns 42000! * 6630: Sqlitelogictest cast NULL to integer failing * 6632: Dataflow causes crash when THRnew fails * 6633: ILIKE clauses don't work on certain characters * 6635: monetdbd exits due to "Too many open files" error * 6637: Within a transaction, \d after an error causes mclient to exit * 6638: (sequences of) mkey.bulk_rotate_xor_hash() can generate NIL from non-NIL making multi-col joins return wrong results * 6639: COMMENT ON TABLE abc IS NULL invalidly sets the remark column to null where remark column is defined as NOT NULLable

1 0

Re: #!ERROR: THRnew: too many threads
by Stefan Manegold 14 Aug '18

14 Aug '18

Hi, The usual questions:Which version of MonetDBAndWhat kind of workload triggers this? Best,Stefan -------- Original message --------From: "Murthy, Gautham" <Gautham.Murthy(a)harman.com> Date: 13/08/2018 18:06 (GMT+01:00) To: users-list(a)monetdb.org Subject: #!ERROR: THRnew: too many threads Hello, We had a DB crash reported with the below messages, Do we need alter any system level parameters or changes to avoid the below issue – Server has 128 CPU and 1TB memory. 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads /etc/security/limits.conf monetadmin soft nproc 102400 monetadmin hard nproc 102400 monetadmin soft nofile 500000 monetadmin hard nofile 500000 monetadmin soft stack 10240 monetadmin soft memlock unlimited monetadmin hard memlock unlimited [monetadmin@lnx1535 DBFARM_TSV_P_B]$ /monet_binaries/MonetDB-11.27.13_PY/bin/monetdb -p 50010 get all TSV_PROD_DB_B name prop source value TSV_PROD_DB_B name - TSV_PROD_DB_B TSV_PROD_DB_B type default database TSV_PROD_DB_B shared default yes TSV_PROD_DB_B nthreads default 128 TSV_PROD_DB_B optpipe local sequential_pipe TSV_PROD_DB_B readonly local yes TSV_PROD_DB_B embedr default no TSV_PROD_DB_B embedpy local yes TSV_PROD_DB_B embedpy3 default no TSV_PROD_DB_B nclients local 2048 TSV_PROD_DB_B dbextra default <unknown> [monetadmin@lnx1535 DBFARM_TSV_P_B]$ 2018-08-13 08:46:30 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:30 MSG merovingian[36790]: proxying client 10.106.5.250:29929 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:30 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:33 MSG merovingian[36790]: proxying client 10.106.5.250:29957 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:33 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:36 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:40 MSG merovingian[36790]: proxying client 10.106.5.250:64830 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:40 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:42 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:47:47 MSG discovery[36790]: new neighbour lnx1536.ch3.prod.i.com (lnx1536.ch3.prod.i.com) 2018-08-13 08:47:48 MSG discovery[36790]: new database mapi:monetdb://lnx1536.ch3.prod.i.com:50010/TSV_PROD_DB_B (ttl=660s) 2018-08-13 08:48:55 MSG merovingian[36790]: database 'TSV_PROD_DB_B' (57408) has crashed (dumped core) 2018-08-13 08:49:12 MSG merovingian[36790]: database 'TSV_PROD_DB_B' has crashed after start on 2018-08-13 05:35:17, attempting restart, up min/avg/max: 2h/5d/3w, crash average: 1.00 0.70 0.43 (23-10=13) 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: arguments: /monet_binaries/MonetDB-11.27.13_PY/bin/mserver5 --dbpath=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B --set merovingian_uri=mapi:monetdb://lnx1535.ch3.prod.i.com:50010/TSV_PROD_DB_B --set mapi_open=false --set mapi_port=0 --set 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: mapi_usock=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock --set monet_vault_key=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.vaultkey --set gdk_nr_threads=128 --set max_clients=2048 --set sql_optimizer=sequential_pipe --set embedded_py=true --readonly --set monet_daemon=yes 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:16467 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:45439 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 MSG merovingian[36790]: starting a proxy failed: cannot connect: Connection refused 2018-08-13 08:49:12 ERR control[36790]: !monetdbd: an internal error has occurred 'cannot connect: Connection refused' 2018-08-13 08:49:12 ERR merovingian[36790]: client error: cannot connect: Connection refused 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:45447 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 ERR control[36790]: !monetdbd: an internal error has occurred 'cannot connect: Connection refused' 2018-08-13 08:49:12 MSG merovingian[36790]: starting a proxy failed: cannot connect: Connection refused 2018-08-13 08:49:13 ERR merovingian[36790]: client error: cannot connect: Connection refused 2018-08-13 08:49:13 MSG merovingian[36790]: proxying client 10.106.5.250:36605 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B /etc/security/limits.conf monetadmin soft nproc 102400 monetadmin hard nproc 102400 monetadmin soft nofile 500000 monetadmin hard nofile 500000 monetadmin soft stack 10240 monetadmin soft memlock unlimited monetadmin hard memlock unlimited [monetadmin@lnx1535 DBFARM_TSV_P_B]$ /monet_binaries/MonetDB-11.27.13_PY/bin/monetdb -p 50010 get all TSV_PROD_DB_B name prop source value TSV_PROD_DB_B name - TSV_PROD_DB_B TSV_PROD_DB_B type default database TSV_PROD_DB_B shared default yes TSV_PROD_DB_B nthreads default 128 TSV_PROD_DB_B optpipe local sequential_pipe TSV_PROD_DB_B readonly local yes TSV_PROD_DB_B embedr default no TSV_PROD_DB_B embedpy local yes TSV_PROD_DB_B embedpy3 default no TSV_PROD_DB_B nclients local 2048 TSV_PROD_DB_B dbextra default <unknown> [monetadmin@lnx1535 DBFARM_TSV_P_B]$

2 2

#!ERROR: THRnew: too many threads
by Murthy, Gautham 13 Aug '18

13 Aug '18

Hello, We had a DB crash reported with the below messages, Do we need alter any system level parameters or changes to avoid the below issue - Server has 128 CPU and 1TB memory. 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads /etc/security/limits.conf monetadmin soft nproc 102400 monetadmin hard nproc 102400 monetadmin soft nofile 500000 monetadmin hard nofile 500000 monetadmin soft stack 10240 monetadmin soft memlock unlimited monetadmin hard memlock unlimited [monetadmin@lnx1535 DBFARM_TSV_P_B]$ /monet_binaries/MonetDB-11.27.13_PY/bin/monetdb -p 50010 get all TSV_PROD_DB_B name prop source value TSV_PROD_DB_B name - TSV_PROD_DB_B TSV_PROD_DB_B type default database TSV_PROD_DB_B shared default yes TSV_PROD_DB_B nthreads default 128 TSV_PROD_DB_B optpipe local sequential_pipe TSV_PROD_DB_B readonly local yes TSV_PROD_DB_B embedr default no TSV_PROD_DB_B embedpy local yes TSV_PROD_DB_B embedpy3 default no TSV_PROD_DB_B nclients local 2048 TSV_PROD_DB_B dbextra default <unknown> [monetadmin@lnx1535 DBFARM_TSV_P_B]$ 2018-08-13 08:46:30 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:30 MSG merovingian[36790]: proxying client 10.106.5.250:29929 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:30 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:31 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:33 MSG merovingian[36790]: proxying client 10.106.5.250:29957 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:33 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:36 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:46:40 MSG merovingian[36790]: proxying client 10.106.5.250:64830 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:46:40 MSG merovingian[36790]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-08-13 08:46:42 MSG TSV_PROD_DB_B[57408]: #!ERROR: THRnew: too many threads 2018-08-13 08:47:47 MSG discovery[36790]: new neighbour lnx1536.ch3.prod.i.com (lnx1536.ch3.prod.i.com) 2018-08-13 08:47:48 MSG discovery[36790]: new database mapi:monetdb://lnx1536.ch3.prod.i.com:50010/TSV_PROD_DB_B (ttl=660s) 2018-08-13 08:48:55 MSG merovingian[36790]: database 'TSV_PROD_DB_B' (57408) has crashed (dumped core) 2018-08-13 08:49:12 MSG merovingian[36790]: database 'TSV_PROD_DB_B' has crashed after start on 2018-08-13 05:35:17, attempting restart, up min/avg/max: 2h/5d/3w, crash average: 1.00 0.70 0.43 (23-10=13) 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: arguments: /monet_binaries/MonetDB-11.27.13_PY/bin/mserver5 --dbpath=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B --set merovingian_uri=mapi:monetdb://lnx1535.ch3.prod.i.com:50010/TSV_PROD_DB_B --set mapi_open=false --set mapi_port=0 --set 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: mapi_usock=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock --set monet_vault_key=/monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.vaultkey --set gdk_nr_threads=128 --set max_clients=2048 --set sql_optimizer=sequential_pipe --set embedded_py=true --readonly --set monet_daemon=yes 2018-08-13 08:49:12 MSG TSV_PROD_DB_B[72455]: 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:16467 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:45439 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 MSG merovingian[36790]: starting a proxy failed: cannot connect: Connection refused 2018-08-13 08:49:12 ERR control[36790]: !monetdbd: an internal error has occurred 'cannot connect: Connection refused' 2018-08-13 08:49:12 ERR merovingian[36790]: client error: cannot connect: Connection refused 2018-08-13 08:49:12 MSG merovingian[36790]: proxying client 10.106.5.250:45447 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B 2018-08-13 08:49:12 ERR control[36790]: !monetdbd: an internal error has occurred 'cannot connect: Connection refused' 2018-08-13 08:49:12 MSG merovingian[36790]: starting a proxy failed: cannot connect: Connection refused 2018-08-13 08:49:13 ERR merovingian[36790]: client error: cannot connect: Connection refused 2018-08-13 08:49:13 MSG merovingian[36790]: proxying client 10.106.5.250:36605 for database 'TSV_PROD_DB_B' to mapi:monetdb:///monet_data02/DBFARM_TSV_P_B/TSV_PROD_DB_B/.mapi.sock?database=TSV_PROD_DB_B /etc/security/limits.conf monetadmin soft nproc 102400 monetadmin hard nproc 102400 monetadmin soft nofile 500000 monetadmin hard nofile 500000 monetadmin soft stack 10240 monetadmin soft memlock unlimited monetadmin hard memlock unlimited [monetadmin@lnx1535 DBFARM_TSV_P_B]$ /monet_binaries/MonetDB-11.27.13_PY/bin/monetdb -p 50010 get all TSV_PROD_DB_B name prop source value TSV_PROD_DB_B name - TSV_PROD_DB_B TSV_PROD_DB_B type default database TSV_PROD_DB_B shared default yes TSV_PROD_DB_B nthreads default 128 TSV_PROD_DB_B optpipe local sequential_pipe TSV_PROD_DB_B readonly local yes TSV_PROD_DB_B embedr default no TSV_PROD_DB_B embedpy local yes TSV_PROD_DB_B embedpy3 default no TSV_PROD_DB_B nclients local 2048 TSV_PROD_DB_B dbextra default <unknown> [monetadmin@lnx1535 DBFARM_TSV_P_B]$

1 0

SQL Statements are skipped when run via scripts concurrently on different tables
by Murthy, Gautham 06 Aug '18

06 Aug '18

Hello, Application Teams are experiencing a behavior where few statements are skipped when executed via scripts, They are running multiple scripts concurrently on different tables. Please let us know if there are any know issues or a fix for the reported behavior. Database: MonetDB v11.27.13 (Jul2017-SP4) ü DROP, CREATE, INSERT - New SQL sequence, however looks like SQL statement are not executing as in order in database as per system catalog table log time entries. We need to check on this. ü First DROP, INSERT started and then after couple of statements, CREATE SQL started executing. DROP TABLE IF EXISTS "KEYFOOD_IT_S_22_43321_C";^M CREATE TABLE "KEYFOOD_IT_S_22_43321_C"(ATTR_NAME VARCHAR(500) NOT NULL, COL_NAME VARCHAR(30) NOT NULL, AVP_KEY INTEGER NOT NULL, ATTR_VALUE VARCHAR(3000), ATTR_LONG_VALUE VARCHAR(3000), ATTR_SHORT_VALUE VARCHAR(100), ATTR_MEDIUM_VALUE VARCHAR(100), PARENT_AVP_KEY INTEGER, SORT_ORDER INTEGER); INSERT INTO "KEYFOOD_IT_S_22_43321_C" (ATTR_NAME, COL_NAME, AVP_KEY, ATTR_VALUE, ATTR_LONG_VALUE, ATTR_SHORT_VALUE, ATTR_MEDIUM_VALUE, PARENT_AVP_KEY, SORT_ORDER) SELECT ATTR_NAME, COL_NAME, AVP_KEY, ATTR_VALUE, ATTR_LONG_VALUE, ATTR_SHORT_VALUE, ATTR_MEDIUM_VALUE, PARENT_AVP_KEY, SORT_ORDER FROM "IT_DSJ_KEYFOOD_43321" WHERE COL_NAME='S_22_KEY' AND SORT_TYPE='CATG'; We were able to statements being skipped in sys.querylog_catalog and sys.querylog_calls. Thank You, Gautham

2 1

incorrect data being fetched with WHERE clause
by Murthy, Gautham 06 Aug '18

06 Aug '18

Hello, Data is retrieved which are not available in "where" clause. Application teams have seen some cases where this works fine after some hours. After data load we are seeing incorrect data being fetched. Are there any know issues with the below versions ? Database: MonetDB v11.27.13 (Jul2017-SP4) sql>select * FROM "IT_DSJ_NCKLDN_45121" WHERE COL_NAME='M_59647_KEY' AND SORT_TYPE='ORTH'; +-------------------+--------------+------------+-------------------+-------------------+--------------------+--------------------+------+-------+------+ | attr_name | col_name | avp_key | attr_value | attr_long_value | attr_short_value | attr_medium_value | pare | sort_ | sort | : : : : : : : : nt_a : order : _typ : : : : : : : : : vp_k : : e : : : : : : : : : ey : : : +===================+==============+============+===================+===================+====================+====================+======+=======+======+ | ABRASIVE INFO | S_22_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | CATG | | ABRASIVE INFO | S_22_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | ORTH | | ABRASIVE INFO | S_22_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | ORTH | | ABSORBENCY LEVEL | S_23_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | CATG | | ABSORBENCY LEVEL | S_23_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | ORTH | | ABSORBENCY LEVEL | S_23_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | ORTH | | ACTIVE INGREDIENT | S_25_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | CATG | | ACTIVE INGREDIENT | S_25_KEY | 30000 | N/A | N/A | N/A | N/A | null | 30000 | ORTH | Thank You, Gautham

2 1

Do "upserts" supports in MonetDB?
by Sharma, Sreejith 06 Aug '18

06 Aug '18

Hello, Is there any roadmap for implementing "upserts" or "merge" alike in oracle for MonetDB? Or is it already available? Regards, Sreejith

1 0

Django + MonetDB
by ALEX OSSIPOV 05 Aug '18

05 Aug '18

Hi All I was looking to use MonetDB with Django framework but couldn't find MonetSQLdb package and all references point to 10 years old git code. Is it possible to use current versions of MonetDB with Django and where can I find required packages with configuration instructions? Thanks Alex

1 0

outstanding updates
by Roberto Cornacchia 03 Aug '18

03 Aug '18

Hi, We are having a problem, for a while, that is difficult to isolate. Our ETL includes the following steps: 1) Open JDBC connection, add data to a table, commit, close connection 2) Open JDBC connection, some ALTER TALBLE statements, commit, close connection Occasionally, we get the following during step 2) : java.sql.SQLException: ALTER TABLE: set READ or INSERT ONLY not possible with outstanding updates (wait until updates are flushed) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2732) What puzzles me is that step one is completed when this happens, the commit is done and the connection is closed (we triple-checked that is actually is closed). Notice that inserting a sleep between the to steps makes it work correctly. As I said, this is hard to isolate and reproduce. Still, can anyone guess what is exactly happening? Can it be that a background process is still flushing updates from step 1) ? Even if the JDBC connection is closed ? Can we force a blocking flush, so that it doesn't return until it's safe? If not, how can we know when updates are flushed? Thanks, Roberto

2 5

Mercurial source compilation fails on Mac OS 10.13.5
by Ying Zhang 19 Jul '18

19 Jul '18

Dear all, Not sure how long this has been going on, but I was just trying to refresh my MonetDB installation (compiled from Mercurial source code), but it failed with the following errors: In file included from /Users/jennie/monet/Mar2018/sql/backends/monet5/UDF/pyapi3/pyapi3.c:13: /Users/jennie/monet/Mar2018/sql/backends/monet5/UDF/pyapi3/../pyapi/pyapi.c:968:18: error: assigning to 'char *' from 'const char *' discards qualifiers [-Werror,-Wincompatible-pointer-types-discards-qualifiers] retnames[i] = PyUnicode_AsUTF8(colname); ^ ~~~~~~~~~~~~~~~~~~~~~~~~~ /Users/jennie/monet/Mar2018/sql/backends/monet5/UDF/pyapi3/../pyapi/pyapi.c:1382:19: error: assigning to 'char *' from 'const char *' discards qualifiers [-Werror,-Wincompatible-pointer-types-discards-qualifiers] py_error_string = PyString_AS_STRING(error); ^ ~~~~~~~~~~~~~~~~~~~~~~~~~ Mac OS 10.13.5 Darwin Kernel Version 17.6.0 Happens with both Mar2018 and default branch Does anyone know how to fix these errors and get MonetDB compiled? Thanks in advance! Regards, Jennie

2 2

Mar2018 production compilation fails on Fedora 28
by Roberto Cornacchia 19 Jul '18

19 Jul '18

Compiling from Mar2018 branch, today's hg tip, with production settings: --disable-debug --disable-developer --disable-assert --enable-optimize Fails with this on Fedora 28: In file included from /usr/include/string.h:494, from /home/roberto/MonetDBServer/MonetDB.Spinque_Mar2018/src/gdk/gdk.h:316, from /home/roberto/MonetDBServer/MonetDB.Spinque_Mar2018/src/gdk/gdk_calc.c:10: In function ‘memcpy’, inlined from ‘addstr_loop’ at /home/roberto/MonetDBServer/MonetDB.Spinque_Mar2018/src/gdk/gdk_calc.c:3415:3: /usr/include/bits/string_fortified.h:34:10: error: ‘__builtin_memcpy’ forming offset [3, 65535] is out of the bounds [0, 2] of object ‘str_nil’ with type ‘const char[2]’ [-Werror=array-bounds] return __builtin___memcpy_chk (__dest, __src, __len, __bos0 (__dest)); ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ (same error in several files) Compiling with debug settings succeeds: --enable-debug --enable-developer --enable-assert --disable-optimize Is this reproducible or has it something to do with my machine?

3 2

MonetDB Mar2018 Release - Connection terminates abruptly with no error codes/messages
by Murthy, Gautham 18 Jul '18

18 Jul '18

Hello, MonetDB release - MonetDB Mar2018 We are facing issues when trying to run queries concurrently on the newer version of MonetDB. When 2 or more sessions (create/drop tables) are executed concurrently, sessions get terminated without any error messages. Do we need to explicitly enable error feedback? Does hitting concurrency abruptly terminates the session/connection ? [monetadmin@lnx1490 ~]$ /monet_binaries/MonetDB-11.27.13_PY/bin/mclient mapi:monetdb://lnx1490.ch3.prod.i.com:50105/TSV_PROD_DB_A_C -u DM_POS_TSV_MRT_P6A password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP4) Database: MonetDB v11.29.7 (Mar2018-SP1), 'mapi:monetdb://lnx1490.ch3.prod.i.com:50105/TSV_PROD_DB_A_C' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\</factorydata/prddata02/prd/crosscut_output/create_desc_it_dsj_campbell_33423.sql [monetadmin@lnx1490 ~]$ Thank You, Gautham

1 0

MonetDB Crashed - Version Jul2017-SP4 - GDKmmap(327680) fails, try to free up space memory in use=48376596752, virtual memory in use=102304729360
by Murthy, Gautham 12 Jul '18

12 Jul '18

Hello All, We had a crash reported on our MonetDB database, Below are the error messages. Can you share any insights on the below reported issue. There were no memory/cpu crunch during the crash, System had ample resource available. MonetDB Version - Jul2017-SP4 2018-07-03 06:33:40 ERR TSV_PROD_DB_A315293<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315293>: #GDKmmap(327680) fails, try to free up space memory in use=48376596752,virtual memory in use=102304729360<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/memory+in+use%3…> 2018-07-03 06:33:40 ERR TSV_PROD_DB_A315293<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315293>: #GDKmmap(327680) fails, try to free up space memory in use=48368938424,virtual memory in use=102295694776<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/memory+in+use%3…> 2018-07-03 06:33:40 ERR TSV_PROD_DB_A315293<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315293>: #GDKmmap(327680) fails, try to free up space memory in use=48367612864,virtual memory in use=102294369216<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/memory+in+use%3…> 2018-07-03 06:33:40 ERR TSV_PROD_DB_A315293<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315293>: #GDKmmap(327680) fails, try to free up space memory in use=48374211792,virtual memory in use=102300968144<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/memory+in+use%3…> 2018-07-03 06:33:47 ERR TSV_PROD_DB_A315293<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315293>: #GDKmmap(524288) fails, try to free up space memory in use=48312652864,virtual memory in use=103295521856<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/memory+in+use%3…> 2018-07-03 06:34:33 MSG merovingian315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: database 'TSV_PROD_DB_A' has crashed after start on 2018-07-02 04:52:12, attempting restart, up min/avg/max: 2h/6d/4w, crash average: 1.00 0.60 0.20 (14-8=6) 2018-07-03 06:34:38 ERR control315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: !monetdbd: an internal error has occurred 'unknown or impossible state: 4' 2018-07-03 06:34:40 ERR merovingian315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: client error: unknown or impossible state: 4 2018-07-03 06:34:40 ERR control315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: !monetdbd: an internal error has occurred 'unknown or impossible state: 4' 2018-07-03 06:34:41 ERR merovingian315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: client error: unknown or impossible state: 4 2018-07-03 06:34:41 ERR control315279<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/315279>: !monetdbd: an internal error has occurred 'unknown or impossible state: 4' What was running during the crash ? : Users were dropping, recreating tables and loading data. monetadmin@lnx1535 ~<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/monetadmin%40ln…>$ cat /etc/redhat-release CentOS Linux release 7.4.1708 (Core) monetadmin@lnx1535 ~<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/monetadmin%40ln…>$ free -g total used free shared buff/cache available Mem: 1007 199 419 1 388 804 Swap: 31 0 31 monetadmin@lnx1535 ~<https://monetdbsolutions.atlassian.net/wiki/display/SUPPORT/monetadmin%40ln…>$ cat /proc/cpuinfo|grep -i processor|wc -l 128 Thank You, Gautham

1 0

Memory management, 8 years later
by Roberto Cornacchia 28 Jun '18

28 Jun '18

Hi all, 8 yeas after this interesting thread about using ulimit to limit the data segment size ( https://www.monetdb.org/pipermail/users-list/2010-March/004007.html) a few things have changed and I find myself a bit lost again about memory management. At some point (can't remember when) mserver5 stopped respecting ulimit. No matter how I change the limits, it always sees all available physical ram. Does any other mechanism exist? The issue I'm facing all the time, and for which I find very little documentation, is: - multiple dbfarms, possibly tens of mserver5 instances, running on the same (virtual) machine, plus other applications - Not necessarily, but most times each dmfarm is served by a Docker container - CPU and memory resources need to be share fairly (ideally, controlling priorities) - No mserver5 should ever be killed because of memory issues Now, especially when using Docker, there are means to control the maximum memory size allowed to each process. However, the resolution in case of overusage is to kill the process. That is of course not a solution. What I'm looking for is: can I set a cap to resources *at process level*, in a way that mserver5 will just *see* those, make the best out of them, and never try to use more? (yes, one way would be a VM, but far from ideal for me) Does anyone have experience with / tips about multi-dbfarm environments? Thanks! Roberto

2 2

Troublesome Query
by Doherty, Adam 23 Jun '18

23 Jun '18

Hi all: I'm trying to figure how to calculate the time delta between two events. My events have a timestamp, event source, event type, and an event state and event id. So an event looks like - event_date_time event_source event_type event_state event_id 2018-06-22 09:05:07.000000 MODULE123 TYPEABC ACK 24567890 2018-06-22 11:32:14.000000 MODULE123 TYPEABC UNACK 24812266 This makes up a unique "event", start to finish. However I have hundreds or thousands of entries between and I want be able to group them together in their unique pairings as above. I'm not sure how to wrap my head around this one. Adam Doherty

2 1

Performance issues when streaming records from table.
by Manuel Pagliai 20 Jun '18

20 Jun '18

We are interested in adding support to import records from Monetdb tables in the ETL we are developing. We have performance issues when executing queries like: select col0, col1, ... coln from <table> on tables with a large number of records. What I am concerned about is the time spent upfront, before the first record is retrieved: once records become available we can start processing them. For example, on my machine, executing a executing a select * from <table> query on a 68 million records table takes around 24 seconds for the query to execute, before it is actually possible to start retrieving records from the server response. Queries are submitted via the JDBC driver. Is there a more efficient way of streaming records from a table ? Thanks, Best regards, *Manuel Pagliai* Visokio Ltd www.visokio.com +44 20 7351 4646

1 0

Announcement: New Mar2018-SP1 Bugfix release of MonetDB suite
by Panagiotis Koutsourakis 20 Jun '18

20 Jun '18

The MonetDB team at CWI/MonetDB BV is pleased to announce the Mar2018-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/>. Mar 2018-SP1 bugfix release (11.29.7) Build Environment * On Linux and Unix, try not to link to libraries multiple times: that only causes the code (and worse, the variables in those libraries) to be included multiple times. On Windows, we do need to link to libraries multiple times (once for each DLL we create if the DLL references anything from the library) but that doesn't cause the linked library to be loaded multiple times. SQL Frontend * Corrected the definition of view: sys.ids. Bug Fixes * 6562: Sqlitelogictest crash on group by query with not in operator * 6565: Sqlitelogictest crash on complex select query with coalesce call * 6566: Sqlitelogictest unavailable calc.- MAL operations * 6568: Sqlitelogictest crash on complex case query * 6569: Sqlitelogictest select query with not between cause with wrong results * 6570: Sqlitelogictest select coalesce undefined calc * 6572: ordered index Error in optimizer garbageCollector * 6573: Sqlitelogictest crash on complex select query with case statement * 6574: server crashed could not find ordered index while creating a table * 6576: Sqlitelogictest aritmetic expressions with negative numbers handling * 6577: creating temp table kills performance of the original query * 6578: One two-tuple insert gives different results than two single inserts * 6581: Join condition errors. * 6583: Fixed size string concatenation with integer results in fixed size string of size 0 * 6584: SELECT FROM REMOTE TABLE WHERE IS NOT NULL produces wrong result * 6585: Nested Merge tables cause an infinite loop in rel_optimizer * 6587: Sqlitelogictest crash on complex case statement * 6589: Sqlitelogictest crash on complex on complex expression * 6594: Sqlitelogictest crash on complex case statement * 6595: Remote decimal division triggers assertion / returns wrong answer * 6598: Python 3.4 not supported (due to usage of Py_DecodeLocale) * 6600: Sqlitelogictest queries fail to execute * 6601: "where is null" clause on remote table causes problem with next query * 6602: Sqlitelogictest wrong results in IN query * 6603: Sqlitelogictest: Aggregation query with distinct clause produces duplicated rows

1 0

pymonetdb or Python UDF
by Doherty, Adam 17 Jun '18

17 Jun '18

Hello all: This is more of curiosity but I have data in my events table currently trapped in a nvarchar field. I am wondering if I can build a table and do execute a select into with a python UDF pulling data from the existing fields plus the values of my nvarchar field. My string data has the following format - "key1=value, key2=val, key3=value"... I want to break out the data first by splitting on the , and then the = Keys would be the columns in the table. I can do this with a python script and pymonetdb but a UDF would be faster. Adam Doherty Process Control Systems Administrator Kearl Technical, Imperial 505 Quarry Park Blvd SE W3B.224 Calgary, AB T2C 5N1 adam.doherty(a)esso.ca<mailto:adam.doherty@esso.ca> P 587.476.4640<tel:+15874764640> M 403.869.1636<tel:+14038691636> imperialoil.ca<http://www.imperialoil.ca/Canada-English/default.aspx> | Twitter<http://www.twitter.com/imperialoil> | YouTube<http://www.youtube.com/imperialoil>

3 3

joinable generate_series with arguments from a table
by Stefan de Konink 14 Jun '18

14 Jun '18

Goodmorning, At this moment there is no functional compatibility between the PostgreSQL and MonetDB with respect to the generate_series function. The range in PostgreSQL is inclusive, in MonetDB exclusive. More importantly generate_series can't be used with argument that is joined from a different table, MonetDB just seems to support a subquery, hence joining that back without a primary is impossible. PostgreSQL; select generate_series(a, b), a from (select 1 as a, 2 as b) as a; generate_series | a -----------------+--- 1 | 1 2 | 1 (2 rows) MonetDB; select * from generate_series((select 1 as a, 2 as b)); +-------+ | value | +=======+ | 1 | +-------+ If I would like to functionally achieve the following; I have list that states the speeds over partial segments, identified by linear referencing. From 10 meter to 12 meter, the distance was 2 meter and speed was 2s. 10 2 2 I would like to expand these values to segments of one meter, hence end up with a table that would state: 10 2 2 11 2 2 12 2 2 Now I am aware of the Python bindings and I could achieve what I wanted by the function below, but I would find generate_series still very practical. DROP FUNCTION expand_per_meter; CREATE FUNCTION expand_per_meter(trip_hash BIGINT, userstopcode STRING, distancesincelastuserstop INTEGER, s INTEGER, t INTEGER) RETURNS TABLE(trip_hash BIGINT, userstopcode STRING, distancesincelastuserstop INTEGER, s INTEGER, t INTEGER) LANGUAGE PYTHON { result = dict() result['trip_hash'] = [] result['userstopcode'] = [] result['distancesincelastuserstop'] = [] result['s'] = [] result['t'] = [] for i in range(0, len(trip_hash)): expanded = range(distancesincelastuserstop[i], distancesincelastuserstop[i] + s[i] + 1) expand = len(expanded) result['trip_hash'] += [trip_hash[i]] * expand result['userstopcode'] += [userstopcode[i]] * expand result['distancesincelastuserstop'] += expanded result['s'] += [s[i]] * expand result['t'] += [t[i]] * expand return result }; -- Stefan

4 4

Re: Combine a table-returning function with the source table
by Mark Clements 13 Jun '18

13 Jun '18

Stefan, This is a good question. For the specific example, you are quite correct. However, the motivation for the question came from using the MonetDB-rmath extension and embedded R: often it is more efficient to calculate a number of values (e.g. the confidence intervals and p-values) together and then return all of the values as a table. This probably also reflects that I am a statistician and an old R user. The recent article by Raasveldt suggested that analysis objects from Python could be stored natively rather than being serialised BLOBs. This could be further extended to storing R objects natively. Members of the objects could then be extracted as they are needed. Admittedly, this would lose many relational advantages. My suggestion is halfway, where R and Python can be used for analytics, with simple relational storage of the results. Actually, is there any way to optimise a lateral call using BATs? -- Mark On 13 Jun 2018 17:43, Stefan Manegold <Stefan.Manegold(a)cwi.nl> wrote: Hi Mark, just out of curiosity, (in the given case/example) what's wrong with a simple select *, mean-1.96*sd as lci, mean+1.96*sd as uci from test; or create or replace function ci(se double) returns double begin return 1.96*se; end; select *, mean-ci(sd) as lci, mean+ci(sd) as uci from test; in case you really want/need to avoid the "redundant" occurrence or "1.96" ... Thanks! Best, Stefan ----- On Jun 13, 2018, at 5:30 PM, Mark Clements mark.clements(a)ki.se wrote: > Jennie, > > Thank you for replying to this inquiry - it's appreciated. > > Your solution gives the right result - but it requires changing the > function call and will not generalise easily to other column types or > other number of covariates. If we had another table such as: > > drop table test; > create table test as select 1 as id, 2 as sex, cast(3 as double) as > mean, cast(4.0 as double) as sd; > -- and repeating the function > create or replace function cis(mu double, se double) returns table(lci > double, uci double) > begin return select mu-1.96*se, mu+1.96*se; > end; > > we can get all of the test table combined with the confidence interval by: > > -- row_number() > select * from > (select *, row_number() over() as rid from test) as t1 > natural join > (select *, row_number() over() as rid from cis((select mean,sd from > test))) as t2; > -- or using lateral > select * from test, lateral cis(test.mean,test.sd); > > The row_number solution is fast, and the lateral solution is simple. I > was hoping for: > > select *, cis(mean,test).* from test; > > Again, thank you for your help. > > -- Mark > > On 06/08/2018 01:53 PM, Ying Zhang wrote: >> Hai Mark, >> >> I’m just wonder if this gives you wat you want: >> >> create function cis(mu double, se double) >> returns table(mu_out double, lci double, uci double) >> begin >> return select mu, mu-1.96*se, mu+1.96*se; >> end; >> >> Regards, >> Jennie > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/<http://www.CWI.nl/~manegold/> | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

Combine a table-returning function with the source table
by Mark Clements 13 Jun '18

13 Jun '18

For using the MonetDB-rmath extension, I was interested in using table-returning functions to return multiple values. Is there a fast and elegant way to combine the results with the source table in SQL? To make this concrete, consider some test data and a function to calculate a 95% confidence interval from a mean and standard error: drop table test; create table test as select cast(value as double) as value from generate_series(0,5); drop function cis(double,double); create function cis(mu double, se double) returns table(lci double, uci double) begin return select mu-1.96*se, mu+1.96*se; end; We can then call the table-returning function: select * from cis((select value, 1.0 from test)); If we want to re-join the results with the source table, we could use row_number() or use lateral: -- Using row_number: select * from (select *, row_number() over() as rid from test) as t1 natural join (select *, row_number() over() as rid from cis((select value,1.0 from test))) as t2; -- Using lateral: select * from test, lateral cis(test.value, cast(1.0 as double)) as t2; I believe that the latter will lead to a slow loop, even when BAT functions are available. The table-returning function can be used in the select statement only for a scalar expression: select cis(0,1).*; -- ok select cis(0,1).* from test; -- Error: no such operator 'cis' select cis(value,1).* from test; -- Error: identifier 'value' unknown Again, is there a fast and elegant way to combine the source table with a table-returning function in SQL? Kindly, Mark.

3 4

Temporal Function Issues
by Doherty, Adam 13 Jun '18

13 Jun '18

Hello all: I am trying to find the most efficient way to filter results for those matching today only. My events table has 550 million rows and I want a view as follows- CREATE VIEW events_today AS SELECT * FROM events WHERE str_to_date(timestamp_to_str(event_timestamp, '%Y-%m-%d'), '%Y-%m-%d') = CURRENT_DATE which should give me all events with today's date. Running the SELECT by itself also never returns. However, the query just hangs without ever returning. I'm running 11.29.3 (Mar2018) on CentOS 7 with 128GB and 16 Cores. Adam

2 2

High Memory Usage - Sever Not Releasing Resources?
by Doherty, Adam 08 Jun '18

08 Jun '18

Hi all: I ran into an issue yesterday when I ran a simple group count query - The query took 7 minutes, 13 seconds to run. Query was as follows - SELECT zone, count(*) FROM chronicle.events WHERE event_type = 'mb' GROUP BY zone This table, which a merge table currently has 546 million events. The merge table is made up of currently two merge table each representing a year (events_2018, and events_2017). Those merge tables are each made up of four tables, events_year_zone_(1-4) I have a scheduled task which inserts events into the relevant tables every 5 minutes from 5 Microsoft SQL Servers. Roughly 12,000 events broken across 5 inserts (one for each server). The import process is handled with a C# Windows service that utilizes ODBC to talk to MonetDB. The server is currently MonetDB v11.27.11 (July2017-SP3) running on VMware 6.5 in a CentOS 7 VM with 64GB of RAM, and 16 CPU cores. When I ran into this issue yesterday, the mserver5 process was utilizing 90% of available RAM. This was making my insertion task take longer and longer each time, and simple queries like the one above took minutes. Logged into the server via SSH, the input lag as a result of the almost total resource usage caused me to reboot the server. I restarted the VM, and the same query took 20 seconds. I'd like to sort where this issue might be coming from, but I'm not sure where to look. This server is currently part of a proof-of-concept project that will soon be going to production. Any guidance is greatly appreciated. Adam

2 2

ODBC Error on Insert
by Doherty, Adam 06 Jun '18

06 Jun '18

Hi All: I'm having some issues using ODBC with C#. I am trying to insert the contents of a DataTable into Monet using a foreach loop with MonetDB ODBC client under Windows. This is the error I receive... System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [MonetDB][ODBC Driver 11.27.11]syntax error, unexpected ',' in: "execute 5 (timestamp '2018-06-04 21:22:25',8651,'EVENT','','SYSTEM','1810_INFRA_" This is my loop which is wrapped in a transaction - foreach (DataRow row in events.Rows) { cmd.CommandText = String.Format("INSERT INTO chronicle.events_{0}_zone_{1} VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", year, zone); cmd.Parameters.AddWithValue("@date_time", row["Date_Time"]); cmd.Parameters.AddWithValue("@fracsec", row["FracSec"]); cmd.Parameters.AddWithValue("@event_type", row["Event_Type"]); Truncated for brevity. I'm not sure where the error in my insert statement is because the error message itself appear to be truncated. Very frustrating as it hangs on the first row. Adam

2 1

new MonetDB SQL overview documentation
by martin van dinther 31 May '18

31 May '18

1 0

JIT implementation of the rmath extension
by Mark Clements 30 May '18

30 May '18

The JIT functions by Mark Raasveldt provide a powerful alternative approach to bring C and C++ code into MonetDB. I have provided an alternative implementation of the MonetDB-rmath extension using JIT functions. The JIT implementation is considerably shorter and simpler, and slightly more general - where the code can deal with function arguments that are any combination of scalars and BATs. However, there are indications that the functions are considerably slower. As a minor point, I needed to rename the functions (e.g. using r_ as a prefix) -- otherwise the function names would collide with the function signatures in Rmath.h. To use the JIT-based rmath extension: git clone https://github.com/mclements/MonetDB-rmath.git git checkout jit make sudo make install monetdb create test monetdb release test monetdb set embedc=yes test mclient -d test -s "select r_pnorm(1.96,0,1);" As a challenge for JIT-based extensions, new C functions (e.g. new functions that call Rmath.h, and for which we want to call the new functions in MonetDB) will need a header file that is available to MonetDB. A fragile (non-portable?) solution to find the MonetDB include folder is to use: MONETDBINCLUDE = $(patsubst -I%,%,$(firstword $(shell pkg-config --cflags-only-I monetdb5))) in the Makefile. This assumes that the MonetDB include folder is the first include folder in CFLAGS. The extension-specific header for any new functions can then be copied to that folder: cp rmath_ext.h $(MONETDBINCLUDE) I hope that this is useful. Sincerely, Mark.

1 0