Search code examples
sqlpostgresqlpostgraphilesavepoints

Is there any chance of `ROLLBACK TO SAVEPOINT` erasing data in PostgreSQL?

Question

When working with SQL, I am familiar with preparing SQL statements, and then either committing them if nothing goes wrong, or bailing out if something does (which to my understanding leaves the database unchanged).

I am now however in a position where I think I need to use SAVEPOINT instead (using postgraphile/postgreSQL - see context below). However, if you run:

await someSqlClient("SAVEPOINT blah");

try {
  ...
} catch (e) {
  await someSqlClient("ROLLBACK TO SAVEPOINT blah");
  ...
} finally {
  await someSqlClient("RELEASE SAVEPOINT blah");
}

...Does that run the risk of erasing any other SQL which might have been done at the same time? e.g. maybe this query takes 5 seconds, and in the meantime, say either a few other connections hit my API and each change the database in some way, or maybe I run another query on this same API connection which also changes the database.

I'm not hugely experienced in SQL so I might be misunderstanding something here. e.g. possibly connections might be isolated from each other, and rolling back to a savepoint doesn't deleted any data committed in any other queries/on other API connections?

Context

I am using Postgraphile (which itself uses PostGreSQL) to construct a GraphQL endpoint, and am using savepoints similar to here https://postgraphile.org/postgraphile/current/make-extend-schema-plugin#mutation-example. My resolver needs to run some more GraphQL inside (as is done here https://postgraphile.org/postgraphile/current/usage-schema#calling-a-resolver-from-a-resolver), which, by my understanding, commits the resulting sql i.e. you have no option to "prepare" a statement in GraphQL. I therefore need to use savepoints, like in the example.

Solution

  • Don't worry. The savepoint concerns only data from your own session and your own transaction. If you ROLLBACK TO SAVEPOINT, all that happened in your current transaction since the savepoint was set will be undone.

    The documentation does not spell that out in that level of detail, I guess because the authors assume that it is evident that a savepoint is part of your transaction. It is evident as soon as you understand that savepoints are implemented as subtransactions of your current transaction.

    See the documentation of the internals for some implementation details.