現(xiàn)象

記錄一個(gè)生產(chǎn)問題，DBA在執(zhí)行下面這個(gè)GRANT操作的時(shí)候，偶發(fā)的會(huì)出現(xiàn)這個(gè)報(bào)錯(cuò)

[guqi@intel175 ~]$ psql -p 51300
psql (14devel)
Type "help" for help.

guqi=# grant select on all tables in schema public to guqi ;
ERROR:  tuple concurrently updated
guqi=# \q

分析

GRANT操作不會(huì)更新表內(nèi)的數(shù)據(jù)，應(yīng)該只是會(huì)更新系統(tǒng)表內(nèi)的行。換言之，當(dāng)執(zhí)行GRANT的時(shí)候，有些數(shù)據(jù)表在系統(tǒng)表內(nèi)的行被別人更新了。設(shè)置下errverbose，發(fā)現(xiàn)是在更新pg_class的relacl屬性

[guqi@intel175 ~]$ psql -p 51300
psql (14devel)
Type "help" for help.
guqi=#\errverbose
guqi=# grant select on all tables in schema public to guqi ;
^CCancel request sent
ERROR:  canceling statement due to user request
CONTEXT:  while updating tuple (6,6) in relation "pg_class"
guqi=# select * from pg_class where ctid = '(6,6)';
  oid  | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples
| relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasrules |
relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relr
ewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
-------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------
+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+-------------+-
---------------+----------------+----------------+---------------------+----------------+--------------+----------------+-----
-------+--------------+------------+--------+------------+--------------
 16458 | tb1     |         2200 |   16460 |         0 |       10 |     2 |       16462 |             0 |        0 |        -1
|             0 |             0 | f           | f           | p              | r       |        2 |         0 | f           |
f              | f              | f              | f                   | t              | d            | f              |
     0 |          580 |          1 |        |            |
(1 row)

同時(shí)查看pg_stat_activity，發(fā)現(xiàn)有TRUNCATE在運(yùn)行，與內(nèi)核開發(fā)的小伙伴溝通了下，truncate在內(nèi)部會(huì)重建表，重建會(huì)導(dǎo)致表的物理存儲(chǔ)發(fā)生變化?；谶@一點(diǎn)，我測(cè)試了下

# 窗口1
guqi=# begin;
BEGIN
guqi=*# truncate tb1 ;
TRUNCATE TABLE
-- 暫不提交

# 窗口2
guqi=# grant select on all tables in schema public to guqi ;
-- 操作會(huì)處于鎖等狀態(tài)

# 窗口1
guqi=*# commit;
COMMIT

# 窗口2
guqi=# grant select on all tables in schema public to guqi ;
ERROR:  tuple concurrently updated

按照上述順序執(zhí)行，100%觸發(fā)，而且通過跟蹤tb1在pg_class內(nèi)的ctid值，發(fā)現(xiàn)TRUNCATE前后確實(shí)變了，那么基本原因就是這個(gè)了。

解決的話，把GRANT和TRUNCATE分開執(zhí)行，不要并發(fā)。