How to generate a postgres params slice?

help
#1

I am trying to perform inserts as efficient as possible into postgres (and YugabyteDB, which is wire compatible with postgres) using rust.
In order to do that, I have created the following function that works:

pub fn run_insert_prepared_10(connection_pool: Pool<PostgresConnectionManager<NoTls>>) {
    println!("=== run_insert_prepared_10 ===");
    let mut connection = connection_pool.get().unwrap();
    //connection.simple_query("select current_timestamp").unwrap();
    let mut histogram = Histogram::with_buckets(10);
    let overall_start_time = Instant::now();
    let mut overall_query_time: u128 = 0;

    let base_insert = "insert into test_table (id, f1, f2, f3, f4) values";
    let mut fields = String::from("");
    let mut values = Vec::new();
    for fields_nr in 0..10 {
        fields.push_str(format!("(${}, ${}, ${}, ${}, ${}),", (fields_nr*5)+1, (fields_nr*5)+2, (fields_nr*5)+3, (fields_nr*5)+4, (fields_nr*5)+5 ).as_str());
    }
    fields.pop();
    let statement = format!("{} {}", base_insert, fields);
    let statement = connection.prepare(statement.as_str()).unwrap();

    for nr in (0..LOOP_NR).step_by(10) {
        for value_nr in 0..10 {
            values.push(Values::Int(nr+value_nr+1) );
            values.push( Values::String(random_characters(FIELD_LENGTH) ));
            values.push( Values::String(random_characters(FIELD_LENGTH) ));
            values.push( Values::String(random_characters(FIELD_LENGTH) ));
            values.push( Values::String(random_characters(FIELD_LENGTH) ));
        }
        //let values_as_slice: Vec<_> = values.iter().map(|x| x as &(dyn ToSql + Sync)).collect();
        let query_start_time = Instant::now();
        connection.query( &statement,
                          &[
                              &nr,
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &(&nr+1),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                                  &(&nr+2),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                                  &(&nr+3),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                                  &(&nr+4),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                                  &(&nr+5),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                                  &(&nr+6),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                                  &(&nr+7),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                                  &(&nr+8),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                                  &(&nr+9),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH),
                              &random_characters(FIELD_LENGTH)
                          ]
        ).unwrap();
        //connection.query( &statement, &values[..]);
        //connection.query( &statement, &values_as_slice[..]);
        histogram.add(query_start_time.elapsed().as_micros().try_into().unwrap());
        overall_query_time += query_start_time.elapsed().as_micros()
    }
    let overall_time = overall_start_time.elapsed().as_micros();
    println!("total time (s) : {:12.6}", overall_time as f64/1000000.0);
    println!("total query (s): {:12.6} {:5.2}%", overall_query_time as f64/1000000.0, overall_query_time as f64/overall_time as f64*100.0);
    println!("{}", histogram);
    println!("=== run_insert_prepared_10 ===");
}
  • I get a connection from the rd2d connection pool.
  • I then create the insert statement I want to perform. I do that by dynamically adding 10 values lists. that is valid postgres syntax.
  • The prepare creates the prepared statement.
  • I then loop for LOOP_NR times, and execute the (multi-values list) statement in the statement variable.
  • ->This uses a statical defined params slice.

This works!

However, what I would like to do, is create the params slice dynamic, like I do for the values list.

I have tried doing it (as you can see with the values definition, and the loop with value_nr. I also created an enum to allow the i32 and String to be pushed into the Vec:

enum Values {
    Int(i32),
    String(String),
}

However, when I try to use the values Vec or try to convert as can be seen in the commented values_as_slice Vec, I do get the error:

174 |         let values_as_slice: Vec<_> = values.iter().map(|x| x as &(dyn ToSql + Sync)).collect();
    |                                                             ^ the trait `ToSql` is not implemented for `Values`
    |
    = note: required for the cast to the object type `dyn ToSql + Sync`

Or, if I don't perform the iteration and mapping and use the values Vec directly:

232 |         connection.query( &statement, &values[..]);
    |                                       ^^^^^^^^^^^ expected `&dyn ToSql + Sync`, found enum `Values`
    |
    = note: expected reference `&[&dyn ToSql + Sync]`
               found reference `&[Values]`

How can I still dynamically build a params list?

Thank you!

#2

You can't just expect random 3rd-party traits to be implemented for your own type magically. You have to do that yourself. Alternatively, you could just not implement ToSql for your enum, and instead reach into the contained value by matching on it:

let values_as_slice: Vec<_> = values
    .iter()
    .map(|x| match *x {
        Values::Int(ref inner) => inner as &dyn ToSql,
        Values::String(ref inner) => inner as &dyn ToSql,
    })
    .collect();
1 Like