Re: Replaceing records

On Wed, 2003-09-10 at 00:31, Jan Wieck wrote:
> Ron Johnson wrote:
>
> > On Fri, 2003-09-05 at 08:29, Jan Wieck wrote:
> >> It was not meant against anyone in person and I agree that nested
> >> transactions and/or catchable exceptions and continuing afterwards is
> >> usefull and missing in PostgreSQL. What Stephan and Richard where
> >> actually discussing was more like emulating the REPLACE INTO, and I was
> >> responding to that.
> >>
> >> However, even with nested transactions and exceptions and all that, your
> >> problem will not be cleanly solvable. You basically have 2 choices,
> >> trying the INSERT first and if that fails with a duplicate key then do
> >> the UPDATE, or try the UPDATE first and if no rows got hit do an INSERT.
> >> Now if 2 concurrent transactions do try the UPDATE they can both not
> >> find the row and do INSERT - one has a dupkey error. But if you try to
> >> INSERT and get a duplicate key, in the time between you get the error
> >> and issue the UPDATE someone else can issue a DELETE - the row is gone
> >> and your UPDATE will fail.
> >
> > SERIALIZABLE transactions will solve this.
>
> Sure will they.
>
> Care to elaborate a bit about the side effects of SERIALIZABLE? I mean
> semantics AND performance wise ... people tend to use suggestions like
> this without thinking (about the consequences).

Well, unless INSERT/UPDATE/DELETE transactions are very short, there
will definitely be a performance hit because of increased locking.

However, I prefer that consequence rather than the artifacts from
READ COMMITTED.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

LUKE: Is Perl better than Python?
YODA: No... no... no. Quicker, easier, more seductive.
LUKE: But how will I know why Python is better than Perl?
YODA: You will know. When your code you try to read six months
from now.