Thread: Fw: postgresql experts please help

Fw: postgresql experts please help

From
"Andrei Ilitchev"
Date:
Dear PostgreSQL jdbc experts,

I created a table with identity:
CREATE TABLE MAN (ID  SERIAL, NAME VARCHAR(40), PRIMARY KEY (ID))
then through jdbc connection inserted a row into the table,
then attempted to select pk value back through the same jdbc connection
using currval - that failed with "not yet defined in this session"
exception.

What's wrong here?

Please take a look at the test case and results below.

Thanks a lot,

Andrei

----- Original Message -----
From: "Marina Vatkina" <Marina.Vatkina@Sun.COM>
To: <persistence@glassfish.dev.java.net>
Sent: Wednesday, October 17, 2007 6:57 PM
Subject: Re: postgresql experts please help


> Andrei,
>
> This is what I got so far:
>> I actually don't see the problem here.  All of what they're getting is
>> the behavior I would expect.
>
> They suggested to contact pgsql-jdbc@postgresql.org to get more people to
> look at the problem.
>
> Regards,
> -marina
>
> Andrei Ilitchev wrote:
>> I am trying to implement support for both Identity and sequenceObjects on
>> the same database platform.
>> For some reason currval doesn't work for me: called right after insert it
>> throws exception indicating that nextval hasn't yet been called in this
>> session -
>> and yet insert is successfull: the pk value has been inserted.
>>  I am using
>>  Database: PostgreSQL  Version: 8.2.5
>>  Driver: PostgreSQL Native Driver  Version: PostgreSQL 8.2 JDBC3 with SSL
>> (build 505)
>>  Here's the JDBC test:
>>     public void testJdbcSequencing() throws java.sql.SQLException {
>>         Accessor accessor =
>> this.getServerSession().getConnectionPool("default").acquireConnection();
>>         java.sql.Connection conn = accessor.getConnection();
>>         try {
>>             java.sql.PreparedStatement pstmt0 =
>> conn.prepareStatement("select ID FROM MAN");
>>             java.sql.ResultSet resultSet = pstmt0.executeQuery();
>>             System.out.println("MAN before insert: ");
>>             while(resultSet.next()) {
>>                 System.out.print(resultSet.getInt(1) + "; ");
>>             }
>>             resultSet.close();
>>             System.out.println();
>>  java.sql.PreparedStatement pstmt1 = conn.prepareStatement("INSERT INTO
>> MAN (NAME) VALUES (null)");
>>             int nInserted = pstmt1.executeUpdate();
>>             pstmt1.close();
>>             System.out.println("Inserted rows: " + nInserted);
>>  resultSet = pstmt0.executeQuery();
>>             System.out.println("MAN after insert: ");
>>             while(resultSet.next()) {
>>                 System.out.print(resultSet.getInt(1) + "; ");
>>             }
>>             resultSet.close();
>>             pstmt0.close();
>>             System.out.println();
>>  System.out.println("Attempt: select currval(\'MAN_ID_seq\')");
>>             resultSet = null;
>>             java.sql.PreparedStatement pstmt2 =
>> conn.prepareStatement("select currval(\'MAN_ID_seq\')");
>>             try {
>>                 resultSet = pstmt2.executeQuery();
>>             } catch (java.sql.SQLException ex) {
>>                 ex.printStackTrace();
>>                 pstmt2.close();
>> //                throw ex;
>>             }
>>             if(resultSet != null) {
>>                 System.out.print("select currval: ");
>>                 while(resultSet.next()) {
>>                     System.out.println(resultSet.getString(1));
>>                 }
>>                 resultSet.close();
>>                 pstmt2.close();
>>             }
>>            System.out.println("Now attempt: select
>> nextval(\'MAN_ID_seq\')");
>>             resultSet = null;
>>             java.sql.PreparedStatement pstmt3 =
>> conn.prepareStatement("select nextval(\'MAN_ID_seq\')");
>>             try {
>>                 resultSet = pstmt3.executeQuery();
>>             } catch (java.sql.SQLException ex3) {
>>                 ex3.printStackTrace();
>>                 pstmt3.close();
>>             //                throw ex;
>>             }
>>             if(resultSet != null) {
>>                 System.out.print("select nextval: ");
>>                 while(resultSet.next()) {
>>                     System.out.println(resultSet.getString(1));
>>                 }
>>                 resultSet.close();
>>                 pstmt3.close();
>>             }           System.out.println("Now again attempt: select
>> currval(\'MAN_ID_seq\')");
>>             resultSet = null;
>>             java.sql.PreparedStatement pstmt4 =
>> conn.prepareStatement("select currval(\'MAN_ID_seq\')");
>>             try {
>>                 resultSet = pstmt4.executeQuery();
>>             } catch (java.sql.SQLException ex4) {
>>                 ex4.printStackTrace();
>>                 pstmt4.close();
>>             //                throw ex;
>>             }
>>             if(resultSet != null) {
>>                 System.out.print("select currval: ");
>>                 while(resultSet.next()) {
>>                     System.out.println(resultSet.getString(1));
>>                 }
>>                 resultSet.close();
>>                 pstmt4.close();
>>             }           } finally {
>>
>> this.getServerSession().getConnectionPool("default").releaseConnection(accessor);
>>         }
>>     }
>> And here's result:
>> MAN before insert:
>>  Inserted rows: 1
>> MAN after insert:
>> 2;
>> Attempt: select currval('MAN_ID_seq')
>> org.postgresql.util.PSQLException: ERROR: currval of sequence
>> "man_id_seq" is not yet defined in this session
>>  at
>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
>>  at
>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
>>  at
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
>>  at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
>>  at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
>>  at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
>>  at
>> oracle.toplink.testing.tests.cmp3.advanced.AdvancedJunitTest.testJdbcSequencing(AdvancedJunitTest.java:246)
>>  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>  at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>>  at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>>  at java.lang.reflect.Method.invoke(Method.java:585)
>>  at junit.framework.TestCase.runTest(TestCase.java:154)
>>  at junit.framework.TestCase.runBare(TestCase.java:127)
>>  at junit.framework.TestResult$1.protect(TestResult.java:106)
>>  at junit.framework.TestResult.runProtected(TestResult.java:124)
>>  at junit.framework.TestResult.run(TestResult.java:109)
>>  at junit.framework.TestCase.run(TestCase.java:118)
>>  at junit.framework.TestSuite.runTest(TestSuite.java:208)
>>  at junit.framework.TestSuite.run(TestSuite.java:203)
>>  at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
>>  at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
>>  at junit.framework.TestResult.runProtected(TestResult.java:124)
>>  at junit.extensions.TestSetup.run(TestSetup.java:23)
>>  at junit.swingui.TestRunner$16.run(TestRunner.java:623)
>> Now attempt: select nextval('MAN_ID_seq')
>> select nextval: 216
>> Now again attempt: select currval('MAN_ID_seq')
>> select currval: 216
>> Thanks a lot!
>>  Andrei
>


Re: Fw: postgresql experts please help

From
Mark Lewis
Date:
On Thu, 2007-10-18 at 09:17 -0400, Andrei Ilitchev wrote:
> Dear PostgreSQL jdbc experts,
>
> I created a table with identity:
> CREATE TABLE MAN (ID  SERIAL, NAME VARCHAR(40), PRIMARY KEY (ID))
> then through jdbc connection inserted a row into the table,
> then attempted to select pk value back through the same jdbc connection
> using currval - that failed with "not yet defined in this session"
> exception.
>
> What's wrong here?

You haven't disabled JDBC autocommit for this connection.  That means
that each statement gets executed in a separate transaction.

Since currval is only valid within the scope of a single transaction,
and you're executing your "select currval" from a separate transaction,
that's why you can't see it.

-- Mark Lewis

Re: Fw: postgresql experts please help

From
Kris Jurka
Date:

On Thu, 18 Oct 2007, Mark Lewis wrote:

> You haven't disabled JDBC autocommit for this connection.  That means
> that each statement gets executed in a separate transaction.
>
> Since currval is only valid within the scope of a single transaction,
> and you're executing your "select currval" from a separate transaction,
> that's why you can't see it.
>

False.  currval maintains state across transactions:

jurka=# create sequence myseq;
CREATE SEQUENCE
jurka=# begin;
BEGIN
jurka=# select nextval('myseq');
  nextval
---------
        1
(1 row)

jurka=# commit;
COMMIT
jurka=# select currval('myseq');
  currval
---------
        1
(1 row)


Re: Fw: postgresql experts please help

From
Kris Jurka
Date:

On Thu, 18 Oct 2007, Andrei Ilitchev wrote:

> I created a table with identity:
> CREATE TABLE MAN (ID  SERIAL, NAME VARCHAR(40), PRIMARY KEY (ID))
> then through jdbc connection inserted a row into the table,
> then attempted to select pk value back through the same jdbc connection using
> currval - that failed with "not yet defined in this session" exception.
>

Any chance you have two man_id_seq sequences in different schemas?  The
table insert might use one and the direct currval/nextval calls might use
another?

Kris Jurka

Re: Fw: postgresql experts please help

From
Michael Schmidt
Date:
Mark Lewis wrote:
> On Thu, 2007-10-18 at 09:17 -0400, Andrei Ilitchev wrote:
>> Dear PostgreSQL jdbc experts,
>>
>> I created a table with identity:
>> CREATE TABLE MAN (ID  SERIAL, NAME VARCHAR(40), PRIMARY KEY (ID))
>> then through jdbc connection inserted a row into the table,
>> then attempted to select pk value back through the same jdbc connection
>> using currval - that failed with "not yet defined in this session"
>> exception.
>>
>> What's wrong here?
>
> You haven't disabled JDBC autocommit for this connection.  That means
> that each statement gets executed in a separate transaction.
>
> Since currval is only valid within the scope of a single transaction,
> and you're executing your "select currval" from a separate transaction,
> that's why you can't see it.
>
> -- Mark Lewis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>
Also, from my read of the documentation and experience, you have to call
nextval before you can call currval in the transaction.

Michael Schmidt

Re: Fw: postgresql experts please help

From
Kris Jurka
Date:

On Thu, 18 Oct 2007, Michael Schmidt wrote:

> Also, from my read of the documentation and experience, you have to call
> nextval before you can call currval in the transaction.
>

Right, but the insert should trigger the default on the serial column to
call nextval behind the scenes.

Kris Jurka

Re: Fw: postgresql experts please help

From
Mark Lewis
Date:
On Thu, 2007-10-18 at 13:41 -0400, Kris Jurka wrote:
>
> On Thu, 18 Oct 2007, Mark Lewis wrote:
>
> > You haven't disabled JDBC autocommit for this connection.  That means
> > that each statement gets executed in a separate transaction.
> >
> > Since currval is only valid within the scope of a single transaction,
> > and you're executing your "select currval" from a separate transaction,
> > that's why you can't see it.
> >
>
> False.  currval maintains state across transactions:

Oops.  I knew that-- not thinking straight this morning I guess :)  I
wish mailing list archives had take-backs.

Re: Fw: postgresql experts please help

From
"Andrei Ilitchev"
Date:
> Any chance you have two man_id_seq sequences in different schemas?  The
> table insert might use one and the direct currval/nextval calls might use
> another?

I don't know how that happened but I think that's right:
select of the row after insert returned ID=2,
but somehow subsequent call to sequence's nextval returned 216.

In the docs I discovered "select lastval()" which seems to behave exactly
like @@Identity in Sybase returning the latest nextval that was obtained in
the current session by no-matter-which sequence.
That's exactly the functionality I need - much easier to use because no need
to track the name.

Can you think of any reason why using "select lastval()" would be a bad
idea?

Thanks a lot,

Andrei

----- Original Message -----
From: "Kris Jurka" <books@ejurka.com>
To: "Andrei Ilitchev" <andrei.ilitchev@oracle.com>
Cc: <pgsql-jdbc@postgresql.org>; "Marina Vatkina" <Marina.Vatkina@Sun.COM>
Sent: Thursday, October 18, 2007 1:45 PM
Subject: Re: [JDBC] Fw: postgresql experts please help


>
>
> On Thu, 18 Oct 2007, Andrei Ilitchev wrote:
>
>> I created a table with identity:
>> CREATE TABLE MAN (ID  SERIAL, NAME VARCHAR(40), PRIMARY KEY (ID))
>> then through jdbc connection inserted a row into the table,
>> then attempted to select pk value back through the same jdbc connection
>> using currval - that failed with "not yet defined in this session"
>> exception.
>>
>
> Any chance you have two man_id_seq sequences in different schemas?  The
> table insert might use one and the direct currval/nextval calls might use
> another?
>
> Kris Jurka
>


Re: Fw: postgresql experts please help

From
Kris Jurka
Date:

On Thu, 18 Oct 2007, Andrei Ilitchev wrote:

> In the docs I discovered "select lastval()" which seems to behave exactly
> like @@Identity in Sybase returning the latest nextval that was obtained in
> the current session by no-matter-which sequence.
> That's exactly the functionality I need - much easier to use because no need
> to track the name.
>
> Can you think of any reason why using "select lastval()" would be a bad idea?
>

If you have an after trigger on the table that you're inserting into and
it inserts into another table with a serial column, the lastval call will
use the wrong sequence.  Now this is also a problem with currval if you
have a trigger that inserts into the same table, but that's an unlikely
application design.  Inserting into another table is something that you'll
find with systems that do auditing or sometimes horizontal partitioning of
a table.

Kris Jurka

Re: Fw: postgresql experts please help

From
Josh Berkus
Date:
Kris,

> If you have an after trigger on the table that you're inserting into and
> it inserts into another table with a serial column, the lastval call will
> use the wrong sequence.  Now this is also a problem with currval if you
> have a trigger that inserts into the same table, but that's an unlikely
> application design.  Inserting into another table is something that you'll
> find with systems that do auditing or sometimes horizontal partitioning of
> a table.

I'm pretty sure there's a version of lastval() which takes the table name as a
parameter.  That would be the safer one to use.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

Re: Fw: postgresql experts please help

From
Kris Jurka
Date:

On Thu, 18 Oct 2007, Josh Berkus wrote:

> I'm pretty sure there's a version of lastval() which takes the table
> name as a parameter.  That would be the safer one to use.
>

There is:

SELECT currval(pg_get_serial_sequence('mytab','mycol'));

Kris Jurka

Re: Fw: postgresql experts please help

From
Josh Berkus
Date:
Kris,

> SELECT currval(pg_get_serial_sequence('mytab','mycol'));

You mean lastval().

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

Re: Fw: postgresql experts please help

From
Kris Jurka
Date:

On Thu, 18 Oct 2007, Josh Berkus wrote:

>> SELECT currval(pg_get_serial_sequence('mytab','mycol'));
>
> You mean lastval().
>

No, lastval only comes in a zero argument variety.

Kris Jurka

Re: Fw: postgresql experts please help

From
Michael Schmidt
Date:
Kris Jurka wrote:
>
>
> On Thu, 18 Oct 2007, Michael Schmidt wrote:
>
>> Also, from my read of the documentation and experience, you have to
>> call nextval before you can call currval in the transaction.
>>
>
> Right, but the insert should trigger the default on the serial column to
> call nextval behind the scenes.
>
> Kris Jurka
>
Exactly, because nextval is the default for the sequence.  However, if
you do a transaction in which you don't do an insert, then currval won't
be available.

Michael Schmidt

Re: Fw: postgresql experts please help

From
"Andrei Ilitchev"
Date:
> If you have an after trigger on the table that you're inserting into and
> it inserts into another table with a serial column, the lastval call will
> use the wrong sequence.  Now this is also a problem with currval if you
That's a pretty exotic scenario...
Looks like lastval is good  enough - the same applies to Sybase @@Identity.

Using currval actually seems much more dangerous (as my test shows):
Obviously there's more than one man_id_seq in our db, how to find the right
one to call currval on?

I can select from tables (without providing scheme name) - the same should
be right for the sequences.
That means that the sequence defined in my schema is NOT the one that was
created with CREATE TABLE MAN(ID SERIAL...

Then where this (created with the table) sequence is?
Is it in some kind of system scheme?
If so - what would happen if we create the same named tables in two schema -
would then sequences override each other?

Apparently I had a preexisting sequence named man_id_seq in my scheme -> and
that caused postgresql to create a new sequence somewhere else.
As soon as I deleted this sequence my test strated working!

Thanks a lot for your help, it's greatly appreciated,

Andrei

IMO this behaviour (if there's existing sequence create another one with the
same name but in some other place) is very wrong - be predictible, throw
exception.

----- Original Message -----
From: "Kris Jurka" <books@ejurka.com>
To: "Andrei Ilitchev" <andrei.ilitchev@oracle.com>
Cc: <pgsql-jdbc@postgresql.org>; "Marina Vatkina" <Marina.Vatkina@Sun.COM>
Sent: Thursday, October 18, 2007 3:09 PM
Subject: Re: [JDBC] Fw: postgresql experts please help


>
>
> On Thu, 18 Oct 2007, Andrei Ilitchev wrote:
>
>> In the docs I discovered "select lastval()" which seems to behave exactly
>> like @@Identity in Sybase returning the latest nextval that was obtained
>> in the current session by no-matter-which sequence.
>> That's exactly the functionality I need - much easier to use because no
>> need to track the name.
>>
>> Can you think of any reason why using "select lastval()" would be a bad
>> idea?
>>
>
> If you have an after trigger on the table that you're inserting into and
> it inserts into another table with a serial column, the lastval call will
> use the wrong sequence.  Now this is also a problem with currval if you
> have a trigger that inserts into the same table, but that's an unlikely
> application design.  Inserting into another table is something that you'll
> find with systems that do auditing or sometimes horizontal partitioning of
> a table.
>
> Kris Jurka
>


Re: Fw: postgresql experts please help

From
Kris Jurka
Date:

On Thu, 18 Oct 2007, Andrei Ilitchev wrote:

> IMO this behaviour (if there's existing sequence create another one with the
> same name but in some other place) is very wrong - be predictible, throw
> exception.

This is not true, but it can be confusing.  A serial's sequence will
always be put in the same schema as the table it's attached to.  If
another sequence exists with the same name in that schema, it will choose
another name for the serial sequence:

jurka=# create sequence t2_a_seq;
CREATE SEQUENCE
jurka=# create table t2 (a serial);
NOTICE:  CREATE TABLE will create implicit sequence "t2_a_seq1" for serial
column "t2.a"
CREATE TABLE


Notice that it created "t2_a_seq1" to avoid the conflict with the existing
"t2_a_seq" sequence.  If you've got another sequence with the same name in
another schema it does not conflict, but depending on your search_path you
can get them mixed up.  Both of these problems are solved by using
pg_get_serial_sequence, which should perhaps be mentioned more prominently
in the documentation.  There is definitely a use for keeping multiple
tables/sequences with identical names in different schemas and not having
them conflict.  In fact that's the primary use case for having schemas at
all.

Kris Jurka

Re: Fw: postgresql experts please help

From
Oliver Jowett
Date:
Kris Jurka wrote:

> Both of these problems are
> solved by using pg_get_serial_sequence, which should perhaps be
> mentioned more prominently in the documentation.

Alternatively if you really do want a particular sequence name rather
than letting SERIAL pick one for you, you can use an explicit CREATE
SEQUENCE and DEFAULT nextval('whatever') yourself. This is essentially
what SERIAL does under the covers anyway. See
http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SERIAL

-O