Re: [CDBI] using different DSNs for reads/writes

On 20/12/2007, Brian Duggan <bduggan-ID3/[email protected]> wrote:
> On Thursday, December 20, Bill Moseley wrote:
> > On Wed, Dec 19, 2007 at 08:50:31PM -0500, Brian Duggan wrote:
> > >
> > > sub db_Main {
> > >     my $class  = shift;
> > >     return $class->useWriteDBH ? $class->db_Write(@_) : $class->db_Read(@_);
> > > }
> >
> > I wonder if would be possible to determine read or write by looking up
> > the call stack.

I doubt it - when using split master/write slave/read, you have to
worry about all kinds of side effects of your queries, any database
triggers or other side effects of calling the query on the slave
rather than master (esp if you are calling your own functions or
stored procedures).

Just because a query starts with select doesn't mean it doesn't
contain a condition that modifies data.

> Sounds possible...alternatively, CDBI triggers could set the useWriteDBH flag.
> (i.e. a select trigger would set it to 0, insert/update/delete triggers would
> set it to 1)

As it happens, I've been refactoring a manual/inhouse plain DBI system
to use split read/write, and concluded it's best to cherry pick which
methods / queries read from master/slave database - for instance if
you have more than a couple of seconds lag you could have problems
when a user is updating data on the master and then it's read from the
slave - they would expect to see their updates.

An automated solution isn't workable for anything nontrivial IMHO and
you need to explicitly flag when to read from the slave database
handle, really this needs to be done when fetching the database
handle, which I believe would be considerably simpler in Class::DBI
than DBIx::Class.

One possibility would be to use sub attributes to label any method
that should use a read/write database handle, but that looks pretty
complicated.

A.

-- 
http://www.aarontrevena.co.uk
LAMP System Integration, Development and Hosting