Re: [NOVICE] Last ID Problem

Tom Lane Writes:
> 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?

I have a suggestion...

For libpq:

Since OID's are now deprecated, and will eventually disappear,
wouldn't it be a good idea, to have INSERT and UPDATE return
a copy of the tuple that was inserted/updated?

This way, you could have a funtion to fetch an arbitrary named
column from that tuple.
Like: last_insert_value(tuple,'column_name')

... John