Date: 2020-09-30 17:39:23 +0200
From: @swingbit
To: SQL devs <>
Version: -- development
CC: @PedroTadim

Last updated: 2020-10-19 11:06:20 +0200

Comment 28128

Date: 2020-09-30 17:39:23 +0200
From: @swingbit

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.125 Safari/537.36
Build Identifier:

I successfully registered with CMake a sql script for my module to be executed upon database creation.

The script, here simplified, creates a schema, a user, and a table in that schema:

CREATE USER "spinque" WITH UNENCRYPTED PASSWORD xxxx NAME 'Test' SCHEMA "sys";
CREATE SCHEMA "spinque" AUTHORIZATION "spinque";
ALTER USER "spinque" SET SCHEMA "spinque";

SET SCHEMA "spinque";

CREATE TABLE info (attribute string, value string);

When I create the database ad I login as user "spinque", I am indeed in the schema "spinque" and, as expected, a table "spinque.info" exists:

created database in maintenance mode: x
taken database out of maintenance mode: x
password:

Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.39.0 (hg id: 26c69af56ba), 'mapi:monetdb://tardis.spinque.com:50000/x'
FOLLOW US on https://twitter.com/MonetDB or https://github.com/MonetDB/MonetDB
Type \q to quit, ? for a list of available commands
auto commit mode: on

sql>select * from sys.var();
+--------+------------------+--------------+--------------+
| schema | name | type | value |
+========+==================+==============+==============+
| sys | debug | int | 0 |
| sys | current_schema | varchar | spinque |
| sys | current_user | varchar | spinque |
| sys | current_role | varchar | spinque |
| sys | optimizer | varchar | default_pipe |
| sys | current_timezone | sec_interval | 7200000 |
| sys | last_id | bigint | 0 |
| sys | rowcnt | bigint | 37 |
+--------+------------------+--------------+--------------+
8 tuples

sql>select "schema", "table", "column" from sys.storage('spinque','info');
+---------+-------+-----------+
| schema | table | column |
+=========+=======+===========+
| spinque | info | attribute |
| spinque | info | value |
+---------+-------+-----------+
2 tuples

However, the command \d does not show any table:

sql>\d
sql>

Reproducible: Always

$ MonetDB/bin/mserver5 --version
MonetDB 5 server 11.39.0 (hg id: 26c69af56ba) (64-bit, 128-bit integers)
This is an unreleased version
Copyright (c) 1993 - July 2008 CWI
Copyright (c) August 2008 - 2020 MonetDB B.V., all rights reserved
Visit https://www.monetdb.org/ for further information
Found 15.5GiB available memory, 4 available cpu cores
Libraries:
libpcre: 8.44 2020-02-12
openssl: OpenSSL 1.1.1g FIPS 21 Apr 2020
libxml2: 2.9.10
Compiled by: roberto@tardis.spinque.com (x86_64-pc-linux-gnu)
Compilation: /usr/bin/cc -Werror -Wall -Wextra -Werror-implicit-function-declaration -Wpointer-arith -Wundef -Wformat=2 -Wformat-overflow=1 -Wno-format-truncation -Wno-format-nonliteral -Wno-cast-function-type -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wno-missing-field-initializers -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -Wstack-protector -fstack-protector-all -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wduplicated-cond -Wduplicated-branches -Wrestrict -Wnested-externs -Wmissing-noreturn -Wuninitialized -Wno-char-subscripts -Wunreachable-code
Linking : /usr/bin/ld

Comment 28129

Date: 2020-09-30 18:25:55 +0200
From: @PedroTadim

Hello Roberto, I just did your commands, but my description includes the 'spinque'.'info' table. Are you sure you didn't change the schema before calling \d, because this commands lists tables from the session's current schema.

Comment 28130

Date: 2020-09-30 18:32:05 +0200
From: @swingbit

This shows that I was in the right schema:

sql>select * from sys.var();
+--------+------------------+--------------+--------------+
| schema | name | type | value |
+========+==================+==============+==============+
| sys | debug | int | 0 |
| sys | current_schema | varchar | spinque |
| sys | current_user | varchar | spinque |
| sys | current_role | varchar | spinque |
| sys | optimizer | varchar | default_pipe |
| sys | current_timezone | sec_interval | 7200000 |
| sys | last_id | bigint | 0 |
| sys | rowcnt | bigint | 37 |
+--------+------------------+--------------+--------------+
8 tuples

But I also tried to explicitly set the schema, same result.

The weird thing is that I was not able to reproduce this unless the script was executed automatically at database creation time.

I can't pinpoint why this happens, but the following sequence should be impossible, and yet I get it:

sql>set schema spinque;
operation successful

sql>select * from sys.var() where name='current_schema';
+--------+----------------+---------+---------+
| schema | name | type | value |
+========+================+=========+=========+
| sys | current_schema | varchar | spinque |
+--------+----------------+---------+---------+
1 tuple

sql>select "schema", "table", "column" from sys.storage('spinque','info');
+---------+-------+-----------+
| schema | table | column |
+=========+=======+===========+
| spinque | info | attribute |
| spinque | info | value |
+---------+-------+-----------+
2 tuples

sql>\d

Comment 28134

Date: 2020-10-01 09:42:42 +0200
From: @PedroTadim

Hello Roberto,

After some debugging I finally found the issue. When a table is created during the startup scripts, it gets labeled as a 'system' table. This is because tables created during this phase, are generally of high importance. Meanwhile the '\d' command from mclient only lists non system tables. I think the purpose of this command is to list user created tables after the database is generated.

Comment 28138

Date: 2020-10-01 10:48:54 +0200
From: @swingbit

Hi Pedro, thanks for looking into this.

I understand. Indeed, \dS shows the tables I was looking for.

Then this is not a bug, just a change of behaviour.

I don't know all the implications of having a table as "system table", so I can't really say whether this change of behaviour may impact existing applications (I haven't seen any related issue so far). No knowing can be confusing though, I would suggest to mention it in the release notes.