Date: 2020-04-01 03:21:06 +0200
From: Bilal <<bilal.ahmed>>
To: SQL devs <>
Version: 11.33.3 (Apr2019)
CC: @PedroTadim

Last updated: 2020-06-03 16:58:54 +0200

Comment 27637

Date: 2020-04-01 03:21:06 +0200
From: Bilal <<bilal.ahmed>>

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36
Build Identifier:

I'm using MonetDB Server and MonetDB Client on windows shell. I created a Global temporary table in client connection 1 (name close_d). I tried to access this table under client connection 2 but it throws error of "no such table 'close_d' ". While I can see this table under sys.tables in client connection 2.
My assumption is that all connections shall be able to see and use Global Temporary tables of same name. Can you please elaborate the purpose and usage of Global Temporary table in MonetDB, as there is not much detail on documentation about it?

Reproducible: Always

Steps to Reproduce:

1. Start MonetDB Server.
2. Start MonetDB client
3. Inside MonetDB client, execute following commands:
   create global temporary table close_d (qaid int, value_ float) ON commit preserve rows;
   4.insert into close_d values (11,1100);
   5.insert into close_d values (13,1300);
   6.select * from tmp.close_d;

It would all work fine. Now:
7. Open another MonetDB client session.
8. Inside MonetDB client, execute:
select * from tmp.close_d;

Actual Results:

Following error is thrown:

SELECT: no such table 'close_d'

Expected Results:

The table shall be accessible in client session 2 as well. If I execute following query in client session 2:
select * from sys.tables order by 1 desc limit 1;
Then it shows me the name of close_d as well like:
+------+-------------+------+-------------+------+-------+------+------+------+
| id | name | sche | query | type | syste | comm | acce | temp |
: : : ma_i : : : m : it_a : ss : orar :
: : : d : : : : ctio : : y :
: : : : : : : n : : :
+======+=============+======+=============+======+=======+======+======+======+
| 8231 | close_d | 2114 | null | 20 | false | 2 | 0 | 1 |

Comment 27638

Date: 2020-04-01 11:26:36 +0200
From: MonetDB Mercurial Repository <>

Changeset 37eb0372ed86 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=37eb0372ed86

Changeset description:

Added test for bug #6846

Comment 27639

Date: 2020-04-01 11:43:09 +0200
From: Bilal <<bilal.ahmed>>

(In reply to MonetDB Mercurial Repository from comment 1)

Changeset 37eb0372ed86 made by Pedro Ferreira
pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this
bug.

For complete details, see
https//devmonetdborg/hg/MonetDB?cmd=changeset;node=37eb0372ed86

Changeset description:

Added test for bug #6846

Hi Pedro,

I'm sorry but I did not understand this message completely. Does it mean that there is some new fix that is available for me to apply or it's yet work in progress on MonetDB end?

Thanks.

Comment 27640

Date: 2020-04-01 11:48:34 +0200
From: @PedroTadim

Hello Bilal,

As a coincidence, some of us spotted this bug yesterday as well. We are working on a fix for it. For now, restarting the server fixes the issue, but we will make global temporary tables available for every session on the fly.

Comment 27641

Date: 2020-04-01 11:52:39 +0200
From: Bilal <<bilal.ahmed>>

"For now, restarting the server fixes the issue"

Do you mean, that I have to download and reinstall MonetDB on my machine to validate this? Or simply restarting, will make this global table available to other connections? :|

Comment 27642

Date: 2020-04-01 12:04:07 +0200
From: @PedroTadim

Simply restarting the server, not installing again :) However as a temporary table all its data will be lost. Sorry for the inconvenience.

Comment 27643

Date: 2020-04-01 12:09:08 +0200
From: Bilal <<bilal.ahmed>>

:) Great Thanks!

Just to know, when can this fix possibly be expected?
Secondly, will you please explain that what is Global temporary table functionality in MonetDB and how it differs from Local temporary table? I didn't see much detail in documentation.
I've worked with Global temp tables a lot in SQL server but not sure what I shall expect in MonetDB.

Regards

Comment 27644

Date: 2020-04-01 13:07:52 +0200
From: @PedroTadim

Our next planned release is a feature release next June. However if a paid customer asks for a bug fix release on Nov2019, we can backport this bugfix to it, and make it available earlier.

On any temporary table, data is never persisted across server restarts. Local temporary tables are visible to the current session only, while global are visible to every session, although their content it's not replicated (ie each session has its own data on the global temporary table). Global temporary tables' metadata persists across server restarts, while local ones do not.

Comment 27645

Date: 2020-04-01 14:29:27 +0200
From: Bilal <<bilal.ahmed>>

Pedro,
Thanks a lot for explanation and assistance.