How to check if prepared statement already exists?

[harmic]

In earlier versions of libpqxx, we could check if a prepared statement had already been prepared using something like this:

if (! tx.prepared(statement_name).exists()) {
    // now we know it has not already been prepared, we can
    // go ahead and prepare it
}

We can still do this, but we get deprecation warnings because prepared is deprecated, so I guess we can't do it for much longer!

I can't see any alternative way though - seems like there should be a prepared_exists method at the DB connection level?

The use case is for a particular statement that needs to be used from more than one place in the code, and we don't have any clean place to prepare it straight after connecting.

[elelel]

You can use C++ singleton pattern like this:

std::string my_class::actually_prepare_my_statement() {  // Static method
  // Prepare statement ...
  // Return it's name as string ...
}

std::string& my_class::my_statement_name() { // Static method
  // Will be initialized only once on first use. You can also use thread_local specifier for multithreaded scenarios
  static std::string statement_name{actually_prepare_my_statement()};  
  return statement_name;
}

void my_class:use_statement() {
  // ...
  t.exec(..., my_statement_name(), params....);
}

That becomes more complicated if you have non-static parameters to prepare it though

[jtv]

Yes, I'm sorry I had to remove this feature; the library had become far too heavy and complicated, and it introduced risks of strange and subtle bugs with the weird semi-transactional behaviour of prepared statements. I believe most people could do just fine without it, but obviously that's not everyone.

My first instinct would be to go with what @elelel suggests — although it's really something you associate with the connection, not necessarily a singleton. The Singleton pattern is overrated. :-)

Alternatively, you could try re-preparing the statement inside a subtransaction, so that it can fail without breaking your transaction.

[harmic]

Thanks @elelel & @jtv.

I had thought of using the singleton pattern, but our application has multiple database connections going towards different databases, so I don't think we can use that.

These statements are being executed very frequently, so I don't want to re-prepare each time - I assume that would result in extra round trips to the database.

Maybe we'll have to wrap the database connection with something that tracks the state of these prepared statements.

[elelel]

You can also always replicate what db server would do to manage the named statements - store the names in in a hash set (e.g. std::unordered_set) after they've been prepared and check if it exists. It costs CPU time, but that's what tx.prepared(...) would have to do either on server or on client side anyway.

Singleton is not necesserily singleton relative to the whole application. It may be singleton relative to a worker thread, or a single client connection if you are writing a server application and shared for all queries that this client does. It's up to you to decide relative to what it's goind to be single.

Another way out is to wrap the connection in your own class and prepare statements on construction or use:

struct my_connection {
  my_connection() {
   prepare_stmts_on_construct(); 
  }  

  pqxx::connection_base conn;

  const char* stmt_on_use() {     
    if (!stmt_on_use_prepared) { 
      // prepare on first use.....
      stmt_on_use_prepared = true;
    }
    return stmt_on_use_name;    
  }

  private:
    void prepare_stmts_on_constructs() {
       // prepare everything that can be prepared on connection construction
       conn.prepare( "stmt_that_always_prepared_in_constructor" ....);
    }

    bool stmt_on_use_prepared{false};
};

my_connection c;
t.exec_prepared(c.conn, "stmt_that_always_prepared_in_constructor", ....);
t.exec_prepared(c.conn, c.stmt_on_use(), ....);

[jtv]

Again I would go with what @elelel says, with one small caveat: preparing a prepared statement is sort of semi-transactional.

IIRC off the top of my head it worked something like: If you prepare your statement inside a transaction and the transaction aborts, the statement was not prepared. But if the transaction commits, the statement remains prepared outside the transaction.

So it's not too hard to track whether a statement has been prepared, but it's slightly more complicated to get it completely right in all circumstances. For most applications the difference probably doesn't matter, and that was another reason to remove the tracking from the library: libpqxx had to get it completely right, but it wasn't clear that anyone really benefited from it.

[harmic]

Thanks guys. In our use case we really need to track it per connection, so I think we'll use a per DB connection wrapper to add a function of tracking statement preparation.

Thanks for the suggestions.