Re: Re: [JDBC] New backend functions?

-----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(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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-----

Ned Wolpert <ned(dot)wolpert(at)knowledgenet(dot)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

> 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(dot)wolpert(at)knowledgenet(dot)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-----

-----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(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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(at)postgresql(dot)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(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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(at)postgresql(dot)org)
>>
>>

Virtually,
Ned Wolpert <ned(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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(at)postgresql(dot)org)
> >>
> >>
>
>
> Virtually,
> Ned Wolpert <ned(dot)wolpert(at)knowledgenet(dot)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(at)candle(dot)pha(dot)pa(dot)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(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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(at)postgresql(dot)org)
>>>
>>>
>>>
>
>
> Virtually,
> Ned Wolpert <ned(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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

On Thu, 23 Aug 2001 14:44:19 -0400, you wrote:
>Ned Wolpert <ned(dot)wolpert(at)knowledgenet(dot)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(dot)wolpert(at)knowledgenet(dot)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-----

-----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(dot)wolpert(at)knowledgenet(dot)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(at)postgresql(dot)org)

Virtually,
Ned Wolpert <ned(dot)wolpert(at)knowledgenet(dot)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-----

Rene Pijlman <rpijlman(at)wanadoo(dot)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(dot)wolpert(at)knowledgenet(dot)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-----

Tom Lane wrote:
>
> Ned Wolpert <ned(dot)wolpert(at)knowledgenet(dot)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

Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)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(dot)wolpert(at)knowledgenet(dot)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 <ned(dot)wolpert(at)knowledgenet(dot)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:

> 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(at)gmx(dot)net http://funkturm.homeip.net/~peter

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

last_oid() works for me. I have no specific concern on the exact name, just
the functionality. (Call it fred() if you like. :-)

On 24-Aug-2001 Peter Eisentraut wrote:
> 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(at)gmx(dot)net http://funkturm.homeip.net/~peter

Virtually,
Ned Wolpert <ned(dot)wolpert(at)knowledgenet(dot)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

iD8DBQE7hrARiysnOdCML0URAtv/AJ9UmsbpbA4M6VVOYD90LMrV0FelTwCcCo/r
+aMk4VQ11xWYktgbBWCyOM0=
=qQry
-----END PGP SIGNATURE-----

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(at)gmx(dot)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(at)gmx(dot)net http://funkturm.homeip.net/~peter

Virtually,
Ned Wolpert <ned(dot)wolpert(at)knowledgenet(dot)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?
>
>

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

-----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(dot)wolpert(at)knowledgenet(dot)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-----

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