PostgreSQL中的依赖SERIAL列

Question

我希望在我的表格contacts中得到这个结果：

 |contact_id |  user_id         |  user_contact_id     |
 +-----------+------------------+----------------------+
 |  1        |  1               |          1           |
 +-----------+------------------+----------------------+
 |  2        |  1               |          2           |
 +-----------+------------------+----------------------+
 |  3        |  1               |          3           |
 +-----------+------------------+----------------------+
 |  4        |  2               |          1           |
 +-----------+------------------+----------------------+
 |  5        |  2               |          2           |
 +-----------+------------------+----------------------+
 |  6        |  2               |          3           |
 +-----------+------------------+----------------------+
 |  7        |  3               |          1           |
 +-----------+------------------+----------------------+

我只会插入user_id。

INSERT INTO contacts (user_id) VALUES ($user_id);

contact_id将自动递增，因为它是一个serial。我希望user_contact_id也能够由数据库自动填充，这样它就可以在并发写入时保持100%的稳定性。

- CodeRows

---

user_contact_id 的值是否总是从1到3计数？ - Scopey

关于之前相关问题的后续。https://dev59.com/2Yvda4cB1Zd3GeqPXUmH - Erwin Brandstetter

由于并发问题，这不是一件容易的事情。你需要非常准确。请在你的问题中澄清：你使用的Postgres版本是什么？ (user_id, user_contact_id)必须是唯一的吗？偶尔出现重复是否可以用contact_id进行区分（这会简单得多）？如何处理由 DELETE 和/或 UPDATE引起的user_contact_id中的间隙，还是只有 INSERT？序列中的间隙是否可以接受？您知道在并发加载下，在serial列中预计会出现间隙，对吗？这个用例是针对少量还是大量用户的？ - Erwin Brandstetter

@ErwinBrandstetter：我正在使用Postgres 9.4.1，重复数据不是借口，我需要一个严格的排列顺序，因为我将使用contact_user_id与user_id相结合，所以它必须始终唯一。 - CodeRows

我是否正确理解你的简短评论：你不打算进一步澄清你的问题了吗？ - Erwin Brandstetter

@ErwinBrandstetter 我使用了另一种选项，通过内部查询选择列的最大值并在其基础上加1。 - CodeRows

Answer 1

INSERT INTO CONTACTS
    (user_id, user_contact_id)
VALUES
    ($user_id, (SELECT COALESCE(MAX(user_contact_id), 0) + 1 FROM CONTACTS WHERE user_id = $user_id))

- Dondi Michael Stroma

---

Michael - 那很酷，但并发性如何？我将在有许多客户端向数据库插入记录的地方使用它。 - CodeRows

如果有新用户，这也不起作用，因为MAX将返回null，而null+1 == null。 - Sami Kuhmonen

@SamiKuhmonen，没错，我同意，你有解决这个问题的方法吗？ - CodeRows

@CodeRows 可以使用 COALESCE 来实现，使用 COALESCE(MAX(user_contact_id), 0)+1。 - Sami Kuhmonen

@CodeRows 你对并发的担忧是正确的。你不应该使用它 - 它容易出现竞争条件问题。 - Radek Postołowicz

Answer 2

正如其他用户建议的那样，只有sequence或serial类型才能保证并发安全。

如果您真的需要在每个user_id中“重新启动”user_contact_id，也许您可以使用以下视图：

create view contacts_v as
select
  contact_id,
  user_id,
  rank() over (partition by user_id order by contact_id) as user_contact_id
from contacts;

- Radek Postołowicz

Answer 3

你应该将序列作为user_contact_id的默认值。而这正是SERIAL列类型所做的。

http://www.postgresql.org/docs/9.1/static/datatype-numeric.html http://www.postgresql.org/docs/9.4/static/sql-createsequence.html

序列在并发写入时是安全的。

CREATE TABLE contacts (
  contact_id      SERIAL PRIMARY KEY,
  user_id         INTEGER,
  user_contact_id SERIAL
);

INSERT INTO contacts (user_id) VALUES
  (1), (1), (1), (2), (2), (2), (3);

以下是结果：

> SELECT * FROM contacts;
 contact_id | user_id | user_contact_id
------------+---------+-----------------
          1 |       1 |               1
          2 |       1 |               2
          3 |       1 |               3
          4 |       2 |               4
          5 |       2 |               5
          6 |       2 |               6
          7 |       3 |               7

> \d contacts
                                    Table "public.contacts"
     Column      |  Type   |                             Modifiers
-----------------+---------+--------------------------------------------------------------------
 contact_id      | integer | not null default nextval('contacts_contact_id_seq'::regclass)
 user_id         | integer |
 user_contact_id | integer | not null default nextval('contacts_user_contact_id_seq'::regclass)
Indexes:
    "contacts_pkey" PRIMARY KEY, btree (contact_id)

- Clément Prévost

---

我理解原问题是OP希望user_contact_id在每个user_id中有独立递增的值。 - fvu

哦，我没有注意到，抱歉。如果是这种情况，我会删除这个答案。 - Clément Prévost