计算下一个主键-特定格式 [英] Calculate next Primary Key - of specific format
问题描述
我有一个表，其中包含ID列表以及其他各种列，例如IDName。
I have a table which holds a list of IDs, and various other columns such as IDName.
表的主键是ID本身，但是它是不会自动递增。因此，我希望能够生成/计算下一个主键，但是有一个不同之处：
The Primary Key of the table is the ID itself, however it does not auto_increment. So, I want to be able to generate / calculate the next primary key, however there is a twist:
主键应采用特定格式，即8数字ID由三部分组成：
< level><代码<序列＃> ，例如< 2>< 777>< 0123> = 27770123
The primary key should be in a specific format, i.e. the 8 digit ID is made up of three parts:
<the level><a code><a sequence #>, e.g. <2><777><0123> = 27770123
因此，当我为表创建新的ID时，我想要特定级别和代码的下一个序列号。例如。按照上面的示例，我可能想知道代码2的级别2的下一个序列号，结果应该是ID 27770124（0124是序列中的下一个）。
So, when I am creating a new ID for the table, I want the next sequence number for a specific level and code. E.g. following the example above I might want to know the next sequence number for level 2 with code 777, the result should be an ID 27770124 (0124 being the next in the sequence).
任何帮助将不胜感激。
推荐答案
无间隙序列具有严重的性能和并发性问题。
Gapless sequences have serious performance and concurrency problems.
很难思考一次插入多个插入时会发生什么。您必须准备好重试失败的插入，或者在 INSERT 之前以独占模式 LOCK TABLE myTable ，所以只有一个 INSERT 可以一次运行。
Think very hard about what will happen when multiple inserts happen at once. You have to be prepared to retry failed inserts, or LOCK TABLE myTable IN EXCLUSIVE MODE before the INSERT so only one INSERT can be in flight at a time.
在这种情况下，我要做的是：
What I'd do in this situation is:
CREATE TABLE sequence_numbers(
level integer,
code integer,
next_value integer DEFAULT 0 NOT NULL,
PRIMARY KEY (level,code),
CONSTRAINT level_must_be_one_digit CHECK (level BETWEEN 0 AND 9),
CONSTRAINT code_must_be_three_digits CHECK (code BETWEEN 0 AND 999),
CONSTRAINT value_must_be_four_digits CHECK (next_value BETWEEN 0 AND 9999)
);
INSERT INTO sequence_numbers(level,code) VALUES (2,777);
CREATE OR REPLACE FUNCTION get_next_seqno(level integer, code integer)
RETURNS integer LANGUAGE 'SQL' AS $$
UPDATE sequence_numbers
SET next_value = next_value + 1
WHERE level = $1 AND code = $2
RETURNING (to_char(level,'FM9')||to_char(code,'FM000')||to_char(next_value,'FM0000'))::integer;
$$;
然后获取ID：
INSERT INTO myTable (sequence_number, blah)
VALUES (get_next_seqno(2,777), blah);
这种方法意味着只有一个事务可以插入任何给定（级别，模式）的行一次配对，但我认为它是无种族的。
This approach means that only one transaction can ever be inserting a row with any given (level,mode) pair at a time, but I think it's race-free.
如果两个并发事务试图在其中插入行，它们仍然可能死锁。不同的顺序。没有简单的解决方案；您必须对插入进行排序，以便始终在高电平之前插入低电平和低端模式，每个事务执行一次插入，否则将陷入死锁并重试。我个人愿意做后者。
There's still a problem where two concurrent transactions can deadlock if they try to insert rows in a different order. There's no easy fix for this; you have to either order your inserts so that you always insert low level and mode before high, do one insert per transaction, or live with deadlocks and retry. Personally I'd do the latter.
有两个psql会话的问题示例。设置为：
Example of the problem, with two psql sessions. Setup is:
CREATE TABLE myTable(seq_no integer primary key);
INSERT INTO sequence_numbers VALUES (1,666)
然后在两个会话中：
SESSION 1 SESSION 2
BEGIN;
BEGIN;
INSERT INTO myTable(seq_no)
VALUES(get_next_seqno(2,777));
INSERT INTO myTable(seq_no)
VALUES(get_next_seqno(1,666));
INSERT INTO myTable(seq_no)
VALUES(get_next_seqno(2,777));
INSERT INTO myTable(seq_no)
VALUES(get_next_seqno(1,666));
您会注意到会话2中的第二个插入将挂起而不会返回，因为它正在等待会话1持有的锁。当会话1继续尝试在其第二个插入中获取会话2持有的锁时，它也将挂起。无法取得任何进展，因此一两秒钟后，PostgreSQL将检测到死锁并中止其中一个事务，从而允许另一个继续进行：
You'll notice that the second insert in session 2 will hang without returning, because it's waiting on a lock held by session 1. When session 1 goes on to try to get a lock held by session 2 in its second insert, it too will hang. No progress can be made, so after a second or two PostgreSQL will detect the deadlock and abort one of the transactions, allowing the other to proceed:
ERROR: deadlock detected
DETAIL: Process 16723 waits for ShareLock on transaction 40450; blocked by process 18632.
Process 18632 waits for ShareLock on transaction 40449; blocked by process 16723.
HINT: See server log for query details.
CONTEXT: SQL function "get_next_seqno" statement 1
必须准备处理您的代码并重试整个交易，否则它必须避免使用单插入交易或谨慎排序的僵局。
Your code must either be prepared to handle this and retry the whole transaction, or it must avoid the deadlock using a single-insert transactions or careful ordering.
BTW，如果要使用 sequence_numbers 表格是首次使用时要创建的，由于它是提出问题。我将亲自修改 get_next_seqno 看起来像这样：
BTW, if you want (level,code) combinations that don't already exist in the sequence_numbers table to be created on first use, that's surprisingly complicated to get right as it's a variant of the upsert problem. I'd personally modify get_next_seqno to look like this:
CREATE OR REPLACE FUNCTION get_next_seqno(level integer, code integer)
RETURNS integer LANGUAGE 'SQL' AS $$
-- add a (level,code) pair if it isn't present.
-- Racey, can fail, so you have to be prepared to retry
INSERT INTO sequence_numbers (level,code)
SELECT $1, $2
WHERE NOT EXISTS (SELECT 1 FROM sequence_numbers WHERE level = $1 AND code = $2);
UPDATE sequence_numbers
SET next_value = next_value + 1
WHERE level = $1 AND code = $2
RETURNING (to_char(level,'FM9')||to_char(code,'FM000')||to_char(next_value,'FM0000'))::integer;
$$;
此代码可能会失败，因此您始终必须准备重试事务。正如该depesz文章所解释的那样，更可靠的方法是可能的，但通常不值得。如上文所述，如果两个事务同时尝试添加相同的新（级别，代码）对，则其中一个失败，并且将失败：
This code can fail, so you always have to be prepared to retry transactions. As that depesz article explains, more robust approaches are possible but usually not worth it. As written above, if two transactions concurrently try to add the same new (level,code) pair, one will fail with:
ERROR: duplicate key value violates unique constraint "sequence_numbers_pkey"
DETAIL: Key (level, code)=(0, 555) already exists.
CONTEXT: SQL function "get_next_seqno" statement 1
这篇关于计算下一个主键-特定格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
