Cockroach: sql: support SET LOCAL and txn-scoped session variable changes

[knz]

https://www.postgresql.org/docs/10/sql-set.html

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not. A special case is SET followed by SET LOCAL within a single transaction: the SET LOCAL value will be seen until the end of the transaction, but afterwards (if the transaction is committed) the SET value will take effect.

The effects of SET or SET LOCAL are also canceled by rolling back to a savepoint that is earlier than the command.

[coco98]

Hi folks! Keen on having this supported. If possible, would love to know when this when this might land (roughly ofcourse).

[knz]

What about you start by explaining why this is important to you:

• why do you need this?
• what is the use case?
• what other options have you explored? what didn't work?

[coco98]

Sure! I'm one of the creators of Hasura and was exploring what it would take to support cockroach. :)

Our access control layer uses SET LOCAL for passing session variables (derived from the HTTP request) as context to some triggers (defined on "updatable" views in postgres).

An option on our end is to not use SET LOCAL, but that would make it harder to some of the underlying Postgres magic.

We can go into more detail if relevant to this issue.

[coco98]

Also, just wanted to add a note to the team that having the error message while connecting with a postgres client to point to the right issue on github is amazingly helpful. Thanks!

[knz]

Thank you for your answer and your kind words!

1) So I think it's unreasonable to focus on this one feature before you've made a larger-scale inventory of all the postgres features that your app/framework uses. Enabling tracing in postgres to create a SQL trace would be the first step, then go through that trace in CockroachDB to see what's supported and what isn't

2) CockroachDB does not support triggers nor updatable views. It would be interesting to explore the "larger picture" and see what the framework is trying to achieve.

3) CockroachDB does not support WITH RECURSIVE (recursive common table expressions, see #21085) which are commonly used for graph databases.

Given points 2 and 3, maybe SET LOCAL is not the main concern here. Maybe a better strategy would be to create a dedicated CockroachDB back-end for Hasura.

Let us know what you think!

[coco98]

Thanks for your prompt response and for the breakdown. Will get back on this thread with any relevant thoughts!

[nadilas]

@coco98 @knz has there been any consensus on whether cockroachdb will support hasura or not?

[knz]

@nadilas this is not the right place to discuss hasura support. This issue is specifically about the SET LOCAL statement in SQL.

(You should post a comment on the hasura repository, not here.)

[rafiss]

@nadilas you may be interested in following this issue: https://github.com/hasura/graphql-engine/issues/678

[chriscasano]

FWIW - A customer and myself investigated using PostgREST with CockroachDB. We both ran into this issue today:

{"hint":"You have attempted to use a feature that is not yet implemented.\nSee: https://github.com/cockroachdb/cockroach/issues/32562","details":"source SQL:\nset local schema ''\n ^","code":"0A000","message":"at or near \"schema\": syntax error: unimplemented: this syntax"}
Attempting to connect to the database...