Support batch changeset updates

[rob0rt]

Previously discussed in #2032.

There is currently no support for updating many structs annotated with AsChangeset. The current implementation denotes the only current way to update rows is to iterate over the changesets and update them synchronously, which can lead to bad performance. Batch updates are possible in many SQL engines, but through non-intuitive and more complicated queries, such as a VALUES clause or utilizing a CTE. For the purposes of this discussion, I'm going to be focusing on Postgres, given my use-case, but this should apply to other SQLs.

After a while of working, I have been able to achieve my desired results of batch-updating by generating a query in the format of:

WITH raw AS (
  SELECT * FROM (
    VALUES (1, 'foo'), (2, 'bar')
  ) AS _data (id, name)
)
UPDATE table
SET
  name = r.name
FROM raw AS r
WHERE table.id = raw.id

Note: I have forked Diesel at the 1.4.x branch in order to adjust the visibility of some struct fields in order to implement batch updates in user-land, though this would not be necessary if implemented in Diesel proper.

My implementation is as follows:

In order to iterate over column names for the purposes of generating the AS clause, and the SET clause, as well as the various VALUES statements, I've implemented the following macro, utilizing the pre-existing __diesel_for_each_tuple:

// Create a list of column names
pub trait ColumnList {
    type Table;
    fn walk_ast(&self, out: AstPass<Pg>) -> QueryResult<()>;
}

// Create a sequence of setters, in the format:
//   foo = r.foo, bar = r.bar, baz = r.baz 
pub trait ColumnSetters {
    type Table;
    fn walk_ast(&self, out: AstPass<Pg>) -> QueryResult<()>;
}

// Create clauses for the VALUES statements, in the format:
//   $1, $2, $3
// corresponding to each value in the tuple
pub trait ColumnValues {
    type Table;
    fn walk_ast(&self, out: AstPass<Pg>) -> QueryResult<()>;
}

macro_rules! tuple_impls {
    ($(
        $Tuple:tt {
            $(($idx:tt) -> $T:ident, $ST:ident, $TT:ident,)+
        }
    )+) => {
        $(
            impl<$($T,)+ Tab> ColumnList for ($($T,)+)
            where
                $($T: diesel::Column<Table = Tab>,)+
            {
                type Table = Tab;

                fn walk_ast(&self, mut out: AstPass<diesel::pg::Pg>) -> QueryResult<()> {
                    $(
                        if $idx != 0 {
                            out.push_sql(", ");
                        }
                        out.push_identifier($T::NAME)?;
                    )+
                    Ok(())
                }
            }

            impl<$($T,)+ Tab> ColumnSetters for ($($T,)+)
            where
                $($T: diesel::Column<Table = Tab>,)+
            {
                type Table = Tab;

                fn walk_ast(&self, mut out: AstPass<diesel::pg::Pg>) -> QueryResult<()> {
                    $(
                        if $idx != 0 {
                            out.push_sql(", ");
                        }
                        out.push_identifier($T::NAME)?;
                        out.push_sql(" = ");
                        out.push_identifier("r")?;
                        out.push_sql(".");
                        out.push_identifier($T::NAME)?;
                    )+
                    Ok(())
                }
            }

            impl<$($T,)+ $($ST,)+ Tab> ColumnValues for ($(diesel::query_builder::Assign<$T, $ST>,)+)
                where
                    $($T: diesel::Column<Table = Tab>,)+
                    $($ST: QueryFragment<diesel::pg::Pg>,)+
            {
                type Table = Tab;

                fn walk_ast(&self, mut out: AstPass<diesel::pg::Pg>) -> QueryResult<()> {
                    $(
                        if $idx != 0 {
                            out.push_sql(", ");
                        }
                        self.$idx.expr.walk_ast(out.reborrow())?;
                    )+
                    Ok(())
                }
            }
        )+
    }
}

diesel::__diesel_for_each_tuple!(tuple_impls);

The usage of Assign and accessing of the struct value expr is specifically where I needed to change the visibility in my forked version.

I was then able to utilize these tuple implementations for the main QueryFragment generation:

/**
 * Create a batch update query, in the format of:
 *
 *     WITH raw AS (
 *         SELECT * FROM (
 *             VALUES (1, 'foo'), (2, 'bar')
 *          ) AS _data (id, name)
 *     )
 *     UPDATE table
 *     SET
 *         name = r.name
 *     FROM raw AS r
 *     WHERE table.id = raw.id
 *
 * Due to the janky nature of this implementation, there's a few assumptions:
 *  - The table has a single primary key
 *  - The primary key is a UUID
 *  - The primary key is the first item in the table struct
 *  - There is at least one record to update
 * If any of these assumptions are violated, this will produce invalid SQL
 * and fail at runtime.
 */
impl<'a, T, U> QueryFragment<Pg> for BatchUpdate<T, &'a [U]>
where
    T: Table + HasTable,
    <<T as HasTable>::Table as Table>::PrimaryKey: QueryFragment<Pg>,
    T::AllColumns: ColumnList<Table = T> + ColumnSetters,
    T::PrimaryKey: Column,
    T::FromClause: QueryFragment<Pg>,
    &'a U: Identifiable<Id = &'a Uuid> + AsChangeset<Target = T>,
    <&'a U as AsChangeset>::Changeset: ColumnValues,
{
    fn walk_ast(&self, mut out: AstPass<Pg>) -> QueryResult<()> {
        out.push_sql("WITH raw AS (");
        out.push_sql(" SELECT * FROM (");
        out.push_sql(" VALUES ");

        for (i, record) in self.records.iter().enumerate() {
            out.push_sql("(");
            out.push_bind_param::<diesel::sql_types::Uuid, _>(record.id())?;
            out.push_sql(", ");
            record.as_changeset().walk_ast(out.reborrow())?;
            out.push_sql(")");
            if i < self.records.len() - 1 {
                out.push_sql(",");
            }
        }

        out.push_sql(" ) AS _data ( ");
        ColumnList::walk_ast(&T::all_columns(), out.reborrow())?;
        out.push_sql(" )) ");
        out.push_sql(" UPDATE ");
        self.target.from_clause().walk_ast(out.reborrow())?;
        out.push_sql(" SET ");

        ColumnSetters::walk_ast(&T::all_columns(), out.reborrow())?;

        out.push_sql(" FROM raw as r WHERE ");

        T::table().primary_key().walk_ast(out.reborrow())?;
        out.push_sql(" = ");
        out.push_identifier("r")?;
        out.push_sql(".");
        out.push_identifier(T::PrimaryKey::NAME)?;

        Ok(())
    }
}

Due to time constraints, I elided some more generalized parts of this implementation and noted the assumptions made in-line. Also note that, for all intents and purposes, I have very little idea of what I'm doing, and this was a "best-effort" attempt to get something workable due to, again, time constraints. However I thought it pertinent, due to the conversations I've had both on Twitter and in Gitter regarding this problem, to share my solution in hopes of being able to contribute it back in a more polish, less-janky manner.

[weiznich]

Thanks for posting your solution. As already stated I would be interested in having this feature in diesel itself, but at least for me personally that's something I do not want to spend time on working myself. (This is something that could be added after the 2.0 release, so it's not important for finishing the release).

That written I've put together a small experimental implementation to checkout what would be required to support this in tree and to see if we should in cooperate changes into the 2.0 release to make this possible. The corresponding implementation is here.

If someone is interested in doing the required work to turn this into a actual PR these are the things that are left to do:

• Write a few tests
• Write documentation
• Include the ID in the value clause + generate a corresponding where clause
• Include any other where clause passed to the update statement
• Maybe support other containers as Vec<T> there as well? (&[T], [T; N], Box<[T; N]>)
• Research in how to handle RETURNING clauses. Should they be supported in this context?
• Research if the generated SQL is supported for sqlite and mysql as well. If not is there an alternative way to write this for one of the backends?
• Depending on the outcome of the last two points maybe write a few compile fail tests to show that unsupported things are rejected

For any interested person: It's not required to do this all on your own without any help from me (or someone else). I'm willing to help with some advice and pointing to the right locations here, I just cannot afford the time to finish this by myself currently.