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.
Is there a way to make the Couchbase server store the queries not in the memory cache, making it resilience for restarting?
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.
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
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.
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.)
