具有交替序列的列 [英] Column with alternate serials

问题描述

我想创建一个user_widgets的表，它是由user_id和user_widget_id主要键入的，user_widget_id的作用类似于一个序列号，除了它以每个用户的1开始。

I would like to create a table of user_widgets which is primary keyed by a user_id and user_widget_id, where user_widget_id works like a serial, except for that it starts at 1 per each user.

是否有一个常见或实际的解决方案？我正在使用PostgreSQL，但是不可推卸的解决方案也将不胜感激。

Is there a common or practical solution for this? I am using PostgreSQL, but an agnostic solution would be appreciated as well.

示例表：user_widgets

Example table: 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  |
 +-----------+------------------+----------------------+

编辑：

我只是想包括这个设计的一些原因。

I just wanted to include some reasons for this design.

1。较少的信息披露，而不仅仅是通过晦涩的安全

在用户不应该彼此意识到的系统中，他们也不应该意识到eachother的widget_id的。如果这是一个库存表，奇怪的商业秘密，发票或更敏感的东西，他们可以开始为这些小部件创建自己的一套ID。除了明显的常规安全检查之外，还添加了一个隐式安全层，其中表具有将被窗口小部件id 和用户id过滤。

In a system where user's should not be aware of one another, they also should not be aware of eachother's widget_id's. If this were a table of inventory, weird trade secrets, invoices, or something more sensitive, they be able to start have their own uninfluenced set of ID's for those widgets. In addition to the obvious routine security checks, this adds an implicit security layer where the table has to be filtered by both the widget id and the user id.

2。数据导入

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

Users should be permitted to import their data from some other system without having to trash all of their legacy IDs (if they have integer IDs).

3。清洁度

与我的第一点不太相似，但我认为创建的内容比其他用户少的用户可能会因为他们的widget的重大跳跃而感到困惑或懊恼ID的。这当然比功能更肤浅，但仍然有价值。

Not terribly dissimilar from my first point, but I think that users who create less content than other may be baffled or annoyed by significant jumps in their widget ID's. This of course is more superficial than functional, but could still be valuable.

可能的解决方案

其中一个答案表明应用层处理这个。我可以在该用户的表上存储一个next_id列，该列将增加。或者甚至只是计算每个用户的行数，而不允许删除记录（改为使用已删除/取消的标志）。这可以用触发器函数，甚至是存储过程而不是在应用程序层中完成？

One of the answers suggests the application layer handles this. I could store a next_id column on that user's table that gets incremented. Or perhaps even just count the rows per user, and not allow deletion of records (using a deleted/deactivated flag instead). Could this be done with a trigger function, or even a stored procedure rather than in the application layer?

推荐答案

如果你有表：

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 并查询：

You could assign user_widget_id dynamically and query:

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 情况并没有差距，显然，添加，更改或删除条目可能会导致更改。

user_widget_id is applied alphabetically per user in this scenario and has no gaps, Adding, changing or deleting entries can result in changes, obviously.

有关手册中的窗口功能。

有一点（但不完全）稳定：

Somewhat more (but not completely) stable:

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;

---

解决问题更新

您可以实现一个权限来计算每个用户的现有小部件。但是，您将很难在并发写入时对其进行防弹。您必须锁定整个表格，或使用 SERIALIZABLE 交易模式 - 这两个都是真正的降级表现，需要额外的代码。

---

Addressing question update

You can implement a regime to count existing widgets per user. But you will have a hard time making it bulletproof for concurrent writes. You would have to lock the whole table or use SERIALIZABLE transaction mode - both of which are real downers for performance and need additional code.

但如果您保证没有行被删除，您可以使用我的第二种方法 - 在表格中的 user_widget_id 的一个序列，为您提供一个 ID。一个序列是经过验证的并发负载解决方案，保留了 user_widget_id 中的相对顺序，并且是 fast 。您可以使用视图提供访问表格，该视图将动态替换raw user_widget_id 与相应的 user_widget_nr 像我上面的查询

But if you guarantee that no rows are deleted you could go with my second approach - one sequence for user_widget_id across the table, that giving you a "raw" ID. A sequence is a proven solution for concurrent load, preserves the relative order in user_widget_id and is fast. You could provide access to the table using a view that dynamically replaces the "raw" user_widget_id with the corresponding user_widget_nr like my query above.

你可以（另外）实现一个无缝的 user_widget_id 在短时间内由 user_widget_nr 替换，或者由您选择的事件触发。

You could (in addition) "materialize" a gapless user_widget_id by replacing it with user_widget_nr at off hours or triggered by events of your choosing.

为了提高性能，我将 user_widget_id 的序列以非常高的数字开头。似乎每个用户只能有少量的小部件。

To improve performance I would have the sequence for user_widget_id start with a very high number. Seems like there can only be a handful of widgets per user.

SELECT setval(user_widgets_user_widget_id_seq', 100000);

如果没有数字足够高以便安全，请添加一个标志。使用条件 WHERE user_widget_id> 100000 来快速识别原始ID。如果您的桌面很大，您可能需要添加部分索引使用条件（这将是小）。在 CASE 语句中的上述视图中使用。并在此声明中实现ID：

If no number is high enough to be safe, add a flag instead. Use the condition WHERE user_widget_id > 100000 to quickly identify "raw" IDs. If your table is huge you may want to add a partial index using the condition (which will be small). For use in the mentioned view in a CASE statement. And in this statement to "materialize" IDs:

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 at off hours。考虑一个 填充 低于100，因为列将至少更新一次。

Possibly follow up with a REINDEX or even VACUUM FULL ANALYZE user_widgets at off hours. Consider a FILLFACTOR below 100, as columns will be updated at least once.

我一定会不离开这到应用程序。这引起了多个额外的失败点。

I would certainly not leave this to the application. That introduces multiple additional points of failure.

这篇关于具有交替序列的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！