In Postgres, how to get a list of current defined savepoint?

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.

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.

StackExchange Database Administrators Q#75474, answer score: 13