Skip site navigation (1) Skip section navigation (2)

Re: REPLACE INTO table a la mySQL

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Dale Johnson <djohnson(at)mi(dot)ab(dot)ca>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: REPLACE INTO table a la mySQL
Date: 2001-06-06 15:00:11
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Dale Johnson wrote:
> I know we're not in the business of copying mySQL,
> but the REPLACE INTO table (...) values (...) could be
> a useful semantic.  This is a combination INSERT or
> UPDATE statement.  For one thing, it is atomic, and
> easier to work with at the application level.  Also
> if the application doesn't care about previous values,
> then execution has fewer locking issues and race
> conditions.
> comments?

    First  it's  not  standard  SQL, so chances aren't that good.
    Second, how do you think the  system  should  behave  in  the
    following case:

    * Table  A  has  one  trigger BEFORE INSERT doing some checks
      plus inserting a row into table newA and updating a row  in
      table  balanceA.   It  also  has triggers BEFORE UPDATE and
      BEFORE DELETE that update balanceA.

    * Now we do your REPLACE INTO

    The problem is that in a concurrent multiuser environment you
    cannot  know  if  that  row  exists until you actually do the
    insert (except you lock the  entire  table  and  check  for).
    Since  there's  a  BEFORE  trigger  which  potentially  could
    suppress the INSERT, you can't do the insert  before  fireing
    it. Now it has been run, did it's inserts and updates and the
    statement must be converted into an UPDATE  because  the  row
    exists - how do you undo the trigger work?

    I  know,  mySQL  doesn't have triggers, referential integrity
    and all that damned complicated stuff. That's why it can have
    such a powerful non-standard command like REPLACE INTO.



# 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 #

Do You Yahoo!?
Get your free address at

In response to


pgsql-hackers by date

Next:From: Peter EisentrautDate: 2001-06-06 15:00:54
Subject: Re: Can the backend return more than one error message per PQexec?
Previous:From: Alessio BragadiniDate: 2001-06-06 14:53:43
Subject: Strange error, probably WAL-related

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group