Обсуждение: Fw: postgresql experts please help
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
>
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
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)
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
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
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
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.
> 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 >
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
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
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
Kris,
> SELECT currval(pg_get_serial_sequence('mytab','mycol'));
You mean lastval().
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
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
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
> 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 >
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
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