sql: support SQL declare (cursor)

[mattcrdb]

Is your feature request related to a problem? Please describe.
A customer tried to use tableau to connect to cockroachDB, using this guide.

Tableau uses declare for its queries, CockroachDB does not currently support compatibility with SQL level declare.

Tableau can not be used.

Describe the solution you'd like
Support for the declare keyword.

postgres docs on declare.

Epic: CRDB-11397

[RoachietheSupportRoach]

Zendesk ticket #3853 has been linked to this issue.

[tfoldi]

I can easily fix this by adding Cockroach as a supported database driver to Tableau. Only thing you have to do is to set params["UseDeclareFetch"] = "0"; in connectionBuilder.js in the Tableau TACO file.

If you're interested I can show how to do build a proper driver customization for Tableau...

[tfoldi]

OK, I tested it and works like a charm. Code is here: https://github.com/tfoldi/tableau_cockroachdb_connector

[mattcrdb]

cc @jordanlewis

[otan]

this is needed for QGIS support for PostGIS.

[jordanlewis]

@otan, could you say more? Including links to sample queries or use cases would be very helpful.

[otan]

QGIS uses DECLARE BINARY CURSORS cursors: https://github.com/qgis/QGIS/blob/b4c21fa44e25c23c9ee542f56f031b3de1a94fea/src/providers/postgres/qgspostgresconn.cpp#L1353

and then FETCH: https://github.com/qgis/QGIS/blob/b4c21fa44e25c23c9ee542f56f031b3de1a94fea/src/providers/postgres/qgspostgresconn.cpp#L1698

... all for the sake of deducing endianness :3

this is a startup sequence; i'm unable to open the tool without it, unfortunately.

---

QGIS is a widely used (and quite neat!) open source geospatial plotting tool as well.

[jordanlewis]

@otan @rafiss would we be able to send a PR to qgis to use a simpler method of deducing endianness?

[otan]

Maybe, but I found something perhaps even more useful -- the ogr2ogr tool requires DECLARE CURSOR to work so we can export from CRDB into other exotic data formats.

Modifying this code is hard because it works on a general cause, and it has no way to paginate results otherwise.

Code: https://github.com/OSGeo/gdal/blob/f031bd34baf9ab136637cb1e790c942aa947b828/gdal/ogr/ogrsf_frmts/pg/ogrpglayer.cpp#L1433

[jordanlewis]

Note that there is more support required than just DECLARE CURSOR here.

FETCH is also required. FETCH has a few variants, including relative, absolute, and forward and backward fetches. The linked code significantly needs both forward relative and absolute fetches. Implementing absolute fetches is significant because it requires buffering all of the results of the cursor on the gateway, presumably in memory and then spilling to disk. Just wanted to say it is likely just the tip of the iceberg and we have to get a realistic estimate of the work required before we dive into this.

@otan would it be possible for you to run some of these tools against Postgres and collect a log of the statements produced? I would like to make sure we are not missing any other features. Significantly, if you can figure out whether "multiple active result sets" are used (aka - are there other SQL statements intereleaved in between the cursor FETCH statements on the same connection?) that would be very helpful for effort estimation.

[jordanlewis]

I would also be curious to see the comprehensive list of tools we need to support for spatial, and of that list, which tools require cursor support. I continue to find it surprising that geospatial tools all need DECLARE CURSOR. Why would that be exactly? Is this a case of 2 tools (ogr2ogr and qgis) needing the feature making it seem like they all do? Do they actually all need cursors? And in the latter case, why is that exactly? Is it because they're old?

Sorry for all of the questions. I feel we need an improved understanding of the situation here to appropriately understand and prioritize this work.

[otan]

@otan would it be possible for you to run some of these tools against Postgres and collect a log of the statements produced?

See logs: https://drive.google.com/drive/folders/1IQ8Y3VvsrcbEAaQku3d3vXJuPVcWHu5W?usp=sharing

@otan would it be possible for you to run some of these tools against Postgres and collect a log of the statements produced? I would like to make sure we are not missing any other features. Significantly, if you can figure out whether "multiple active result sets" are used (aka - are there other SQL statements intereleaved in between the cursor FETCH statements on the same connection?) that would be very helpful for effort estimation.

AFAICT from reading code (they're both open source), they do not require anything like these -- just lots of FETCHes to get lots of data.

[otan]

I would also be curious to see the comprehensive list of tools we need to support for spatial, and of that list, which tools require cursor support. I continue to find it surprising that geospatial tools all need DECLARE CURSOR. Why would that be exactly? Is this a case of 2 tools (ogr2ogr and qgis) needing the feature making it seem like they all do? Do they actually all need cursors? And in the latter case, why is that exactly? Is it because they're old?

I'm not sure exactly why -- my guess is that it's easy to say "fetch me all data in a paginated manner" without having to read the schema and figure out exactly how it works.

[otan]

I will also note ArcGIS also required DECLARE CURSOR -- I've included it in the folder.

[otan]

I would also be curious to see the comprehensive list of tools we need to support for spatial, and of that list, which tools require cursor support. I continue to find it surprising that geospatial tools all need DECLARE CURSOR. Why would that be exactly? Is this a case of 2 tools (ogr2ogr and qgis) needing the feature making it seem like they all do? Do they actually all need cursors? And in the latter case, why is that exactly? Is it because they're old?

I don't think we've got a comprehensive list anywhere (@awoods187 do you?) -- these are ones that came up that seem popular in either import/export of data (ogr2ogr is the tool) or visualisations (ArcGIS is an extremely well known and used proprietary tool, QGIS is referenced and used heavily from visualisations I've seen on other websites).

[dilip2048]

OK, I tested it and works like a charm. Code is here: https://github.com/tfoldi/tableau_cockroachdb_connector

How do I install it to my tableau?

Just starting using tableau. I want to connect my cockraochDB to tableau and visualize the data on it.

[tfoldi]

Just starting using tableau. I want to connect my cockraochDB to tableau and visualize the data on it.

The process is documented here: https://tableau.github.io/connector-plugin-sdk/docs/share

[xvaara]

Do you have plans to implement cursor support?

[otan]

not yet @xvaara -- do you have a use case to share with us? this helps with prioritisation!

[FranckErnewein]

Hi @otan,
We use tools (stitchdata.com) to synchronise our Postgres databases to our datawarehouse (aws redshift).
We started to migrate services on cockroach but we can not use stitchdata anymore because it scans source database using CURSOR to copy new/updated lines to Redshift.

I hope this will help to prioritize this feature

[xvaara]

I have many triggers, functions etc. so I can't use crdb. But I'd like to use it via postgres_fdw to sync instances and save versioning data. But I'd need to access that directly from postgresql. I'm thinking I'll try to use dblink if this still isn't on the roadmap. I asked about it somewhere few years ago.

[yob]

I also ran into this while experimenting with accessing cockroachdb via the postgres_fdw extension.

I was curious to see if it's possible to use FDW as part of the solution to incrementally migrating from postgres to cockroachdb. Possibly by creating a view that sits on top of the same table in postgres and cockroach, making them look like a single table to users of the database.

I followed the postgres_fdw setup docs and importing the cockroachdb table into postgres appeared to work, however querying it fails. Here's how it looks from the postgres side:

experiment=# \dE
             List of relations
 Schema | Name  |     Type      |  Owner   
--------+-------+---------------+----------
 public | users | foreign table | postgres

experiment=# \d+ users
                                                Foreign table "public.users"
 Column |       Type        | Collation | Nullable | Default |      FDW options      | Storage  | Stats target | Description 
--------+-------------------+-----------+----------+---------+-----------------------+----------+--------------+-------------
 id     | bigint            |           | not null |         | (column_name 'id')    | plain    |              | 
 uuid   | uuid              |           |          |         | (column_name 'uuid')  | plain    |              | 
 email  | character varying |           |          |         | (column_name 'email') | extended |              | 
 name   | character varying |           |          |         | (column_name 'name')  | extended |              | 
Server: cockroachdb
FDW options: (schema_name 'public', table_name 'users')

experiment=# select * from users limit 1;
ERROR:  at or near "declare": syntax error: unimplemented: this syntax
DETAIL:  source SQL:
DECLARE c1 CURSOR FOR
^
HINT:  You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/41412/v20.2
CONTEXT:  remote SQL command: SELECT id, uuid, email, name FROM public.users LIMIT 1::bigint

[rafiss]

I'm closing this since initial DECLARE support will be in v22.1. I've filed issues for additional work on cursors:

#77099
#77100
#77101
#77102
#77103
#77104