Обсуждение: Re: [JDBC] New backend functions? [was Re: JDBC changes for 7.2... some questions...]
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
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 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-----
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: Re: [JDBC] New backend functions? [was Re: JDBC changes for 7.2... some questions...]
От
Hiroshi Inoue
Дата:
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
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 <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 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@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 iD8DBQE7hrARiysnOdCML0URAtv/AJ9UmsbpbA4M6VVOYD90LMrV0FelTwCcCo/r +aMk4VQ11xWYktgbBWCyOM0= =qQry -----END PGP SIGNATURE-----