I just gained access to the DirectQuery early access so was trying to switch over from the PostgreSQL Gateway to the official DirectQuery solution. When experimenting with the types of queries I can include in my SaaS application code, I use Azure Data Studio to query Postgres.
Through ADS, I always get an error with DirectQuery:
cursor "b07f4a07-c9db-4d5d-9f7c-c9adb01abaf7" does not exist
Even for simple queries like this:
select "timestamp"
from public.datapoint_aggregates
WHERE timeseries_external_id = '419b9b45-8e09-48a9-9912-08da820807fc'
AND "timestamp" > '2022-08-23T00:00:00'
AND "timestamp" < '2050-08-23T00:00:00'
This is likely caused by something the client is doing because using this same login, I am able to run the example PowerBI queries.
Hi Ben,
this is most likely happening because DirectQuery is lacking support for DB cursors.
Those are not required for PowerBi that’s why the feature wasn’t prioritized so far.
Any chance that you could turn of the cursors in the client?
Hi@ipolomanyi ,
Azure Data Studio does not have an option in its Postgres plugin to disable cursors, but it is far from the most popular graphical interface for querying Postgres. I wasn’t able to query with pgAdmin either, but I will try some other tools and see if there’s one that can do simple querying without cursors.
Thanks!
pgAdmin 4 v6.12.
Here are the settings I use in the connection.
It logs in and shows me a dashboard with server metrics and activity. But the “Databases” node in the browser will never show a DB and I am not allowed to open any type of SQL Query Tool.
Could just be me never using pgAdmin before.
This is the one I tested and it seemed to work:
https://pgadmin-archive.postgresql.org/pgadmin4/v5.7/index.html
If it’s something that you find useful we can the see what we should improve to support newer versions.
Thanks for the reply. I am not needing pgAdmin to work right now. I was mostly testing it out of curiosity to discover DirectQuery’s capabilities outside of PowerBI usage.
I am using the psql commandline now which does not require cursor functionality. I am not getting any rows back from my query, but can’t figure out why. Am I using `starts_with` correctly here?
pinnacle-dev=> select "timestamp", "average" from public.datapoint_aggregates WHERE starts_with(timeseries_external_id,'7f9109d2-cae7-4aba-3f7a-08da85138eb7') AND "timestamp" > '2020-08-23T00:00:00' AND "timestamp" < '2030-08-23T00:00:00';
timestamp | average
-----------+---------
(0 rows)
I see the time series in CDF:
Figured it out. The PowerQuery StartsWith gets converted to LIKE ‘{search string}%’ so this is what you’ve implemented to translate to the API call. This is not supported for datapoint_aggregates, but is supported for timeseries as documented here:
Capabilities and limitations | Cognite Documentation
This query returns the result I expect:
This is what a working datapoint_aggregates SQL looks like:
Very good reverse engineering on your side :)