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(at)candle(dot)pha(dot)pa(dot)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(at)mcnaught(dot)org> wrote:
> > > Chris Gamache <cgg007(at)yahoo(dot)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(at)postgresql(dot)org
> >
>
> --
> 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

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