Thread: Referencing OLD/NEW Rows on Trigger Definition

I am currently working on a research project at University of Ottawa. 
As part of the project, we need to implement a new feature for the
trigger creation in
PostgreSQL, which defines aliases for the "old" and "new" data by
adding
the REFERENCING clause to the CREATE TRIGGER statement.  Based on my
research, the following items need to be modified to support the
REFERENCING option.

- modify backend\parser\gram.y and keyword.c
- modify CreateTrigStmt node struct
- modify pg_trigger struct in pg_trigger.h, Trigger and TriggerDesc
structs in rel.h, TriggerData struct in trigger.h
- modify CreateTrigger(), RelationBuildTriggers(), InsertTrigger(),
CopyTriggerDesc(), FreeTriggerDesc(), ExecBSInsertTriggers(),
ExecASInsertTriggers(), ExecBRInsertTriggers(),
ExecARInsertTriggers(), ExecBSDeleteTriggers(),
ExecASDeleteTriggers(), ExecBRDeleteTriggers(),
ExecARDeleteTriggers(), ExecBSUpdateTriggers(),
ExecASUpdateTriggers(), ExecBRUpdateTriggers(), ExecBRUpdateTriggers()
- to handle the new option, create new fuctions
ExecBSOInsertTriggers(), ExecASOInsertTriggers(),
ExecBROInsertTriggers(), ExecAROInsertTriggers(),
ExecBSNInsertTriggers(), ExecASNInsertTriggers(),
ExecBRNInsertTriggers(), ExecARNInsertTriggers(),
ExecBSODeleteTriggers(), ExecASODeleteTriggers(),
ExecBRODeleteTriggers(), ExecARODeleteTriggers(),
ExecBSNDeleteTriggers(), ExecASNDeleteTriggers(),
ExecBRNDeleteTriggers(), ExecARNDeleteTriggers(),
ExecBSOUpdateTriggers(), ExecASOUpdateTriggers(),
ExecBROUpdateTriggers(), ExecBROUpdateTriggers(),
ExecBSNUpdateTriggers(), ExecASNUpdateTriggers(),
ExecBRNUpdateTriggers(), ExecBRNUpdateTriggers()
- add supports to ExecutePlan(), ExecInsert(), ExecDelete(),
ExecUpdate() in \backend\executor\execMain.c
- add supports to backend\node\equalfuncs.c and copyfuncs.c

Do I miss anything?  Could anyone give me your advices on the
implementation?

Thanks.


Henry

henryyipca@yahoo.ca (Henry) writes:
> As part of the project, we need to implement a new feature for the
> trigger creation in
> PostgreSQL, which defines aliases for the "old" and "new" data by
> adding
> the REFERENCING clause to the CREATE TRIGGER statement.  Based on my
> research, the following items need to be modified to support the
> REFERENCING option.

> - modify backend\parser\gram.y and keyword.c
> - modify CreateTrigStmt node struct
> - modify pg_trigger struct in pg_trigger.h, Trigger and TriggerDesc
> structs in rel.h, TriggerData struct in trigger.h
> - modify CreateTrigger(), RelationBuildTriggers(), InsertTrigger(),
> CopyTriggerDesc(), FreeTriggerDesc(), ExecBSInsertTriggers(),
> ExecASInsertTriggers(), ExecBRInsertTriggers(),
> ExecARInsertTriggers(), ExecBSDeleteTriggers(),
> ExecASDeleteTriggers(), ExecBRDeleteTriggers(),
> ExecARDeleteTriggers(), ExecBSUpdateTriggers(),
> ExecASUpdateTriggers(), ExecBRUpdateTriggers(), ExecBRUpdateTriggers()
> - to handle the new option, create new fuctions
> ExecBSOInsertTriggers(), ExecASOInsertTriggers(),
> ExecBROInsertTriggers(), ExecAROInsertTriggers(),
> ExecBSNInsertTriggers(), ExecASNInsertTriggers(),
> ExecBRNInsertTriggers(), ExecARNInsertTriggers(),
> ExecBSODeleteTriggers(), ExecASODeleteTriggers(),
> ExecBRODeleteTriggers(), ExecARODeleteTriggers(),
> ExecBSNDeleteTriggers(), ExecASNDeleteTriggers(),
> ExecBRNDeleteTriggers(), ExecARNDeleteTriggers(),
> ExecBSOUpdateTriggers(), ExecASOUpdateTriggers(),
> ExecBROUpdateTriggers(), ExecBROUpdateTriggers(),
> ExecBSNUpdateTriggers(), ExecASNUpdateTriggers(),
> ExecBRNUpdateTriggers(), ExecBRNUpdateTriggers()
> - add supports to ExecutePlan(), ExecInsert(), ExecDelete(),
> ExecUpdate() in \backend\executor\execMain.c
> - add supports to backend\node\equalfuncs.c and copyfuncs.c

Seems to me you are hacking on way more than you want to.  Take another
look at your design and see if there isn't a smaller design struggling
to get out.  In particular, why are you touching any of the executor
(which hardly deals in column names at all) rather than implementing the
aliasing in plpgsql?
        regards, tom lane

> Seems to me you are hacking on way more than you want to.  Take another
> look at your design and see if there isn't a smaller design struggling
> to get out.  In particular, why are you touching any of the executor
> (which hardly deals in column names at all) rather than implementing the
> aliasing in plpgsql?
> 
>             regards, tom lane
Thanks for Tom's advices.

Here are my updated design.

1. modify backend\parser\gram.y and keyword.c to make an automatic alias
for OLD/NEW record (handle REFERENCING option) for CREATE TRIGGER query.
2. add two new fields (int old / char *identifier) into CreateTrigStmt
node struct to receive parameters from the CREATE TRIGGER query.
3. add one field (NameData tgidentifier) into CATALOG(pg_trigger) to store
the alias name.
4. add one field (char *identifier) into Trigger struct to store the alias
name for loading the trigger.
5. modify CreateTrigger() in trigger.c to insert new triggers into
pg_trigger with the new fields.
6. modify RelationBuildTriggers() in trigger.c for loading triggers with
new fields for the relation being accessed.
7. add supports to backend\node\equalfuncs.c and copyfuncs.c.

Any comments or advices would be appreciated.


Henry