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

* Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> [000203 16:32] wrote:
> The thing is, in the relational model there isn't a standard
> defininition of "already exists". For example, when you say
> "already exists", I presume you mean that a record with the
> same primary key already exists. But not all tables have
> primary keys.

I could adopt the tables to use this particular field as a primary
key, but see my questions about interpreting errors in responce
to suggestion #2.

> There are two things you can do...
> 
> 1) remember if a record came out of the database in the first place
> with a flag. This is what an object database would do.

You mean implement an LRU cache outside the database, I've thought about
this and could actually do it, the thing that bugs me about it is
that i'm essentially trying to outsmart a 10+ year (guessing) old
piece of software with something that I'd have to hack up in a
matter of days.

> 2) If there is a unique index, instead of checking 
> whether the record exists with exists_in_table,
> attempt to update the record. If you get a database error, THEN
> do an insert. This is a common programming technique, often used
> with unix system calls. Try one option and if error try the other.
> Don't try to predict yourself whether an error will occur. This
> will save 1 or two database calls depending on whether it exists
> or not.

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?

-Alfred


> 
> Alfred Perlstein wrote:
> > 
> > We were having some trouble doing updates to our database,
> > a lot of our database sort of works like this:
> > 
> > dbfunc(data)
> >         somedatatype    *data;
> > {
> >         somedatatype    *existing_row;
> > 
> >         existing_row = exists_in_table(data);
> > 
> >         if (existing_row != NULL) {
> >                 update_table(existing_row, count = count + data->count)
> >         } else
> >                 insert_into_table(data);
> > 
> > }
> > 
> > Is there anything built into postgresql to accomplish this without
> > the "double" work that goes on here?
> > 
> > something like:
> >   update_row_but_insert_if_it_doesn't_exist(data,
> >     update = 'count = count + data->count');
> > 
> > Meaning, if a row matching the 'new' data exists, update it, otherwise
> > store our new data as a new record?
> > 
> > It seems like the database has to do an awful amount of extra work
> > for our application because we haven't figured out how to do this
> > effeciently.
> > 
> > Any pointers?
> > 
> > thanks,
> > --
> > -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
> > 
> > ************

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]