Prepared queries after server restart

Hi!
I have multiple services that require making queries that contain parameters. These days, I use PREPARE QUERY, but every time the server is being restarted, the queries are being remove and I need to run a script that creates all the PREPARE QUERYs again.

  1. Is there a way to make the Couchbase server store the queries not in the memory cache, making it resilience for restarting?
  2. Is there a way to manage all my prepared queries?
    *Note about not using Views: In my case, the data structure that I use requires me to make the query/view per collection, which (from what I understand) is not possible to do, Views are only per bucket.

If you have multiple Query service nodes, prepared statements are propagated between nodes on start-up. So if you stop only one Query node at a time you should not need to reissue the PREPARE statements.

If you have only one Query node and/or stop all nodes simultaneously then you have to issue the PREPARE statements as you are doing.

There is no permanent storage for prepared statements.

It is typical that the start-up sequence for an application prepares all the statements it will be using - that is, the application is coded to handle it and external actions aren’t required.

Another scheme employed sometimes is reactive preparing within the application - attempt to execute the prepared statement and on error 4040 (“No such prepared statement”) prepare it then loop back and attempt to execute it again.

Managing the statements outside of the application is an opportunity for trouble - it is easy for an external script and the application to be out of sync and thus easy to prepare a statement with differing results to those the application expects. This is why the applications commonly manage the prepare operations themselves.

1 Like

Thank you for your reply!

Is there a way to see all the current “running” prepared queries?
In addition, any option to drop/update a prepared query that is already “running”?
“running” = I ran the script that deployed the prepared query

You can see all the prepared statements on the cluster via the system:prepareds keyspace and/or the /admin/prepareds endpoint:

https://docs.couchbase.com/server/current/manage/monitor/monitoring-n1ql-query.html#sys-prepared

Obviously this content doesn’t mean they’re actually executing - just that they’ve been defined.

The FORCE option on the PREPARE statement does permit a measure of “updating” - it allows the query plan to be recreated but does not permit changes to the statement text.

To delete a prepared statement follow the instructions in the above link - either delete from system:prepareds or send a DELETE request to the admin endpoint.

HTH.

Thanks dh, I did succeed in getting the prepared queries when running the SQL++ query:
SELECT *, meta().plan FROM system:prepareds

But when trying to get it from the url {myurl}:8091/admin/prepareds I got “Not found” result. Any additional thing that I’m missing?

Another question - How many prepared queries can be stored in the memory cache?

The port should be 8093 (by default) for all Query service admin endpoints. (Only the encoded plan is reported by the admin endpoint; if you need the plan, use the system keyspace.)

The “prepared-limit” configuration parameter dictates the size of the prepared statement cache. (See, e.g. https://docs.couchbase.com/server/current/cli/cbcli/couchbase-cli-setting-query.html#options)

HTH.

Yes this helped!
Can you please provide documentation about the 8093 port and all it’s available endpoints?

Thanks :slight_smile:

The REST interface is documented here:

https://docs.couchbase.com/server/current/n1ql/n1ql-rest-api/index.html

(The admin & settings API references can be navigated to via the sub-sections shown on the left of the above page.)

HTH.