Re: implicit abort harmful?

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.

> Example2 - ODBC driver rollbacks :-
>
>  Here is a snippet from a postgres log :-
> 2003-05-24 23:09:14 [1525]   LOG:  duration: 0.134503 sec
> 2003-05-24 23:09:14 [1525]   LOG:  query: select nspname from pg_namespace n, p
> _class c where c.relnamespace=n.oid and c.oid='select'::regclass
> 2003-05-24 23:09:14 [1525]   ERROR:  Relation "select" does not exist
> 2003-05-24 23:09:14 [1525]   LOG:  statement: select nspname from pg_namespace
> , pg_class c where c.relnamespace=n.oid and c.oid='select'::regclass
> 2003-05-24 23:09:14 [1525]   LOG:  query: ROLLBACK
> 2003-05-24 23:09:14 [1525]   LOG:  duration: 0.000538 sec
>
> The rollback here is generated by the odbc driver to clear an error created by
> the odbc driver incorrectly parsing the select from a subselect statement as a
> table name.

Sorry, that's an ODBC problem. For internal stuff it should use queries that
can't fail like that. In this case it should use c.relname = 'select'; that
won't abort if the table don't exist.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington