> For the complete documentation index, see [llms.txt](https://docs.datasentinel.io/manual/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.datasentinel.io/manual/getting-started/postgresql-clusters/internal-function.md).

# Internal Function

An optional read-only function that gives Datasentinel access to planner statistics and execution plans without granting SELECT on application tables.

The `datasentinel_analytics` function gives Datasentinel a safe, read-only window on information that the [monitoring user](https://docs.datasentinel.io/manual/getting-started/postgresql-clusters/monitoring-user.md) cannot read on its own. No SELECT privilege on application tables is needed.

## Objective <a href="#objective" id="objective"></a>

The function lets Datasentinel:

* Read **column statistics** from the `pg_stats` view.
* Read **extended statistics** definitions from the `pg_statistic_ext` catalog.
* Produce the **execution plan** of a query, without ever running it.

{% hint style="info" %}
The function is **optional**. Datasentinel uses it automatically when it is installed. With [local agents](https://docs.datasentinel.io/manual/implementation/upgrade/agent.md), upgrade them to version **4.0.0** or later.

**Without the function, Datasentinel skips these advanced insights:**

* Table and index bloat estimates.
* Column statistics.
* Extended statistics.
* Live execution plans, in [Live360](https://docs.datasentinel.io/manual/features/key-features/live360.md) and [AI Connector](https://docs.datasentinel.io/ai-connector).

These details enrich the [reports](https://docs.datasentinel.io/manual/features/key-features/reporting.md) and AI Connector analyses. **Every other monitoring metric keeps working as normal.**
{% endhint %}

Statistics are collected **hourly**, with both the **agent-based** and **agentless** methods. Before each collection, Datasentinel checks that the function is present in the database and that the monitoring user can execute it. The first hourly pass is not uploaded: the data appears on the platform after the second full hour.

## What the function does <a href="#what-the-function-does" id="what-the-function-does"></a>

{% hint style="info" %}
The function does **not access or return application data** (such as table contents).

It only returns metadata and statistics: null fraction, number of distinct values, column width, correlation, and the number of most common values (never the values themselves). Extended statistics are returned as definitions only.
{% endhint %}

It supports three actions:

| Action     | Returns                                                                                |
| ---------- | -------------------------------------------------------------------------------------- |
| `colstats` | Column statistics from `pg_stats`, for all tables or for one table                     |
| `extstats` | Extended statistics definitions (`CREATE STATISTICS`), for all tables or for one table |
| `explain`  | The execution plan of one statement, in text format                                    |

The `explain` action is locked down inside the function:

* It always runs `EXPLAIN (ANALYZE off, FORMAT text)`. The statement is planned, **never executed**, and the caller cannot add any option.
* It accepts **a single statement**. A text with a second statement after a `;` is rejected.
* A generic plan can be requested on PostgreSQL 16 and later.

The function is `SECURITY DEFINER`: it runs with the privileges of the role that installed it. For this reason, the SQL file revokes the execute privilege from `PUBLIC`, and the installer grants it only to the `datasentinel` role.

{% hint style="info" %}
Extended statistics require PostgreSQL **10** or later.
{% endhint %}

## Deployment <a href="#deployment" id="deployment"></a>

{% hint style="danger" %}
The function must be installed by a **PostgreSQL superuser** or a role with equivalent privileges.
{% endhint %}

{% hint style="warning" %}
The function must be installed **in each application database**. The `postgres` maintenance database and the template databases are skipped.
{% endhint %}

The [agent package](https://docs.datasentinel.io/manual/getting-started/installation/agent.md) ships three files in its root directory:

| File                              | Purpose                                                                                  |
| --------------------------------- | ---------------------------------------------------------------------------------------- |
| `datasentinel_analytics.sql`      | The function definition and the `REVOKE ... FROM PUBLIC`                                 |
| `deploy_datasentinel_function.sh` | Installs the function in every application database and grants EXECUTE to `datasentinel` |
| `remove_datasentinel_function.sh` | Removes the function from every application database                                     |

Use the files from the agent version you run: they are updated with the agent.

With **agentless** connections, no agent package is downloaded. The same three files are published in the `datasentinel_analytics` directory of the public [Datasentinel toolkit](https://github.com/datasentinel/datasentinel_toolkit) repository on GitHub. On an on-premises platform, the toolkit is also installed in the home directory of the `datasentinel` user on the platform server.

### Install <a href="#install" id="install"></a>

Run the installer on the PostgreSQL host, from the directory that holds the SQL file. The optional argument is the PostgreSQL port (default `5432`).

```bash
./deploy_datasentinel_function.sh 5432
```

The script connects through the local socket with `psql`, as the current OS user. Set `PGHOST`, `PGUSER` and the other `PG*` variables to connect differently, for example from a remote machine in an agentless setup:

```bash
PGHOST=db-host PGUSER=postgres PGPASSWORD=secret ./deploy_datasentinel_function.sh 5432
```

On a cloud-managed instance, where no superuser is available, run the script as the master user.

{% hint style="warning" %}
The grant is given to the role named **`datasentinel`**. If your monitoring user has another name, edit `TARGET_USER` in the script first. Otherwise the function is installed but never used.
{% endhint %}

Run the script again:

* after creating a new database, which does not get the function otherwise;
* after an agent upgrade that changes the SQL file.

The script uses `CREATE OR REPLACE`, so running it again is safe.

### Check <a href="#check" id="check"></a>

In each database, run:

```sql
SELECT n.nspname, p.proname, pg_get_userbyid(p.proowner) AS owner, p.prosecdef,
       has_function_privilege('datasentinel', p.oid, 'execute') AS ds_can_execute,
       has_function_privilege('public', p.oid, 'execute')       AS public_can_execute
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname = 'datasentinel_analytics';
```

Expected result: one row, with `prosecdef`, `ds_can_execute` true and `public_can_execute` false.

### Uninstall <a href="#uninstall" id="uninstall"></a>

```bash
./remove_datasentinel_function.sh 5432
```

The function is removed from every application database. Datasentinel detects its absence at the next hourly collection, and every other metric keeps working.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.datasentinel.io/manual/getting-started/postgresql-clusters/internal-function.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
