Thread: PHP and autocommit of PostgreSQL 7.4

PHP and autocommit of PostgreSQL 7.4

From
Sai Hertz And Control Systems
Date:
Dear all,


Please vote for this
 http://bugs.php.net/26592

So that  in next release of PHP we can set autocommit = off from with in
PHP.ini


Regards,
V Kashyap

Re: [ADMIN] PHP and autocommit of PostgreSQL 7.4

From
Robert Treat
Date:
On Thu, 2003-12-11 at 17:17, Peter Eisentraut wrote:
> Sai Hertz And Control Systems wrote:
> > Please vote for this
> >  http://bugs.php.net/26592
>
> Please don't.  See my comment.
>

given you can override php.ini setting via php commands, ISTM
application developers could still control this even if it was an ini
setting.

how do other languages handle this? IIRC in perl/dbi it is a parameter
of the connection string ie. pg_connect(host=foo,user=bar,autocommit=on)
in php-ish speak, which doesn't seem like a bad way to do it.


(personally i'm down with Scott, application developers should just use
begin/commit, but i've met some folks who really look at it the other
way)


Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


Re: [ADMIN] PHP and autocommit of PostgreSQL 7.4

From
Martin Marques
Date:
El Jue 11 Dic 2003 20:25, Robert Treat escribió:
>
> (personally i'm down with Scott, application developers should just use
> begin/commit, but i've met some folks who really look at it the other
> way)

$db = DB::Connect("pgsql://user:pass@host/db");
$db->autocommit(false);
$db->query(query1);
$db->query(query2);
$db->commit();

Changing the first line would make it work on Oracle, or Informix. :-)

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telemática
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------


Re: [ADMIN] PHP and autocommit of PostgreSQL 7.4

From
Martin Marques
Date:
El Jue 11 Dic 2003 21:36, Martin Marques escribió:
> El Jue 11 Dic 2003 20:25, Robert Treat escribió:
> > (personally i'm down with Scott, application developers should just use
> > begin/commit, but i've met some folks who really look at it the other
> > way)
>
> $db = DB::Connect("pgsql://user:pass@host/db");
> $db->autocommit(false);
> $db->query(query1);
> $db->query(query2);
> $db->commit();

Replying to myself isn't a good thing, but I found something interesting here.

By default PEAR::DB has autocommit set to true (it's defined at the begining
on the class), but with the changes in PG 7.4 the default should be false,
which would make it work right.

Now, the problem that I additionally find is that when autocommit is set to
true there isn't a BEGIN / END before and after the query. This should also
change, unless a new function is added to have autocommit changed in the DB
conection and PEAR::DB uses it to change the autocommit setting.

P.D.: I'm adding the pgsql.php authors to the mail to see that they get a copy

--
 09:21:01 up 16 days, 15:37,  2 users,  load average: 0.61, 0.67, 0.45
-----------------------------------------------------------------
Martín Marqués        | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica  |  DBA, Programador, Administrador
             Universidad Nacional
                  del Litoral
-----------------------------------------------------------------


Re: [ADMIN] PHP and autocommit of PostgreSQL 7.4

From
Peter Eisentraut
Date:
Sai Hertz And Control Systems wrote:
> Please vote for this
>  http://bugs.php.net/26592

Please don't.  See my comment.


Re: [ADMIN] PHP and autocommit of PostgreSQL 7.4

From
Peter Eisentraut
Date:
Robert Treat wrote:
> given you can override php.ini setting via php commands, ISTM
> application developers could still control this even if it was an ini
> setting.

Think about which entity determines what setting is required for
autocommit.  It's not the database administrator, it's not the database
end user, it's not some tuning guy, it's not the binding library --
it's the particular client application.  Because only the client
application knows how it will issue SQL commands and what autocommit
mode it expects for proper functioning.  Therefore, there is never a
legitimate reason to control this setting anywhere else.

> how do other languages handle this? IIRC in perl/dbi it is a
> parameter of the connection string ie.
> pg_connect(host=foo,user=bar,autocommit=on) in php-ish speak, which
> doesn't seem like a bad way to do it.

In JDBC there is a method on the connection that controls this setting,
and the default setting is fixed in the spec (not determined by
external configuration).  In ECPG, autocommit is off.  Those are the
only interfaces I know that have sufficient standardization in this
area.  The Perl way you cite seems reasonable as well.