ruby-dbi-devel

[Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Sean C. <se...@ch...>]

I've got a pretty interesting problem in that I'm dynamically creating
a database using Ruby.  I've got something of a problem though with
the way that transactions are handled with the execute() methods in
that they always prepend BEGIN WORK; if you're outside of a
transaction.  There are a couple of workarounds for this, but none of
them elegant or correct, IMHO.

*) What I'm doing now:

   dbh.do('ABORT; #{sql}; COMMIT; BEGIN;')

   It's super ugly, but it works insofar as it keeps the database in
   sync with DBI.

*) A possible hack would be to add :in_transaction as an accessor:

   # make sure you're currently outside of a transaction
   dbh.in_transaction = true
   dbh.do(sql)
   dbh.in_transaction = false

*) Another option would be to create something parallel to #do, only
 have it skip the write-through transaction bits.

 dbh.do_exec(sql)

*) The most elegant and likely correct solution would be to add
 another options tunable that would let #do omit adding a 'BEGIN
 WORK;' to every command.

 dbh['AutoBegin'] = false
 dbh.do(sql)
 dbh.commit

I'm personally a fan of the last option as it'd give the developer
fine grained control over when a transaction should begin or end and
would let people execute administrative SQL commands that can't be
executed inside of a transaction (create/drop database, etc).  Anyone
else have an opinion on this?  -sc

-- 
Sean Chittenden

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Michael N. <uu...@rz...>]

Sean Chittenden wrote:
> I've got a pretty interesting problem in that I'm dynamically creating
> a database using Ruby.  I've got something of a problem though with
> the way that transactions are handled with the execute() methods in
> that they always prepend BEGIN WORK; if you're outside of a
> transaction.  There are a couple of workarounds for this, but none of
> them elegant or correct, IMHO.
> 
> *) What I'm doing now:
> 
>    dbh.do('ABORT; #{sql}; COMMIT; BEGIN;')
> 
>    It's super ugly, but it works insofar as it keeps the database in
>    sync with DBI.
> 
> *) A possible hack would be to add :in_transaction as an accessor:
> 
>    # make sure you're currently outside of a transaction
>    dbh.in_transaction = true
>    dbh.do(sql)
>    dbh.in_transaction = false
> 
> *) Another option would be to create something parallel to #do, only
>  have it skip the write-through transaction bits.
> 
>  dbh.do_exec(sql)
> 
> *) The most elegant and likely correct solution would be to add
>  another options tunable that would let #do omit adding a 'BEGIN
>  WORK;' to every command.
> 
>  dbh['AutoBegin'] = false
>  dbh.do(sql)
>  dbh.commit

Have you tried the following? 

  dbh['AutoCommit'] = false

If this was set to false, no commit should be issued automatically!

You can also disable AutoCommit from the beginning on:

 dbh = DBI.connect("dbi:xxxx", "user", "pw", "AutoCommit" => false)


Hope this helps!


Regards,

  Michael

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Sean C. <se...@ch...>]

> Have you tried the following?=20
>=20
>   dbh['AutoCommit'] =3D false
>=20
> If this was set to false, no commit should be issued automatically!

Ehh...  yes it does.

#<DBI::DatabaseHandle:0x81cf860 @trace_mode=3D2, @handle=3D#<DBI::DBD::Pg::=
Database:0x81cf838 @coerce=3D#<DBI::DBD::Pg::PgCoerce:0x81cf52c>, @connecti=
on=3D#<PGconn:0x81cf568>, @in_transaction=3Dfalse, @type_map=3D{2205=3D>:as=
_str, 1083=3D>:as_str, 27=3D>:as_str, 16=3D>:as_bool, 1562=3D>:as_str, 869=
=3D>:as_str, 704=3D>:as_str, 22=3D>:as_str, 0=3D>:as_str, 2206=3D>:as_str, =
1700=3D>:as_str, 28=3D>:as_str, 17=3D>:as_bytea, 600=3D>:as_str, 1266=3D>:a=
s_str, 650=3D>:as_str, 23=3D>:as_int, 628=3D>:as_str, 1184=3D>:as_str, 700=
=3D>:as_float, 601=3D>:as_str, 29=3D>:as_str, 18=3D>:as_str, 2202=3D>:as_st=
r, 24=3D>:as_str, 1790=3D>:as_str, 602=3D>:as_str, 1042=3D>:as_str, 30=3D>:=
as_str, 19=3D>:as_str, 701=3D>:as_float, 2203=3D>:as_str, 1114=3D>:as_times=
tamp, 25=3D>:as_str, 718=3D>:as_str, 1560=3D>:as_str, 1186=3D>:as_str, 702=
=3D>:as_str, 603=3D>:as_str, 20=3D>:as_int, 1043=3D>:as_str, 790=3D>:as_str=
, 2204=3D>:as_str, 1082=3D>:as_date, 829=3D>:as_str, 26=3D>:as_str, 1033=3D=
>:as_str, 604=3D>:as_str, 703=3D>:as_str, 21=3D>:as_int}, @debug_level=3D0,=
 @autocommit=3Dfalse, @attr=3D{"AutoCommit"=3D>false}>, @trace_output=3D#<I=
O:0x8073218>>

And in the error log:

2002-09-18 01:33:54 LOG:  query: select getdatabaseencoding()
2002-09-18 01:33:54 LOG:  query: SELECT typname, typelem FROM pg_type
2002-09-18 01:33:54 LOG:  query: SELECT datname FROM pg_database WHERE datn=
ame =3D 'foo'
2002-09-18 01:33:54 LOG:  query: BEGIN WORK
2002-09-18 01:33:54 LOG:  query: DROP DATABASE foo
2002-09-18 01:33:54 ERROR:  DROP DATABASE: may not be called in a transacti=
on block
2002-09-18 01:33:54 LOG:  statement: DROP DATABASE foo

The logic in lib/dbd_pg/Pg.rb around line 459 seems broken to me.
Thoughts?  -sc


--=20
Sean Chittenden

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Michael N. <uu...@rz...>]

Sean Chittenden wrote:
> > Have you tried the following? 
> > 
> >   dbh['AutoCommit'] = false
> > 
> > If this was set to false, no commit should be issued automatically!
> 
> Ehh...  yes it does.
> 
> And in the error log:
> 
> 2002-09-18 01:33:54 LOG:  query: select getdatabaseencoding()
> 2002-09-18 01:33:54 LOG:  query: SELECT typname, typelem FROM pg_type
> 2002-09-18 01:33:54 LOG:  query: SELECT datname FROM pg_database WHERE datname = 'foo'
> 2002-09-18 01:33:54 LOG:  query: BEGIN WORK
> 2002-09-18 01:33:54 LOG:  query: DROP DATABASE foo
> 2002-09-18 01:33:54 ERROR:  DROP DATABASE: may not be called in a transaction block
> 2002-09-18 01:33:54 LOG:  statement: DROP DATABASE foo

Could you set in method Database#initialize (Pg.rb), @debug_level to 3 and run the example
again. What's now the output on stdout?


Regards,

  Michael

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Michael N. <uu...@rz...>]

Michael Neumann wrote:
> Sean Chittenden wrote:
> > > Have you tried the following? 
> > > 
> > >   dbh['AutoCommit'] = false
> > > 
> > > If this was set to false, no commit should be issued automatically!

Maybe, I've found a solution.

A new version of Pg.rb is appended. I cannot test it this week, so perhaps could you? 

I've thrown out the whole in_transaction/start_transaction stuff, and 
replaced it with a simple "SET AUTOCOMMIT TO ON|OFF" statement.
I hope this works correctly.

Regards,

  Michael

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Sean C. <se...@ch...>]

> > > > Have you tried the following? 
> > > > 
> > > >   dbh['AutoCommit'] = false
> > > > 
> > > > If this was set to false, no commit should be issued automatically!
> 
> Maybe, I've found a solution.
> 
> A new version of Pg.rb is appended. I cannot test it this week, so perhaps could you? 
> 
> I've thrown out the whole in_transaction/start_transaction stuff, and 
> replaced it with a simple "SET AUTOCOMMIT TO ON|OFF" statement.
> I hope this works correctly.

This looks and acts worlds better, thank you!  One quick suggestion
given that 7.3's in beta right now.  In 7.3 you can turn off
autocommit for the entire database on the backend with a new tunable
'autocommit = false' which forces the user to begin/commit.  On line
105 of Pg.rb, you're forcing autocommit on by default.  Could you
delete that line and just leave it up to the DBA who configured the
machine?  I can't think of a reason why it'd be worth while to send
the extra autocommits to the server.  -sc

-- 
Sean Chittenden

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Michael N. <uu...@rz...>]

Sean Chittenden wrote:
> > > > > Have you tried the following? 
> > > > > 
> > > > >   dbh['AutoCommit'] = false
> > > > > 
> > > > > If this was set to false, no commit should be issued automatically!
> > 
> > Maybe, I've found a solution.
> > 
> > A new version of Pg.rb is appended. I cannot test it this week, so perhaps could you? 
> > 
> > I've thrown out the whole in_transaction/start_transaction stuff, and 
> > replaced it with a simple "SET AUTOCOMMIT TO ON|OFF" statement.
> > I hope this works correctly.
> 
> This looks and acts worlds better, thank you!  One quick suggestion
> given that 7.3's in beta right now.  In 7.3 you can turn off
> autocommit for the entire database on the backend with a new tunable
> 'autocommit = false' which forces the user to begin/commit.  On line
> 105 of Pg.rb, you're forcing autocommit on by default.  Could you
> delete that line and just leave it up to the DBA who configured the
> machine?  I can't think of a reason why it'd be worth while to send
> the extra autocommits to the server.  -sc

The reason was to stay compatible with the old Pg.rb one. 
But I guess, good code explicitly sets AutoCommit, so that we can remove
this line.  

Regards,

  Michael