Re: [HACKERS] how to deal with sparse/to-be populated tables

>This is what I was thinking, the problem then becomes that I'm
>not aware of way to determine the error with
>some degree of accuracy so that I don't mistake:
> insert error because of duplication
>with:
> insert error because of database connectivity (or other factors)
>
>Is it possible to do that?  I guess I could parse the error responce
>from the backend, but maybe there's an easier/more-correct way?

Not sure what interface you are using,  But for example, perl will
easily tell the difference.

========================================================================      execute
            $rv = $sth->execute                || die $sth->errstr;            $rv = $sth->execute(@bind_values)  ||
die$sth->errstr;
 
          Perform whatever processing is necessary to execute          the prepared statement.  An undef is returned if
an         error occurs, a successful execute always returns true          regardless of the number of rows affected
(evenif          it's zero, see below). It is always important to check          the return status of execute (and most
otherDBI          methods) for errors.
 
          For a non-select statement, execute returns the number          of rows affected (if known). If no rows were
affected         then execute returns "0E0" which Perl will treat as 0          but will regard as true. Note that it
isnot an error          for no rows to be affected by a statement. If the          number of rows affected is not known
thenexecute          returns -1.
 
========================================================================

which means the return value will be 0 if the insert is blocked, but
undef in there is a connectivity error.

In other words, failing to insert where a unique index prevents the
insertion is not an error.

PHP is similar.

One trick is to insert all tuple into a temporary table.  Then do an
update using the natural join.  The do the insert from that same
table.

If you can use a copy to create the temporary table, I think your
performance will be best.

Typically I would index the primary key of the temp table so that the
join proceeds well, but you may want to bench yourself with and
without the index.  I don't think it's needed in the case you
describe.

-- 
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com  - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net