pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: DB2 feature
Date: Fri, 03 Dec 2004 17:27:14 -0500
Message-ID: <m33bynqapp.fsf@knuth.knuth.cbbrowne.com> (raw)
References: <56141B8E-456B-11D9-9C9F-000A95DE2550@rilk.com>

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!"



view thread (4+ messages)  latest in thread

Message-ID: <m33bynqapp.fsf@knuth.knuth.cbbrowne.com>
Permalink:  ../m33bynqapp.fsf@knuth.knuth.cbbrowne.com/
Also on:    postgresql.org/message-id/m33bynqapp.fsf@knuth.knuth.cbbrowne.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: DB2 feature
  In-Reply-To: <m33bynqapp.fsf@knuth.knuth.cbbrowne.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox