
>Zeugswetter Andreas writes:
>> I meant: why is a transaction always open in an ecpg program
>
>Because this is how it works with other embedded SQL systems too. I have
>done quite some work with Oracle, and it always has the transaction open.

I am well accustomed to the deficiencies of Oracle. But in Oracle you don't have read locks,
and so a read only program does no harm if it only does one commit when it exits 
(except maybe block the RBS if it did one small update).
Since postgresql does have read locks, such a program will lock all resources as time goes by,
if it does not do frequent commits. Not to speak of memory, that does not get freed.

>>
>>Keep in mind that there is no disconnect command. Instead you go out by
>>issuing a commit.

Hmmm ? you don't tell the backend when the program exits ?

> What I am saying here is, that an ecpg program should be able to run with 
> autocommit mode on.  (Michael Meskes)
>
>I tend to agree. But all embedded SQL programs I've seen so far only use
>commit. I never saw one that issues a begin work since I stopped using
>Ingres.

Try Informix, and you will love the difference and speed in these points.
The begin work statement is also a fundamental part of postgres. I simply would not hide it.

Andreas



Wed, 25 Mar 1998 16:28:25 +-100
Zeugswetter Andreas <andreas.zeugswetter@telecom.at>
AW: [HACKERS] Begin statement again
Zeugswetter Andreas writes:
> I am well accustomed to the deficiencies of Oracle. But in Oracle you don't have read locks,
> and so a read only program does no harm if it only does one commit when it exits 
> (except maybe block the RBS if it did one small update).
> Since postgresql does have read locks, such a program will lock all resources as time goes by,
> if it does not do frequent commits. Not to speak of memory, that does not get freed.

You got a point with this.

> Hmmm ? you don't tell the backend when the program exits ?

So far I don't. Does anyone know whether there's a disconnect command
somewhere? In embedded SQL that is. Oracle uses 'commit work release'.

The function I have to call does exist already.

> Try Informix, and you will love the difference and speed in these points.
> The begin work statement is also a fundamental part of postgres. I simply would not hide it.

I do not hide it all. But I'd like to be as compatible to Oracle as
possible. Maybe we could add an autotransaction flag somehow.

Michael

-- 
Dr. Michael Meskes, Project-Manager    | topsystem Systemhaus GmbH
meskes@topsystem.de                    | Europark A2, Adenauerstr. 20
meskes@debian.org                      | 52146 Wuerselen
Go SF49ers! Go Rhein Fire!             | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux!                  | Fax: (+49) 2405/4670-10

Wed, 25 Mar 1998 16:57:00 +0100 (CET)
Michael Meskes <meskes@topsystem.de>
Re: AW: [HACKERS] Begin statement again

Andreas wrote:
>
>
> >Zeugswetter Andreas writes:
> >> I meant: why is a transaction always open in an ecpg program
> >
> >Because this is how it works with other embedded SQL systems too. I have
> >done quite some work with Oracle, and it always has the transaction open.
>
> I am well accustomed to the deficiencies of Oracle. But in Oracle you don't have read locks,
> and so a read only program does no harm if it only does one commit when it exits
> (except maybe block the RBS if it did one small update).
> Since postgresql does have read locks, such a program will lock all resources as time goes by,
> if it does not do frequent commits. Not to speak of memory, that does not get freed.

    I'm not that familiar with the C level of Oracle connections.
    But I used oratcl from Tom Poindexter sometimes and that  has
    a  AUTOCOMMIT  ON/OFF statement that sets the autocommit flag
    in the library routines somewhere.  Doesn't embedded SQL  use
    the same libraries to connect to oracle that oratcl uses?

    In  oratcl  autocommit is ON by default and I assumed this is
    the libraries default too. Correct me if I'm wrong.

    Anyway - ecpg could work around. It can manage an  autocommit
    flag and an in_trans status by itself. When autocommit is OFF
    and in_trans is false, it sends down  a  'BEGIN  TRANSACTION'
    right  before  the  next  query  and  sets  in_trans to true.
    Later, when PostgreSQL responds 'COMMIT'  from  a  query,  it
    sets  in_trans back to false and we have the behaviour of the
    AUTOCOMMIT.  This way, a program that doesn't explicitly  set
    autocommit to off might sometimes issue a COMMIT that results
    in an empty BEGIN/COMMIT sequence sent down to the backend  -
    not  too  bad  IMHO.  As  soon  as  a  program  requires real
    transactions, it sets autocommit to false and has  (from  the
    embedded   SQL   programmers  point  of  view)  total  Oracle
    compatibility. And as long as autocommit is ON, there are  no
    open  locks  laying  around  since  ecpg  doesn't send 'BEGIN
    TRANSACTION'  and  PostgreSQL's  default  is  somewhat   like
    autocommit too.

>
> >>
> >>Keep in mind that there is no disconnect command. Instead you go out by
> >>issuing a commit.
>
> Hmmm ? you don't tell the backend when the program exits ?

    Isn't EOF information enough? Must a client say BYE?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Wed, 25 Mar 1998 17:33:01 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: AW: [HACKERS] Begin statement again
Jan Wieck writes:
>     I'm not that familiar with the C level of Oracle connections.
>     But I used oratcl from Tom Poindexter sometimes and that  has
>     a  AUTOCOMMIT  ON/OFF statement that sets the autocommit flag
>     in the library routines somewhere.  Doesn't embedded SQL  use
>     the same libraries to connect to oracle that oratcl uses?

Don't know.

>     Anyway - ecpg could work around. It can manage an  autocommit
>     flag and an in_trans status by itself. When autocommit is OFF
>     and in_trans is false, it sends down  a  'BEGIN  TRANSACTION'
>     right  before  the  next  query  and  sets  in_trans to true.
>     Later, when PostgreSQL responds 'COMMIT'  from  a  query,  it
>     sets  in_trans back to false and we have the behaviour of the
>     AUTOCOMMIT.  This way, a program that doesn't explicitly  set
>     autocommit to off might sometimes issue a COMMIT that results
>     in an empty BEGIN/COMMIT sequence sent down to the backend  -
>     not  too  bad  IMHO.  As  soon  as  a  program  requires real

Wait a moment. This is almost as it is handled currently. ecpg issues a
'BEGIN TRANSACTION' before the next statement if commited (as the variable
is called) is set to TRUE. Then it sets commited back to FALSE. Issuing a
COMMIT sets it back to TRUE.

>     transactions, it sets autocommit to false and has  (from  the
>     embedded   SQL   programmers  point  of  view)  total  Oracle
>     compatibility. And as long as autocommit is ON, there are  no

Oracle compatibility means exactly the behaviour we currently have. BEGIN
TRANSACTION is issued automatically. COMMIT has to be called by hand. But
what we were talking about is forcing both to be called by the programmer.

>     open  locks  laying  around  since  ecpg  doesn't send 'BEGIN
>     TRANSACTION'  and  PostgreSQL's  default  is  somewhat   like
>     autocommit too.
> ...
>     Isn't EOF information enough? Must a client say BYE?

No, it need not. But it would be nice if it does, wouldn't it?

Michael
-- 
Dr. Michael Meskes, Project-Manager    | topsystem Systemhaus GmbH
meskes@topsystem.de                    | Europark A2, Adenauerstr. 20
meskes@debian.org                      | 52146 Wuerselen
Go SF49ers! Go Rhein Fire!             | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux!                  | Fax: (+49) 2405/4670-10

Thu, 26 Mar 1998 09:12:20 +0100 (MEZ)
Michael Meskes <meskes@topsystem.de>
Re: AW: [HACKERS] Begin statement again
On Wed, 25 Mar 1998, Michael Meskes wrote:

> Zeugswetter Andreas writes:
> > I am well accustomed to the deficiencies of Oracle. But in Oracle you don't have read locks,
> > and so a read only program does no harm if it only does one commit when it exits 
> > (except maybe block the RBS if it did one small update).
> > Since postgresql does have read locks, such a program will lock all resources as time goes by,
> > if it does not do frequent commits. Not to speak of memory, that does not get freed.
> 
> You got a point with this.
> 
> > Hmmm ? you don't tell the backend when the program exits ?
> 
> So far I don't. Does anyone know whether there's a disconnect command
> somewhere? In embedded SQL that is. Oracle uses 'commit work release'.
> 
The DISCONNECT statement is used to terminate an inactive
SQL-Connection. A SQL-Connection can be closed whether it is the
current SQL-Connection or a dormant SQL-Connection, but may not
closed while a transaction is on-going for its associated
SQL-session.

The required syntax for the DISCONNECT statement is:

DISCONNECT 
      <Connection Name> |
                DEFAULT |
                CURRENT |
                ALL
                                                            Ciao, Jose'


Thu, 26 Mar 1998 14:10:49 +0000 (UTC)
"Jose' Soares Da Silva" <sferac@proxy.bazzanese.com>
Re: AW: [HACKERS] Begin statement again 