Re: DB2 feature - Mailing list pgsql-performance

Clinging to sanity, jg@rilk.com (Pailloncy Jean-Gérard) mumbled into her beard:
> I see this article about DB2
> http://www-106.ibm.com/developerworks/db2/library/techarticle/dm
> -0411rielau/?ca=dgr-lnxw06SQL-Speed
>
> The listing 2 example:
> 1  SELECT D_TAX, D_NEXT_O_ID
> 2     INTO :dist_tax , :next_o_id
> 3     FROM OLD TABLE ( UPDATE DISTRICT
> 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
>
> I am not a expert in Rule System.
> But I ad a look to
> http://www.postgresql.org/docs/7.4/static/rules-update.html
> And it seems possible in PostgreSQL to build non standard SQL query to
> do thing like listing 2.
>
> I would like to know from an "expert" of PostgreSQL if such query is
> really a new stuff to DB2 as the artcile states ? or if PostgreSQL has
> already the same type of power ?

This feature (which evidently was derived from something in Sybase,
which Microsoft therefore brought into their version of SQL Server)
allows the Gentle User to do a mass update on a table (what's
parenthesized), and then do some manipulations on the rows that ware
affected by that mass update, where OLD TABLE returns the _former_
state of rows that were updated/deleted, and NEW TABLE would return
the _new_ state of rows that were inserted/updated.

It would be possible to do something analagous using rules, but the
implementation would look VERY different from this.

In effect, you would have to add, temporarily, a rule that does the
thing akin to "select d_tax, d_next_o_id into some table" for the
three cases:

 1.  on insert, do something with NEW.D_TAX, NEW.D_NEXT_O_ID
     to correspond to the insert case;

 2.  on update, do something with NEW.D_TAX, NEW.D_NEXT_O_ID to
     correspond with an update, doing something with the NEW values;

 3.  on update, do something with OLD.D_TAX, OLD.D_NEXT_O_ID to
     correspond with an update, doing something with the OLD values;

 4.  on delete, do something with OLD.D_TAX, OLD.D_NEXT_O_ID...

You'd create the a rule to do things row-by-row.

The efficiency of this actually ought to be pretty good; such rules
would be tightly firing over and over each time a row was affected by
the query, and since the data being processed would be in cache, it
would be eminently quickly accessible.

But, compared to the DB2 approach, it involves creating and dropping
rules on the fly...
--
"cbbrowne","@","acm.org"
http://www.ntlug.org/~cbbrowne/linuxdistributions.html
Signs of a Klingon Programmer  - 11. "By filing   this bug report  you
have challenged the honor of my family. Prepare to die!"