Re: implicit abort harmful?

** Reply to message from Jan Wieck <JanWieck@Yahoo.com> on Fri, 30 May 2003
15:40:43 -0400
Hi Jan,
 I'm grabbing source right now :)
 That ought to be enough to shut me up for a couple months :)

Regards,
Wayne

> Wayne,
>
> yes, I totally agree. Will you implement UNDO for that and if so, how do
> you propose to get rid of the to be removed index tuples without the
> risk of deadlocking during UNDO? Or is there some other possibility we
> have overlooked so far?
>
> With subtransactions I didn't mean application controlled ones, but
> automatic ones that allow the system to return to the state at the
> beginning of the failing command. It's not just your little INSERT
> problem that needs to be covered. If you want the system to continue
> after an ERROR without rollback, you need to undo every triggered action
> done during this statement before the ERROR occured. Rule generated
> additional queries run before the failing one, triggers, whatnot.
>
> So please, do you have a context diff, do you have a detailed
> implementation proposal, or are you just waving hands telling us what
> you think the user visible behaviour should be?
>
>
> Jan
>
> Wayne Armstrong wrote:
> > ** Reply to message from Jan Wieck <JanWieck@Yahoo.com> on Fri, 30 May 2003
> > 15:06:01 -0400
> > Hi,
> >  Subtransactions here are likely as onerous as the solution that I bet gets
> > used most of the time in this scenario, that is to commit after every insert.
> > And, it's not a matter of good or bad coding here. There are cases where the
> > response to an sql or insert error should be a rollback. There are as many
> > cases where (given that the application is informed there was a problem), the
> > problem can safely be ignored. Again, the decision should be upto the
> > application not the database manager. It is the dbms perogative to reject the
> > statement, not the transaction.
> >
> > Regards,
> > Wayne
> >
> >> Wayne Armstrong wrote:
> >> > ** Reply to message from Jan Wieck <JanWieck@Yahoo.com> on Thu, 29 May 2003
> >> > 20:25:56 -0400
> >> > Hi Jan,
> >> >  Yup, staging tables are probably going to be the answer in this case.
> >> >  However, my point is, you shouldn't need workarounds for this. (or for the
> >> > more general insert on failure update on failure ignore type of logic used in a
> >> > lot of import scenarios)
> >> >  Taking the decision if or not to rollback a transaction (sql error or no) away
> >> > from the application, and enforcing it in the dbm, results in kluges,
> >> > workarounds, and a generally less robust dbm.
> >>
> >> You obviously didn't search the mail archives too much, did you? If
> >> INSERT ... SELECT ... fails half way through due to a duplicate key
> >> error - how to get rid of the so far inserted tuples?
> >>
> >> This problem is well known, has been often discussed and is yet not
> >> solveable because we do not have subtransactions. They are coming, but I
> >> don't know if the proposed implementation will cope well with 120
> >> million single INSERTs each running in it's own subtransaction.
> >>
> >> And I disagree with your last statement. It doesn't result in a less
> >> robust dbm, it results in more complex applications that (if written by
> >> more primitive coders) will instruct a perfectly robust dbm to do the
> >> wrong thing to the data - from a business point of view.
> >>
> >>
> >> Jan
> >>
> >> >
> >> > Regards,
> >> > Wayne
> >> >
> >> >> Martijn van Oosterhout wrote:
> >> >> > On Sun, May 25, 2003 at 10:04:30AM +1000, Wayne Armstrong wrote:
> >> >> >
> >> >> >> During import of 120 thousand records from an isam file system, 3 say records
> >> >> >>fail integrity checks ( files in non-database systems tend not to have
> >> >> >>referential integrity implemented on them except at an application level
> >> >> >>(meaning not at all :). The desired result is to drop the records failing
> >> >> >>integrity checks.
> >> >> >
> >> >> >
> >> >> > Yes, I have that problem too. I actually wrote a script that took an input
> >> >> > file and automatically reissued queries that succeeded but got rolled-back.
> >> >> > I called it quickloader.pl :)
> >> >> >
> >> >> > Probably the biggest problem is that you can't use COPY to load the data.
> >> >> > I've thought about loading into another table and transferring later but I
> >> >> > havn't sorted out the details.
> >> >>
> >> >> The general idea would be to setup a table that has exactly the same
> >> >> structure as the final target table, but with no constraints at all. As
> >> >> long as your data passes all input functions you can even COPY it in.
> >> >>
> >> >> Now you run check queries that show you all tuples in that staging table
> >> >> that would fail constraints on the final table. Fix those and you can do
> >> >>
> >> >>      INSERT INTO final SELECT * FROM staging;
> >> >>
> >> >> If step one fails because of data that doesn't pass the input functions
> >> >> of our data types, you have to go through another level of staging with
> >> >> a table that has text fields only and move it by explicit casting after
> >> >> cleaning up those problems.
> >> >>
> >> >>
> >> >> Jan
> >> >>
> >> >> --
> >> >> #======================================================================#
> >> >> # It's easier to get forgiveness for being wrong than for being right. #
> >> >> # Let's break this rule - forgive me.                                  #
> >> >> #================================================== JanWieck@Yahoo.com #
> >>
> >>
> >>
> >> --
> >> #======================================================================#
> >> # It's easier to get forgiveness for being wrong than for being right. #
> >> # Let's break this rule - forgive me.                                  #
> >> #================================================== JanWieck@Yahoo.com #
>
>
>
> --
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly