GLOBAL TEMPORARY TABLE is a feature found in Oracle, MSSQL, PostgreSQL etc.
Syntax in Oracle:
CREATEGLOBALTEMPORARYTABLE tmp_table
{ (create_definition, ...)
| [SELECT * FROM tbl_NAME WHERE conditions ] }
ONCOMMIT [DELETE | PRESERVE] ROWS
The purpose of this syntax is to create temporary table where each user can have
their own data, which is destroyed at the end of the session.
Any user with access right to this table can access and use it.
One suggested way to implement this in MariaDB is to have "CREATE GLOBAL TEMPORARY t1" create a 'template' for future temporary tables with name t1.
Internally in MariaDB we would only create a .frm file for the table.
On first access to the table, MariaDB would internally execute
"CREATE TEMPORARY TABLE t1 LIKE t1" on the engine used when "CREATE GLOBAL ..." was created. Any future usage of t1 would use the temporary table t1.
"DROP TABLE t1" would drop the internal temporary table.
To drop the global one, one would have to use "DROP GLOBAL TEMPORARY TABLE".
Before starting implementing this, please check with SQL Standard, Oracle, MSSQL and PostgreSQL that the above implementation would work as GLOBAL TEMPORARY tables are expected to work
The table has no concurrent access, so indeed no concurrency control is required.
What I meant is that a local table can be rolled back, not necessarily to an empty state. So, multi-versioning is required can be used in order to support rollbacks and savepoints.
Not to cause any more confusion, it's nothing different to usual local temporary tables.
Nikita Malyavin
added a comment - - edited The table has no concurrent access, so indeed no concurrency control is required.
What I meant is that a local table can be rolled back, not necessarily to an empty state. So, multi-versioning is required can be used in order to support rollbacks and savepoints.
Not to cause any more confusion, it's nothing different to usual local temporary tables.
> 5. Statements involving global temporary tables will use row-based replication.
This means that if @@binlog_format=STATEMENT, any attempt to access a global temporary table should result in an error.
I think it is a good restriction to put, statement-based replication of temporary tables is very complex.
Kristian Nielsen
added a comment - > 5. Statements involving global temporary tables will use row-based replication.
This means that if @@binlog_format=STATEMENT, any attempt to access a global temporary table should result in an error.
I think it is a good restriction to put, statement-based replication of temporary tables is very complex.
knielsen i believe a denying replication is not what is wanted: if the table is not suitable for SRB, we should at least try to replicate the data in RBR. This is what we do when a table that does not exist on replica is involved in some DML, see TABLE_SHARE::table_creation_was_logged.
Currently I do the same for global temporary tables, with exception that its creation and other DDLs are replicated.
Nikita Malyavin
added a comment - knielsen i believe a denying replication is not what is wanted: if the table is not suitable for SRB, we should at least try to replicate the data in RBR. This is what we do when a table that does not exist on replica is involved in some DML, see TABLE_SHARE::table_creation_was_logged .
Currently I do the same for global temporary tables, with exception that its creation and other DDLs are replicated.
it is possible to create a foreign key referencing to a GTT, with no useful effect, but it should be forbidden
FLUSH TABLE gtt denies if gtt is used in current session, but actually it may just skip it.
XA support is not implemented for ON COMMIT DELETE
Global temporary tables intermix with local temporary tables namespace
Not tested:
S3
analyze/check/repair
galera
The latest version for releasing a preview is on 12.0-nikita-global-tmp
Nikita Malyavin
added a comment - - edited Current status for the upcoming preview release:
LOCK TABLE is forbidden for GTT
it is possible to create a foreign key referencing to a GTT, with no useful effect, but it should be forbidden
FLUSH TABLE gtt denies if gtt is used in current session, but actually it may just skip it.
XA support is not implemented for ON COMMIT DELETE
Global temporary tables intermix with local temporary tables namespace
Not tested:
S3
analyze/check/repair
galera
The latest version for releasing a preview is on 12.0-nikita-global-tmp
People
Nikita Malyavin
Michael Widenius
Votes:
0Vote for this issue
Watchers:
10Start watching this issue
Dates
Created:
Updated:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":1492.9000000953674,"ttfb":363.10000014305115,"pageVisibility":"visible","entityId":132525,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"677b5b73-5a8f-4c63-917b-68ebc6a28e6b","navigationType":0,"readyForUser":1580.1000001430511,"redirectCount":0,"resourceLoadedEnd":1555,"resourceLoadedStart":368.40000009536743,"resourceTiming":[{"duration":449.7000000476837,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":368.40000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":368.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":818.1000001430511,"responseStart":0,"secureConnectionStart":0},{"duration":449.7000000476837,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":368.7000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":368.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":818.4000000953674,"responseStart":0,"secureConnectionStart":0},{"duration":462.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":368.7999999523163,"connectEnd":368.7999999523163,"connectStart":368.7999999523163,"domainLookupEnd":368.7999999523163,"domainLookupStart":368.7999999523163,"fetchStart":368.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":368.7999999523163,"responseEnd":831,"responseStart":831,"secureConnectionStart":368.7999999523163},{"duration":558.8999998569489,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":369.10000014305115,"connectEnd":369.10000014305115,"connectStart":369.10000014305115,"domainLookupEnd":369.10000014305115,"domainLookupStart":369.10000014305115,"fetchStart":369.10000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":369.10000014305115,"responseEnd":928,"responseStart":928,"secureConnectionStart":369.10000014305115},{"duration":597.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":369.2000000476837,"connectEnd":369.2000000476837,"connectStart":369.2000000476837,"domainLookupEnd":369.2000000476837,"domainLookupStart":369.2000000476837,"fetchStart":369.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":369.2000000476837,"responseEnd":966.9000000953674,"responseStart":966.9000000953674,"secureConnectionStart":369.2000000476837},{"duration":618.8999998569489,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":369.40000009536743,"connectEnd":369.40000009536743,"connectStart":369.40000009536743,"domainLookupEnd":369.40000009536743,"domainLookupStart":369.40000009536743,"fetchStart":369.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":369.40000009536743,"responseEnd":988.2999999523163,"responseStart":988.2999999523163,"secureConnectionStart":369.40000009536743},{"duration":619.0999999046326,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":369.60000014305115,"connectEnd":369.60000014305115,"connectStart":369.60000014305115,"domainLookupEnd":369.60000014305115,"domainLookupStart":369.60000014305115,"fetchStart":369.60000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":369.60000014305115,"responseEnd":988.7000000476837,"responseStart":988.7000000476837,"secureConnectionStart":369.60000014305115},{"duration":758,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":369.7000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":369.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1127.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":633.5,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":370,"connectEnd":370,"connectStart":370,"domainLookupEnd":370,"domainLookupStart":370,"fetchStart":370,"redirectEnd":0,"redirectStart":0,"requestStart":370,"responseEnd":1003.5,"responseStart":1003.5,"secureConnectionStart":370},{"duration":757.7999999523163,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":370.2000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":370.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1128,"responseStart":0,"secureConnectionStart":0},{"duration":633.8000001907349,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":370.2999999523163,"connectEnd":370.2999999523163,"connectStart":370.2999999523163,"domainLookupEnd":370.2999999523163,"domainLookupStart":370.2999999523163,"fetchStart":370.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":370.2999999523163,"responseEnd":1004.1000001430511,"responseStart":1004.1000001430511,"secureConnectionStart":370.2999999523163},{"duration":1175.8999998569489,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":378.10000014305115,"connectEnd":378.10000014305115,"connectStart":378.10000014305115,"domainLookupEnd":378.10000014305115,"domainLookupStart":378.10000014305115,"fetchStart":378.10000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":378.10000014305115,"responseEnd":1554,"responseStart":1554,"secureConnectionStart":378.10000014305115},{"duration":1176.8999998569489,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":378.10000014305115,"connectEnd":378.10000014305115,"connectStart":378.10000014305115,"domainLookupEnd":378.10000014305115,"domainLookupStart":378.10000014305115,"fetchStart":378.10000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":378.10000014305115,"responseEnd":1555,"responseStart":1555,"secureConnectionStart":378.10000014305115},{"duration":357.2000000476837,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1173.7999999523163,"connectEnd":1173.7999999523163,"connectStart":1173.7999999523163,"domainLookupEnd":1173.7999999523163,"domainLookupStart":1173.7999999523163,"fetchStart":1173.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":1173.7999999523163,"responseEnd":1531,"responseStart":1531,"secureConnectionStart":1173.7999999523163},{"duration":169.69999980926514,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1484.1000001430511,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1484.1000001430511,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1653.7999999523163,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":135,"responseStart":363,"responseEnd":376,"domLoading":366,"domInteractive":1736,"domContentLoadedEventStart":1736,"domContentLoadedEventEnd":1809,"domComplete":2028,"loadEventStart":2028,"loadEventEnd":2028,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1700.5},{"name":"bigPipe.sidebar-id.end","time":1702.7999999523163},{"name":"bigPipe.activity-panel-pipe-id.start","time":1703},{"name":"bigPipe.activity-panel-pipe-id.end","time":1705.6000001430511},{"name":"activityTabFullyLoaded","time":1828.5}],"measures":[],"correlationId":"97374121f7fbc7","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":168,"dbReadsTimeInMs":15,"dbConnsTimeInMs":81,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
New Feature
Critical
MDEV-32521 SQL Server compatibility
MDEV-35973 Oracle Compatibility Project 1 for 2025
The table has no concurrent access, so indeed no concurrency control is required.
What I meant is that a local table can be rolled back, not necessarily to an empty state. So, multi-versioning
is requiredcan be used in order to support rollbacks and savepoints.Not to cause any more confusion, it's nothing different to usual local temporary tables.