Prepared Statement can't be created.

[Boscop]

I wanted to create a prepared statement to prevent sql injection. I do this whenever my web server starts:

    {
        use diesel::Connection;
        database::connection().get().unwrap().execute("SET TIME ZONE 'UTC';").unwrap();
        database::connection().get().unwrap().execute("
            PREPARE search_filters(text) AS
            WITH ranking AS (
                SELECT *
                FROM (
                    SELECT
                        ts_rank(
                            to_tsvector('english', filters.name) ||
                            to_tsvector('english', filters.description) ||
                            to_tsvector('english', array_to_string(filters.tags, ' '))
                        , plainto_tsquery($1), 32) AS score,
                        id,
                        user_id,
                        created_at,
                        updated_at,
                        name,
                        description,
                        code,
                        params,
                        tags
                    FROM filters
                ) s
                WHERE score > 0
                ORDER BY score DESC
            )
            SELECT 
                        id,
                        user_id,
                        created_at,
                        updated_at,
                        name,
                        description,
                        code,
                        params,
                        tags
            FROM ranking;
        ").unwrap();
        use diesel::expression::sql_literal::sql;
        use diesel::LoadDsl;
        assert_eq!(Ok(1), sql("SELECT count(*) FROM pg_prepared_statements;").get_result::<i64>(&*database::connection().get().unwrap()));
    }

But the assertion fails, it returns Ok(0), so the prepared statement wasn't created for some reason!
But when I enter this in the postgres cmd line, it says PREPARE and afterwards that count is 1 and I can call it like search_filters('bar').
What am I doing wrong here?
Also, how to check if that prepare query returns PREPARE or something else?

[durango]

Try declaring &*database::connection().get().unwrap() and use the same connection for each statements

[Boscop]

@durango Thanks!
Now I'm reusing the connection and that assertion succeeds, BUT later when I actually call search_filters I get:

 error: prepared statement "search_filters" does not exist

and in my pg_log:

2017-01-27 14:28:19 CET ERROR:  prepared statement "search_filters" does not exist
2017-01-27 14:28:19 CET STATEMENT:  EXECUTE search_filters('bar');

I'm calling it like this:

pub fn search(search_terms: &str) -> Result<Vec<Filter>, error::MyError> {
	use diesel::prelude::*;
	use models::schema::filters::dsl::*;
	use diesel::expression::sql_literal::sql;
	let query = sql(&format!("EXECUTE search_filters('{}')", search_terms));
	query.get_results::<models::filter::Filter>(&*database::connection().get().unwrap()).map_err(|e| e.into())
}

Prepared statements are only stored during a session. But what constitutes a session when I'm using a session pool like this?

use std::env;

use diesel::pg::PgConnection;
use r2d2_diesel::ConnectionManager;
use r2d2;

lazy_static! {
    static ref CONNECTION: r2d2::Pool<ConnectionManager<PgConnection>> = {
        let database_url = env::var("DATABASE_URL")
            .expect("DATABASE_URL must be set");
        let config = r2d2::Config::default();
        let manager = ConnectionManager::<PgConnection>::new(database_url);
        r2d2::Pool::new(config, manager).expect("Failed to create pool")
    };
}

pub fn connection() -> r2d2::Pool<ConnectionManager<PgConnection>> {
    CONNECTION.clone()
}

In my pg_log I notice A LOT of these lines:

2017-01-27 14:28:35 CET LOG: could not receive data from client: An existing connection was forcibly closed by the remote host.

Does that mean that after every statement execution I do, the pool drops the connection, and the session ends?

If so, what would you do to ensure that the timezone and the prepared statement are always set before any statements are executed?
Is it possible to run a some given startup code whenever the pool creates a new connection/session?
And does it make sense, performance wise, to run that code every time a new connection starts?

Or is there a way to abstract from that, and make it appear like one continuous session while my server runs?

[Boscop]

I figured some stuff out..
With the code below it seems to work.
Is this the right way to solve the problem / how prepared statements are usually set when a connection pool is used that drops the connection?

use std::env;

use diesel::pg::PgConnection;
use r2d2_diesel::ConnectionManager;
use r2d2;

lazy_static! {
    static ref CONNECTION: r2d2::Pool<ConnectionManager<PgConnection>> = {
        let database_url = env::var("DATABASE_URL")
            .expect("DATABASE_URL must be set");
        // let config = r2d2::Config::default();
        let config = r2d2::Config::builder().connection_customizer(box MyCustomizeConnection).build();
        let manager = ConnectionManager::<PgConnection>::new(database_url);
        r2d2::Pool::new(config, manager).expect("Failed to create pool")
    };
}

pub fn connection() -> r2d2::Pool<ConnectionManager<PgConnection>> {
    CONNECTION.clone()
}

use r2d2::CustomizeConnection;

#[derive(Debug)]
struct MyCustomizeConnection;

impl<E> CustomizeConnection<PgConnection, E> for MyCustomizeConnection {
    fn on_acquire(&self, conn: &mut PgConnection) -> Result<(), E> {
        use diesel::Connection;
        use diesel::expression::sql_literal::sql;
        use diesel::LoadDsl;
        conn.execute("SET TIME ZONE 'UTC';").unwrap();
        conn.execute("
            PREPARE search_filters(text) AS
            WITH ranking AS (
                SELECT *
                FROM (
                    SELECT
                        ts_rank(
                            to_tsvector('english', filters.name) ||
                            to_tsvector('english', filters.description) ||
                            to_tsvector('english', array_to_string(filters.tags, ' '))
                        , plainto_tsquery($1), 32) AS score,
                        id,
                        user_id,
                        created_at,
                        updated_at,
                        name,
                        description,
                        code,
                        params,
                        tags
                    FROM filters
                ) s
                WHERE score > 0
                ORDER BY score DESC
            )
            SELECT 
                        id,
                        user_id,
                        created_at,
                        updated_at,
                        name,
                        description,
                        code,
                        params,
                        tags
            FROM ranking;
        ").unwrap();
        assert_eq!(Ok(1), sql("SELECT count(*) FROM pg_prepared_statements;").get_result::<i64>(conn));

        Ok(())
    }
}

[durango]

I believe so, is everything all sorted out for you?

[Boscop]

Yes, thanks!
Btw, I switched to a function, because it persists across sessions:

CREATE OR REPLACE FUNCTION  search_filters(search_terms text)
RETURNS SETOF filters AS
$func$
BEGIN
    return query
    select * from filters
    where
        ts_rank(to_tsvector('english', filters.name) ||
                to_tsvector('english', filters.description) ||
                to_tsvector('english', array_to_string(filters.tags, ' ')),
        plainto_tsquery(search_terms), 32)
    > 0
    order by
        ts_rank(to_tsvector('english', filters.name) ||
                to_tsvector('english', filters.description) ||
                to_tsvector('english', array_to_string(filters.tags, ' ')),
        plainto_tsquery(search_terms), 32)
    desc;
END;
$func$
LANGUAGE 'plpgsql';