用随机数替换序列 [英] Replacing sequence with random number
问题描述
我想用自己定制的ID生成器替换我在postgresql数据库中用于ID的某些序列。生成器将产生一个随机数，最后带有一个校验位。因此：
I would like to replace some of the sequences I use for id's in my postgresql db with my own custom made id generator. The generator would produce a random number with a checkdigit at the end. So this:
SELECT nextval('customers')
将被这样的东西替换：
SELECT get_new_rand_id('customer')
然后，该函数将返回一个数值，例如： [1 -9] [0-9] {9} ，其中最后一位是校验和。
The function would then return a numerical value such as: [1-9][0-9]{9} where the last digit is a checksum.
我的关注点是：
- 如何使事物具有原子性
- 如何避免两次返回相同的id（这将是通过尝试将其插入具有唯一约束的列中而被捕获，但是后来到了我认为很晚）
- 这是个好主意吗？
注意1 ：我不想使用uuid，因为它可以与客户交流，而且10位数字的交流比36字符的uuid容易得多
Note1: I do not want to use uuid since it is to be communicated with customers and 10 digits is far simpler to communicate than the 36 character uuid.
注2 ：很少使用 SELECT get_new_rand_id（）调用该函数，但是会d在id列上被指定为默认值，而不是 nextval（）。
Note2: The function would rarely be called with SELECT get_new_rand_id() but would be assigned as default value on the id-column instead of nextval().
EDIT ：好的，下面的讨论很好！以下是为什么的一些解释：
EDIT: Ok, good discussusion below! Here are some explanation for why:
-
那我为什么要用这种方式使事情过于复杂呢？目的是向客户隐藏主键。
So why would I over-comlicate things this way? The purpouse is to hide the primary key from the customers.
我给每个新客户一个唯一的
customerId（生成的序列号）在
数据库中）。由于我已经与客户沟通了
的号码，因此对于我的竞争对手
来说，监视我的业务是一项
的任务非常简单（还有
个其他数字，例如发票nr和$ b $具有相同
属性的b订单nr）。正是这种监视，我
希望使
更加困难（注意：不是没有可能，但是使
更加困难）。
I give each new customer a unique customerId (generated serial number in the db). Since I communicate that number with the customer it is a fairly simple task for my competitors to monitor my business (there are other numbers such as invoice nr and order nr that have the same properties). It is this monitoring I would like to make a little bit harder (note: not impossible but harder).
为什么要输入校验位？
Why the check digit?
在谈论隐藏序列号之前，我添加了一个因为在生产中的某些时候手指有些笨拙，所以我认为这是将来保留的好习惯。
Before there was any talk of hiding the serial nr I added a checkdigit to ordernr since there were klumbsy fingers at some points in the production, and my thought was that this would be a good practice to keep in the future.
在阅读讨论之后，我当然可以看到我的方法不是解决问题的最佳方法，但是我对解决这个问题没有其他好主意，所以请在这里为我提供帮助。
After reading the discussion I can certainly see that my approach is not the best way to solve my problem, but I have no other good idea of how to solve it, so please help me out here.
- 我应该在我将暴露给客户的ID放入其中的同时添加一个额外的列并保留序列号作为主键？
- 我如何生成以合理有效的方式公开的id？
- 校验位是否必要？
推荐答案
Fo使用密码从序列中生成唯一且具有随机外观的标识符可能是个好主意。由于它们的输出是双射的（输入值和输出值之间存在一对一的映射）-与散列不同，您将没有任何冲突。
For generating unique and random-looking identifiers from a serial, using ciphers might be a good idea. Since their output is bijective (there is a one-to-one mapping between input and output values) -- you will not have any collisions, unlike hashes. Which means your identifiers don't have to be as long as hashes.
大多数加密密码都适用于64位或更大的块，但是PostgreSQL Wiki上有适用于（32位）功能的非加密密码示例PL / pgSQL过程 int 类型。免责声明：我尚未尝试使用此功能。
Most cryptographic ciphers work on 64-bit or larger blocks, but the PostgreSQL wiki has an example PL/pgSQL procedure for a "non-cryptographic" cipher function that works on (32-bit) int type. Disclaimer: I have not tried using this function myself.
要将其用于主键，请从Wiki页面运行CREATE FUNCTION调用，然后在您的空表执行：
To use it for your primary keys, run the CREATE FUNCTION call from the wiki page, and then on your empty tables do:
ALTER TABLE foo ALTER COLUMN foo_id SET DEFAULT pseudo_encrypt(nextval('foo_foo_id_seq')::int);
瞧！
pg=> insert into foo (foo_id) values(default);
pg=> insert into foo (foo_id) values(default);
pg=> insert into foo (foo_id) values(default);
pg=> select * from foo;
foo_id
------------
1241588087
1500453386
1755259484
(4 rows)
这篇关于用随机数替换序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
