Can't set transaction isolation level with `withPgClientTransaction`?

[the-sun-will-rise-tomorrow]

Summary

In my plugin implementing a mutation, I tried doing:

  // ...

  plans: {
    Mutation: {
      submit(_, fieldArgs) {
        const $input = fieldArgs.getRaw("input");
        const executor =
          build.input.pgRegistry.pgResources["versions"].executor;

        const $result = withPgClientTransaction(
          executor,
          $input,
          async (pgClient, input) => {
            await pgClient.query({
              text: "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;",
            });

            // ...

However, this seems to throw an error: SET TRANSACTION ISOLATION LEVEL must be called before any query.

What query could have been called if I'm running SET TRANSACTION as the very first thing inside the withPgClientTransaction callback?

The GraphQL query contains calls to other mutations before this one. Could that have something to do with this?

Could there perhaps be another transaction active when withPgClientTransaction is called? Is there a top-level transaction for all mutations done by one GraphQL call? (If so, what is the point of withPgClientTransaction if everything happens in a transaction anyway?)

Also, do queries run in a transaction? Or, more specifically: which of the "phenomena" - as described on https://www.postgresql.org/docs/current/transaction-iso.html - are possible within a single GraphQL query?

Additional context

PostGraphile 5.0.0-beta.40 with PostgreSQL 16.4.

[benjie]

begin;
select set_config(...);

/* your SQL here */

commit;

All the pgSettings are set within the transaction as the first thing.

[benjie]

what is the point of withPgClientTransaction if everything happens in a transaction anyway

withPgClientTransaction ensures that there is a transaction. withPgClient only creates a transaction if it needs to, e.g. because it needs to set pgSettings. (Even then, transaction is technically optional - you could set the options and then reset all; after - it's just how we do it currently.)

which of the "phenomena" - as described on postgresql.org/docs/current/transaction-iso.html - are possible within a single GraphQL query?

Depends on your default transaction isolation level.

[benjie]

The GraphQL query contains calls to other mutations before this one. Could that have something to do with this?

No, each mutation (and sometimes multiple operations within the same mutation) occur in their own transaction - and potentially with different Postgres clients altogether.

[the-sun-will-rise-tomorrow]

Thanks,

Based on the above, I expected this to work:

// graphile.config.ts

const preset: GraphileConfig.Preset = {
  // ...
  grafast: {
    async context(requestContext, args) {
      return {
        pgSettings: {
          "transaction_isolation": "serializable",
          ...args.contextValue?.pgSettings,
        },
      };
    },
  },
};

export default preset;

but it produces the same error.

Is there a way to set the transaction isolation level for PostGraphile transactions (especially for queries)?

[benjie]

I've not tried, but maybe:

const pool = new pg.Pool({ ... });
pool.on('connect', client => {
  client.query("set default_transaction_isolation to 'serializable';");
  client.on('error', () => {});
});
pool.on('error', () => {});

then pass that pool to PostGraphile via the pgServices.

[the-sun-will-rise-tomorrow]

Works great, thank you very much!

[benjie]

Based on the above, I expected this to work:

// graphile.config.ts

const preset: GraphileConfig.Preset = {
  // ...
  grafast: {
    async context(requestContext, args) {
      return {
        pgSettings: {
          "transaction_isolation": "serializable",
          ...args.contextValue?.pgSettings,
        },
      };
    },
  },
};

export default preset;

Yeah... It doesn't work because of the select set_config(...), set_config(...), ... is my guess. But we could add code to specifically pull out certain settings and issue them via SET statements instead. This might be the only one that actually needs that TBH. This'd be a nice simple feature for someone to add, hopefully.