Re: [NOVICE] Last ID Problem

Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > On Tue, Feb 01, 2005 at 12:56:20AM -0500, Tom Lane wrote:
> >> His point stands though: if you are accessing Postgres through some kind
> >> of connection-pooling software, currval() cannot be trusted across
> >> transaction boundaries, since the pool code might give your connection
> >> to someone else. In this situation the nextval-before-insert paradigm
> >> is the only way.
>
> > I don't disagree with that; if the thread mentioned connection
> > pooling then I must have overlooked it.
>
> >> (But in most of the applications I can think of, your uses of currval
> >> subsequent to an INSERT ought to be in the same transaction as the
> >> insert, so are perfectly safe. If your connection pooler takes control
> >> away from you within a transaction block, you need a less broken
> >> pooler...)
>
> > That's the common situation I was talking about: doing an INSERT
> > and immediately calling currval(), presumably in the same transaction.
> > I should have been more clear about that and warned what could
> > happen in other situations. Thanks.
>
> Apropos to all this: Tatsuo recently proposed a RESET CONNECTION command
> that could be used to reset a connection between pooling assignments, so
> as to be sure that different pooled threads wouldn't see state that
> changes depending on what some other thread did. It seems like RESET
> CONNECTION ought to reset all currval() states to the "error, currval
> not called yet" condition. Comments?

TODO update:

* Add RESET CONNECTION command to reset all session state

This would include resetting of all variables (RESET ALL), dropping of
all temporary tables, removal of any NOTIFYs, cursors, prepared
queries(?), currval()s, etc. This could be used for connection pooling.
We could also change RESET ALL to have this functionality.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073