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!