Обсуждение: help

Hello;
I couln't provide the connection using ODBC driver. I enabled the
tcpip_socket. What can I do more? Please help me..

On Thursday 28 August 2003 08:47, Burcu Akkan wrote:
> Hello;
> I couln't provide the connection using ODBC driver. I enabled the
> tcpip_socket. What can I do more? Please help me..

1. Make sure you can access PG from the client machine using psql
2. Turn on logging for the ODBC driver, then check the logs
3. Come back to the list with the precise error message

--
  Richard Huxton
  Archonet Ltd

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?

Also, I can't find a definitive changelog describing all the various
improvements between 7.02.00.05 and the current stable version. That makes it
difficult to assess the dangers of upgrading!

CG

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

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

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?

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

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

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

On Thu, 2003-09-04 at 16:29, Chris Gamache wrote:

> 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?

That can't happen.  currval() is _guaranteed_ to return only the last
value generated during your own current session.  That is why you cannot
use it unless you have used nextval() first in the same session.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "If any of you lack wisdom, let him ask of God, who
      gives to all men generously and without reproach, and
      it will be given to him."           James 1:5

That is my point exactly. That is why (windows?) connection pooling breaks this
model. It wouldn't be a problem if there were a way to undefine currval() for
all sequences... It would be a command issued by the ODBC driver when the
connection is made (best), or by the connecting process as its first order of
business (do-able).

CG


--- Oliver Elphick <olly@lfix.co.uk> wrote:
> On Thu, 2003-09-04 at 16:29, Chris Gamache wrote:
>
> > 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?
>
> That can't happen.  currval() is _guaranteed_ to return only the last
> value generated during your own current session.  That is why you cannot
> use it unless you have used nextval() first in the same session.
>
> --
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight, UK                             http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>                  ========================================
>      "If any of you lack wisdom, let him ask of God, who
>       gives to all men generously and without reproach, and
>       it will be given to him."           James 1:5
>


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

Chris Gamache wrote:
>
> That is my point exactly. That is why (windows?) connection
> pooling breaks this model. It wouldn't be a problem if there
> were a way to undefine currval() for all sequences...
> It would be a command issued by the ODBC driver when the
> connection is made (best), or by the connecting process as
> its first order of business (do-able).

How could the driver know if the connection is recycled ?
SQL..Connect API function isn't called when the connection
is recycled.

regards,
Hiroshi Inoue
    http://www.geocities.jp/inocchichichi/psqlodbc/

Rats... In that case, the application creating the connection would have to
issue a command to the back-end before running any other SQL statements.

--- Hiroshi Inoue <Inoue@tpf.co.jp> wrote:
> Chris Gamache wrote:
> >
> > That is my point exactly. That is why (windows?) connection
> > pooling breaks this model. It wouldn't be a problem if there
> > were a way to undefine currval() for all sequences...
> > It would be a command issued by the ODBC driver when the
> > connection is made (best), or by the connecting process as
> > its first order of business (do-able).
>
> How could the driver know if the connection is recycled ?
> SQL..Connect API function isn't called when the connection
> is recycled.
>
> regards,
> Hiroshi Inoue
>     http://www.geocities.jp/inocchichichi/psqlodbc/



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