超时

更改 Postgres 超时 #

🌐 Change Postgres timeout

你可以在以下位置更改 Postgres 的超时：

🌐 You can change the Postgres timeout at the:

1. 会话级别
2. 功能级别
3. 全球水平
4. 角色等级

会话级别 #

🌐 Session level

会话级别的设置只会在连接期间有效。

🌐 Session level settings persist only for the duration of the connection.

通过运行以下命令设置会话超时：

🌐 Set the session timeout by running:

1
set statement_timeout = '10min';

因为它只适用于会话，所以只能用于通过 Supavisor 在会话模式（端口 5432）下的连接或直接连接。不能在仪表板中使用，也不能与 Supabase 客户端 API 一起使用，也不能在 Supavisor 的事务模式（端口 6543）下使用。

🌐 Because it applies to sessions only, it can only be used with connections through Supavisor in session mode (port 5432) or a direct connection. It cannot be used in the Dashboard, with the Supabase Client API, nor with Supavisor in Transaction mode (port 6543).

这通常用于单个、长时间运行的管理任务，例如创建 HSNW 索引。设置实现后，你可以通过执行以下操作来查看它：

🌐 This is most often used for single, long running, administrative tasks, such as creating an HSNW index. Once the setting is implemented, you can view it by executing:

1
SHOW statement_timeout;

查看关于更改会话超时的完整指南。

🌐 See the full guide on changing session timeouts.

功能级别 #

🌐 Function level

当从 Supabase 客户端库调用时，这个方法适用于数据库 REST API：

🌐 This works with the Database REST API when called from the Supabase client libraries:

1
create or replace function myfunc()
2
returns void as $$
3
 select pg_sleep(3); -- simulating some long-running process
4
$$
5
language sql
6
set statement_timeout TO '4s'; -- set custom timeout

这主要是针对那些需要对运行时间有特别豁免的重复功能。

🌐 This is mostly for recurring functions that need a special exemption for runtimes.

角色等级 #

🌐 Role level

这设置了特定角色的超时时间。

🌐 This sets the timeout for a specific role.

默认的角色超时时间是：

🌐 The default role timeouts are:

• anon：3s
• authenticated：8s
• service_role：无（如果未设置，则默认为 authenticator 角色的 8 秒超时）
• postgres：无（默认全局超时限制为2分钟）

运行以下查询来更改角色的超时设置：

🌐 Run the following query to change a role's timeout:

1
alter role example_role set statement_timeout = '10min'; -- could also use seconds '10s'

1
NOTIFY pgrst, 'reload config';

与全局设置不同，结果无法通过 SHOW statement_timeout 检查。相反，请运行：

🌐 Unlike global settings, the result cannot be checked with SHOW statement_timeout. Instead, run:

1
select
2
  rolname,
3
  rolconfig
4
from pg_roles
5
where
6
  rolname in (
7
    'anon',
8
    'authenticated',
9
    'postgres',
10
    'service_role'
11
    -- ,<ANY CUSTOM ROLES>
12
  );

全球水平 #

🌐 Global level

这会更改所有角色和会话的语句超时，前提是它们还没有设置明确的超时。

🌐 This changes the statement timeout for all roles and sessions without an explicit timeout already set.

1
alter database postgres set statement_timeout TO '4s';

检查你的更改是否生效：

🌐 Check if your changes took effect:

1
show statement_timeout;

虽然不是必须的，但如果你不确定是否已设置超时，你可以进行一个快速测试：

🌐 Although not necessary, if you are uncertain if a timeout has been applied, you can run a quick test:

1
create or replace function myfunc()
2
returns void as $$
3
  select pg_sleep(601); -- simulating some long-running process
4
$$
5
language sql;

识别超时 #

🌐 Identifying timeouts

Supabase 控制台包含一些工具，可以帮助你识别超时和运行时间较长的查询。

🌐 The Supabase Dashboard contains tools to help you identify timed-out and long-running queries.

查询超时日志 #

🌐 Query timeout logs [#using-the-sql-editor]

进入 Explorer，选择 运行 SQL，选择查询来源 Logs，设置时间范围，然后运行以下查询以识别超时事件（statement timeout）和成功运行超过 10 秒的查询（duration）。

🌐 Go to the Explorer, select Run SQL, choose query source Logs, set a time range, and run the following query to identify timed-out events (statement timeout) and queries that successfully run for longer than 10 seconds (duration).

1
select
2
  timestamp,
3
  event_message,
4
  log_attributes['parsed.error_severity'] as error_severity,
5
  log_attributes['parsed.user_name'] as user_name,
6
  log_attributes['parsed.query'] as query,
7
  log_attributes['parsed.detail'] as detail,
8
  log_attributes['parsed.hint'] as hint,
9
  log_attributes['parsed.sql_state_code'] as sql_state_code,
10
  log_attributes['parsed.backend_type'] as backend_type
11
from logs
12
where
13
  source = 'postgres_logs'
14
  and match(event_message, 'duration|statement timeout')
15
  -- (OPTIONAL) MODIFY OR REMOVE
16
  and log_attributes['parsed.user_name'] = 'authenticator' -- <--------CHANGE
17
order by timestamp desc
18
limit 100;

使用查询性能页面 #

🌐 Using the Query Performance page

进入 查询性能页面，按相关角色和查询速度进行筛选。这只会显示运行缓慢但成功的查询。与日志不同，它不会显示超时的查询。

🌐 Go to the Query Performance page and filter by relevant role and query speeds. This only identifies slow-running but successful queries. Unlike the logs, it does not show you timed-out queries.

理解日志中的角色 #

🌐 Understanding roles in logs

每个 API 服务器都使用一个指定的用户来连接数据库：

🌐 Each API server uses a designated user for connecting to the database:

通过 parsed.user_name 字段筛选，只获取特定用户的日志：

🌐 Filter by the parsed.user_name field to only retrieve logs made by specific users:

1
-- find events based on role/server
2
... query
3
where
4
  -- find events from the relevant role
5
  log_attributes['parsed.user_name'] = '<ROLE>'