Thread: JDBC changes for 7.2... some questions...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Folks-
I'm looking into some modifications for the Castor project on how we get
identies from inserted objects into the database. Specifically, if we have
used the SERIAL type to link in a sequence number during row insertion, it would
be great to get that sequence number when we do the insert. Both Oracle and
mssql have something to provide this. Getting the OID would be second-best
solution for us.
Now, I understand that in the Statement class, we have getInsertedOID() in the
table. However, the problem we run into is that this isn't accessiable if we
use something like poolman to provide database pooling of connections. (You
get the poolMan Statement object which is wraps the Statement classes of the
driver.)
So, what I'm looking for is some of the following ways to get the primary key
back, or the OID if nothing else...
1) Being able to use the RETURNING clause in prepared statements, like this
"INSERT INTO tableName (key1,...)
VALUES (value1,...)
RETURNING primKeyName INTO ?"
Which is what Oracle provides.
2) Working like Sybase and call "select @@OID" on the next call, which would
be trapped by the statment object to return a resultset with the oid of
the last inserted oid.
3) or, someother way that where one doesn't need direct access to method
calls to get 'getInsertedOID()', but indirect ones.
Thoughts?
Virtually,
Ned Wolpert <ned.wolpert@knowledgenet.com>
D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE7gr83iysnOdCML0URAj+QAJ91RZXOoEA+xnu68YhIA4euNfIWOgCfcG1/
5L5ATkdL/wPwTnbQy0NI2jI=
=l/7B
-----END PGP SIGNATURE-----
Ned Wolpert <ned.wolpert@knowledgenet.com> writes:
> 1) Being able to use the RETURNING clause in prepared statements, like this
> "INSERT INTO tableName (key1,...)
> VALUES (value1,...)
> RETURNING primKeyName INTO ?"
> Which is what Oracle provides.
INSERT ... RETURNING was discussed recently, and I think people agreed
it's a good idea, but it got hung up on some unresolved issues about how
it should interact with ON INSERT rules for views. Search the pghackers
mailing list archives for details. At this point I think it's probably
too late to consider it for 7.2, but I'm still open to doing it in 7.3
if we can come up with a bulletproof spec.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 What about the 'select @@last_oid' to make the getInsertedOID() call available even when the driver is wrapped by a pooling manager? How do people feel about this? (I would be happy to contribute to the codebase for this one.) I understand the RETURNING clause is more work... and I would like to help out on that for 7.3. On 21-Aug-2001 Tom Lane wrote: > Ned Wolpert <ned.wolpert@knowledgenet.com> writes: >> 1) Being able to use the RETURNING clause in prepared statements, like >> this >> "INSERT INTO tableName (key1,...) >> VALUES (value1,...) >> RETURNING primKeyName INTO ?" >> Which is what Oracle provides. > > INSERT ... RETURNING was discussed recently, and I think people agreed > it's a good idea, but it got hung up on some unresolved issues about how > it should interact with ON INSERT rules for views. Search the pghackers > mailing list archives for details. At this point I think it's probably > too late to consider it for 7.2, but I'm still open to doing it in 7.3 > if we can come up with a bulletproof spec. > > regards, tom lane Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7gtIoiysnOdCML0URAmgIAJwJVf2BGhFq88bXHY3yni9qzGohegCdHmPf 7Xnb57gfiP2xoMC8x5mIhWU= =p5Bx -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > it should interact with ON INSERT rules for views. Search the pghackers > mailing list archives for details. At this point I think it's probably Ugh... a quick search shows me asking this same thing in May, when the 'big TODO' list was being commented on. Now that I remember, I thought that the RETURNING clause was being implemented. I didn't realize there wasn't agreement on it. I need to pay more attention to what I write. :-) Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7gtSBiysnOdCML0URAtXNAJ4+W3AgpEk5QZM5IKCFFMQ2tGP5UQCeM1Lx TXQw4pL4ew65B1iH+EfmZhk= =Ho0k -----END PGP SIGNATURE-----
Ned Wolpert <ned.wolpert@knowledgenet.com> writes:
> What about the 'select @@last_oid' to make the getInsertedOID() call
> available even when the driver is wrapped by a pooling manager?
> How do people feel about this?
Yech. At least, not with *that* syntax. @@ is a valid operator name
in Postgres.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Ok, so you're not opposed to the idea then, just the syntax. Does anyone oppose having this concept in the JDBC driver? And what syntax is acceptable? Could we just do 'select getInsertedOID()' which would break people who have functions called getInsertedOID() of course... On 21-Aug-2001 Tom Lane wrote: > Ned Wolpert <ned.wolpert@knowledgenet.com> writes: >> What about the 'select @@last_oid' to make the getInsertedOID() call >> available even when the driver is wrapped by a pooling manager? > >> How do people feel about this? > > Yech. At least, not with *that* syntax. @@ is a valid operator name > in Postgres. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7gv5yiysnOdCML0URAq7qAJkBRhAcE9wctn7bUAv7UMwN3n9+nwCeJR4V ymYTw8l3f9WU4V5idFsibAE= =UQ2M -----END PGP SIGNATURE-----
I am assuming that this would be a new function in the server. Therefore this wouldn't be jdbc specific and would be available to all client interfaces. I don't see what this really has to do with JDBC. thanks, --Barry Ned Wolpert wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > Ok, so you're not opposed to the idea then, just the syntax. Does anyone > oppose having this concept in the JDBC driver? And what syntax is acceptable? > Could we just do > 'select getInsertedOID()' > which would break people who have functions called getInsertedOID() of course... > > > > On 21-Aug-2001 Tom Lane wrote: > >>Ned Wolpert <ned.wolpert@knowledgenet.com> writes: >> >>>What about the 'select @@last_oid' to make the getInsertedOID() call >>>available even when the driver is wrapped by a pooling manager? >>> >>>How do people feel about this? >>> >>Yech. At least, not with *that* syntax. @@ is a valid operator name >>in Postgres. >> >> regards, tom lane >> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/users-lounge/docs/faq.html >> > > > Virtually, > Ned Wolpert <ned.wolpert@knowledgenet.com> > > D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.0.6 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > iD8DBQE7gv5yiysnOdCML0URAq7qAJkBRhAcE9wctn7bUAv7UMwN3n9+nwCeJR4V > ymYTw8l3f9WU4V5idFsibAE= > =UQ2M > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Actually, it's not a new function on the server... I'm just trying to find a way to access the getInsertedOID() method in the statement object without having direct access to the statement object. (My use-case is when the JDBC driver is wrapped in a pooling manager, like PoolMan, it wraps all classes.) I wanted to catch the line 'select @@last_oid' which would return a result set with the single entry based on the results of the method call 'getInsertedOID' but some people didn't like the syntax. So, I'm seaking comments to see if there is a better syntax people like, as long as they don't mind the functionality. One option is the 'faking' of the function call on the server, which really isn't a good option in itself, but outside of my original 'catch' line, its all I got. What's your thoughts? Do you see the need for the functionality? Do you have a solution that I need? On 22-Aug-2001 Barry Lind wrote: > I am assuming that this would be a new function in the server. > Therefore this wouldn't be jdbc specific and would be available to all > client interfaces. I don't see what this really has to do with JDBC. > > thanks, > --Barry > > Ned Wolpert wrote: >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> >> Ok, so you're not opposed to the idea then, just the syntax. Does anyone >> oppose having this concept in the JDBC driver? And what syntax is >> acceptable? >> Could we just do >> 'select getInsertedOID()' >> which would break people who have functions called getInsertedOID() of >> course... >> >> >> >> On 21-Aug-2001 Tom Lane wrote: >> >>>Ned Wolpert <ned.wolpert@knowledgenet.com> writes: >>> >>>>What about the 'select @@last_oid' to make the getInsertedOID() call >>>>available even when the driver is wrapped by a pooling manager? >>>> >>>>How do people feel about this? >>>> >>>Yech. At least, not with *that* syntax. @@ is a valid operator name >>>in Postgres. >>> >>> regards, tom lane >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 5: Have you checked our extensive FAQ? >>> >>>http://www.postgresql.org/users-lounge/docs/faq.html >>> >> >> >> Virtually, >> Ned Wolpert <ned.wolpert@knowledgenet.com> >> >> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 >> -----BEGIN PGP SIGNATURE----- >> Version: GnuPG v1.0.6 (GNU/Linux) >> Comment: For info see http://www.gnupg.org >> >> iD8DBQE7gv5yiysnOdCML0URAq7qAJkBRhAcE9wctn7bUAv7UMwN3n9+nwCeJR4V >> ymYTw8l3f9WU4V5idFsibAE= >> =UQ2M >> -----END PGP SIGNATURE----- >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> >> Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7g+agiysnOdCML0URAvbvAJ9GO/spmwQYZessjk4IenhtPuguSwCdHRQN xH+tnGqKpmg/UOSnxOevek0= =pcr+ -----END PGP SIGNATURE-----
> > What's your thoughts? Do you see the need for the functionality? Do you have > a solution that I need? Definitely need the functionality. It's one of the things holding up me porting an Informix system. Laziness is a bigger holdup of course - the Informix system is so bulletproof and I'm slowly re-writing all the 4GL in Java. FWIW, Informix returns the new SERIAL value through a structure SQLCA.SQLERRD[3] from memory. Not applicable to a PostgreSQL solution I'd say. > > On 22-Aug-2001 Barry Lind wrote: > > I am assuming that this would be a new function in the server. > > Therefore this wouldn't be jdbc specific and would be available to all > > client interfaces. I don't see what this really has to do with JDBC. > > > > thanks, > > --Barry > > > > Ned Wolpert wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- > >> Hash: SHA1 > >> > >> > >> Ok, so you're not opposed to the idea then, just the syntax. Does anyone > >> oppose having this concept in the JDBC driver? And what syntax is > >> acceptable? > >> Could we just do > >> 'select getInsertedOID()' > >> which would break people who have functions called getInsertedOID() of > >> course... > >> > >> > >> > >> On 21-Aug-2001 Tom Lane wrote: > >> > >>>Ned Wolpert <ned.wolpert@knowledgenet.com> writes: > >>> > >>>>What about the 'select @@last_oid' to make the getInsertedOID() call > >>>>available even when the driver is wrapped by a pooling manager? > >>>> > >>>>How do people feel about this? > >>>> > >>>Yech. At least, not with *that* syntax. @@ is a valid operator name > >>>in Postgres. > >>> > >>> regards, tom lane > >>> > >>>---------------------------(end of broadcast)--------------------------- > >>>TIP 5: Have you checked our extensive FAQ? > >>> > >>>http://www.postgresql.org/users-lounge/docs/faq.html > >>> > >> > >> > >> Virtually, > >> Ned Wolpert <ned.wolpert@knowledgenet.com> > >> > >> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 > >> -----BEGIN PGP SIGNATURE----- > >> Version: GnuPG v1.0.6 (GNU/Linux) > >> Comment: For info see http://www.gnupg.org > >> > >> iD8DBQE7gv5yiysnOdCML0URAq7qAJkBRhAcE9wctn7bUAv7UMwN3n9+nwCeJR4V > >> ymYTw8l3f9WU4V5idFsibAE= > >> =UQ2M > >> -----END PGP SIGNATURE----- > >> > >> ---------------------------(end of broadcast)--------------------------- > >> TIP 2: you can get off all lists at once with the unregister command > >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >> > >> > > > Virtually, > Ned Wolpert <ned.wolpert@knowledgenet.com> > > D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.0.6 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > iD8DBQE7g+agiysnOdCML0URAvbvAJ9GO/spmwQYZessjk4IenhtPuguSwCdHRQN > xH+tnGqKpmg/UOSnxOevek0= > =pcr+ > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
> > > > What's your thoughts? Do you see the need for the functionality? Do you have > > a solution that I need? > > Definitely need the functionality. It's one of the things holding up me > porting an Informix system. Laziness is a bigger holdup of course - the > Informix system is so bulletproof and I'm slowly re-writing all the 4GL in > Java. > > FWIW, Informix returns the new SERIAL value through a structure > SQLCA.SQLERRD[3] from memory. Not applicable to a PostgreSQL solution I'd > say. If I remember correctly, that is the ANSI standard embedded C way to return such values. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Ned, I would only agree to this functionality if it where a backend function. By putting it in the front end, you now need to front end to understand the special function. (And then you we are likely going to have requests that this special function be available in all the front ends JDBC, ODBC, perl, etc.). For the front end to understand the function it needs to parse the SQL statement. Thus under your proposal every select statement needs to be parsed to see if one of the selected items is the special function. I strive to ensure that the jdbc code does not need to parse the SQL statements and understand the grammer of the SQL language. Since functions can appear in select lists, where clauses, orderbys, and even insert and update statements, you quickly end up with the client needing to reimplement the entire parser that is already in the backend. You could argue that this really could be special cased (i.e. it must be exactly 'select getInsertedOID()' case and whitespace makes a difference, but then all you really have is a kludge for a specific problem, not a framework to solve other similar problems. I would argue that a framework does exist to solve this and other problems and that framework is to add additional functions into the backend. Given that the JDBC driver already does provide the information via the getLastOID() method, we are really dealing with a small isolated problem here because of the use of a connection pool that doesn't let you get at that method. (Unfortunatly for you, it is the problem you are facing). In general I don't think the benefits of this feature (i.e. providing information that is currently available, except when using certain 3rd party connection pooling mechanisms) are worth the long term costs. If the function was implemented in the backend, I think it would be a good idea. thanks, --Barry Ned Wolpert wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Actually, it's not a new function on the server... I'm just trying to find a > way to access the getInsertedOID() method in the statement object without > having direct access to the statement object. (My use-case is when the JDBC > driver is wrapped in a pooling manager, like PoolMan, it wraps all classes.) > > I wanted to catch the line 'select @@last_oid' which would return a result set > with the single entry based on the results of the method call 'getInsertedOID' > but some people didn't like the syntax. So, I'm seaking comments to see if > there is a better syntax people like, as long as they don't mind the > functionality. One option is the 'faking' of the function call on the server, > which really isn't a good option in itself, but outside of my original 'catch' > line, its all I got. > > What's your thoughts? Do you see the need for the functionality? Do you have > a solution that I need? > > On 22-Aug-2001 Barry Lind wrote: > >>I am assuming that this would be a new function in the server. >>Therefore this wouldn't be jdbc specific and would be available to all >>client interfaces. I don't see what this really has to do with JDBC. >> >>thanks, >>--Barry >> >>Ned Wolpert wrote: >> >>>-----BEGIN PGP SIGNED MESSAGE----- >>>Hash: SHA1 >>> >>> >>>Ok, so you're not opposed to the idea then, just the syntax. Does anyone >>>oppose having this concept in the JDBC driver? And what syntax is >>>acceptable? >>>Could we just do >>>'select getInsertedOID()' >>>which would break people who have functions called getInsertedOID() of >>>course... >>> >>> >>> >>>On 21-Aug-2001 Tom Lane wrote: >>> >>> >>>>Ned Wolpert <ned.wolpert@knowledgenet.com> writes: >>>> >>>> >>>>>What about the 'select @@last_oid' to make the getInsertedOID() call >>>>>available even when the driver is wrapped by a pooling manager? >>>>> >>>>>How do people feel about this? >>>>> >>>>> >>>>Yech. At least, not with *that* syntax. @@ is a valid operator name >>>>in Postgres. >>>> >>>> regards, tom lane >>>> >>>>---------------------------(end of broadcast)--------------------------- >>>>TIP 5: Have you checked our extensive FAQ? >>>> >>>>http://www.postgresql.org/users-lounge/docs/faq.html >>>> >>>> >>> >>>Virtually, >>>Ned Wolpert <ned.wolpert@knowledgenet.com> >>> >>>D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 >>>-----BEGIN PGP SIGNATURE----- >>>Version: GnuPG v1.0.6 (GNU/Linux) >>>Comment: For info see http://www.gnupg.org >>> >>>iD8DBQE7gv5yiysnOdCML0URAq7qAJkBRhAcE9wctn7bUAv7UMwN3n9+nwCeJR4V >>>ymYTw8l3f9WU4V5idFsibAE= >>>=UQ2M >>>-----END PGP SIGNATURE----- >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 2: you can get off all lists at once with the unregister command >>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >>> >>> >>> > > > Virtually, > Ned Wolpert <ned.wolpert@knowledgenet.com> > > D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.0.6 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > iD8DBQE7g+agiysnOdCML0URAvbvAJ9GO/spmwQYZessjk4IenhtPuguSwCdHRQN > xH+tnGqKpmg/UOSnxOevek0= > =pcr+ > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 (For those unfamiliar with the topic, looking for a way to get the last inserted OID through a sql call, rather than a method call off the JDBC driver) On 23-Aug-2001 Barry Lind wrote: > I would only agree to this functionality if it where a backend function. For me, the best method to deal with this problem is implement the returning clause on the backend... but isn't an option in the short term.... > For the front end to understand the function it needs to parse the SQL > statement. Thus under your proposal every select statement needs to be > parsed to see if one of the selected items is the special function. I > strive to ensure that the jdbc code does not need to parse the SQL > statements and understand the grammer of the SQL language. Since > functions can appear in select lists, where clauses, orderbys, and even This is a problem, I agree. In short, supporting 'select @@last_oid' (my original syntax) is not a framework within itself, but a short-term "kludge" as you mentioned. But what are the options that should be pursued? I want to solve this one way or another. (And willing to work on an acceptable solution.) > Given that the JDBC driver already does provide the information via the > getLastOID() method, we are really dealing with a small isolated > problem here because of the use of a connection pool that doesn't let > you get at that method. (Unfortunatly for you, it is the problem you > are facing). Well, it's not really that isolated. The method call 'getLastOID()' isn't in the backend either. That's the problem. This method provides functionality which is very useful, but not in a uniformally applied way where each driver can benefit either. > If the function was implemented in the backend, I think it would be a > good idea. Perhaps this is the solution after all. (And the reason I forward this to the pghackers list as well) Should the backend support the function getLastInsertedOID() or even getLastInsertedPrimaryKey() (or both)? Now, I can try to write the functions, and see if it can be separated into the contrib section of the psql repository if people would like. Does this work for you? (And anyone else reading this) Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7hUCLiysnOdCML0URArdAAJ4kI4S00AVzGgazsGS5nTMu+0X8CwCeOLQ8 TVTTzaQdEt6uJrbVAm0Dd4s= =U3aZ -----END PGP SIGNATURE-----
Ned Wolpert <ned.wolpert@knowledgenet.com> writes:
> Should the backend support the function getLastInsertedOID() or even
> getLastInsertedPrimaryKey() (or both)?
I don't think you have any chance of doing the latter --- for one thing,
how are you going to declare that function's return type? But the
former seems doable and reasonable to me: whenever an OID is returned
to the client in an INSERT or UPDATE command result, also stash it in
a static variable that can be picked up by this function.
Please pick a more SQL-friendly (ie, case insensitive) naming
convention, though. And note that it'd apply to both INSERT and UPDATE.
Maybe get_last_returned_oid() ?
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I like your function name, get_last_returned_oid(). That works for me. On 23-Aug-2001 Tom Lane wrote: > Ned Wolpert <ned.wolpert@knowledgenet.com> writes: >> Should the backend support the function getLastInsertedOID() or even >> getLastInsertedPrimaryKey() (or both)? > > I don't think you have any chance of doing the latter --- for one thing, > how are you going to declare that function's return type? But the > former seems doable and reasonable to me: whenever an OID is returned > to the client in an INSERT or UPDATE command result, also stash it in > a static variable that can be picked up by this function. > > Please pick a more SQL-friendly (ie, case insensitive) naming > convention, though. And note that it'd apply to both INSERT and UPDATE. > Maybe get_last_returned_oid() ? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7hWGbiysnOdCML0URAkqAAJ9Liv8VS+CPMYozG1q1tuy7vGLuEACfUJRM Hdbns8MxyOVgurx5ztV8YZU= =BbF3 -----END PGP SIGNATURE-----
Re: [HACKERS] Re: New backend functions? [was Re: JDBC changes for 7.2... some questions...]
From
Rene Pijlman
Date:
On Thu, 23 Aug 2001 14:44:19 -0400, you wrote: >Ned Wolpert <ned.wolpert@knowledgenet.com> writes: >> Should the backend support the function getLastInsertedOID()? > >seems doable and reasonable to me: whenever an OID is returned >to the client in an INSERT or UPDATE command result, also stash it in >a static variable that can be picked up by this function. What should the semantics be exactly? How about the multiple INSERT's i've been reading about on hackers? ... Only the OID of the last row inserted by the statement? How about an UPDATE statement that updates multiple rows? How about JDBC batchExecute() when it performs multiple INSERT/UPDATE's? ... Only the OID of the last UPDATE or INSERT statement in the batch? How about triggers that insert/update extra rows? ... Only the OID of the row directly inserted by the client statement? How about Large Objects? Should inserting or updating a large object affect getLastInsertedOID()? I assume this OID would be associated with a client connection. Is this going to work with client side connection pooling? How about transaction semantics? INSERT row 1, Commit, INSERT row 2, Rollback... what should getLastInsertedOID() return? Can it, with a static variable? Regards, René Pijlman
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 23-Aug-2001 Rene Pijlman wrote: > What should the semantics be exactly? > > How about the multiple INSERT's i've been reading about on > hackers? ... Only the OID of the last row inserted by the > statement? > > How about an UPDATE statement that updates multiple rows? Well, here's my thoughts on this... The functionality would be that the very last inserted or updated OID would be stored in this static variable that is associated with the connection/session. So, in multiple inserts or updates, it is the last oid affect where this variable would be updated. > How about triggers that insert/update extra rows? ... Only the > OID of the row directly inserted by the client statement? It would be the last updated request caused by any insert or update, regardless of if its a trigger, preparedStatement, etc. > How about Large Objects? Should inserting or updating a large > object affect getLastInsertedOID()? Yes. > I assume this OID would be associated with a client connection. > Is this going to work with client side connection pooling? It must... that's the reason for this. Specifically, the JDBC driver has a method in it that is called getInsertedOID() which provides the last sucessfully inserted row's OID. This is specific to the driver, and JDBC pooling techniques do not allow access to this method. (It's not part of the JDBC spec) So, to make this data accessable to the users in a pooling condition, the call "select getLastOID()" needs to return the OID that is specific to the session. In Java, pooling techniques generally are aquired, then released, as dependant on the client or timeout procedures, and not randomly used for individual queries. (Mostly because of the need for the same driver during a transaction that takes multiple queries.) > How about transaction semantics? INSERT row 1, Commit, INSERT > row 2, Rollback... what should getLastInsertedOID() return? Can > it, with a static variable? Good question. I'd start with rollback not affecting this value. Reason being that this function would be mostly used in a transaction anyways. I would not object to making this method only available during a transaction block if that helps. Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7hWEwiysnOdCML0URAk3xAJ92nYoy22mP4Yk8xk53vojlF42w5gCfbnZf uexoQ9yqexctXvQM0yx+g2Y= =yK6n -----END PGP SIGNATURE-----
Re: [HACKERS] Re: New backend functions? [was Re: JDBC changes for 7.2... some questions...]
From
Tom Lane
Date:
Rene Pijlman <rpijlman@wanadoo.nl> writes:
> On Thu, 23 Aug 2001 14:44:19 -0400, you wrote:
>> seems doable and reasonable to me: whenever an OID is returned
>> to the client in an INSERT or UPDATE command result, also stash it in
>> a static variable that can be picked up by this function.
> What should the semantics be exactly?
Just the same as the command result string.
> How about the multiple INSERT's i've been reading about on
> hackers? ... Only the OID of the last row inserted by the
> statement?
No OID is returned when multiple rows are inserted or updated. I'd say
that should be the semantics of this function, too.
> How about JDBC batchExecute() when it performs multiple
> INSERT/UPDATE's?
By definition, this is a backend function. It cannot know anything of
JDBC.
> I assume this OID would be associated with a client connection.
> Is this going to work with client side connection pooling?
Good point. Will this really get around the original poster's problem??
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 23-Aug-2001 Tom Lane wrote: >> I assume this OID would be associated with a client connection. >> Is this going to work with client side connection pooling? > > Good point. Will this really get around the original poster's problem?? It must. If transactions are on, any pooling mechanism needs to continue to use that connection for the client unti the transaction is done. (Most require the client to either tell the pool manager the connection is no longer need, via a close() call, or a pool-manager specific call, precisely because the client needs it to complete the transaction. My feeling is that if this is a problem, then this method call may need to be limited to the transaction context, but I hope that this is not the case. Most pool managers (and I'm only speaking about Java here) require some activity on the client to give up the connection, either directly or indirectly. Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7hYNRiysnOdCML0URAre3AJ94x/4mfeaJX3IQjRtyTWafeaR/BgCeIB4V liQyRjblBSuX38R0kq+NvVw= =ltfC -----END PGP SIGNATURE-----
Re: [HACKERS] Re: New backend functions? [was Re: JDBC changes for 7.2... some questions...]
From
Hiroshi Inoue
Date:
Tom Lane wrote: > > Ned Wolpert <ned.wolpert@knowledgenet.com> writes: > > Should the backend support the function getLastInsertedOID() or even > > getLastInsertedPrimaryKey() (or both)? > > I don't think you have any chance of doing the latter --- for one thing, > how are you going to declare that function's return type? But the > former seems doable and reasonable to me: whenever an OID is returned Hmm OIDs would be optional in 7.2. Is it known(announced) to pgsql-jdbc list ? regards, Hiroshi Inoue
Re: [HACKERS] Re: New backend functions? [was Re: JDBC changes for 7.2... some questions...]
From
Tom Lane
Date:
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Hmm OIDs would be optional in 7.2.
> Is it known(announced) to pgsql-jdbc list ?
Doesn't seem particularly relevant to this issue though. An application
that's using OIDs to identify rows would certainly not choose to create
its tables without OIDs.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Sounds like there aren't objections to my requested function,
get_last_returned_oid(). I'm going to work on the function call for
postgres this weekend.
Purpose: Retain the last oid returned (if any) in a variable
associated with the connection on the backend, so the next request to
the backend has access to it. It will be set when an insert or update
is completed, so preparedStatements and stored procedures can use it.
If I'm able to provide patches by Monday, and if it works fine (without causing
general meltdowns :-) would this be able to be in the 7.2 beta, or will it
need to be part of 7.3? (The answer to this question will help me decide on
how much time I should spend on it this weekend.)
> On 23-Aug-2001 Tom Lane wrote:
>>> I assume this OID would be associated with a client connection.
>>> Is this going to work with client side connection pooling?
>>
>> Good point. Will this really get around the original poster's problem??
>
> It must. If transactions are on, any pooling mechanism needs to continue to
> use that connection for the client unti the transaction is done. (Most
> require
> the client to either tell the pool manager the connection is no longer need,
> via a close() call, or a pool-manager specific call, precisely because the
> client needs it to complete the transaction.
>
> My feeling is that if this is a problem, then this method call may need to be
> limited to the transaction context, but I hope that this is not the case.
> Most pool managers (and I'm only speaking about Java here) require some
> activity on the client to give up the connection, either directly or
> indirectly.
Virtually,
Ned Wolpert <ned.wolpert@knowledgenet.com>
D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE7hpkHiysnOdCML0URAhPnAJ9z/aWCR88kk60WmZJRalusOYm78ACeLPl7
jRlgOPLcuPd7JCsJy5JomUA=
=ruJB
-----END PGP SIGNATURE-----
Ned Wolpert writes: > Now, I understand that in the Statement class, we have getInsertedOID() in the > table. However, the problem we run into is that this isn't accessiable if we > use something like poolman to provide database pooling of connections. (You > get the poolMan Statement object which is wraps the Statement classes of the > driver.) I think no one has asked yet *why* it isn't "accessible". Maybe the getInsertedOID function needs to be moved to some other class? -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Ned Wolpert <ned.wolpert@knowledgenet.com> writes:
> If I'm able to provide patches by Monday, and if it works fine (without causing
> general meltdowns :-) would this be able to be in the 7.2 beta, or will it
> need to be part of 7.3?
7.2 is still wide open; in fact I'm working on major restructuring of
pg_log that I have every intention of committing before 7.2 beta ...
regards, tom lane
Ned Wolpert writes: > Sounds like there aren't objections to my requested function, > get_last_returned_oid(). I'm going to work on the function call for > postgres this weekend. Please don't name functions get_*. All functions "get" something. Btw., if you call get_last_returned_oid() and then call it again, do you get the "last returned oid" returned by get_last_returned_oid? The "returned" needs to be revised. last_oid should be sufficient. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 It's not accessiable since its not in the JDBC spec. Specifically, when you use PoolMan (www.codestudio.com) as the pooling manager, PoolMan has its own JDBC classes that wraps any JDBC compliant driver. Since this method is specific to PostgreSQL, it's not in the standard JDBC classes. Most JDBC poolers wrap classes similarly. (Well, except oracle's pool manager. They provide their own pooling manager.) In any case, I think this functionality is useful beyond just JDBC. On 24-Aug-2001 Peter Eisentraut wrote: > Ned Wolpert writes: > >> Now, I understand that in the Statement class, we have getInsertedOID() in >> the >> table. However, the problem we run into is that this isn't accessiable if >> we >> use something like poolman to provide database pooling of connections. (You >> get the poolMan Statement object which is wraps the Statement classes of the >> driver.) > > I think no one has asked yet *why* it isn't "accessible". > > Maybe the getInsertedOID function needs to be moved to some other class? > > -- > Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7hrDTiysnOdCML0URAj93AJ9fPjtXEuVj6Wvc6bbOp/eEV6EdtACdEqe+ /MCOd239rhEXfb4j1yajuoQ= =nem4 -----END PGP SIGNATURE-----
The reason why it isn't accessible is that some implementations of connection pools (including one I have written), return a wrapper object around the connection object. This wrapper object just implements the java.sql.Connection interface defined by the jdbc spec. If the wrapped connection (an org.postgresql.Connection object in this case) has extra methods (such as getInsertedOID()) there is no way to access those extra methods as the wrapper does not contain them. If you were using the postgres connection object directly you would simply cast the object to an org.postgresql.Connection and then you would be able to access the extra methods. But you can't in this case because you are dealing with a wrapper object (i.e. something like com.foo.connectionpool.Connection). thanks, --Barry Peter Eisentraut wrote: > Ned Wolpert writes: > > >> Now, I understand that in the Statement class, we have getInsertedOID() in the >>table. However, the problem we run into is that this isn't accessiable if we >>use something like poolman to provide database pooling of connections. (You >>get the poolMan Statement object which is wraps the Statement classes of the >>driver.) >> > > I think no one has asked yet *why* it isn't "accessible". > > Maybe the getInsertedOID function needs to be moved to some other class? > >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Yes. Every class is wrapped; that is how most connection pooling managers in Java tend to work. Specifically, you load PoolMan directly (via DriverManager) or indirectly (JNDI) and it reads its configuration properties to figure out what classes to load. You only have access to PoolMan's classes, which are a proxy to the PostgreSQL JDBC classes. (Or which-ever JDBC classes that are in use. PoolMan works with and JDBC-compliant driver and datasource.) On 24-Aug-2001 Rene Pijlman wrote: > Do they also wrap Connection? Since the last oid is associated > with a client connection to the backend, it might be a solution > to add a getLastOID() method to Connection. > > Regards, > Ren� Pijlman Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7htPviysnOdCML0URApzMAKCAp5PpDRXkQVRgkin46+QvibJFogCdFQsl sFesIHynhct9C+CEQSK6WZs= =wnEQ -----END PGP SIGNATURE-----
On Fri, 24 Aug 2001 12:53:55 -0700 (MST), you wrote: >It's not accessiable since its not in the JDBC spec. Specifically, when you >use PoolMan (www.codestudio.com) as the pooling manager, PoolMan has its own >JDBC classes that wraps any JDBC compliant driver. Do they also wrap Connection? Since the last oid is associated with a client connection to the backend, it might be a solution to add a getLastOID() method to Connection. Regards, René Pijlman
At 03:02 PM 8/24/2001, Rene Pijlman wrote: >Do they also wrap Connection? Since the last oid is associated >with a client connection to the backend, it might be a solution >to add a getLastOID() method to Connection. PoolMan wraps Connection, Statement, PreparedStatement, CallableStatement and ResultSet with their own "smart" versions. The wrappers do not provide accessors to the underlying objects. Note that I'm not using PoolMan. I evaluated it several months ago and decided to just write a *very* simple ConnectionPool for the time being. Dave Harkness
It's been mentioned before, but a set of error numbers for database errors would make trapping exceptions and dealing with them gracefully a LOT simpler. I have java code that runs against Oracle, Informix, PostgreSQL, MS SQL Server and Cloudscape. All(?) the others have an error code as well as an error message and it's a lot easier to get the error code. Of course, they all have *different* error codes for the same error (ie primary key violation). Nothing is ever simple. Peter Wiley
This is on the TODO list for the backend. As a post a couple of days ago stated, when the backend has error code support, it will also be added to JDBC. Until then.... thanks, --Barry Peter Wiley wrote: > It's been mentioned before, but a set of error numbers for database errors > would make trapping exceptions and dealing with them gracefully a LOT > simpler. I have java code that runs against Oracle, Informix, PostgreSQL, > MS SQL Server and Cloudscape. All(?) the others have an error code as well > as an error message and it's a lot easier to get the error code. > > Of course, they all have *different* error codes for the same error (ie > primary key violation). Nothing is ever simple. > > Peter Wiley > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > >
On Mon, 27 Aug 2001 08:48:52 +1000 (EST), you wrote: >It's been mentioned before, but a set of error numbers for database errors >would make trapping exceptions and dealing with them gracefully a LOT >simpler. I have java code that runs against Oracle, Informix, PostgreSQL, >MS SQL Server and Cloudscape. All(?) the others have an error code as well >as an error message and it's a lot easier to get the error code. I agree. Its on the list on http://lab.applinet.nl/postgresql-jdbc/#SQLException. This requires new functionality in the backend. >Of course, they all have *different* error codes for the same error (ie >primary key violation). Nothing is ever simple. Perhaps the SQLState string in SQLException can make this easier (if we can support this with PostgreSQL). This is supposed to contain a string identifying the exception, following the Open Group SQLState conventions. I'm not sure how useful these are. Regards, René Pijlman