PostgreSQL Question posted in
The official documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Due to the inappropriate terminology of PostGreSQL which does not follow the SQL language standard, the term "subtransaction" should not be understood in the sense of subtransaction, but of partial transaction as noted in the SQL standard ISO/IEC 9075-2 (foundation) of which here is a copy of the extract.

    SAVEPOINT as defined in the ISO SQL standard

    This means that it is possible to cancel the end of a transaction by going back to any point in time referenced by a SAVEPOINT, but the transaction will always start at the moment of the START TRANSACTION (or BEGIN TRANSACTION, both commands being admitted ) and not as one might think, a cancellation of part of the transaction, for example between two SAVEPOINTs….

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search