sequence "testsequence" is not yet defined in this session in postgresql java

Question

I am using java application with google juice as a injector along with jdbi as database layer.I need to fetch sequence from database using java code.

I have tables in database for the application,which sequence are not used.But for other purpose I need sequence value as current ,next and last.

although the following queries are working in pg-admin

select currval('testsequence'); 

select nextval('testsequence'); 

select last_value FROM testsequence;

the code is as follows.

public long getCurrentSequenceFromDB()
{
    LOGGER.info("SequenceHelper :getCurrentSequenceFromDBSTART");
    try (Handle handle = jdbi.open())
    {
        SequenceDAO squenceDAO = handle.attach(SequenceDAO.class);
        long sequence = squenceDAO.getCurrentSequence();
        if (sequence != 0)
        {
            LOGGER.info("SequenceHelper :getCurrentSequenceFromDBEND");
            return sequence;
        }
        else
        {
            LOGGER.info("SequenceHelper :getCurrentSequenceFromDBsequence sequence is null");
            return 0;
        }

    }
    catch (Exception e)
    {
        LOGGER.error("Error in getting sequence from database", e);
        throw new SignageServiceException(ErrorCodes.UNEXPECTED_ERROR, "Error in getting sequence from database", e);
    }
}

and at query level as:

@SqlQuery("select currval('public.testsequence')")
public long getCurrentSequence();

I get error as

Caused by: org.postgresql.util.PSQLException: 
ERROR: currval of sequence "testsequence" is not yet defined in this session

score 5 · Answer 1 · answered Apr 04 '19 at 11:20

The doc says

currval

Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

So you need to call nextval first, within this session.

Otherwise, if you want the last value for any session, you can use

SELECT last_value FROM sequence_name;

score 3 · Answer 2 · answered Apr 04 '19 at 12:25

The PostgreSQL nextval function must be called before calling the currval function, or an error will be thrown. The PostgreSQL currval function is used to access the current value of the specified sequence. Note that currval can only be used if nextval has been referenced in the current user session at least once.

now its working fine with sequence of database call as,

    @SqlQuery("select last_value from testsequence")
    public long getLastSequence();

    @SqlQuery("select nextval('testsequence')")
    public long getNextSequence();

    @SqlQuery("select currval('testsequence')")
    public long getCurrentSequence();

sequence "testsequence" is not yet defined in this session in postgresql java

2 Answers2