Обсуждение: currval of sequence xxx_seq is not yet defined in this session?
HI,everybody: recently i found some errors in pg_log when i do some pressure testing,looks like "2013-01-05 21:48:29.870 CST,"postgres","wedding",15850,"172.25.10.28:37100",50e82f2d.3dea,1,"SELECT",2013-01-05 21:48:29 CST,19/80509,0,ERROR,55000,"currval of sequence ""t_wedding_wedding_id_seq"" is not yet defined in this session" ,,,,,," SELECT currval('t_wedding_wedding_id_seq')as weddingId ",,,"" 2013-01-05 21:48:29.984 CST,"postgres","wedding",15856,"172.25.10.28:37123",50e82f2d.3df0,1,"SELECT",2013-01-05 21:48:29 CST,25/91770,0,ERROR,55000,"currval of sequence ""t_wedding_wedding_id_seq"" is not yet defined in this session" ,,,,,," SELECT currval('t_wedding_wedding_id_seq')as weddingId " the code is begin; insert into t_wedding(wedding_id,share_id,share_type,user_id,wedding_time) select nextval('t_wedding_wedding_id_seq') ,#ShareId#,#shareType#,#userId#,#weddingTime# <selectKey resultClass="int" keyProperty="weddingId"> SELECT currval('t_wedding_wedding_id_seq')as weddingId </selectKey> ; COMMIT; the column wedding_id type is serial + primary key, and i set the sequence cache from 1 to 50,errors still exists,but reduced。 I use ibatis,but it seems not support returning wedding_id !!! MY environment: Postgres 9.1.2 ibatis pgbouncer ( Transaction mode ) Postgres-9.1-901.jdbc4.jar as my drive As i used *begin * to wrapped it as a transaction, then why zhe errors still occurs ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/currval-of-sequence-xxx-seq-is-not-yet-defined-in-this-session-tp5738893.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
kenyon <lqchenzjut@sina.com> writes: > recently i found some errors in pg_log when i do some pressure testing,looks > like > "2013-01-05 21:48:29.870 > CST,"postgres","wedding",15850,"172.25.10.28:37100",50e82f2d.3dea,1,"SELECT",2013-01-05 > 21:48:29 CST,19/80509,0,ERROR,55000,"currval of sequence > ""t_wedding_wedding_id_seq"" is not yet defined in this session" > ,,,,,," SELECT currval('t_wedding_wedding_id_seq')as weddingId > ",,,"" This implies that currval('t_wedding_wedding_id_seq') is being called before any nextval('t_wedding_wedding_id_seq') has been done in the current session. It has nothing to do with sequence cache properties; it's an application programming error, plain and simple. > the code is > begin; > insert into t_wedding(wedding_id,share_id,share_type,user_id,wedding_time) > select nextval('t_wedding_wedding_id_seq') > ,#ShareId#,#shareType#,#userId#,#weddingTime# > <selectKey resultClass="int" keyProperty="weddingId"> > SELECT currval('t_wedding_wedding_id_seq')as weddingId > </selectKey> > ; > COMMIT; I don't know what the HTML-ish markup in that is supposed to mean, but if what it means is that some piece of client-side code is pulling out the contained query and using it separately, you need to trace what that logic is doing exactly. Because it sure looks like it's using the SELECT query wrongly --- either before the INSERT has been done, or perhaps in a different connection. regards, tom lane
Thanks for your reply! yeath,I quite accept your opinion,once i guess the drive adds savepoint between the two SQL,but not sure i want to get the last insert id to ensure the INSERT correctly ,so i called currval(). as the error occurs seldom,i find it's difficult to trace the problem or the logic is doing exactly later i modified the code looks like: >select nexval('t_wedding_wedding_id_seq') as Wedding_id; >insert into t_wedding(wedding_id...) select Wedding_id...; and in the later pressure tesing never saw the error again. ----- God believes postgres is Good -- View this message in context: http://postgresql.1045698.n5.nabble.com/currval-of-sequence-xxx-seq-is-not-yet-defined-in-this-session-tp5738893p5739294.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 01/08/2013 05:58 PM, kenyon wrote: > Thanks for your reply! > > yeath,I quite accept your opinion,once i guess the drive adds savepoint > between the two SQL,but not sure Off hand I would say the drive has nothing to do with it. The issue would seem to be that as originally written ibatis was issuing two queries in different sessions. This meant the query trying to fetch the currval could not find it because the sequence nextval() was happening in another session. -- Adrian Klaver adrian.klaver@gmail.com