Currently Crate’s default behaviour is to throw a query exception if an object column is queried that isn’t recognised. This can be changed to return NULLs (which is more desireable behaviour for us), but only by setting session variable error_on_unknown_object_key to false. From what I can see this is not available as a cluster-wide configuration option.
Unfortunately a software package which is mandatory for us has Postgres support, but does not have the option to execute queries on connect, where we could feasibly call the SET error_on_unknown_object_key=false query. It does have the ability to send variables on connect via JDBC connection parameters (e.g. “-c error_on_unknown_object_key=false” - as per the “options” section at PostgreSQL: Documentation: 15: 34.1. Database Connection Control Functions), but from what I can see Crate does not seem to honour these.
Am I missing something obvious here? If not, would it be possible to add the error_on_unknown_object_key as a cluster-wide setting, so this could be set per CrateDB cluster as opposed to a session-only variable? Alternatively, allowing Crate to honour variables set via additional options at connect time would also help us achieve this.
I have been able to reproduce that indeed not all session parameters can be configured in JDBC connections the way you described. It works fine for search_path, but not for others, such as error_on_unknown_object_key.
I have opened an issue on GitHub for our developers to look into this and provide their feedback:
our developers have just merged a fix for this issue. It is seen as a new feature and therefore will be part of the next feature release, which is CrateDB 5.4. The release is planned for the end of this quarter. For testing purposes, the change will be in the next nightly build, available from tomorrow on (docker pull crate/crate:nightly-5.4.0).
I will have a test tomorrow and confirm this works for us. Is there any way of testing this earlier in CrateDB Cloud or is it currently Docker image only? Not a major problem either way but would be good to know.
it is possible to launch CrateDB cloud clusters based on a nightly image. That option isn’t visible in the Cloud Console user interface as of now, but we can do it manually for you if needed.
You have recently already been in contact with my colleague @hernanc via email, right? The best option is to reach out to him when you want to proceed with either starting a second cloud cluster or replacing your existing one.
