schema-exploration

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Explore a PostgreSQL database

探索PostgreSQL数据库

Start with the user's question, not a full-database audit unless that is the explicit ask. Use the database connection already available to you; if none is available, ask for access or work from provided schema files. Stay within the database and schemas the user has authorized. Catalog metadata can reveal sensitive names or source code: do not export it unnecessarily.

从用户的问题出发，除非用户明确要求，否则不要进行全库审计。使用已有的数据库连接；如果没有可用连接，请申请访问权限或基于提供的schema文件开展工作。仅在用户授权的数据库和schema范围内操作。目录元数据可能会泄露敏感名称或源代码：请勿不必要地导出这些内容。

Workflow

工作流程

Establish the current database, server version, role, and scope. Use overview for a small inventory. Choose relevant schemas; do not assume
```
public
```
contains the application.
Pick the most promising object(s) and read only the matching drill-down reference: tables, partitioning/inheritance, foreign tables, views, routines, triggers, security/RLS, or extensions. Follow cross-references only when the question requires them.
Corroborate meaning with comments, definitions, keys, and known dependencies. Names are clues, not proof of business meaning. If data-derived values would help, use data-derived values only after obtaining approval for the specific columns and access method. Ask the user when semantics remain ambiguous.
If the user wants SQL, follow query authoring: establish join keys and grain, then validate a vetted read-only query with plain
```
EXPLAIN
```
where authorized. Do not mistake a valid plan for proof of business semantics.
Stop once you can answer. Report the specific schema-qualified objects and evidence, distinguish observations from inferences, and state limitations (permissions, stale statistics, missing dependencies, unknown application logic).

确认当前数据库、服务器版本、角色和操作范围。使用概览进行小型清点。选择相关的schema；不要默认
```
public
```
包含应用程序数据。
选择最相关的对象，仅阅读对应的深度参考文档：表、分区/继承、外部表、视图、例程、触发器、安全/RLS、扩展。仅当问题需要时才跟进交叉引用内容。
通过注释、定义、键和已知依赖项验证对象的含义。名称只是线索，不能作为业务含义的证明。如果数据衍生的值有帮助，必须在获得针对特定列和访问方式的批准后，才能使用数据衍生值。如果语义仍然不明确，向用户询问。
如果用户需要SQL，遵循查询编写规范：确认关联键和粒度，然后在授权范围内使用普通
```
EXPLAIN
```
验证经过审查的只读查询。不要将有效的执行计划等同于业务语义的证明。
能够回答问题时即可停止。报告具体的带schema限定的对象和证据，区分观察结果和推断，并说明局限性（如权限、统计信息过期、依赖缺失、应用逻辑未知等）。

Example finding

示例发现

Illustrative only; report facts verified in the target database:

Observed:

sales.orders

has a primary key on

order_id

and a foreign key from

account_id

sales.accounts.account_id

Inferred:
```
sales.orders
```
likely records one row per order; the keys support this, but do not establish what the business calls an “order.”
Unresolved: The catalog does not show whether canceled orders remain in this table. Confirm with the application owner before assuming they do.

仅作说明用；请报告在目标数据库中验证过的事实：

观察结果：
```
sales.orders
```
表的主键为
```
order_id
```
，且存在从
```
account_id
```
到
```
sales.accounts.account_id
```
的外键。
推断：
```
sales.orders
```
可能每个订单对应一行记录；键的设计支持这一推断，但无法确定业务中对“订单”的定义。
未明确： 目录中未显示已取消的订单是否仍保留在该表中。在假设已取消订单保留在此表之前，请先与应用负责人确认。

Safety and execution

安全与执行

Prefer structural catalog queries;
```
pg_stats
```
is data-derived and requires approval too. Do not change schema, data, roles, or session-wide settings without authorization. Never call discovered functions or procedures, refresh materialized views, or run
```
EXPLAIN ANALYZE
```
on unknown queries. A function marked
```
STABLE
```
or
```
IMMUTABLE
```
is not a safety guarantee.
If your client supports transactions, use a read-only transaction and a reasonable statement timeout for exploration. Metadata queries are not a license to run full-table counts or unrestricted scans. Ask before sampling data; sample only when necessary, with explicit limits and a clear understanding of table size and access controls.
All reference queries are plain PostgreSQL SQL. Bind
```
$1
```
,
```
$2
```
, etc. as values using your client's API.
```
psql
```
users can follow the optional psql adapter. Never interpolate an untrusted object name as raw SQL; placeholders cannot replace SQL identifiers in data queries.
Queries target PostgreSQL 18. Each version-sensitive section notes alternatives for older majors where applicable. Check
```
server_version_num
```
first. If a query fails due to permissions or version differences, report the limitation instead of guessing.

优先使用结构类目录查询；
```
pg_stats
```
属于数据衍生内容，同样需要批准。未经授权，请勿修改schema、数据、角色或会话级设置。切勿调用发现的函数或存储过程、刷新物化视图，或对未知查询运行
```
EXPLAIN ANALYZE
```
。标记为
```
STABLE
```
或
```
IMMUTABLE
```
的函数并不代表绝对安全。
如果你的客户端支持事务，请使用只读事务并设置合理的语句超时时间进行探索。元数据查询不代表可以运行全表计数或无限制扫描。采样数据前请先询问；仅在必要时进行采样，且需设置明确的限制，并清楚了解表大小和访问控制规则。
所有参考查询均为标准PostgreSQL SQL。使用客户端API将
```
$1
```
、
```
$2
```
等绑定为值。
```
psql
```
用户可以参考可选的psql适配器。切勿将不可信的对象名直接拼接进原始SQL；在数据查询中，占位符无法替代SQL标识符。
查询针对PostgreSQL 18版本。每个与版本相关的部分都会在适用时说明旧主版本的替代方案。请先检查
```
server_version_num
```
。如果查询因权限或版本差异失败，请报告该局限性，不要猜测。