Distributed Transactions

[Thadeus Burgess]

Massimo,

Would you mind giving a more in depth example on distributed transactions with postgres? I know web2py supports them, however there is not much information on it. How would you accomplish a join across databases?

Is there an api to abstract this?

dba = DAL('postgres...')
dbb = DAL('postgrse...')

dba.define_table('customers'....)
dba.define_table('employees'....)
dbb.define_table('products'....)
dbb.define_table('sales'.....)

db = DISTRIBUTED_DAL(dba, dbb)

rows = db(db.customers.id == request.vars.cust_id)(db.sales.id == request.vars.sale_id).select()

for row in rows:
   print row.customer.name, row.customer.address, row.sale.time, row.sale.product.name

-Thadeus

[mdipierro]

There is a single function. In your case you would call

dba.distributed_transaction_commit(dba,dbb)

or

try:
dba.distributed_transaction_commit(dba,dbb)
except:
session.flash="distributed transaction failed and rolled back"

I have not tried this in some time but when I tried worked fine.
postgresql only.

[Thadeus Burgess]

So you would have to insert records for each object individually, then call distributed_transaction_commit.


dba.customers.insert(....)
dbb.sales.insert(....)

dba.distributed_transaction_commit(dba, dbb)



How would you perform a join?

dba(dba.customers.id == dbb.sales.id_customer).select()

for row in rows:
   row.customers.name, row.sales.total
   row.update_record(sales.tax=1.08)

dba.distributed_transaction_commit(dba,dbb)

-Thadeus

[mdipierro]

On Oct 29, 4:54 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> So you would have to insert records for each object individually, then call
> distributed_transaction_commit.
>
> dba.customers.insert(....)
> dbb.sales.insert(....)
>
> dba.distributed_transaction_commit(dba, dbb)

yes

> How would you perform a join?

You cannot cross database joins.

[Thadeus Burgess]

Is this a limitation of using a distributed database system, or just not having support in the DAL for this yet.


-Thadeus
$

[mdipierro]

I am not aware of any any system that allows joins across databases. I
do not think it is a well defined concept since if information reside
in distinct places there is no way to guarantee referential integrity.

In web2py you can have tables in distinct with records that reference
each other using integer keys and you can use the a record pulled from
one to reference a record pulled from another and you can use
distributed transactions tot make sure things do not get messed up.

Massimo

On Oct 29, 7:23 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Is this a limitation of using a distributed database system, or just not
> having support in the DAL for this yet.
>
> -Thadeus
> $
>

[Thadeus Burgess]

So if two tables need to be joined often, keep them on the same database.

Cool, thanks! :)

-Thadeus

[mdipierro]

Since we are discussing this. I have added distributed transaction
support for mysql and firebird (postgresql was already there).
Example:

db1=DAL('postgres://...')
db2=DAL('mysql://...')
db3=DAL('firebird://...')

SQLDB.distributed_transaction_begin(db1,db2,db3)
db1.table.insert(....)
db2.table.insert(....)
db3.table.insert(....)
SQLDB.distributed_transaction_commit(db1,db2,db3)

I would appreciate help with testing this.

On Oct 29, 9:08 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
>  So if two tables need to be joined often, keep them on the same database.
>
> Cool, thanks! :)
>
> -Thadeus
>