Skip site navigation (1) Skip section navigation (2)

Re: [NOVICE] Last ID Problem

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Michael Fuhr <mike(at)fuhr(dot)org>,Mitch Pirtle <mitch(dot)pirtle(at)gmail(dot)com>,Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>, pgsql-hackers(at)postgresql(dot)org,operationsengineer1(at)yahoo(dot)com, pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Last ID Problem
Date: 2005-02-02 17:26:32
Message-ID: 200502021726.j12HQWK25159@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-novice
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

In response to

pgsql-novice by date

Next:From: Tim GoodaireDate: 2005-02-02 19:58:35
Subject: Re: How to "COPY schema1.table TO schema2.table" ?
Previous:From: AkbarDate: 2005-02-02 16:40:01
Subject: how to know which object depend of it?

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-02-02 17:36:36
Subject: Re: Enhancement suggestion
Previous:From: Dann CorbitDate: 2005-02-02 17:21:09
Subject: Enhancement suggestion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group