Postgraphile: Allow transaction isolation level to be specified per query/mutation

[omatrot]

I'm submitting a ...

• [ ] bug report
• [X] feature request
• [ ] question

PostGraphile version:

4.0.0-beta.10

I would like to be able to specify the PostgreSQL transaction isolation level per query/mutation. Is that even possible in the query syntax ?

Update : May be using a directive ?

[benjie]

A directive could work; but we'd want it for the entire transaction (not just one field) so I'm thinking a header might be more effective, or metadata alongside the query?

What's your use case for allowing the user to specify the transaction isolation level? That seems risky.

[omatrot]

In my particular case, the idea is to be able to use the Serializable Isolation Level, with retries, to leverage the power of PostgreSQL in that field. This allows you to build highly concurrent systems without blocking.
I'm used to it and honestly this saved my life in a number of use cases, especially when you're dealing with accounting for instance.

[benjie]

Sure; but you can use that server side - you were expressing a desire for the client to be able to specify it on a per-query basis?

Or maybe you mean specifying the transaction level for an entire GraphQL field? The issue there is that PostGraphile (currently) runs the entire request through a single transaction, so we couldn't change it on a per-field basis currently.

[omatrot]

Sorry about that, for me a query is a single round trip to the server. This is fine to have a single transaction for the entire GraphQL field.

[sjmcdowall]

I think we are having a bit of a language definition issue. Let's see if we can simplify the issue by asking some questions:

Q1: Do you expect the client doing the query to indicate for that query the isolation transaction level (ITL) or do you expect the back-end server to be programmed to use a specific isolation level based on "something" ?

Q2: How about mutations?

Q3: If the requestor can indicate the ITL in the query/mutation .. ah .. how? I am not savvy enough with GraphQL to know how one can indicate "non-standard" hints and things of that ilk that aren't normal run of the mill GraphQL ..

Q4: If the logic for the ITL is all server side .. and it's not just a single ITL for everything .. what logic and how to specify the ITL for the query/mutation ??

Not sure these questions help -- but they would certainly help me understand :)

--

[omatrot]

Q1 and Q2, I expect the client to be able to indicate the isolation level.
Q3 : same as you, I do not know how.
Q4: The isolation level remains the same for the whole duration of the transaction.

[benjie]

I think we could do it with a header. I want to be able to change the begin / commit statements anyway as it'll allow for easier integration into tests (i.e. use rollback instead of commit; or maybe even use savepoints). I wouldn't want this in core; but I should be able to make it so you can do it with a small plugin. It's not currently possible, to be clear.

[omatrot]

You mean an HTTP header ? clever idea ! That would be fine by me.

[benjie]

Yeah; e.g.

POST /graphql HTTP/1.1
X-PostGraphile-Isolation-Level: serializable
...

[sjmcdowall]

Quite clever indeed!

[benjie]

I think this should be achieved by adding a few more pluginHook hooks and then enabling this header functionality via a plugin rather than building the header functionality into core. This also enables a few more use-cases like setting additional database parameters or causing rollbacks instead of commits (useful when testing).

[benjie]

Another use case for this: a pre-request function like in https://github.com/graphile/postgraphile/pull/763

Interestingly, this is desired to happen after the pgSettings are set.

An alternative implementation would be to pass the function the pgSettings and expect it to set the settings that are valid (or raise an exception).

Having 7 hooks seems perhaps a bit excessive:

• forceTransaction < hookable to enable you to force a transaction
• BEGIN; < hookable to change to a different isolation level
• < hookable beginning of transaction
• SELECT set_config(....) < hookable to override configuration setting
• < hookable to enable the pre-request function
• [... transaction body ...]
• < hookable to enable a post-request function
• COMMIT; < hookable to enable ROLLBACK/etc.

So I'm thinking:

• forceTransaction, BEGIN and COMMIT could all come from one trigger called up front.
• pgSettings is already hookable via options
• the "post-BEGIN, pre-set_config" hook isn't really necessary
• then just add pre- and post-transaction hooks that we pass the pgClient to.

So this results in 3 new hooks:

1. Called [here], postgraphile:transaction:config would be passed and return {needTransaction, beginStatement, commitStatement, localSettings}; and as it's options would include the pgClient and the PostGraphile options.
2. Called here, postgraphile:transaction:prebody
3. Called here and carefully wrapped in a try/catch block, postgraphile:transaction:finally

I'm not happy with prebody; alternatives I can think of are post-setup, start, post-config, setup but none seem quite right. finally maybe should be changed to match also, e.g. prebody / postbody or start / finish.