On Wed, Mar 14, 2012 at 01:35:21PM +0100, Elizabeth Mattijsen wrote:
> Maybe some background first:
>
> I'm working on code that does queries on large tables. To be able to support
> the growth of these tables, we need to shard the tables to multiple database
> servers. One way to shard this evenly, would be to shard per second of data:
> even seconds go to one database, odd seconds go to another.
>
> We have a lot of code that creates minutely / hourly / daily aggregations (in
> Perl, the database just cannot do the kind of aggregations that we need) by
> querying these tables.
>
> When moving to sharded tables, I would like to keep the changes to the code
> as minimal as possible.
>
> By monkeypatching DBI::st, I've been able to reduce the code change to only
> having to add another loop construct to the code, so something like:
>
> =============================
> my %visitors;
> my $values;
> $visitors{ $values->[0] }= undef
> while $values= $sth->fetchrow_arrayref;
> printf "Found %d unique visitors\n", scalar keys %visitors;
> =============================
BTW, using bind_col() would be faster:
$sth->bind_col(1, \my $visitor_id);
$visitors{ $visitor_id } = undef
while $sth->fetchrow_arrayref;
though it should be faster still to do a SELECT DISTINCT on the server
for that particular case.
> Becomes:
>
> =============================
> my %visitors;
> my $values;
> while ( $sth= $sth->next ) {
> $visitors{ $values->[0] }= undef
> while $values= $sth->fetchrow_arrayref;
> }
> printf "Found %d unique visitors\n", scalar keys %visitors;
> =============================
There's actually an (almost completely undocumented) more_results()
method that's meant for that kind of thing. I was rather surprised
to find it wasn't documented as I'm pretty sure we thrashed out the
semantics some time ago.
[later] Wow, "some time ago" was 2004 :)
See
http://markmail.org/thread/i7drouwtpdiybjzn#query:+page:1+mid:p4dy4uv3t2kpku56+state:results
The "example implementation" in there assumed $sth->prepare works.
Given current realities the more_results method would be like:
$sth = $sth->{Database}->prepare($sql);
$sth->execute;
return $sth; # caller must use this returned handle
> Then I found out about "swap_inner_handles" and realised that *if* I
> could catch the depletion of the result set, I could do the $sth->next
> logic using swap_inner_handles. And make it completely invisible to
> the outside world.
swap_inner_handle (aka "brain surgery for handles") is kind'a scary.
But yes, it's worth exploring.
my $new_sth = $old_sth->{Database}->prepare($sql);
$new_sth->swap_inner_handle($old_sth);
$old_sth->execute;
return $old_sth; # call can ignore this
> However, I've not been able to find a way to catch that state change.
>
> I have tried:
>
> Stealing $sth->finish (on the assumption that that would be called when the
> result set depletes)
Compiled drivers would tend to call the drivers own internal function
for that.
> Stealing DBI::st::STORE (on the assumption that the tie interface would be
> used to reset the Active attribute)
Again, compiled drivers tend to take shortcuts (in this case via the
DBIc_ACTIVE_off macro defined in DBIXS.h).
> Neither of these work, unfortunately.
>
>
> So I wonder, would it make sense to add a statement handle attribute
> like "last_record_seen" which would contain a code ref? And have that
> code execute with the statement handle as its only parameter whenever
> the result set of a statement handle gets depleted, but before
> fetchrow_arrayref returns undef? If that would exist, then it would
> simply be a matter of putting the swap_inner_handle logic in that code.
>
> something like:
>
> $sth->{last_record_seen}= sub {
> my ($old)= @_;
> $old->swap_inner_handle($new); # wherever $new comes from, not important
> here
> }
>
> Then it would be possible to completely hide the sharding of the
> databases from our code. Which would make life a lot easier on a lot
> of people now and in the future.
It's not quite that simple if you're trying to avoid code changes in the
application - i.e., if a while(fetch) loop should transparently return
rows from multiple statements. The problem is the control flow.
At what point in the dispatcher does last_record_seen get called
and how does it, or the dispatcher, then arrange to (re)call the
fetch method to get the next row?
Hooking into the Active flag being turned off wouldn't be safe
since the driver is not expecting to be reentered at that point.
The existing Callback mechanism
http://search.cpan.org/~timb/DBI/DBI.pm#Callbacks
only works on the pre-call side of the dispatcher, currently.
Extending it to allow callbacks on the post-call (return) side is
certainly a possibility that Ive outlined previously. To avoid being
too expensive for your needs the mechanism would have to support an
optional "only call this callback if the return value is false" flag.
So perhaps we'd end up with something like this
$sth->{Callbacks}{fetchrow_arrayref} = [
undef, # pre-call hook
undef, # post-call hook for true returns
sub { # post-call hook for false returns
...
}
];
> Actually, this callback would need to return whether or not to call the
> original fetch code again. Something like:
>
> $sth->{last_record_seen}= sub {
> my ($old)= @_;
> if ($new) { # wherever $new comes from, not important here
> $old->swap_inner_handle($new);
> return 1; # please try again
> }
> return 0; # we're really done, thank you
> }
I don't know how easy or safe it would be to introduce a loop into the
dispatcher to allow that. Maybe not too hard. It may be a better API to
treat the return value of the post-call callback as the value to be
returned to the application, so the callback would end with
return $sth->fetchrow_arrayref;
Tim.
