是否可以在每个记录标签上使用 PG 序列? [英] Is it possible to use a PG sequence on a per record label?

问题描述

PostgreSQL 9.2+ 是否提供任何功能来生成命名空间为特定值的序列?例如:

 .. |用户 ID |seq_id |身体|...----------------------------------- |4 |1 |ABC……"- |4 |2 |确定……"- |5 |1 |啧……"- |5 |2 |xyz……"- |5 |3 |123……"

这对于为用户生成自定义网址很有用:

domain.me/username_4/posts/1domain.me/username_4/posts/2domain.me/username_5/posts/1domain.me/username_5/posts/2domain.me/username_5/posts/3

我在 PG 文档(关于序列和序列函数)中没有找到任何内容来执行此操作.INSERT 语句中的子查询或自定义 PG 函数是否是唯一的其他选项?

解决方案

您可以在 INSERT 语句中使用子查询，例如 @Clodoaldo 演示.然而，这违背了在并发事务中安全使用序列的本质.此解决方案可能并且将会导致竞争条件并最终重复违反密钥.

您应该重新考虑您的方法.只需一个简单的表序列，并将其与 user_id 结合以获得所需的排序顺序.

您始终可以使用 row_number() 和一个简单的查询来生成具有所需数字的自定义 url，例如:

SELECT format('domain.me/username_%s/posts/%s'，用户身份,row_number() OVER (PARTITION BY user_id ORDER BY seq_id))从T;

-> SQLfiddle.>

Does PostgreSQL 9.2+ provide any functionality to make it possible to generate a sequence that is namespaced to a particular value? For example:

 .. | user_id | seq_id | body | ...
 ----------------------------------
  - |    4    |   1    |  "abc...."
  - |    4    |   2    |  "def...."
  - |    5    |   1    |  "ghi...."
  - |    5    |   2    |  "xyz...."
  - |    5    |   3    |  "123...."

This would be useful to generate custom urls for the user:

domain.me/username_4/posts/1    
domain.me/username_4/posts/2

domain.me/username_5/posts/1
domain.me/username_5/posts/2
domain.me/username_5/posts/3

I did not find anything in the PG docs (regarding sequence and sequence functions) to do this. Are sub-queries in the INSERT statement or with custom PG functions the only other options?

解决方案

You can use a subquery in the INSERT statement like @Clodoaldo demonstrates. However, this defeats the nature of a sequence as being safe to use in concurrent transactions. This solution can and will result in race conditions and eventually duplicate key violations.

You should rather rethink your approach. Just one plain sequence for your table and combine it with user_id to get the sort order you want.

You can always generate the custom urls with the desired numbers using row_number() with a simple query like:

SELECT format('domain.me/username_%s/posts/%s'
              ,user_id
              ,row_number() OVER (PARTITION BY user_id ORDER BY seq_id)
             )
FROM   t;

-> SQLfiddle.

这篇关于是否可以在每个记录标签上使用 PG 序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！