Re: [NOVICE] Last ID Problem

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?

regards, tom lane