Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35915

Implement Global temporary tables

Details

    • Q2/2025 Development

    Description

      GLOBAL TEMPORARY TABLE is a feature found in Oracle, MSSQL, PostgreSQL etc.

      Syntax in Oracle:

      CREATE GLOBAL TEMPORARY TABLE tmp_table
      		 
      { (create_definition, ...)
      		 
        | [SELECT * FROM tbl_NAME WHERE conditions ] }
      		 
      ON COMMIT [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

      Attachments

        Issue Links

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MCOL-5642 support querying temporary tables with cross engine joins

          • Major - Major loss of function.
          • Open
          is part of

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-32521 SQL Server compatibility

          • Major - Major loss of function.
          • Open

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-35388 Postgres compatible syntax

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-35973 Oracle Compatibility Project 1 for 2025

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress
          relates to

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-10872 Providing compatibility Oracle database

          • Major - Major loss of function.
          • Open
          links to

          Web Link MSSQL T-SQL syntax

          Activity

            Ascending order - Click to sort in descending order
            View 8 older comments
            monty Michael Widenius added a comment -

            Code can be found at 12.0-nikita-global-tmp

            monty Michael Widenius added a comment - Code can be found at 12.0-nikita-global-tmp
            knielsen 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 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.
            nikitamalyavin 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.

            nikitamalyavin 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.
            nikitamalyavin 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

            nikitamalyavin 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
            nikitamalyavin Nikita Malyavin added a comment -

            Before we'll make a final polishing, we'd like to see some feedback on the released preview by the beginning of May. We'll plan landing the final version by mid-June

            nikitamalyavin Nikita Malyavin added a comment - Before we'll make a final polishing, we'd like to see some feedback on the released preview by the beginning of May. We'll plan landing the final version by mid-June

            People

              nikitamalyavin Nikita Malyavin
              monty Michael Widenius
              Votes:
              0 Vote for this issue
              Watchers:
              10 Start 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.