Re: Apache::DBI and DBIx::Class

On Jun 28, 2011, at 11:34 AM, Dave Morgan wrote:

> First off, please let me apologize for the tone of my last email,
> It was certainly not what I intended.

No worries. I assumed it was a caffeine shortage. That's what I suffer from sometimes. :-)

> I have to stop having discussions about system design and
> philosophy after having meetings with a developer who's most
> intelligent statement was "It works on my machine". Cold Fusion not
> mod-perl, and the problem is not with Cold Fusion.

Someone put cold fusion in your espresso. That can cause a bad day no question.

> >> IMHO, worth exactly what you paid for it
> This was meant to refer to my opinion, not your code, sorry.

Ah, okay.

> Never ran Bricolage in production. Did run a test system, ughhhhh.
> I suspect the issue was with the Bricolage code, not Apache::DBI.

Based on what evidence, exactly?

> I have never used connect_cached, never had the need.

If you rely on Apache::DBI, you wouldn't need connect_cached, of course.

> Postgres supports it but DBD::Pg doesn't, not with the standard DBI
> fetch* APIs. At least as of last year it didn't. If I didn't have to spend
> all my time reviewing and running other peoples code  I would take the time
> to add the functionality.

What's wrong with SQL? Example:

    #!/usr/local/bin/perl -w

    use v5.14;
    use utf8;
    use DBI;

    my $dbh = DBI->connect(
        'dbi:Pg:dbname=try', '', '', {
            PrintError        => 0,
            RaiseError        => 1,
            AutoCommit        => 1,
            pg_enable_utf8    => 1,
            pg_server_prepare => 0,
        }
    );

    $dbh->begin_work;
    $dbh->do('CREATE TABLE foo (id INT)');
    $dbh->do('INSERT INTO foo VALUES (1), (2), (3), (4), (5), (6)');

    $dbh->do('DECLARE getfoo CURSOR FOR SELECT id FROM foo');

    my $sth = $dbh->prepare('FETCH FORWARD 2 FROM getfoo');
    for (1..3) {
        say $_;
        $sth->execute;
        while (my $r = $sth->fetch) {
            say ".. $r->[0]";
        }
    }
    $dbh->rollback;

Output:

    1
    .. 1
    .. 2
    2
    .. 3
    .. 4
    3
    .. 5
    .. 6

>> Well, DBIx::Connector does not do connection pooling (or caching), so that wouldn't be a problem.
> 
> I am being unclear here. Each apache child/process will open 2 connections, one for
> Apache::DBI and one for DBIx::Class or DBIx::Connector.

DBIx::Connector disables Apache::DBI, so there should only be one. If not, there's a bug. Also, most folks who use DBIx::Connector don't use Apache::DBI at all.

> Possibly more connections if
> a variety of different modules are being used, each with it's own connection handling.

Yes, that's a risk. Don't do that.

> This explains an issue I saw with a client 2 months ago where the number of open database
> sessions doubled with the introduction of some new code based on DBIx::Class.

Sounds like a bug in DBIx::Class.

> I believed the doubling of sessions was a symptom of poor code. Neither I nor their
> developers had any idea that Apache::DBI was being bypassed. Luckily it was a prototype
> and the issue was caught while stress testing in their staging environment.

Yes, DBIx::Class should disable Apache::DBI when it does its connecting, just like DBIx::Connector does. Apache::DBI should still be usable if you're connecting by some means other than DBIx::Class. But this is one of the three raisons d'être for DBIx::Connector: Use it to connect instead of the DBI and do your own connection caching. I believe DBIx::Class will be switching to DBIx::Connector eventually.

> And so into the design and philosophy. As an admin I need to be able to deploy code without
> side effects that may adversely affect other processes or the system itself.

Yeah, that's exactly the complaint about Apache::DBI. Its entire purpose is side-effects.

> Now at least you document your connection logic, so that if I do a quick review I can see the
> implications (I doubt I would actually catch it but at least you are giving me a chance :)
> whereas for DBIx::Class, if there is documentation about connecting I don't know where it is.

Yeah, I had to do some mining to dig it out for DBIx::Connector.

> Even so, I believe the logic should be
> 	if Apache::DBI use it
> 	else use my stuff

IIRC, I can't use Apache::DBI and still support the ability to reconnect when a database connection has dropped. That's its second raison d'être (pings mostly go away when you use it in fixup mode, which is recommended).

> I do not know the internals of DBI or the derived classes so I do not know if the above is
> practical. However, it is respectful of the environment it is going to run in. Writing code
> that specifically ignores how a system is configured is ....... impolite!

Well, one could interpret Apache::DBI as doing exactly the same thing. Someone loads it unbeknownst to you, and all of a sudden the behavior of DBI->connect is globally changed.

> And yet Apache::DBI also has side effects. I revoke ALTER SESSION from any schema/user that will
> be accessed via Apache::DBI as there will be some unaware developer who will change the
> session characteristics (to get a different data format perhaps) and then will not change it
> back. And so the next time the connection/handle is used the code fails. Is this his fault?
> I don't believe so. It is a systems issue not a development issue.

Right, but this is why DBIx::Connector doesn't do connection caching itself. The developer controls the scope in which it lives, and it DBIx::Connector just worries about keeping a solid connection across forks and threads and disconnects.

> Yeah it was a lot of fun debugging the issue that resulted in that solution :)

I think it would've been more straight-forward if Apache::DBI had never been used at all.

> What I find so frustrating is that with all these magnificent tools we have now, modern
> hardware, screaming fast network, Apache, perl, mod_perl, the networking libraries and
> such I am still dealing with the same problems I had to deal with 10 and 15 years ago:
> 	- let the database do the work
> 	- do not try to configure network parameters
> 	- close all connections (file handles, RPC, serial port, network and database)
> 	- do not assume your code completes properly.
> 	- do not try to manage or allocate hardware resources, just use them
> 	- you have to share, your code is not the only thing running
> 	- the problem is not with the tools you are using, it is your code

Amen.

> And it must be extremely frustrating to be a developer, have his systems people chew him
> out for some problem, when he/she has not changed anything and the problem is caused by
> an "impolite" library. And the database side is usually pretty good, the modules used
> for SCADA are much worse.

Yeah, this describes the problem with Apache::DBI quite nicely. Action-at-a-distance and all that.

> It is inherent in the nature of perl, you have this wonderful library called CPAN,
> but, how do you ensure that the modules you use do what you want and nothing more?
> Do you really want to review (and understand) DateTime.pm?
> 
> And finally back to the Cold Fusion issue :
> "You don't get paid to make it run on your machine,
> you get paid to make it run on mine!"
> 
> I love my job, I love my job, I love my job ;)
> 
> Sorry once again,

No worries. Think I'll not contribute to this thread anymore, though, as we're veering pretty far off-topic. Hope the points are of interest to others, and historically relevant going forward, but I don't think I could add much more. And I'm happy to agree to disagree and go back to work.

Best,

David