currval of sequence xxx_seq is not yet defined in this session?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

currval of sequence xxx_seq is not yet defined in this session?

kenyon
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 ?

God believes postgres is Good
Reply | Threaded
Open this post in threaded view
|

Re: currval of sequence xxx_seq is not yet defined in this session?

Tom Lane-2
kenyon <[hidden email]> 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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: currval of sequence xxx_seq is not yet defined in this session?

kenyon
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
Reply | Threaded
Open this post in threaded view
|

Re: currval of sequence xxx_seq is not yet defined in this session?

Adrian Klaver-3
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
[hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general