# Collector commands

What DBGorilla may run against your databases through the collector, and how to turn each command
on or off.

## Database commands

`[commands]` decides which statements DBGorilla may ask the collector to run against your
databases.  There are three.

| Command | In the samples | What it does |
|---|---|---|
| [`explain`](#explain-on-in-every-sample) | On | Fetches a query plan.  Your query never runs. |
| [`collect_statistics`](#collect_statistics-on-in-every-sample) | On | Copies optimizer statistics, never table rows. |
| [`execute_query`](#execute_query-off-until-you-add-it) | Off | Runs one `SELECT` in a read-only transaction that is always rolled back. |

Every complete sample config DBGorilla publishes, and every config the CLI writes, enables
`explain` and `collect_statistics` and leaves `execute_query` off.  The short examples at the top
of [Collector configuration](/docs/reference/collector-configuration/#minimal-configuration) leave
`[commands]` out, so they allow no commands.  Add this block to them:

```toml
[commands]
enabled = true
# explain: query plans only, your query never runs.
# collect_statistics: copies optimizer statistics, never table rows, so a recommendation can be tested before you see it.
allowed = ["explain", "collect_statistics"]   # see https://www.dbgorilla.com/docs/reference/collector-commands/#database-commands
# allowed = ["explain", "collect_statistics", "execute_query"]   # adds read-only checks: one SELECT, always rolled back
```

### `explain`: on in every sample

It fetches the plan your database would use for a query, with plain `EXPLAIN`.  The collector
never sends `EXPLAIN ANALYZE`, so the query never runs and no rows come back.  The plan lets
DBGorilla base advice about a slow query, such as which index would help, on how your database
actually runs it.

To turn it off, remove it from the database's own `commands` list and restart the collector.  The
CLI writes that list for every database it installs, and the list wins over `[commands]`:
with it in place, `enabled = false` leaves `explain` on.

### `collect_statistics`: on in every sample

It copies the optimizer statistics for a set of tables: the numbers your database's planner uses
to choose a plan.  It copies no table rows.

- **PostgreSQL**: row and page counts plus per-column distributions.  The distributions contain
  sample values from your data: each column's most common values and histogram boundaries.  It
  needs `SELECT` on each table.
- **MySQL**: row counts, index sizes and index cardinality only.  It needs two extra
  [`SELECT` grants](/docs/connect/prepare-mysql/#database-grants).

Before a recommendation reaches you, DBGorilla tests it in a
planner replay experiment:
an empty, version-matched copy of your schema with your real statistics restored, so the planner
on the copy chooses the plans production would.
Sandboxes use the same copy.
Statistics are fetched when an experiment needs them, and only for the tables it touches, so the
load on your database is small and the test always uses your current statistics.

To turn it off, remove it from `allowed` and restart the collector.  Recommendations then reach
you without that test.

### No `[commands]` section means no commands

A `collector.toml` with no `[commands]` section allows no commands at all.  The same applies
when `enabled = false` and no component has a `commands` list.  Asking DBGorilla to explain a query
is then refused with `this component does not allow that command`.  Metrics, schema and query
statistics continue collecting either way.

### `execute_query`: off until you add it

| Command | What it adds | Bounds |
|---|---|---|
| `execute_query` | Runs one `SELECT` and returns its rows.  This is what Data Access uses. | A single statement, in a read-only transaction that is always rolled back.  A 30-second statement timeout.  At most 1,000 rows. |

Add it by listing it in `allowed`, or by leaving `allowed` out, which grants every command the
engine supports.

### Keys

| Key | Type | Default | Description |
|---|---|---|---|
| `enabled` | bool | `false` | Whether commands are allowed on components that do not set their own `commands` list.  A component with an explicit list is unaffected by this setting. |
| `allowed` | list | all supported | Which commands are permitted when enabled.  Omit to allow everything the engine supports. |

### Guarantees that hold whenever commands are enabled

- `explain` is plan-only.  `EXPLAIN ANALYZE` is never issued, so the statement is not executed.
- `execute_query` runs a single statement.  On PostgreSQL it can only be a `SELECT`.  On MySQL,
  the grants you give the collector's login decide what it can change: the
  [read-only MySQL grants](/docs/connect/prepare-mysql/#database-grants)
  allow no DDL.
- It runs inside a **read-only transaction**, which is always rolled back.  The database refuses
  `INSERT`, `UPDATE`, `DELETE`, `SELECT ...
  FOR UPDATE` and functions that write.
- `execute_query` returns at most **1,000 rows**.  `execute_query` and `explain` stop after
  **30 seconds** (on MySQL, the limit applies to `SELECT` statements).
- Statement stacking is blocked; one statement per request.
- The database user's own grants still apply.  A `pg_monitor`-only user cannot read application
  tables regardless of what is requested.

### Per-component overrides

An explicit list on a component applies even when the global switch is off:

```toml
[[component]]
name     = "payments"
engine   = "postgres"
commands = []          # this system permits nothing, whatever the global setting
```

Naming a command the engine does not support grants nothing.
To turn commands off everywhere, set `enabled = false` and leave `commands` unset on every
component.

## Commands

A command that is not allowed is refused with:

```text
this component does not allow that command. Collector commands are off by default: set
`[commands] enabled = true` in collector.toml, or add the command to `[commands] allowed`, then
restart the collector.
```

Metrics, schema capture and query statistics are unaffected and collect either way, so a collector
in this state is healthy and reports so.  What each command does is under
[Database commands](#database-commands).

## Query plans are on in the sample

The collector reads the file at startup, so restart the container after editing it.  What each
command permits, and what stays bounded regardless, is under [Database commands](#database-commands).