PostgreSQL 中的 nextval 和 curval 混淆
如果多个用户同时运行以下查询会发生什么？
INSERT INTO "Retail"."Attributes"("AttributeId","AttributeCode","AttributeName")
VALUES(nextval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass),'COL','Color');
INSERT INTO "Retail"."AttributeDetails"
("AttributeId","AttributeDetailCode","AttributeDetailName")
VALUES
(
currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Red', 'Color Red'
),
(
currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Blu', 'Color Blue'
),
(
currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Gre', 'Color Green'
);
这个方法和 SQL SERVER 的 SCOPE_IDENTITY() 一样可靠吗？任何建议将受到高度赞赏。
What will happen if multiple user run the following query at the same time?
INSERT INTO "Retail"."Attributes"("AttributeId","AttributeCode","AttributeName")
VALUES(nextval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass),'COL','Color');
INSERT INTO "Retail"."AttributeDetails"
("AttributeId","AttributeDetailCode","AttributeDetailName")
VALUES
(
currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Red', 'Color Red'
),
(
currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Blu', 'Color Blue'
),
(
currval('"Retail"."CompoundUnits_CompoundUnitId_seq"'::regclass), 'COL-Gre', 'Color Green'
);
Is this as method as reliable as SCOPE_IDENTITY() of SQL SERVER? Any suggestion will be highly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
currval()是特定于会话的。如果单独的会话增加您的序列，currval() 将继续返回您的预期值。也就是说，您可能想要使用
插入返回：currval()is session specific. If a separate session increments your sequence, currval() will continue to return your expected value.That said you may want to use
insert returning:根据文档 - “多个会话保证分配不同的序列值”，
因此您将获得不同的值，但根据创建序列对象时指定的“缓存”设置，在各个并发会话中获得的值可能并不总是连续的。
阅读有关序列的文档，尤其是“cache”参数。
Based on the documentation - 'multiple sessions are guaranteed to allocate distinct sequence values'
So you will get distinct values but depending on the 'cache' setting specified while creating the sequence object, the values obtained across various concurrent sessions might not always be sequential.
Read the documentation on sequences, especially the 'cache' parameter.
您对 currval 的使用完全没问题，
我不知道 SCOPE_IDENTITY() 所以我无法比较这两个概念。
顺便说一句：您不需要在语句中进行强制转换：
currval('"Retail"."CompoundUnits_CompoundUnitId_seq"')就足够了
Your usage of currval is perfectly OK
I don't know SCOPE_IDENTITY() so I cannot compare the two concepts.
Btw: you don't need the cast in your statement:
currval('"Retail"."CompoundUnits_CompoundUnitId_seq"')is enough