Re: Replaceing records

Whatever you guy's try or suggest, it's doomed to suffer.

The whole problem stems from using a non-standard feature. And in my
opinion MySQL's "REPLACE INTO" is less a feature or extension to the
standard than more another stupid and lesser thought through addition of
apparently speed gaining crap at the cost of proper design.

One possible reason why this sort of "feature" was left out of the SQL
standard could be that the source of an ID, that is supposed to be
unique in the end, should by default ensure it's uniqueness. Defining a
column UNIQUE is a last line of defense, and aborted actions because of
constraint violation should be the exception, not the normal mode of
operation. If it's the DB to ensure uniqueness, it has to generate the
ID and one can use a sequence. If it's the application to generate it,
the application should know if this is an INSERT or an UPDATE.

Wherever one is using this "REPLACE INTO" language violation, the client
application or even something in front of it is generating ID's but it's
not sure if it is sending down a new or existing one. The real question
is "why is this piece of garbage unable to tell the ID is newly created
or has to exist already?"

I don't think there should be a way to subsitute this. Fix the
application design instead.

Jan

Richard Ellis wrote:

> On Thu, Sep 04, 2003 at 12:29:17PM -0700, Stephan Szabo wrote:
>>
>> On Thu, 4 Sep 2003, Richard Ellis wrote:
>>
>> > On Thu, Sep 04, 2003 at 12:17:35PM +0200, Csaba Nagy wrote:
>> > > [philosophical post regarding a missing feature of Postgres]
>> > >
>> > > I found there's no way to avoid failed inserts because of
>> > > unique constraint violations, causing automatic roll-back of
>> > > the running transaction.
>> > >
>> > > Now contention on insert has a quite high probability for this
>> > > operation in our application.
>> >
>> > Did you ever try this:
>> >
>> > insert into test (a, b, c, d)
>> > (select 1, 2, 3, 4 where not exists
>> > (select 1 from test where a=1 and b=2 and c=3 and d=4)
>> > );
>> >
>> > If your table contains a=1, b=2, c=3, and d=4, nothing will
>> > happen, and there will be no failed transaction. If your table
>> > does not contain a=1, b=2, c=3, and d=4, you'll get an insert of
>> > a row containing 1, 2, 3, 4.
>>
>> Unfortunately that doesn't work if two transactions want to insert
>> a row containing 1,2,3,4 that are running concurrently.
>
> True, if the row does not already exist. But in that situation,
> because of the unique constraint premise in the original quote, there
> is always going to be at least one failed transaction. So the battle
> is already lost before it's even begun.
>
> If, however, the same row already exists in the table, then both of these
> inserts will silently do nothing, and both transactions will continue
> without aborting.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #