具有备用序列的列

我想创建一个 user_widgets 表，该表由 user_id 和 user_widget_id 主键，其中 user_widget_id 的工作方式类似于序列号，除了每个用户从 1 个开始。

对此有通用或实用的解决方案吗？我正在使用 PostgreSQL，但也希望有一个不可知的解决方案。

示例表：user_widgets

 |  user_id  |  user_widget_id  |  user_widget_name    |
 +-----------+------------------+----------------------+
 |  1        |  1               | Andy's first widget  |
 +-----------+------------------+----------------------+
 |  1        |  2               | Andy's second widget |
 +-----------+------------------+----------------------+
 |  1        |  3               | Andy's third widget  |
 +-----------+------------------+----------------------+
 |  2        |  1               | Jake's first widget  |
 +-----------+------------------+----------------------+
 |  2        |  2               | Jake's second widget |
 +-----------+------------------+----------------------+
 |  2        |  3               | Jake's third widget  |
 +-----------+------------------+----------------------+
 |  3        |  1               | Fred's first widget  |
 +-----------+------------------+----------------------+

Edit:

我只是想说明一下这种设计的一些原因。

1. 减少信息泄露，而不仅仅是“隐匿安全”

在一个用户不应该知道彼此的系统中，他们也不应该知道彼此的 widget_id。如果这是一张库存表、奇怪的商业秘密、发票或更敏感的东西，他们就能够开始为这些小部件拥有自己的不受影响的 ID 集。除了明显的例行安全检查之外，这还添加了一个隐式安全层，其中表has通过小部件 ID 进行过滤and用户 ID。

2. 数据导入

应允许用户从其他系统导入数据，而不必丢弃所有旧 ID（如果他们有整数 ID）。

3. 清洁度

与我的第一点并没有太大不同，但我认为创建内容少于其他人的用户可能会对其小部件 ID 的显着跳跃感到困惑或恼火。这当然比功能更肤浅，但仍然很有价值。

一个可能的解决方案

答案之一表明应用程序层可以处理这个问题。我可以在该用户的表上存储一个递增的 next_id 列。或者甚至可能只是计算每个用户的行数，并且不允许删除记录（而是使用删除/停用标志）。这可以通过触发器函数甚至存储过程而不是在应用程序层中完成吗？

如果你有一张桌子：

CREATE TABLE user_widgets (
  user_id int
 ,user_widget_name text  --should probably be a foreign key to a look-up table
  PRIMARY KEY (user_id, user_widget_name)
)

你可以分配user_widget_id动态并查询：

WITH x AS (
   SELECT *, row_number() OVER (PARTITION BY user_id
                                ORDER BY user_widget_name) AS user_widget_id 
   FROM   user_widgets
   )
SELECT *
FROM   x
WHERE  user_widget_id = 2;

user_widget_id在这种情况下，按每个用户的字母顺序应用，并且没有间隙，显然，添加、更改或删除条目可能会导致更改。

更多关于手册中的窗口功能 http://www.postgresql.org/docs/current/interactive/functions-window.html.

稍微更稳定（但不完全）：

CREATE TABLE user_widgets (
  user_id int
 ,user_widget_id serial
 ,user_widget_name
  PRIMARY KEY (user_id, user_widget_id)
)

And:

WITH x AS (
   SELECT *, row_number() OVER (PARTITION BY user_id
                                ORDER BY user_widget_id) AS user_widget_nr 
   FROM   user_widgets
   )
SELECT *
FROM   x
WHERE  user_widget_nr = 2;

解决问题更新

You can实施一种制度来计算每个用户的现有小部件。但是您将很难使其对于并发写入来说无懈可击。您必须锁定整个表或使用SERIALIZABLE交易方式 http://www.postgresql.org/docs/current/interactive/transaction-iso.html#XACT-SERIALIZABLE- 两者都会真正降低性能并且需要额外的代码。

但如果你保证没有行被删除你可以采用我的第二种方法 - 一个序列user_widget_id桌子对面，给你一个"raw"ID。序列是并发加载的一种经过验证的解决方案，保留了相对顺序user_widget_id and is fast。您可以使用以下方式提供对表的访问view动态替换“原始”user_widget_id与相应的user_widget_nr就像我上面的查询一样。

您还可以（此外）“实现”无间隙user_widget_id将其替换为user_widget_nr在下班时间或由您选择的事件触发。

为了提高性能，我将有以下顺序user_widget_id从一个非常高的数字开始。似乎每个用户只能有几个小部件。

SELECT setval(user_widgets_user_widget_id_seq', 100000);

如果没有足够高的数字来保证安全，请添加一个标志。使用条件WHERE user_widget_id > 100000快速识别“原始”ID。如果您的桌子很大，您可能需要添加一个部分索引 http://www.postgresql.org/docs/current/interactive/indexes-partial.html使用条件（该条件会很小）。用于上述视图中CASE陈述。在这个“具体化”ID 的语句中：

UPDATE user_widgets w
SET    user_widget_id = u.user_widget_nr
FROM (
   SELECT user_id, user_widget_id
         ,row_number() OVER (PARTITION BY user_id
                             ORDER BY user_widget_id) AS user_widget_nr 
   FROM   user_widgets
   WHERE  user_widget_id > 100000
   ) u
WHERE  w.user_id = u.user_id
AND    w.user_widget_id = u.user_widget_id;

可能会跟进REINDEX甚至VACUUM FULL ANALYZE user_widgets下班时间。考虑一个FILLFACTOR http://www.postgresql.org/docs/current/interactive/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS低于 100，因为列将至少更新一次。

我当然会not将其留给应用程序。这会带来多个额外的故障点。