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

Re: DB2 feature

From: Pailloncy Jean-Gérard <jg(at)rilk(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: DB2 feature
Date: 2004-12-03 22:48:10
Message-ID: (view raw or whole thread)
Lists: pgsql-performance
>> The listing 2 example:
>> 2     INTO :dist_tax , :next_o_id
>> 4                       SET  D_NEXT_O_ID = D_NEXT_O_ID + 1
>> 5                       WHERE D_W_ID = :w_id
>> 6                         AND D_ID = :d_id
>> 7                     ) AS OT
> A lot of this is non-standard SQL, so I can't really tell what DB2 is  
> doing
> here.  Can you explain it?

Quote from the article at: 
> First, DB2 deals with the DISTRICT table. Data needs to be returned  
> and an update needs to be performed. Conventional wisdom states that  
> this requires 2 SQL statements, and that the UPDATE ought to be done  
> prior to the SELECT; otherwise deadlocks may occur as concurrency  
> increases.
> DB2 however supports a new SQL feature which is in the process of  
> being standardized. This feature allows access to what is known as  
> transition tables in triggers. The OLD TABLE transition table holds  
> the original state of the affected rows before they are processed by  
> the UPDATE or DELETE statement. The NEW TABLE transition table holds  
> the affected rows immediately after an INSERT or UPDATE was processed.  
> That is the state prior to when AFTER triggers fire. Users with a  
> Microsoft or Sybase background may know these tables by the names  

So, if I understand they use only ONE query to get the UPDATE and the  
SELECT of the old value.

Jean-Gérard Pailloncy

In response to

pgsql-performance by date

Next:From: Mike RylanderDate: 2004-12-04 01:53:07
Subject: Re: Alternatives to Dell?
Previous:From: Kiran MukhyalaDate: 2004-12-03 22:31:11
Subject: Performance difference in similar queries

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