Hi,
...
> > "PostgreSQL*" is postgres whith queries inside transactions.
> > But as long as transactions are broken in PostgreSQL you cant use them
in real
> > life (if a query fails inside a transactions block, PostgreSQL
"RollBack"s
> > the whole transaction block, and thats broken. You can not convince me
of anything
> > else).
>
> What do you think _should_ happen when a query fails inside a
> transaction block? (I am not trying to convince you of anything, just
being curious.)
If a query fails it should return an error, and let me decide if I want to
rollback, change my query or continue as nothing happened.
A good example is this:
Make a SELECT to check if the row exist.
If not it make a INSERT, or if its there it make an UPDATE (or something
totally different).
Everything is swell, but when several pids are trying to insert there is a
window of error between the SELECT and INSERT. And the test program
triggered it frequently. What if there were a lot of insert/updated before
and after the failing one (inside this transaction) and it would be ok if
this row was inserted by someone else. The dba does not know about that, *I
do* and can write my program in such a way.
How do you fix that in PostgreSQL! The only way I know of is not to use
transactions. Then if the INSERT fails you can try again with SELECT to
check if the row has been inserted by someone else. And ofcourse you would
need to build your own rollback function.
The "all or nothing approach" ala PostgreSQL is broken!
Nuff about transactions. I do not think I can convince you and you cant
convince me about that they are not. And why do you not check how the other
dbas has solved this. I bet they work as I describe.
// Jarmo
