Re: implicit abort harmful? - Mailing list pgsql-general

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 #