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(at)debis(dot)com (Jan Wieck) #