Nested transactions

On Sun, Oct 11, 2009 at 8:41 PM, Bill Todd <pg@dbginc.com> wrote:

Does PostgreSQL support nested transactions as shown below?

BEGIN;
 ...do some stuff...
 BEGIN;
  ...more stuff...
 COMMIT;
COMMIT;

Postgresql uses savepoints.

Bill Todd wrote:

Does PostgreSQL support nested transactions as shown below?

BEGIN;
...do some stuff...
BEGIN;
...more stuff...
COMMIT;
COMMIT;

no, but in recent versiosn, you can use SAVEPOINT to achieve much the
same effect.

http://www.postgresql.org/docs/current/static/sql-savepoint.html

On Sun, Oct 11, 2009 at 07:41:54PM -0700, Bill Todd wrote:

Does PostgreSQL support nested transactions as shown below?

BEGIN;
...do some stuff...
BEGIN;
...more stuff...
COMMIT;
COMMIT;

It depends what you want to have happen when the outer transaction
rolls back. If you want all the sub-commits to roll back, use
SAVEPOINTs. If you don't, you'll have to write something in an
untrusted PL that uses a separate database connection.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Scott Marlowe wrote:

On Sun, Oct 11, 2009 at 8:41 PM, Bill Todd <pg@dbginc.com> wrote:

Does PostgreSQL support nested transactions as shown below?

BEGIN;
...do some stuff...
BEGIN;
...more stuff...
COMMIT;
COMMIT;

Postgresql uses savepoints.

Savepoints do not provide the same functionality as nested or parallel
transactions because you cannot commit a savepoint. If I need a second
transaction I can always open a second connection so it is easy to get
the behavior I need. Thanks.

Bill

On Mon, 2009-10-12 at 16:18 -0700, Bill Todd wrote:

Savepoints do not provide the same functionality as nested or parallel
transactions because you cannot commit a savepoint.

What does it mean to "commit" a subtransaction or savepoint? What can
you do with a subtransaction that you can't do with a savepoint?

Regards,
Jeff Davis

Jeff Davis escribi�:

On Mon, 2009-10-12 at 16:18 -0700, Bill Todd wrote:

Savepoints do not provide the same functionality as nested or parallel
transactions because you cannot commit a savepoint.

What does it mean to "commit" a subtransaction or savepoint? What can
you do with a subtransaction that you can't do with a savepoint?

What Bill wants is an "autonomous" transaction. We don't have those yet
(except thru use of dblink or database connections inside a function.)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Oct 12, 2009 at 5:18 PM, Bill Todd <pg@dbginc.com> wrote:

Scott Marlowe wrote:

On Sun, Oct 11, 2009 at 8:41 PM, Bill Todd <pg@dbginc.com> wrote:

Does PostgreSQL support nested transactions as shown below?

BEGIN;
 ...do some stuff...
 BEGIN;
  ...more stuff...
 COMMIT;
COMMIT;

Postgresql uses savepoints.

Savepoints do not provide the same functionality as nested

Yes they do. There was a REALLY long discussion on the subject in the
hackers list I believe that led to pgsql supporting savepoints but
not nested transactions

or parallel transactions

That's something entirely different and a question you did not ask