Mar2025 (11.53)

The Mar2025 documentation can be found here.

Mar2025-SP1 Bugfix Release (11.53.9)

  • It is now possible to specify an idle timeout using –set idle_timeout=<seconds> (see mserver5 manual page) which gets triggered if a connection to the server is idle (i.e. does not send any queries to the server) while there is a SQL transaction active.

Client Package

  • When connecting to a database, if there are multiple monetdbd servers running, mclient will try them all, and also both UNIX domain sockets and then TCP, in order to find a server that accepts the connection. However, when a server that handles the requested database does exist but refuses the connection for some other reason, mclient would continue searching. This has now been changed. If monetdbd reports an error other than database unknown, mclient will now stop looking and report the error. This is actually a change in the “mapi” library, so any program using the library gets the new behavior.

  • There is a new option –quiet (or just -q) in mclient. If used, the welcome message that is normally printed in an interactive invocation is suppressed.

  • There is now a \dm command in the interactive mclient to show information about merge tables.

SQL Frontend

  • When a prepared statement is executed, sys.queue now shows the text of the original PREPARE statement along with the EXEC and its arguments.

  • Add optional parameters omit_unlogged (bool) and omit_table_ids (str) to sys.hot_snapshot(). If omit_unlogged is set to true, the data in UNLOGGED tables is omitted from the snapshot. If omit_table_ids is given, it must be a comma-separated list of table ids as found in sys.tables. The data in each of those tables will be omitted from the snapshot.

  • Empty BATs are omitted from the snapshot, the restored server will created them if necessary.

  • Corrected reading decimal type columns from external ODBC data sources via proto_loader(‘odbc:…’). Those columns were mapped to varchar type columns. Now they will be mapped to decimal type, when possible.

Merovingian

  • When mserver5 is started by monetdbd due to an implicit request (application trying to connect to a database), and mserver5 crashes or exits before a connection can be established, monetdbd will stop trying to start the server after a few attempts. When using an explicit command to start the server (using monetdb start), monetdbd will always attempt to start the server.

Bug Fixes

  • 7625: Missing entry in sys.table_types table for new LOCAL TEMPORARY VIEW

  • 7626: crash in window function with constant aggregation

  • 7627: Increased memory consumption, slowness and crash

  • 7629: monetdbd causes SELinux denial

  • 7632: Unexpected Left Join Crash

  • 7633: Unexpected Out of Memory of Inner Join

  • 7634: Join with subquery crash

  • 7636: Unexpected Anti Join Crash

  • 7638: PREPARE statement increases the memory use of the session even when DEALLOCATEd

  • 7644: Unexpected anti join crash

  • 7646: Unexpected Left Join Crash

  • 7654: Query remote table that targets remote server table not owned by monetdb default user

Mar2025 Feature Release (11.53.3)

  • There is a new shared library called libmutils that contains some utility functions that are used by several programs.

  • Hot snapshot: allow member files larger than 64 GiB. By member files we mean the files inside the resulting .tar file, not the tar file itself. Huge member files are written using a GNU tar extension to the original tar format, which doesn’t support more than 8 GiB.

Client Package

  • Support for dumping databases from servers from before Jul2021 (11.41.X) has been removed.

MonetDB Common

  • The implementation for the imprints index on numeric columns has been removed. It hasn’t been used in years, and when it is enabled, it doesn’t really make queries go faster.

Geom Module

  • Removed type geometryA (geometry array). It was deprecated in the Jun2023 release (11.47.X) because there was no use for the type.

MonetDB5 Server

  • Removed function bat.attach since it wasn’t used.

  • Removed the MAL type “identifier” and supporting functions. There has never been an SQL interface to this type.

  • Removed the MAL type “color” and supporting functions. There has never been an SQL interface to this type.

SQL Frontend

  • Added scalar functions: dayname(d date) and monthname(d date) returns varchar(10).

  • ranking window functions are now optimized into topn’s For the grouped case we added the missing grouped/heap based topn implementation.

  • Added support for reading external data in a generic way via table returning function: proto_loader(string uri). The uri string value must start with the scheme name, ending with : character. Supported schemes are: monetdb: and odbc:. The monetdb scheme allows you to connect to a remote MonetDB server and retrieve the data of a specific table or view in a specific schema. The uri syntax: monetdb://[<host>[:<port>]]/<database>/<schema>/<table> Example:

    SELECT * FROM proto_loader('monetdb://127.0.0.1:50000/demo_db/sys/tables');
    

    The odbc scheme allows you to connect to any ODBC data source via an ODBC driver and retrieve the data of a supplied query. The uri syntax:

     odbc:{{DSN|FILEDSN}=<data source name>|DRIVER=<path_to_driver>};
                        [<ODBC connection parameters>;]QUERY=<SQL query>
    

    For ODBC you normally configure a data source first. This is done using the ODBC administrator (on windows: odbcad32.exe, on linux: odbcinst). Once a data source for a specific ODBC driver has been setup using a unique name, you can reference it as: DSN=my_bigdata; or FILE_DSN=/home/usernm/dsns/my_bigdata.dsn; If you do not want to setup a data source, you can use DRIVER=…; to specify the ODBC driver program to use. However this also means you have to specify all the required connection parameters yourself, such as UID=…;PWD=…;DATABASE=…; etc. The QUERY=<SQL query> part is mandatory and must be specified at the end of the uri string, after the optional ODBC connection parameters. Examples:

    SELECT * FROM proto_loader(
    'odbc:DSN=Postgres;UID=claude;PWD=monet;QUERY=SELECT * FROM customers');
    SELECT * FROM proto_loader('odbc:DRIVER=/usr/lib64/libsqlite3odbc.so;
      Database=/home/martin/sqlite3/chinook.db;QUERY=SELECT * FROM customers');
    

    Note that the ‘odbc:’ scheme is experimental and not enabled by default. To enable it, the MonetDB server has to be started with argument: --loadmodule odbc_loader

  • Extended the constant aggregate optimizer in order to eliminate aggregates with constant arguments whenever possible.

  • REMOTE TABLES and REPLICA TABLES now fully support the monetdb:// and monetdbs:// URL’s introduced in Aug2024. Any mapi:monetdb:// URL’s are normalized to the new style.

  • Add function sa_msettings_create() to allocate an msettings object using the arena allocator.

  • Unused helper function mapiuri_database() has been removed from rel_remote.h.

  • Added support for aggregates which order within the group such as quantile and which potentially order within the group such as group_concat. The ordering for such operators in now handled once in the relational plan. For this the create function statements can now have an optional order specification, using the keywords ‘ORDERED’ and ‘WITH ORDER’.

  • Added support for recursive CTE’s.

  • The SQL parser was cleaned up. This resulted in some keywords being used more strictly. If any of these keywords are to be used as column names, they have to be quoted using double quotes: AS, TABLE, COLUMN, DISTINCT, EXEC, EXECUTE.

  • Introduce the RETURNING clause for INSERT, UPDATE and DELETE statements. Specifying a RETURNING clause causes the SQL statement to return the modified records which can be queried using SELECT like expressions in the RETURNING clause. Aggregate functions are allowed. This is a common non-standard SQL extension. Examples:

    INSERT INTO foo values (1,10), (-1,-10) RETURNING i+2*j AS bar
    

    returns: 21 -21;

    UPDATE foo SET i = -i WHERE i > 0 RETURNING sum(j), count(j)
    

    returns: -60|3

  • Introduce division_min_scale SQL environment variable for specifying minimum scale of the division result. The default value is 3.

Bug Fixes

  • 7101: Feature request: nextafter() in SQL

  • 7159: CREATE LOCAL TEMPORARY VIEW

  • 7331: Support RETURNING clause

  • 7578: explain result in Mal is truncated in large UDFs and their input bats is not shown

  • 7609: Upgrade 11.49.11 to 11.51.7 issues

  • 7611: Not possible to create table with multiple composite UNIQUE NULLS NOT DISTINCT constraints

  • 7614: Filter function creates a cartesian product when used with a view

  • 7615: Filter function creates a cartesian product when used with a view (2)

  • 7616: Filter function disappears

  • 7618: Tables loose their columns

  • 7619: Resource leak in prepared statements

  • 7621: crash on aggregate with case statement

  • 7622: In PREPARE queries with many parameters, information about parameters is truncated when sent to client.

  • 7623: Database crashed when using UPDATE xxx SET xxx RETURNING xx