时间戳

首页 > 解决方案 > 如何在 Postgres RDBMS 中为没有重叠和竞争条件的用户创建时间块条目（日历事件）

postgresql - 如何在 Postgres RDBMS 中为没有重叠和竞争条件的用户创建时间块条目（日历事件）

问题描述

我有一个表，其中包含 from 和 to 列，这些列是 Postgres DB 中带有时区的 DateTime 字段。该表还具有 user_id 以将此条目与用户相关联。

我想阻止特定用户的时间范围，当它可用时。

我目前的做法是

  1. 检查用户是否有任何行与我要阻止的时间重叠
  2. 如果未发现重叠，则为用户创建一个新行，其中包含在 from 和 to 字段中指定的时间块。

然而，

可能存在竞态条件，在第 1 步发生之后和第 2 步发生之前，另一个 API 请求可能会阻止时间重叠。

如何，

标签： postgresqldatabase-design

解决方案

SQL 需要不同的思考过程。SQL 是声明性的，并且基于集合操作而不是过程。但是您当前的方法是程序性的（第一次这样做，然后如果成功就这样做）。基于集合的视图将其减少到仅 1 步（如果不是，请执行此操作）。您的具体情况变为：insert a time block for the user when that time block does not overlap an existing one for the user以下以一种方式来完成此操作。
假设此功能对您来说很常见，下面将构建一个 SQL 函数来完成它。如果请求的时间范围与它返回的用户的现有时间范围重叠，否则它返回插入的行。（您没有提供表定义，所以我只是做了一个）。

create table user_cal ( id integer generated always as identity 
                      , user_id    integer
                      , start_time timestamptz
                      , end_time   timestamptz
                      ) ;  

create or replace 
function gen_user_cal(user_id_in    user_cal.user_id%type
                     ,start_time_in user_cal.start_time%type
                     ,end_time_in   user_cal.end_time%type
                     ) 
  returns user_cal
  language sql
as $$
   insert into user_cal(user_id, start_time, end_time) 
      select user_id_in,start_time_in,end_time_in
    where not exists 
          ( select null 
              from user_cal uc
             where uc.user_id = user_id_in   
               and tstzrange(start_time_in,end_time_in,'[]') &&
                   tstzrange(uc.start_time,uc.end_time,'[]')
          )
    returning * 
$$;

它通过创建一个 TSTZRANGE（请参阅范围类型和范围运算符）来工作，其中包括请求和现有期间的开始时间和结束时间。然后检查它们是否重叠（范围重叠运算符（&&））。请参见此处的示例。

这大大降低了达到比赛条件的机会。但并没有完全消除它。如果仍然担心，请参阅 Transaction Isolation Serializable。

推荐阅读