I'm using postgres SAVEPOINT which create a new savepoint within the current transaction, and would like to display the list of currently defined savepoint in a connection.

To be more precise: I'd like to check which name would NOT trigger the "no such savepoint" error in a connection.

up vote 6 down vote accepted

Interesting question! Short answer: no.

Long answer: there does not appear to be any existing way to get a list of savepoints defined. Even worse, it doesn't seem possible to create a PostgreSQL extension which would let you do this: looking at src/backend/access/transam/xact.c, you can see that functions like RollbackToSavepoint (which is where that "no such savepoint" error message you mentioned comes from) rely on the variable CurrentTransactionState, which is declared static to xact.c, i.e. would not be visible globally to extension code.

Now, if you were daring and quite desperate to generate a list of defined savepoints from the server-side (as opposed to just having your client remember...), you could add in a helper function to xact.c that would display this information for you. In fact, here is just such a patch. That's a very rough patch for illustration purposes only, and just elogs the savepoint names, it should really be returning those names as setof text.

As to why this feature is missing, I surmise that there is simply no plausible use case for a client needing to fetch a list of defined savepoints from the server. What would the client do with this list -- just choose one at random and ROLLBACK to it? ROLLBACK to the last one blindly? AFAICT savepoints are only useful if a client remembers what savepoints it has defined and where they were in order to be able to make use of them.

  • Thanks for this answer. The client should remember of course, but in case of complicated multi-threaded access to one connection, this would be helpful to debug the client code ! And more information possibly accessible is often better than less IMHO. – vaab Sep 15 '14 at 9:22
  • Yes, for running code it does make no sense. But for debugging it would be helpful to see which savepoints are open. Thank you for your answer. – guettli Nov 21 '17 at 14:00

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.