Re: ODBC Connection Pooling (Windows 2000 MDAC 2.7 patched, pgodbc-7.02.00.05)

If for no other reason that it is reasonable to assume that currval() would be
undefined at the start of a connection, it should be resetable.


<pseudo-code...>

create temporary table t_tbl (id serial, myvalue text);
NOTICE:  CREATE TABLE will create implicit sequence 't_tbl_id_seq' for SERIAL
column 't_tbl.id'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 't_tbl_id_key' for
table 't_tbl'
CREATE

begin;
update t_tbl set myvalue='newvalue' where id=currval('t_tbl_id_seq');
UPDATE 0
commit;
COMMIT

if recaff = 0 {

  begin;
  BEGIN
  insert into t_tbl (myvalue) values ('newvalue');
  INSERT 253902900 1
  commit;
  COMMIT

}

</pseudo-code>


If sequences are used to keep track of the row last inserted, isn't it critical
to be able to insure the value of currval() doesn't belong to someone else's
connection?

CG


--- Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> Chris Gamache wrote:
> >
> > Doug, thanks for the reply!
> >
> > Bruce, back me up (or shoot me down..): currval() should be undefined when
> a
> > connection is first made for any given sequence. If a connection is
> recycled,
> > shouldn't currval() be undefined for any given sequence to simulate the
> > behavior of a connection first being made?
>
> It would be nice if we wiped out currval when a shared connection is
> passed.  We have added RESET ALL to clear out most SET values, but we
> don't have any code to clear currval values.
>
> Is there a reason the person would be calling currval when they haven't
> called nextval yet?
>
> ---------------------------------------------------------------------------
>
> >
> > CG
> >
> > --- Doug McNaught <doug@mcnaught.org> wrote:
> > > Chris Gamache <cgg007@yahoo.com> writes:
> > >
> > > > We have a problem where the value of currval() transitions from one
> pooled
> > > > connection to another with pgodbc-7.02.00.05. I am wondering if pgodbc
> has
> > > been
> > > > fixed to wipe connection-related variables like currval and nextval
> when a
> > > > pooled connection is recycled. Is there perhaps some setting that I am
> > > missing?
> > >
> > > If this happens, your application code is broken.  You should always
> > > call nextval() before calling currval() on a given connection.  These
> > > are server-side functions (not variables) and the ODBC driver can't
> > > "reset" them.
> > >
> > > -Doug
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 7: don't forget to increase your free space map settings
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! SiteBuilder - Free, easy-to-use web site design software
> > http://sitebuilder.yahoo.com
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com