DB Transaction with model helpers

[s1037989]

Using the Mojo::Pg Blog example, how could the $self->posts->add(...) call in the Posts controller be used in a transaction?

You need to get a database object with my $db = $pg->db; and then use that handle for all transaction calls, right?

[kraih]

Yes. Your transaction scope is probably too large anyway if you want to do that. Keep your transactions small.

[fluca1978]

What's the aim of using a transaction in there, since then there's a redirect?
What is your use case for the need of a transaction?

[daleif]

I usually have a has schema => sub { croak "missing schema" }; in my model. Such that when the controller creates an instance of my model it need to pass schema to it (in order to overwrite it)

You don't mention how $self->posts is created.

I do something similar to be able to access the validation object from inside my model.

[s1037989]

$self->posts is created the same as in the example Blog app.

What you do sounds very intriguing to me, especially the part about the validation object. What is the schema object?

Here's me taking a guess at what you're doing:

App:

sub startup ($self) {
  # Do you still create your model as a helper?
  # Is "pg" attribute here same as what you called "schema"?
  $self->helper(posts => sub { state $posts = Blog::Model::Posts->new(pg => shift->pg) });
}

Model:

has schema => sub { croak "missing schema" };
has validation => sub { croak "missing validation" };
sub add ($self) {
  my $schema = $self->schema;
  my $v = $self->validation;
  return $schema->insert('posts', $v->output, {returning => 'id'})->hash->{id};
}

Controller:

sub store ($self) {
  # This is where I really lose confidence in my guess
  my $posts = $self->posts->new(pg => $self->pg, schema => $self->pg->db, validation => $self->validation);

  my $id = $posts->add;
  $self->redirect_to('show_post', id => $id);
}

[daleif]

$schema is just because I usually use DBIx.

Exactly here I'd just have a has pg ... in my Posts model. I just use croak, to really let me know that I forgot to set it in the helper.

I often use something like

$self->helper(
      model => sub ($c) {
	  Foo::Model->new(
	      schema     => $c->app->schema, # $c->schema, does not work here
	      log        => $c->log,
	      validation => $c->validation,
	      config     => $c->config,
	  )
	});`

whether this is a good ide, I do not know, it works just fine. I also don't use the state part so I don't know if I end up with too many connections. My apps does probably not see enough trafic to notice.

Regarding the $schema comment above, I usually have something like this in my main file (the one with startup)

has schema => sub ($app) {

    my $dbc = $app->config('db');

    return undef unless ref $dbc;
   
    my $schema = Foo::Schema->connect(
	$dbc->{dsn},
	$dbc->{username},
	$dbc->{password},
	$dbc->{dbi_options},
    ); 

    return $schema;
};

[kraih]

The big difference between the Mojo::Pg and DBIx::Class style of doing things is that Mojo::Pg can have multiple connections active, while DBIx::Class only has one that is shared globally. The downside of the Mojo::Pg approach is that it makes wrapping transactions around abstract methods very hard. And the downside of the DBIx::Class approach is that it makes database deadlocks caused by unnecessarily large transaction scopes very easy.

After having tracked down a few deadlocks in DBIx::Class code, i'm leaning towards the Mojo::Pg way, with some duplicated code, being the lesser evil.

[daleif]

Then my stuff is probably just very short. Haven't had any deadlock issues that I've been aware of.

[fluca1978]

In an app that I'm building, the form input (like the blog post example) contains data that needs to go into more than one table. If there's a failure writing to any of the tables, then none of the tables should be written to. It seems like this is what transactions are for, right?

That's what a transaction is made for, but the point here, according to me, is when you are going to complete the transaction.
If the user has to visit several pages (forms) to give you all the data that will be committed, chances are that the transaction will become too long (in time) and difficult to manage. It would be better, in this scenario, to pass around your data and to create the transaction at the very last, ensuring all the data is flushed where you want to.

[fluca1978]

The big difference between the Mojo::Pg and DBIx::Class style of doing things is that Mojo::Pg can have multiple connections active, while DBIx::Class only has one that is shared globally. The downside of the Mojo::Pg approach is that it makes wrapping transactions around abstract methods very hard. And the downside of the DBIx::Class approach is that it makes database deadlocks caused by unnecessarily large transaction scopes very easy.

Isn't there a way to instrument DBIx::Class to open multiple connections? Then the question could be: how to determine the threshold to fire a new connection...

[kraih]

This is not about what could theoretically be hacked into a module, it's about what the APIs are designed for. DBIx::Class is designed for one connection with a very wide scope, and Mojo::Pg for a pool of connections with a very narrow scope (because of the possibility of async queries).