Re: Triggers on columns
Re: Triggers on columns
От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
On Sat, 2009-10-10 at 00:04 +0300, Peter Eisentraut wrote: > On Mon, 2009-09-14 at 18:58 +0900, Itagaki Takahiro wrote: > > Itagaki Takahiro wrote: > > > > > Ok, the attached patch implements standard-compliant version of > > > column trigger. > > > > Here is an updated version of column-level trigger patch. > > I forgot to adjust pg_get_triggerdef() in the previous version. > > pg_dump also uses pg_get_triggerdef() instead of building > > CREATE TRIGGER statements to avoid duplicated codes if the > > server version is 8.5 or later. > > I have committed the parts involving pg_get_triggerdef and pg_dump. I > will get to the actual column trigger functionality next. Attached is a merged up patch with some slightly improved documentation. I think the patch is almost ready now. One remaining issue is, in TriggerEnabled() you apparently check the column list only if it is a row trigger. But columns are supported for statement triggers as well per SQL standard. Check please. Btw., I might not get a chance to commit this within the next 48 hours. If someone else wants to, go ahead.
Triggers on columns
От:
Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>
Дата:
Here is a patch to implement "Support triggers on columns" in our ToDo list.
The syntax is:
CREATE TRIGGER name
BEFORE UPDATE OF col1, col12, ...
ON tbl FOR EACH ROW EXECUTE PROCEDURE func();
I consulted the previous work following:
Column-level triggers (From: Greg Sabino Mullane, Date: 2005-07-04)
http://archives.postgresql.org/pgsql-patches/2005-07/msg00107.php
and completed some under-construction parts.
It's still arguable that we should add dependencies from column
triggers to referenced columns. In the present patch, dropeed
columns are just ignored and always considered as not-modified.
Please grep with "TODO: (TRIGGER)" to check the issue.
Comments welcome.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
Re: Triggers on columns
От:
Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>
Дата:
Here is a updated version of column-trigger patch.
Changes from the previous patch:
* Add dependency of columns with recordDependencyOn().
Regression tests are also adjusted.
* Recheck columns if NEW values are modified, but each trigger
will be firec only one per row.
Peter Eisentraut wrote:
> The SQL standard specifies that a trigger is fired if the column is
> mentioned in the UPDATE statement, independent of whether the value is
> actually changed through the update.
We are discussing how to determine modified columns
(UPDATE-target vs. changes of actual values), but in the patch
I used value-based checking. The reasons are:
1. Other triggers could modify referred columns even if the columns
are not specifed in UPDATE-target.
2. IMHO, almost users don't expect their triggers are not called
if the actual values are not modified.
3. Restriction of implementation; We don't have RTE in trigger
routine for now. The current patch doesn't modify codes a lot.
Comments welcome.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
Re: Triggers on columns
От:
Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>
Дата:
Peter Eisentraut wrote:
> Therefore, it cannot be completely unexpected if column triggers are
> called even if the column was not actually changed in a semantically
> significant way.
Ok, the attached patch implements standard-compliant version of
column trigger.
Retrieving modified columns is not so difficult as I expected.
It is in:
rt_fetch(relinfo->ri_RangeTableIndex, estate->es_range_table)->modifiedCols
and the information are passed from caller to trigger routines.
However, to be honest, I think standard-compliant column trigger is
useless... I'm thinking additional extension for triggers -- if we
want to check modifications of actual values, it could be defined as:
CREATE TRIGGER trig BEFORE UPDATE ON tbl FOR EACH ROW
WHEN (NEW.col <> OLD.col) EXECUTE PROCEDURE trigger_func();
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
Re: Triggers on columns
От:
Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>
Дата:
Itagaki Takahiro wrote: > Ok, the attached patch implements standard-compliant version of > column trigger. Here is an updated version of column-level trigger patch. I forgot to adjust pg_get_triggerdef() in the previous version. pg_dump also uses pg_get_triggerdef() instead of building CREATE TRIGGER statements to avoid duplicated codes if the server version is 8.5 or later. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
Re: Triggers on columns
От:
Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp>
Дата:
Peter Eisentraut wrote: > I think the patch is almost ready now. One remaining issue is, in > TriggerEnabled() you apparently check the column list only if it is a > row trigger. But columns are supported for statement triggers as well > per SQL standard. Check please. I added it. I've missed it because I tried to implement column trigger based on value-comparison, but now we check conditions based on a target-list. Statement column triggers are reasonable. diff-of-column-trigger.patch is a diff from the previous column-trigger.patch. column-trigger_20091014.patch is a full patch to the HEAD. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
