Re: Problem with aborting entire transactions on error

On Tue, Dec 11, 2012 at 1:15 AM, David Johnston <polobo(at)yahoo(dot)com> wrote:
>> -----Original Message-----
>> From: Zbigniew [mailto:zbigniew2011(at)gmail(dot)com]
>> Sent: Monday, December 10, 2012 6:26 AM
>> To: David Johnston
>> Subject: Re: [GENERAL] Problem with aborting entire transactions on error
>>
>> No idea, why common belief is "allowing the user to decide is unwise".
>> Is the "common assumption", that the users of Postgres are children
>> 4-5 years old?
>
> If it requires coding something to provide the user the desired flexibility
> then whether or not such flexibility is wise or unwise is going to go into
> said decision.

There's a fundamental difference here between PostgreSQL and, for
example, MySQL. With PG, you get a well-built database and your
application is permitted to access it in accordance with the database
admin's rules. With MySQL, your application stores data. There's a
vastly different focus there, and that's why you feel that PostgreSQL
is treating you like a child. The fact is that your application is NOT
in command here; the database is. You cannot violate the database's
rules (eg constraints, permissions lists, etc) in the application.
That means that you can be confident that those constraints will
always be followed!

The other part of the problem is that you're using transactions for
something other than transactional integrity. You're batching up
transactions in order to improve performance. That's certainly
possible, but you're using the database "oddly", so you can't expect
it to behave according to your modified set of rules. That's why the
transaction functions like a transaction, not like a commit-point.

I second the recommendation to look into savepoints. Chances are
that's all you need to do to make this work, but I wouldn't gamble my
job/reputation on it being perfect; so... have backups. Make sure you
can start the whole job over if need be.

ChrisA