Vertica DB 中的独特约束 [英] Unique Contrains in Vertica DB

问题描述

免责声明:我的数据库知识主要来自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 UPDATEs 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屋！