pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDB2 feature
4+ messages / 3 participants
[nested] [flat]
* DB2 feature
@ 2004-12-03 20:38 Pailloncy Jean-Gérard <jg@rilk.com>
0 siblings, 2 replies; 4+ messages in thread
From: Pailloncy Jean-Gérard @ 2004-12-03 20:38 UTC (permalink / raw)
To: pgsql-performance
Hi
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 ?
Cordialement,
Jean-Gérard Pailloncy
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DB2 feature
@ 2004-12-03 21:02 Josh Berkus <josh@agliodbs.com>
parent: Pailloncy Jean-Gérard <jg@rilk.com>
1 sibling, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2004-12-03 21:02 UTC (permalink / raw)
To: pgsql-performance; +Cc: Pailloncy Jean-Gérard <jg@rilk.com>
Jean-Gerard,
> 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
A lot of this is non-standard SQL, so I can't really tell what DB2 is doing
here. Can you explain it?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DB2 feature
@ 2004-12-03 22:27 Christopher Browne <cbbrowne@acm.org>
parent: Pailloncy Jean-Gérard <jg@rilk.com>
1 sibling, 0 replies; 4+ messages in thread
From: Christopher Browne @ 2004-12-03 22:27 UTC (permalink / raw)
To: 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!"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DB2 feature
@ 2004-12-03 22:48 Pailloncy Jean-Gérard <jg@rilk.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: Pailloncy Jean-Gérard @ 2004-12-03 22:48 UTC (permalink / raw)
To: pgsql-performance
>> 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
>
> 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:
http://www-106.ibm.com/developerworks/db2/library/techarticle/dm
-0411rielau/?ca=dgr-lnxw06SQL-Speed
> 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
> DELETED and INSERTED.
So, if I understand they use only ONE query to get the UPDATE and the
SELECT of the old value.
Cordialement,
Jean-Gérard Pailloncy
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-12-03 22:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-03 20:38 DB2 feature Pailloncy Jean-Gérard <jg@rilk.com>
2004-12-03 21:02 ` Josh Berkus <josh@agliodbs.com>
2004-12-03 22:48 ` Pailloncy Jean-Gérard <jg@rilk.com>
2004-12-03 22:27 ` Christopher Browne <cbbrowne@acm.org>
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