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

Application period tables: WITHOUT OVERLAPS

    XMLWordPrintable

    Details

      Description

      From sql standard:

      <unique constraint definition> ::=
        <unique specification> <left paren> <unique column list> 
        [ <comma> <without overlap specification> ] <right paren>
        | UNIQUE ( VALUE )
      <without overlap specification> ::= <application time period name> WITHOUT OVERLAPS
      

      The simplest observed implementation is:
      1. Add period start/end to the index at the very end (even after SYSTEM_TIME row_end)
      2. On each insert/update, check if there's such a key, with period overlapping the inserting/updating one. `ha_index_*` functions fit this purpose. It is sufficient to fetch a row with `ha_index_read_map` with `HA_READ_KEY_OR_PREV` flag, and fetch another one with `ha_index_next`

      Although this check can be done totally on the `sql/` side, the constraint should always be satisfied, so it should be close to engine. It is supposed to be done as `handler::check_overlaps` function called from `ha_write_row` and `ha_update_row`

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nikitamalyavin Nikita Malyavin
              Reporter:
              nikitamalyavin Nikita Malyavin
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: