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 (even if
it's zero, see below). It is always important to check
the return status of execute (and most other DBI
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 is not an error
for no rows to be affected by a statement. If the
number of rows affected is not known then execute
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(at)alert(dot)infoplease(dot)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