Re: Transaction vs. Savepoints

Tom Lane wrote:
> Andreas <maps(dot)on(at)gmx(dot)net> writes:
>
>> As far as I know, PG handles 1 transaction per connection an several
>> savepoints boxed within.
>>
>
> One transaction *at a time*, not one for the whole life of the session.
>
Err .. That was what I meant. ;-)

> A good general design rule is that no transaction should stay open for
> very long: do what you need to do and commit.
I understand this in regards to a programms that run queries in a
certain order e.g. like a script without user interaction.
There I do "this", then "that" and afterwards "something else".

A interactive application on the other hand would open records and have
them in an edit-form as long as the user needs them.
Probaply I just don't get it how to use transactions in such a
interactive scenario the right way.

I have classes that do the data handling and talk to the DB.
So a form would instanciate such a class and present its data to get edited.
Some time later the user might klick OK and the form puts the changed
data back into the object and lets it store the changes in the DB.
Would you begin the transaction with the instantiacion of the object and
put the commit in the destructor or BEGIN right where the form got
opened and COMMIT in the close of the form?

What if I enter a new record (customer) then attach some records that
reference this new customer like a phone notes or a document history.
How could I do this without nested transactions?

> So the scenarios you're
> envisioning are not relevant to a well-designed application.
>
Is there documentation that describes how to do such a good design?
I figure this is an issue that is not bound to PostgreSQL specifically.