github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/docs/RFCS/20170601_pause_resume_cancel.md (about)

     1  - Feature Name: Syntax for pausing, resuming, and canceling operations
     2  - Status: completed
     3  - Start Date: 2017-06-01
     4  - Authors: Nikhil Benesch, Jordan Lewis, Bilal Akhtar
     5  - RFC PR: [#16273]
     6  - Cockroach Issue: None
     7  
     8  # Motivation
     9  
    10  For the 1.1 release, we're adding support for canceling SQL queries and for
    11  pausing, resuming, and canceling long-running jobs (i.e., backups, restores, and
    12  schema changes).
    13  
    14  We draw a distinction between queries and jobs, both in internal implementations
    15  and in user-facing interfaces, because queries and jobs have vastly different
    16  expected durations. Queries are expected to run for milliseconds or seconds, or
    17  minutes in the worst case. Jobs are expected to run for minutes or hours, or
    18  days in the worst case.
    19  
    20  This difference is more significant than in might seem on a first pass. Queries
    21  are short-lived enough that they benefit from an RPC-based cancel mechanism.
    22  Jobs can spare the latency of waiting for gossip or polling a system table.
    23  CockroachDB already takes advantage of this difference: the list of running
    24  queries exists only in memory on each node, while the list of running jobs is
    25  stored and replicated in a proper SQL table, `system.jobs`.
    26  
    27  Still, we want the syntax to cancel a query to be similar to the syntax to
    28  cancel a job because, conceptually, the operations are identical.
    29  
    30  # Detailed design
    31  
    32  We propose the following syntax:
    33  
    34  ```sql
    35  PAUSE JOB <job-id>;
    36  RESUME JOB <job-id>;
    37  CANCEL [QUERY|JOB] <job-or-query-id>;
    38  ```
    39  
    40  This syntax is symmetric with the soon-to-be-merged syntax for listing running
    41  queries, `SHOW QUERIES`, and a soon-to-be-proposed syntax for listing running
    42  jobs, `SHOW JOBS`.
    43  
    44  These statements will all complete as soon as the pause, resume, or cancel
    45  request has been submitted. For example, when the `CANCEL` statement completes,
    46  the operation in question may still be in progress. Users can detect when the
    47  operation has fully canceled by polling `SHOW QUERIES` or `SHOW JOBS`.
    48  
    49  # Drawbacks
    50  
    51  This is a lot of new SQL syntax with no direct basis in another SQL dialect.
    52  
    53  # Alternatives
    54  
    55  ## M{Y,S}SQL syntax
    56  
    57  MySQL and MSSQL use a similar syntax, but with `KILL` in place of `CANCEL`:
    58  
    59  ```sql
    60  KILL <id>;
    61  ```
    62  
    63  MySQL additionally supports modifiers (`KILL CONNECTION <connection-id>` and
    64  `KILL QUERY <query-id>`), much like we do with `QUERY` and `JOB`.
    65  
    66  We prefer `CANCEL` to `KILL`, as "kill" implies the query or job will be
    67  terminated immediately. This will not be true of our cancel implementations. In
    68  particular, large schema changes may take several hours to cancel as the cluster
    69  rolls back all backfill progress.
    70  
    71  ## Postgres syntax
    72  
    73  Postgres (ab)uses a built-in function:
    74  
    75  ```sql
    76  SELECT pg_cancel_backend(<query-id>);
    77  SELECT pg_cancel_backend(generate_series) FROM generate_series(1, 100000); -- Yes, this is valid.
    78  ```
    79  
    80  Postgres's approach avoids introducing new syntax... by introducing the world's
    81  largest side effect. Pausing, resuming, and canceling are novel operations that
    82  seem like they *should* have new syntax.
    83  
    84  Alas, we may eventually end up supporting this syntax for compatibility's sake.
    85  
    86  ## Oracle syntax
    87  
    88  Oracle uses a hybrid approach and nests new syntax under an `ALTER SYSTEM`
    89  command:
    90  
    91  ```sql
    92  ALTER SYSTEM KILL SESSION <session-id>;
    93  ```
    94  
    95  We have no precedent for `ALTER SYSTEM`, and it doesn't seem worth introducing
    96  `ALTER SYSTEM` just to avoid three new top-level constructions (`PAUSE...`,
    97  `RESUME...`, `CANCEL...`). We already have precedent for new top-level syntax
    98  for new operations, like `BACKUP...` and `RESTORE...`.
    99  
   100  [Peter Mattis suggested][peter-suggestion] a slight variation on
   101  the Oracle syntax, `ALTER JOB...`/`ALTER QUERY...`, but the RFC authors feel the
   102  proposed top-level `PAUSE`/`RESUME`/`CANCEL` verbs read more naturally.
   103  
   104  # Unresolved questions
   105  
   106  None. How these operations will actually be implemented is out of scope.
   107  
   108  [#16273]: https://github.com/cockroachdb/cockroach/pull/16273
   109  [peter-suggestion]: https://github.com/cockroachdb/cockroach/pull/16273#issuecomment-305590661