Re: Does psql use nested transactions?

> Bruce Momjian
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Added to TODO list:
> >
> > > * Add an option to automatically use savepoints for each
> statement in a
> > > multi-statement transaction.
> >
> > > When enabled, this would allow errors in multi-statement
> transactions
> > > to be automatically ignored.
> >
> > Note that whoever implements this had better be mindful of the bad
> > experience we had with autocommit. If there is any way that this
> > behavior could be turned on underneath an application/script without
> > the app knowing about it, this would be spectacularly dangerous.
> >
> > Like I said, my preference would be that it couldn't be turned on at all
> > except when psql's input is interactive.
>
> Should we allow it only to be enabled inside a multi-statement
> transaction, and it turns off when the transaction completes?
>

Yes, this is a very very good option to have.

This behaviour allows much closer mimicking of Oracle and other RDBMS's
transactional behaviour - also some people (not on this list) have decided
that this is in fact the correct ANSI behaviour for transactions (accepting
that this is unclear, and not wishing to restart that debate).

- It should not apply ONLY to psql, but to all interfaces i.e. it should be
server-side behaviour.

- The option should be available at the session level, so that all
transactions within a session exhibit this behaviour - and not just as an
option within a transaction.

Tom is right to draw analogy to Autocomit, not just because of earlier
fiascos, but also because of the similarity that is required in the way that
the option is set. i.e. it can be manipulated as a session level default,
but is not completely transparent to the user.

These aren't my just preferences, but an attempt at a clear statement of
what is required for compatibility....

Best Regards, Simon Riggs