Re: Disable Transaction - plans ? - Mailing list pgsql-general

Tom Lane wrote:
>
> Mike Mascari <mascarm@mascari.com> writes:
> > So would it be
> > possible to modify PQFinish() to examine whether autocommit = true,
> > and if so, issue a commit before disconnect, else just disconnect?

Sorry, that should have read autocommit = false.

>
> That's just a slightly different way of shooting yourself in the foot.
> What's the difference whether it's libpq or the backend that pulls
> the trigger?  It's still not an explicit decision by the client.
>
> I guess I do not understand the motivation for this proposal.

Its only based upon Oracle precedence, really. People start up
SQL*Plus, issue a few statements, and quit. Some aren't even aware
that they're involved in a transaction; only that they know they can
undo their work by issuing a ROLLBACK and their work won't be saved
if their machine crashes. I haven't used ecpg, but if I wanted to
port Pro*C to PostgreSQL, I would have to modify code to ensure that
my application wasn't issuing an implicit commit after every
statement.

> As I see it, the idea is that the client does not want an autocommit,
> so he sets an option saying "no autocommit, hold transaction open until
> I explicitly commit".  Why exactly would such a client think that an
> autocommit on disconnect is a good idea?  The whole POINT is to require
> an explicit commit command.  (ie, the client wants the gun in his own
> hand, no delegation of the trigger decision, thank you very much)

I guess I was thinking in terms of a postgresql.conf parameter that
affected all backends, that it would be a conscious decision by the
DBA to install an RDBMS which did not autocommit each statement, but
behaved like Oracle. Modifying libpq's PQFinish() would allow a psql
talking to an auto-committing system to behave as it does now, but
would behave like SQL*Plus for the DBA that chose to run an
Oracle-like PostgreSQL installation. A secondary motivation is that
I believe IT departments may choose to test PostgreSQL performance
by submitting a bunch of their Oracle scripts against it and seeing
how long it takes. It would be easier to tell them to set autocommit
= false in postgresql.conf then to tell them to modify all their
scripts. One day, perhaps, autocommit = false would become the
default, and then PostgreSQL's speed, which has been unfairly
characterized by the uninformed, will compare favorably to Oracle
(and others) out-of-the-box. You know an IT department would do
something like this:

"Let's test the speed of PostgreSQL":

rpm -Uvh postgresql*
/etc/rc.d/init.d/postgresql start
su -l postgres
createdb test
date; psql test < my_oracle_timing_test.sql; date

"Boy that PostgreSQL sure is slow!"

rpm -qa | fgrep "postgres" | xargs rpm -e

>
> ISTM people who like autocommit will be using our existing behavior.
>

I'm not married to the idea, BTW ;-)

Mike Mascari
mascarm@mascari.com