更新时间:2023-12-01 22:02:10
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