sql: make transaction_rows_{read|written}_err and large_full_scan_rows guardrails halt query execution

[michae2]

We recently added guardrails to limit the number of rows read or written by a single transaction. A transaction reading more than transaction_rows_read_err rows (or writing more than transaction_rows_written_err rows) now fails with an error.

To simplify implementation, we chose to emit this error after the violating query has already finished executing and delivered results to the application. This is still quite useful: well-behaved applications will see the error, and the transaction will be aborted. But there is a risk that poorly written applications might not see the error after receiving results of a read-only autocommit query. There is also a risk that the violating query will have already hurt cluster performance before hitting the guardrail.

It would be more defensive for the current query execution to halt immediately upon violating one of these limits.

Here's an example:

CREATE TABLE t (i INT PRIMARY KEY);
INSERT INTO t SELECT generate_series(0, 19);
SET transaction_rows_read_err = 10;
SET transaction_rows_written_err = 10;

[email protected]:26257/defaultdb> SELECT * FROM t;
  i
------
   0
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
(20 rows)
(error encountered after some results were delivered)
ERROR: txn has read 20 rows, which is above the limit: TxnID cea43f29-a4a0-4996-a92d-f283c2626462 SessionID 16a6bd70640294880000000000000001
SQLSTATE: 54000

[email protected]:26257/defaultdb> INSERT INTO t SELECT generate_series(20, 39) RETURNING i;
  i
------
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
(20 rows)
(error encountered after some results were delivered)
ERROR: txn has written 20 rows, which is above the limit: TxnID 32334f62-5054-4c26-afca-5b88ff5a317f SessionID 16a6bd70640294880000000000000001
SQLSTATE: 54000

Jira issue: CRDB-10088

[vy-ton]

There is also a risk that the violating query will have already hurt cluster performance before hitting the guardrail.

@rytaft @jordanlewis We should revisit this issue. The motivation of these guardrails would be to protect the cluster before destabilization.

[rytaft]

This is likely a non-trivial amount of work. We need to see if it's needed. @vy-ton

[michae2]

Note for posterity: when fixing this, we might be able to take advantage of whatever mechanism is used for transaction timeouts (though there will be more to this fix than just invoking that mechanism).

[michae2]

When we fix this, it would be nice to use the same technique to make a full scan halt immediately if it reads more than large_full_scan_rows when disallow_full_table_scans is in use.