Re: Support for 2-Phase Commit protocol

Thanks for all the comments you've already sent.

It's simpler than it sounds... I'll give you a practical example, so you
have a better idea of what I need.

Suppose I have a Business Rule (object) that encapsulates a "Delete
Category" operation. That operation consists of:

- Deleting all products in that category.
- Deleting other objects (entities) related to the category.
- Deleting THE category itself.

There is a ProductDAO object with a delete method that the business rule
object will invoke when deleting every instance (record) of a product.
The same concept is applied to the other entities related to the
category. Also, there is CategoryDAO, with the same behaviour.

I want that Business Rule encapsulating a "delete" operation that spans
those "n" DAO objects to be Transactional. That is, if any error occurs
at any point during the process, rollback.

I'm already aware that this is THE TYPICAL situation where you'd use an
O/R mapping tool (Hibernate, OJB... you name it), but, for many reasons,
it's impossible for us to incorporate it into the system's architecture.
Therefore, my intent for - at least - having some (alternative) way for
making the system as transactional as possible. I believe there are only
2 options: plain JDBC transactions (which I'd want to avoid since that
would force me to hardcode, screw up the design) or JTA.

As I mentioned earlier, all transactions involve a single DataSource, a
single JVM. Therefore, no "distributed" transactions.

This is it. Let me know what you think.

Regards,
Freddy.

-----Mensaje original-----
De: Alexander Staubo [mailto:alex(at)byzantine(dot)no]
Enviado el: lunes, 29 de marzo de 2004 22:11
Para: Freddy Villalba Arias
CC: PostgreSQL JDBC Mailing List
Asunto: Re: [JDBC] Support for 2-Phase Commit protocol

on 2004-03-29 19:43 Freddy Villalba Arias wrote:

> I'm currently envolved in a Project requiring v7.4 (7.4.2?). I won't
> be using distributed transaction, but transactions that span multiple
> objects (all of them on the same JVM). Therefore, as I understood it
> from reading the JTA spec, I should not need to use the XA "objects"
> (XAConnection...) but just "plain JDBC"'s ones (Connection,
> Statement...).

What do you mean by multiple objects? Sounds like you have multiple
transactions on a single back end that all need to complete atomically
-- more related to nested transactions and savepoints than to
two-phase commits.

> Then, the following question arises: does the fact that 2-Phase commit

> isn't yet supported imply that I cannot use JTA?

It's been a while since I read the JTA spec, but even if the
transaction manager did not require XA support from the driver, it
still would not be able to glue your transactions together using a
two-phase commit, which is what you want. So the TM would commit each
transaction individually, but it would not be able to roll them back
in the case of mid-point failure.

The point of the XA interfaces is to encapsulate the two-phase
protocol, allowing local transactions to be prepared before they are
committed. Without the XA support, JTA is basically a standard
interface to a transaction manager and X/Open-style transaction
semantics.

I assume Bruce is talking either about this patch:

http://archives.postgresql.org/pgsql-patches/2003-10/msg00273.php

or this work:

http://snaga.org/pgsql/

Alexander.

on 2004-03-29 19:43 Freddy Villalba Arias wrote:

> I'm currently envolved in a Project requiring v7.4 (7.4.2?). I won't
> be using distributed transaction, but transactions that span multiple
> objects (all of them on the same JVM). Therefore, as I understood it
> from reading the JTA spec, I should not need to use the XA "objects"
> (XAConnection...) but just "plain JDBC"'s ones (Connection,
> Statement...).
>
> Then, the following question arises: does the fact that 2-Phase
commit
> isn't yet supported imply that I cannot use JTA? Are these to issues
> unrelated?
>
> I'll look forward for your feedback.
>
> -----Mensaje original-----
> De: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]
> Enviado el: lunes, 29 de marzo de 2004 18:36
> Para: Alexander Staubo
> CC: Freddy Villalba Arias; PostgreSQL JDBC Mailing List
> Asunto: Re: [JDBC] Support for 2-Phase Commit protocol
>
>
> Someone has a 2-phase commit patch and we are looking to get it into
> 7.5.
>
>
>
------------------------------------------------------------------------
> ---
>
> Alexander Staubo wrote:
>
>>This is on the PostgreSQL todo list:
>>
>> http://developer.postgresql.org/todo.php
>>
>>However, as far as I know it's not something people are working
>>actively on, or at least not to the extent that we will see a release
>>in the near term. I would like to be wrong, though.
>>
>>Seeing as this is a server issue and not specifically a JDBC issue,
>>the pgsql-hackers list is probably the place to ask about the
>>development status of future features.
>>
>>To my knowledge, Firebird (http://www.firebirdsql.org/) is the only
>>open-source database that supports two-phase commits; its JDBC driver
>>comes with JTA support. You may have better luck with that. Firebird
>>is itself a decent database, but is going to feel like a step back
>>into the '80s once you've gotten used to PostgreSQL's sleek, modern
>>facilities [*].
>>
>>[*] Variable-length "text" data type that does not need a
pre-declared
>
>
>>size, virtually unlimited row sizes, online alter/drop/rename for
>>tables/columns/etc., ability to index fields declared as >250 chars
>>long, SQL shell with proper readline support, intelligible error
>>messages, mature documentation, I could go on.
>>
>>Alexander.
>>
>>on 2004-03-29 17:19 Freddy Villalba Arias wrote:
>>
>>
>>>Hi everybody,
>>>
>>>
>>>
>>>I forgot to include this other question (also urgent, sorry if I?m
>>>stressing you up): does anybody know what is PostgreSQL?s current
>
> status
>
>>>regarding ?Two Phase Commit Protocol Support??
>>>
>>>
>>>
>>>Again, thanx.
>>>
>>>
>>>
>>>Regards,
>>>
>>>Freddy.
>>>
>>
>>
>>---------------------------(end of
>
> broadcast)---------------------------
>
>>TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>
>