Auto reload PREPARE statement after Couchbase Server restart

Couchbase Server
1

When Couchbase Server restart, we need execute PREPARE statement by hand.
Can Couchbase Server auto reload PREPARE statement after Couchbase Server restart?

2

Hi Atom_yang,
no built-in support for auto loading scripts/n1ql commands.
Here is an excerpt from docs (Couchbase SDKs):

Prepared statements are stored in memory until you restart the Couchbase Server.
After restarting the server, you must prepare the statements again before you can
execute the prepared statements.

You may use shell scripting techniques to auto-run any shell/n1ql commands along with starting couchbase server. Also, Couchbase 4.5 comes with cbq tool that supports scripting. Couchbase SDKs

-Prasad

3

Thank you.
but I want to know that why Couchbase don’t provide auto loading Prepared statements?
it is not recommend way to using N1QL for application?

4

any update?
I want to know that why Couchbase don’t provide auto loading Prepared statements?
it is not recommend way to using N1QL for application?

5

I’m not exactly sure what you mean by “auto loading”. I assume you mean for the server to automatically detect and prepare, but you could also mean having some API that exposes the prepare in the SDKs?

In the initial design for prepared statements, the approach worked out was not to have it be automatic. There were a couple reasons for this. One was a limited cache. The other was a philosophical assertion that the application developer should need to specify this just like in many RDBMSs. That latter part wasn’t agreed upon by everyone.

Going forward, we are re-evaluating this. In fact @marcog is looking at an approach that would largely make prepared statements transparent to the app developer.

It’d be great to get any input on what you think would be best!

6

thank you for the feedback. to run N1QL for application,we has 2 choice

  1. run N1QL directly in SDK;
  2. create some prepare statement, then application can call the REST API to run N1QL;
    for option 1), the logic of data processing will be mixed in application logic. any change for N1QL will change the application logic.
    option 2) will insulate data processing logic and application logic, it is good for decoupling data processing logic.

so I think option 2) should be the best choice.now the problem is , if Couchbase Server restart for some reason, I should recreate prepare statement by hand (or by some extra script).
If the option 2) is the best choice,auto recreate prepare statement after Couchbase Server restart will be better.

7

Hi @atom_yang,
in general terms prepared statements are not stored anywhere in relational engines, and have to be reprepared by applications upon engine restart (the only exception here would be IBM DB2 LUW where statements for a specific application are store in an application related file called a “package”, which can be reused after the engine restarts) - so applications have to actually pass statements to the engine they are connecting to, whether these statements are prepared or not.

N1QL mimics this approach but extends it a little, whereby prepared statements can be shared across multiple client applications, if so desired.

A common technique for some decoupling application and processing logic is to have a common function at the beginning of the application which prepares all the statements the application will need during execution, and then the application logic just executes the statements identifiers, without having to intermingle query language and application logic .
The prepared statements don’t survive engine restarts, but are easily reinstated next time the application starts (let’s call this technique #1).

Application and processing logic are normally fully decoupled using scripting capabilities such as stored procedures or in general named statement blocks.
These are stored in system catalogs and survive engine restarts (let’s call this technique #2).

N1QL does not yet offer technique #2, but in the application code you can happily use technique #1 by creating multiple query objects in a single initial place of the application, each associated with a different request text, and just executing the one you need in each part of the application logic.

I am sure technique #2 will be a theme of subsequent N1QL release. As @ingenthr was saying, as the next step we are looking at making prepared statements more transparent to the user application.

HTH,
Marco

2 Likes
8

thank you very much for detailed explanation.