问题描述
限时送ChatGPT账号..免责声明:我的数据库知识主要来自Mysql,所以我可能会误解vertica中的一些东西......
我想知道是否存在一种在 vertica 中插入/更新值的技术,同时在多个会话中强制执行唯一约束.假设我有一张桌子:
I would like to know if there exist a technique of inserting/updating values in vertica while enforcing unique constrains across multiple sessions. Let's assume I have a table:
'id', 'unique_field', 'some_filed'
并且在 unique_field 上有一个唯一约束.我的理解是,在 vertica 中,首先需要执行插入操作,然后执行 ANALYZE_CONSTRAINTS 以验证是否违反了约束.在我的特定情况下,我有多个会话执行 INSERTS 到同一个表,他们可能会尝试推送相同的记录.单个会话的顺序似乎是:
And there is a unique constraint on unique_field. My understanding is that in vertica one first needs to do an insert and then do ANALYZE_CONSTRAINTS to verify if the constraint was violated. In my specific case, I have multiple sessions preforming INSERTS to the same table and they may try to push the same record. The sequence for a single session appears to be:
Insert into table (id, unique_field, some_field) values (5, 'abc', 'data');
SELECT ANALYZE_CONSTRAINTS(table);
COMMIT;
如果我在两个单独的会话中运行此序列,则两个查询都可能尝试插入相同的 unique_field,并且对于它们中的每一个,由于它们在单独的事务中,因此不会检测到约束违规,这将导致重复数据.
If I run this sequence in two separate sessions, there is a chance that both queries try to insert the same unique_field and for each of them there will be no constrain violation detected as they're in separate transactions, which will result in duplicate data.
有没有更好的方法来防止这种情况发生?或者我错过了一些东西.
Is there a better way to prevent this from happening ? Or I'm missing something.
推荐答案
由于与操作相关的潜在开销,Vertica 不会强制执行主键的唯一性或负载的唯一性约束.
Vertica does not enforce uniqueness of primary key or unique constraints on load due to the potential overhead associated with the operation.
如果 ANALYZE_CONSTRAINTS()
在提交之前运行,您应该能够捕获潜在的重复项.执行 UPDATE
也有开销,应该避免这些开销.
If ANALYZE_CONSTRAINTS()
is run before committing, you should be able to capture potential duplicates. There's also overhead with doing UPDATE
s and those should be avoided.
有多种方法可以在加载时强制执行唯一性,例如使用 MERGE
,或将数据暂存到临时表中.每种方法都有其自身的局限性.您可以在我的 博客中阅读有关强制执行数据唯一性的更多信息发布.
There are ways to enforce uniqueness on load such as using MERGE
, or staging the data in a temporary table. Each method has its own limitations. You can read more about enforcing uniqueness of data in my blog post.
更新:从 7.2 开始,Vertica 可以自动强制执行主键和唯一键约束.请参阅文档了解更多信息.
Update: As of 7.2, Vertica can automatically enforce primary and unique key constraints. See the documentation for more information.
这篇关于Vertica DB 中的独特约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
更多推荐
[db:关键词]
发布评论