如何在 vertica 中获取最后发布的序列 ID? [英] How can I get the last issued sequence ID in vertica?
问题描述
背景:我正在从 postgreSQL 迁移到 Vertica，发现 IDENTITY 或 AUTO_INCREMENT 列中存在一些问题.这些问题之一是，vertica 无法将值分配给 IDENTITY 列或更改已将数据放入 IDENTITY 列的列.因此我创建了一个序列并将列的默认值设置为唯一的:
Background: I am migrating from postgreSQL to Vertica and found, that there are some issues in IDENTITY or AUTO_INCREMENT columns. One of these issues is, that vertica cannot assign values to IDENTITY columns or alter a column, that already has data into an IDENTITY column. Therefore I created a sequence and set the default value of the column to be unique doing:
SELECT MAX(id_column) FROM MY_SCHEMA.my_table;
这是 12345
CREATE SEQUENCE MY_SCHEMA.seq_id_column MINVALUE 12346 CACHE 1;
ALTER TABLE MY_SCHEMA.my_table
ALTER COLUMN id_column SET DEFAULT(MY_SCHEMA.seq_id_column.nextval);
ALTER TABLE MY_SCHEMA.log ADD UNIQUE(id_column);
按预期工作.在这种情况下，我禁用了缓存，因为我在单节点安装上并且我希望我的 ID 列是连续的.但是，这不是集群安装的一个选项，因为所需的锁会导致瓶颈.
Which works as expected. In this case, I have the cache deactivated, as I am on a single node installation and I want my ID column to be contiguous. However, this is not an option on a cluster installation as the needed lock leads to a bottleneck.
问题: 在具有多个节点的 vertica 集群中，如何访问会话中最后一次插入的 ID(无需额外选择)?
Question: In a vertica cluster with several nodes, how can I access the ID of the last insert in a session (without an additional select)?
例如在 postgreSQL 中，我可以做类似的事情
E.g. in postgreSQL I could do something like
INSERT INTO MY_SCHEMA.my_table RETURNING id_column;
这在 Vertica 中不起作用.此外，Vertica 的 LAST_INSERT_ID() 函数不适用于命名序列.我也觉得，查询 MY_SCHEMA.seq_id_column 的 current_value 可能会由于缓存而给出错误的结果，但我不确定这一点.
which does not work in Vertica. Furthermore, the LAST_INSERT_ID() function of Vertica does not work for named sequences. I also feel, that querying the current_value of MY_SCHEMA.seq_id_column could be giving wrong results due to caching, but I am unsure about this.
为什么没有额外的 SELECT?
据我所知，选择只会在提交后给出正确的值.由于性能原因，我无法在每次插入后进行提交.
To my knowledge, the select will only give correct values after a commit. I cannot do a commit after every single insert due to performance.
推荐答案
LukStorms 的评论为我指明了正确的方向.
The comments from LukStorms pointed me in the right direction.
NEXTVAL() 函数(据我测试)在这种情况下提供连续的值，其中一个会话查询它们.此外，在并发访问中，如果在插入后发出，CURRVAL 将检索缓存的值，该值保证是唯一的但不一定是连续的.因为我从来没有像在我的默认子句中那样在其他任何地方调用 NEXTVAL，这对我来说解决了这个问题，尽管在某些情况下，插入之间对 NEXTVAL 的额外调用会增加序列计数器.
The NEXTVAL() function (as far as I have tested) gives contiguous values in the case, where one single session queries them. Furthermore, on concurrent access, if issued after an insert, CURRVAL retrieves the cached value, which is guaranteed to be unique but not necessarily contiguous. As I never call NEXTVAL anywhere else as in my default clause, this solves the problem for me, although there might be cases, where an additional call to NEXTVAL between inserts increments the sequence counter.
我能想到的一种情况(我将在未来测试)是如果 AUTO COMMIT 设置为 OFF，即 ON 会发生什么 默认用于 vertica 客户端驱动程序.
One case I can think of (and that I will test in the future) is what happens if AUTO COMMIT is set to OFF, which is ON by default for the vertica client drivers.
更新:
这甚至似乎适用于 AUTOCOMMIT 为 OFF(使用 vertica-python 客户端驱动程序显示，其中 C 是连接，cur 是游标):
This even seems to work with AUTOCOMMIT being OFF (shown using the vertica-python client driver, where C is the connection and cur the cursor):
cur.execute("SELECT NEXTVAL('my_schema.my_sequence');")
cur.fetchall()
--> 1
cur.execute("SELECT CURRVAL('my_schema.my_sequence');")
cur.fetchall()
--> 1
cur.execute("SET SESSION AUTOCOMMIT TO OFF")
cur.execute("SELECT NEXTVAL('my_schema.my_sequence');")
cur.execute("SELECT NEXTVAL('my_schema.my_sequence');")
cur.execute("SELECT NEXTVAL('my_schema.my_sequence');")
cur.execute("SELECT CURRVAL('my_schema.my_sequence');")
cur.fetchall()
--> 4
然而，这在连接回滚期间似乎没有改变.所以会发生以下情况:
However, this seems to be unchanged during a rollback of the connection. So the following happens:
C.rollback()
cur.execute("SELECT CURRVAL('my_schema.my_sequence');")
cur.fetchall()
--> 4
这篇关于如何在 vertica 中获取最后发布的序列 ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
