Re: Replaceing records
От | Jan Wieck |
---|---|
Тема | Re: Replaceing records |
Дата | |
Msg-id | 3F57F731.8070901@Yahoo.com обсуждение исходный текст |
Ответ на | Re: Replaceing records (Richard Ellis <rellis9@yahoo.com>) |
Ответы |
Re: Replaceing records
(Csaba Nagy <nagy@ecircle-ag.com>)
|
Список | pgsql-general |
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@Yahoo.com #
В списке pgsql-general по дате отправления: