Re: implicit abort harmful? - Mailing list pgsql-general
From | Wayne Armstrong |
---|---|
Subject | Re: implicit abort harmful? |
Date | |
Msg-id | 200305301954.h4UJst7v022537@mail.bacchus.com.au Whole thread Raw |
In response to | Re: implicit abort harmful? (Jan Wieck <JanWieck@Yahoo.com>) |
List | pgsql-general |
** 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
pgsql-general by date: