postgresql currval 还没有定义这个会话，如何获取多会话序列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12481448/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
currval has not yet been defined this session, how to get multi-session sequences?
提问by MUY Belgium
My objective is to get a primary key field automatically inserted when inserting new row in the table.
我的目标是在表中插入新行时自动插入主键字段。
How to get a sequence going from session to session in PostgreSQL?
如何在 PostgreSQL 中从会话到会话获取序列？
doubleemploi@hanbei:/home/yves$ psql -d test
Mot de passe :
psql (8.4.13)
Saisissez ? help ? pour l''aide.
test=> create sequence test001 start 10;
CREATE SEQUENCE
test=> select currval('test001');
ERREUR: la valeur courante (currval) de la séquence ? test00 ? n''est pas encore définie dans cette session
--- current value not yet defined this session (???)
test=> select setval('test001', 10);
setval
--------
10
(1 ligne)
test=> select currval('test00');
currval
---------
10
(1 ligne)
test=> \q
test@hanbei:/home/yves$ psql -d test
Mot de passe :
psql (8.4.13)
Saisissez ? help ? pour l''aide.
test=> select currval('test001');
ERREUR: la valeur courante (currval) de la séquence ? test00 ? n''est pas encore définie dans cette session
采纳答案by Erwin Brandstetter
This may be simpler than you think ...
这可能比你想象的更简单......
My objective is to get a primary key field automatically inserted when inserting new row in the table.
我的目标是在表中插入新行时自动插入主键字段。
Just set the default value of the column:
只需设置列的默认值：
ALTER TABLE tbl ALTER COLUMN tbl_id SET DEFAULT nextval('my_seq'::regclass);
Or simpler yet, create the table with a serialtype for primary key to begin with:
或者更简单的是，创建一个serial主键类型的表：
CREATE TABLE tbl(
tbl_id serial PRIMARY KEY
,col1 txt
-- more columns
);
It creates a dedicated sequence and sets the default for tbl_id automatically.
它创建一个专用序列并自动为 tbl_id 设置默认值。
This way tbl_idis assigned the next value from the attached sequence automatically if you don't mention it in the INSERT. Works with anysession, concurrent or not.
这种方式tbl_id被分配从附加序列的下一个值，如果自动你不提它INSERT。适用于任何会话，无论是否并发。
INSERT INTO tbl(col1) VALUES ('foo');
If you want the new tbl_idback to do something with it:
如果您希望新的tbl_id背部对其进行处理：
INSERT INTO tbl(col1) VALUES ('foo') RETURNING tbl_id;
回答by MatheusOl
The currvalwill return the last value generated for the sequence within the current session. So if another session generates a new value for the sequence you still can retrieve the last value generated by YOUR session, avoiding errors.
在currval将返回在当前会话中序列生成的最后一个值。因此，如果另一个会话为该序列生成一个新值，您仍然可以检索您的会话生成的最后一个值，从而避免错误。
But, to get the last generated value on any sessions, you can use the above:
但是，要在任何会话中获取最后生成的值，您可以使用上述内容：
SELECT last_value FROM your_sequence_name;
Be careful, if the value was used by other session with an uncommited (or aborted) transaction and you use this value as a reference, you may get an error. Generally people just need the currvalor even the return of setval.
请注意，如果该值已被具有未提交（或中止）事务的其他会话使用，并且您将此值用作参考，则可能会出现错误。通常人们只需要currval甚至返回setval.
回答by Kemin Zhou
I will give a practical answer for this matter. My database server is used by my programs and my psql terminal; so there are multiple sessions. currently I am in my psql terminal:
对于这个问题，我会给出一个实际的答案。我的数据库服务器被我的程序和我的 psql 终端使用；所以有多个会话。目前我在我的 psql 终端：
fooserver=> select currval('fusion_id_seq');
ERROR: currval of sequence "fusion_id_seq" is not yet defined in this session
fooserver=> select nextval('fusion_id_seq');
nextval
---------
320032
(1 row)
fooserver=> select currval('fusion_id_seq');
currval
---------
320032
(1 row)
It looks that you can only see the values in your own session. This will also affect the currval of another session. This is probably related to multi-threading of the server to isolate different session. The counter (serial in psql) is a shared object. In my opinion, this session should be able to get the current value of the counter as long as the counter is properly locked to ensure only a single thread (session) can increment it (atomic operation). But I could be wrong here (not an expert on database server writer).
看起来您只能在自己的会话中看到这些值。这也会影响另一个会话的电流。这可能与服务器的多线程隔离不同的会话有关。计数器（psql 中的串行）是一个共享对象。在我看来，只要计数器被正确锁定以确保只有单个线程（会话）可以增加它（原子操作），这个会话应该能够获取计数器的当前值。但我在这里可能是错的（不是数据库服务器编写者的专家）。
回答by Jalal
Actually nextvalwill advance sequence and return the new value, so that would be the answer for your question.
实际上nextval将推进序列并返回新值，因此这将是您问题的答案。
currval will return the value most recently obtained with nextval for specified sequence (This though might fail if there wasn't nextval used in current session).
currval 将返回最近使用 nextval 获得的指定序列的值（如果当前会话中没有使用 nextval，这可能会失败）。
回答by Sandeep naik
This issue seems to be intermittent , For consistency use CTE to get inserted sequence for current session
此问题似乎是间歇性的，为了保持一致性，请使用 CTE 获取当前会话的插入序列
WITH inserted AS (
INSERT INTO notifn_main (notifn_dt,stat_id)
SELECT now(),22
FROM notifn
RETURNING id )
SELECT id
from inserted INTO tmp_id;
