Обсуждение: 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