问题描述
在日常管理 PostgreSQL，有时候我们需要对不需要使用的用户进行删除，有可能会出现删除不了的情况，报错如下：
rudonx=# drop user rudonx1;
ERROR: role "rudonx1" cannot be dropped because some objects depend on it
DETAIL: 1 object in database rudonx1
我们应该如何排查此类问题？
问题分析
删除用户之前，我们需要预先删除依赖该用户的对象，如 我们要删除 rudonx1 这个用户，需要删除所有 owner 为 rudonx1 的对象。
分析解决
有了前面的问题分析，那么后续的处理办法无非是两种：
- 删除这些对象
- 将这些对象的 owner 修改为其他用户，这样适用于这些对象还继续使用的情况，生产上这种情况较多。
这里面有个问题是，通常一个用户下面的表很多，我们如何可以快速的找到这些对象，并生成对应的语句？
操作步骤
1.确保当前的 search path 是正确的
rudonx1=# show search_path;
search_path
----------------
"$user",public
(1 row)
2.运行如下语句找到依赖的对象
rudonx1=# SELECT
'alter table "' || nsp.nspname || '"."' || cls.relname || '" owner to username;' || chr ( 13 )
FROM
pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp,
pg_user pu
WHERE
nsp.nspname IN ( 'public' )
AND cls.relnamespace = nsp.oid
and pu.usesysid=cls.relowner
AND cls.relkind IN ('r','v')
and pu.usename='rudonx1'
ORDER BY
nsp.nspname,
cls.relname;
?column?
------------------------------------------------------
alter table "public"."ttt" owner to username;\r
alter table "public"."ttt1" owner to username;\r
alter table "public"."ttt2" owner to username;\r
alter table "public"."ttt_view" owner to username;\r
(4 rows)
- public：指定搜索 public schema 下的对象
- relkind IN ('r','v')：指定搜索的对象为表和视图，相关的定义如下：r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table, I = partitioned index[1]。
3.最后执行拼接出来的语句，然后再删除用户即可。
当然我们也可以使用 \d 命令来进行筛选，这样的方式在表很多的方式下并不优雅。
rudonx1=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------+----------+---------
public | t1 | table | rudonx
public | t11 | table | rudonx
public | t1_id_seq | sequence | rudonx
public | ttt | table | rudonx1
public | ttt1 | table | rudonx1
public | ttt2 | table | rudonx1
public | ttt_view | view | rudonx1
(7 rows)
注意：建议在测试环境中充分测试，避免误操作带来业务中断的风险。
在 alter 表时语句阻塞问题
在前面我们已经拼接了 alter table 的语句，有一个问题是如果语句被阻塞，我们该如何排查？
1.使用 pg_stat_activity 查询等待事件为 Lock 的事务：
rudonx1=# SELECT * FROM pg_stat_activity WHERE wait_event_type ='Lock';
-[ RECORD 1 ]----+---------------------------------------------------
datid | 16469
datname | rudonx1
pid | 2323750
usesysid | 16468
usename | rudonx
application_name | psql
client_addr | 172.17.247.129
client_hostname |
client_port | 58358
backend_start | 2022-03-14 23:11:41.684693+08
xact_start | 2022-03-14 23:11:44.201661+08
query_start | 2022-03-14 23:11:44.201661+08
state_change | 2022-03-14 23:11:44.201669+08
wait_event_type | Lock
wait_event | relation
state | active
backend_xid | 11678
backend_xmin | 11676
query | alter table "public"."rudonx11_t" owner to rudonx;
backend_type | client backend
2.查看 阻塞的更信息信息
rudonx1=# SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocked_activity.client_addr as blocked_client_addr,
blocked_activity.client_hostname as blocked_client_hostname,
blocked_activity.client_port as blocked_client_port,
blocked_activity.application_name as blocked_application_name,
blocked_activity.wait_event_type as blocked_wait_event_type,
blocked_activity.wait_event as blocked_wait_event,
blocked_activity.query AS blocked_statement,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocking_activity.client_addr as blocking_user_addr,
blocking_activity.client_hostname as blocking_client_hostname,
blocking_activity.client_port as blocking_client_port,
blocking_activity.application_name as blocking_application_name,
blocking_activity.wait_event_type as blocking_wait_event_type,
blocking_activity.wait_event as blocking_wait_event,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted ORDER BY blocked_activity.pid;
-[ RECORD 1 ]-------------------------+---------------------------------------------------
blocked_pid | 2323750
blocked_user | rudonx
blocked_client_addr | 172.17.247.129
blocked_client_hostname |
blocked_client_port | 58358
blocked_application_name | psql
blocked_wait_event_type | Lock
blocked_wait_event | relation
blocked_statement | alter table "public"."rudonx11_t" owner to rudonx;
blocking_pid | 2320785
blocking_user | rudonx11
blocking_user_addr | 172.20.165.1
blocking_client_hostname |
blocking_client_port | 34002
blocking_application_name | psql
blocking_wait_event_type | Client
blocking_wait_event | ClientRead
current_statement_in_blocking_process | insert into rudonx11_t values(1);
从上面可以看出，pid 为 2320785 阻塞了 2323750 的执行。在与业务方商量之后，如果可以的话，手动终止 pid 为 2320785 的事务。
3.终止事务
rudonx1=# SELECT pg_terminate_backend(2320785);
-[ RECORD 1 ]--------+--
pg_terminate_backend | t
终止完事务之后，我们会发现 drop table 的操作会顺利执行，然而生产环境更加复杂，建议找到业务上可维护的时间窗口，确保语句可以顺利执行。
参考文档
[1] https://www.postgresql.org/docs/11/catalog-pg-class.html
如果您有其他问题，欢迎您联系火山引擎技术支持服务
