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

Support for SEQUENCE objects

Details

    Description

      Add support for SEQUENCES, like in PostgreSQL or Oracle:

      CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
      		 
          [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
      		 
          [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

      See https://www.postgresql.org/docs/8.1/static/sql-createsequence.html
      for details.

      The main part of the task is to add support for sequence tables in the storage engine interface. The main difference between transactional tables are only that they don't roll back when issuing a ROLLBACK statement.

      Example:

      CREATE SEQUENCE s1;
      		 
      SELECT s1.NEXTVAL FROM DUAL;

      Supported statements:

      • CREATE SEQUENCE
      • DROP SEQUENCE
      • ALTER SEQUENCE
      • NEXT VALUE FOR sequence_name and NEXTVAL(sequence_name)
      • PREVIOUS VALUE FOR sequence_name and LASTVAL(sequence_name)
      • SETVAL(sequence_name, next_value, is_used, round)
      • SHOW CREATE SEQUENCE

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14824 Assertion `!trx_is_started(trx)' failed in innobase_start_trx_and_assign_read_view

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-22491 Support mariadb-check and CHECK TABLE with SEQUENCE

          • Critical - Crashes, loss of data, severe memory leak.
          • In Testing

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32795 ALTER SEQUENCE IF NOT EXISTS non_existing_seq Errors rather than note

          • Major - Major loss of function.
          • Closed
          includes

          Task - A task that needs to be done. MDEV-12930 Testing SEQUENCE object

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-12941 First release of SEQUENCEs

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13005 Fixing bugs in SEQUENCE, part 3

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13679 crash when using SEQUENCE as column default

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13711 Assertion `is_temporary_table(tables)' failed in open_and_process_table on CREATE TABLE .. LIKE <sequence>

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13714 Value of SEQUENCE table option is ignored upon creation

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13720 Server crashes in SEQUENCE::write_lock or Assertion `table && table->s->sequence' failed in Item_func_nextval::val_int

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13732 User with SELECT privilege can ALTER sequence

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14824 Assertion `!trx_is_started(trx)' failed in innobase_start_trx_and_assign_read_view

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Task - A task that needs to be done. MDEV-13075 AliSQL: [Feature] Issue #41 Sequence Engine

          • Major - Major loss of function.
          • Closed
          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-10137 Providing compatibility to other databases

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12883 CREATE SEQUENCE with huge MAXVALUE

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13006 update_time column of on i_s.tables remains NULL for sequence objects

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13008 Documentation improvements for Sequence objects

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13010 NEXTVAL should increment only once for a given row

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13497 concurrently used sequence hangs in SEQUENCE::next_value

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32350 Can't selectively restore sequences using innodb tables from backup

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Task - A task that needs to be done. MDEV-12168 AUTO_INCREMENT with CYCLE support

          • Major - Major loss of function.
          • Closed

          Technical task - A technical task. MDEV-12533 sql_mode=ORACLE: Add support for database qualified sequence names in NEXTVAL and CURRVAL

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13393 SEQUENCE related crash when running concurrent I_S.TABLES and FLUSH queries

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13497 concurrently used sequence hangs in SEQUENCE::next_value

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20111 (CREATE|ALTER) SEQUENCE / SETVAL: only integer literal accepted as parameter

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 11 older comments
            michaeldg Michaël de groot added a comment -

            Cool!!

            I have a question about this, does auto_increment_increment and _offset change anything to sequence behaviour? I guess it could otherwise lead to conflicts in a multi-master cluster.

            michaeldg Michaël de groot added a comment - Cool!! I have a question about this, does auto_increment_increment and _offset change anything to sequence behaviour? I guess it could otherwise lead to conflicts in a multi-master cluster.
            monty Michael Widenius added a comment - - edited

            Yes, auto_increment_increment and _offset will work for sequences defined with INCREMENT=0.
            This is documented at:
            https://mariadb.com/kb/en/mariadb/sequence-overview/#replication

            monty Michael Widenius added a comment - - edited Yes, auto_increment_increment and _offset will work for sequences defined with INCREMENT=0. This is documented at: https://mariadb.com/kb/en/mariadb/sequence-overview/#replication
            monty Michael Widenius added a comment -

            About GRANT.

            Sequences uses normal table grants. To read from state of a sequence, one needs SELECT privilege,
            to update the sequence one needs INSERT privilege. To use ALTER SEQUENCE one will need the ALTER privilege.

            monty Michael Widenius added a comment - About GRANT. Sequences uses normal table grants. To read from state of a sequence, one needs SELECT privilege, to update the sequence one needs INSERT privilege. To use ALTER SEQUENCE one will need the ALTER privilege.
            abienemann Alexander Bienemann (Inactive) added a comment -

            For migrations from Oracle: Access to SEQUENCE objects through migrated SYNONYM constructs should be checked, e.g. through FederatedX and VIEWs.

            abienemann Alexander Bienemann (Inactive) added a comment - For migrations from Oracle: Access to SEQUENCE objects through migrated SYNONYM constructs should be checked, e.g. through FederatedX and VIEWs.
            monty Michael Widenius added a comment -

            Pushed into 10.3 tree a long time ago

            monty Michael Widenius added a comment - Pushed into 10.3 tree a long time ago

            People

              monty Michael Widenius
              monty Michael Widenius
              Votes:
              12 Vote for this issue
              Watchers:
              28 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.