如何在多线程环境下正确使用PostgreSQL自定义缓存表？

问题1：避免缓存中出现User+Securable+Object重复行 #

核心方案：添加唯一约束并使用INSERT ... ON CONFLICT #

1. 给缓存表添加唯一约束，从底层防止重复数据插入：

ALTER TABLE "Permissions: Cache"
ADD CONSTRAINT unique_user_securable_object
UNIQUE ("User", "Securable", "Object");

2. 修改填充缓存的存储过程，替换原有的NOT IN判断（存在竞态和NULL值逻辑漏洞），改用NOT EXISTS结合ON CONFLICT DO NOTHING，确保并发场景下不会产生重复行：

Create Procedure "Permissions: Cache Persons Access Level For Operation"(_User uuid, _Operation uuid)
language plpgsql
As $$
begin
Insert Into "Permissions: Cache" ("User", "Securable", "Object", "Access Level")
Select _User, _Operation, "Persons"."id", "Permissions"."Access Level"
From "Persons"
Left Join Lateral "Permissions: Object Access Level For Person Operation"(_User, "id", _Operation) "Permissions" On True
Where NOT EXISTS (
    Select 1 From "Permissions: Cache"
    Where "User" = _User
    And "Securable" = _Operation
    And "Object" = "Persons"."id"
)
ON CONFLICT ("User", "Securable", "Object") DO NOTHING;
End; $$;

问题2：保证查询时缓存数据始终有效 #

方案A：软删除缓存（推荐，低并发阻塞） #

这种方式不直接删除缓存数据，而是标记为失效，避免其他事务的清空操作影响当前事务的查询：

1. 给缓存表添加有效性标记字段：

ALTER TABLE "Permissions: Cache"
ADD COLUMN is_valid boolean DEFAULT true;

2. 修改缓存清空触发器函数，改为标记失效而非硬删除：

Create Or Replace Function "Permissions: Clear Cache"() Returns Trigger As
$$
Begin
UPDATE "Permissions: Cache" SET is_valid = false;
Return Null;
End;
$$ Language plpgsql;

3. 修改缓存访问函数，只返回有效数据：

Create Function "Permissions: Access Cache"(_User uuid, _Securable uuid)
Returns Table ("Object" uuid, "Access Level" int) As
$$
Begin
Return Query
(
    Select "Cache"."Object", "Cache"."Access Level"
    From "Permissions: Cache" "Cache"
    Where "Cache"."User" = _User
    And "Cache"."Securable" = _Securable
    And "Cache".is_valid = true
);
End
$$
language 'plpgsql';

4. 更新存储过程，新增刷新失效缓存的逻辑：

Create Procedure "Permissions: Cache Persons Access Level For Operation"(_User uuid, _Operation uuid)
language plpgsql
As $$
begin
-- 先刷新已存在但失效的缓存记录
UPDATE "Permissions: Cache"
SET "Access Level" = p."Access Level", is_valid = true
From (
    Select "Persons"."id", "Permissions"."Access Level"
    From "Persons"
    Left Join Lateral "Permissions: Object Access Level For Person Operation"(_User, "id", _Operation) "Permissions" On True
) p
Where "Permissions: Cache"."User" = _User
And "Permissions: Cache"."Securable" = _Operation
And "Permissions: Cache"."Object" = p."id"
And "Permissions: Cache".is_valid = false;

-- 插入新的缓存记录
Insert Into "Permissions: Cache" ("User", "Securable", "Object", "Access Level")
Select _User, _Operation, "Persons"."id", "Permissions"."Access Level"
From "Persons"
Left Join Lateral "Permissions: Object Access Level For Person Operation"(_User, "id", _Operation) "Permissions" On True
Where NOT EXISTS (
    Select 1 From "Permissions: Cache"
    Where "User" = _User
    And "Securable" = _Operation
    And "Object" = "Persons"."id"
)
ON CONFLICT ("User", "Securable", "Object") DO NOTHING;
End; $$;

5. 将缓存填充和查询放在同一事务中，使用REPEATABLE READ隔离级别，确保事务内的缓存视图一致：

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Call "Permissions: Cache Persons Access Level For Operation"(u, o);

Select ...
From "Persons"
Inner Join "Permissions: Access Cache"(u, o) Cache On Cache."Object" = "Persons"."id"
Where Cache."Access Level" > 0;

COMMIT;

方案B：事务内加表锁（简单直接，高并发下可能阻塞） #

如果不需要软删除，可以在事务内给缓存表加共享锁，阻止其他事务的删除/更新操作，确保缓存数据在查询时存在：

BEGIN TRANSACTION;

-- 加共享锁，允许其他事务读，但阻止写操作
LOCK TABLE "Permissions: Cache" IN SHARE MODE;

Call "Permissions: Cache Persons Access Level For Operation"(u, o);

Select ...
From "Persons"
Inner Join "Permissions: Access Cache"(u, o) Cache On Cache."Object" = "Persons"."id"
Where Cache."Access Level" > 0;

COMMIT;

内容的提问来源于stack exchange，提问作者Raider