Re: update/insert, delete/insert efficiency WRT vacuum

Ühel kenal päeval, T, 2006-07-04 kell 14:53, kirjutas Zeugswetter
Andreas DCP SD:
> > > >Is there a difference in PostgreSQL performance between these two
> > > >different strategies:
> > > >
> > > >
> > > >if(!exec("update foo set bar='blahblah' where name = 'xx'"))
> > > > exec("insert into foo(name, bar) values('xx','blahblah'"); or
>
> In pg, this strategy is generally more efficient, since a pk failing
> insert would create
> a tx abort and a heap tuple. (so in pg, I would choose the insert first
> strategy only when
> the insert succeeds most of the time (say > 95%))
>
> Note however that the above error handling is not enough, because two
> different sessions
> can still both end up trying the insert (This is true for all db systems
> when using this strategy).

I think the recommended strategy is to first try tu UPDATE, if not found
then INSERT, if primary key violation on insert, then UPDATE

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com