Re: Begin a transaction on a SAVEPOINT that is outside any transaction

On Mon, Nov 22, 2021 at 4:50 AM Gurjeet Singh <gurjeet(at)singh(dot)im> wrote:
> Instead of throwing an error, if we allowed it such that a SAVEPOINT
> outside a transaction implicitly started a transaction, and the
> corresponding ROLLBACK TO or RELEASE command finished that
> transaction, I believe it will provide a uniform behavior that will
> let SAVEPOINT be used on its own, to the exclusion of BEGIN, and I
> believe the users will find it very useful as well.

I think I would find this behavior confusing.

> For example, I was looking at a SQL script that is self-contained
> (creates objects that it later operates on), but did not leverage
> Postgres' ability to perform DDL and other non-DML operations inside a
> transaction. My first instinct was to enclose it in a BEGIN-COMMIT
> pair. But doing that would not play well with the other SQL scripts
> that include/wrap it (using, say, \include or \ir). So the next
> thought that crossed my mind was to wrap the script in a
> SAVEPOINT-RELEASE pair, but that would obviously fail when the script
> is sourced on its own, because SAVEPOINT and RELEASE are not allowed
> outside a transaction.

I don't find this a compelling argument, because it's an extremely
specific scenario that could also be handled in other ways, like
having the part that's intended to run in its own subtransaction in
one file for the times when you want to run it that way, and having a
wrapper script file that does BEGIN \ir END when you want to run it
that way. Alternatively, I imagine you could also find a way to use
psql's \if.

--
Robert Haas
EDB: http://www.enterprisedb.com