Thread: event trigger should provide more details
hi,
I plan to create an event trigger to detect schema change (e.g. add/remove a column, change column type), and write it into a separate table (e.g. EVENTS). Then a process periodically reads this table to send schema change notification. However, the event trigger (43.10.2. Triggers on Events) does not provide me info such as which table is altered, old and new schema. Am I missing something? Thanks very much for any hints.
Regards
Lian
> On 30/05/2023 22:23 CEST Lian Jiang <jiangok2006@gmail.com> wrote: > > I plan to create an event trigger to detect schema change (e.g. add/remove > a column, change column type), and write it into a separate table (e.g. > EVENTS). Then a process periodically reads this table to send schema change > notification. However, the event trigger (https://www.postgresql.org/docs/current/plpgsql-trigger.html) > (43.10.2. Triggers on Events) does not provide me info such as which table > is altered, old and new schema. Am I missing something? Thanks very much for > any hints. You must use ddl_command_end event triggers[0] and call function pg_event_trigger_ddl_commands[1] to get info such as altered table and column. [0] https://www.postgresql.org/docs/current/event-trigger-definition.html [1] https://www.postgresql.org/docs/current/functions-event-triggers.html -- Erik
Thanks. This is helpful. Below is the result when I add a column to public.accounts.
obj.classid, -- 1259
obj.objid, -- 16409
obj.objsubid, -- 0
obj.command_tag, -- ALTER TABLE
obj.object_type, -- table
obj.schema_name, -- public
obj.object_identity, -- public.accounts
obj.in_extension; -- f
The info useful for me is command_tag, object_type, object_identity.
classid, objid is not useful since object_identity is more explicit.
objsubid is not useful because I don't need comment information for schema change.
Besides table name, I still need:
* which columns are added and their types.
* which columns have type change, the old and new types.
* which columns are dropped.
Will command field provide this info? I don't have an example and decoding it needs C code. If I cannot get such info from pg_event_trigger_ddl_commands, I may need to maintain schema snapshots myself and diff the old and new snapshots upon an alter table/view event. Which way should I go? Thanks a lot.
On Tue, May 30, 2023 at 2:42 PM Erik Wienhold <ewie@ewie.name> wrote:
> On 30/05/2023 22:23 CEST Lian Jiang <jiangok2006@gmail.com> wrote:
>
> I plan to create an event trigger to detect schema change (e.g. add/remove
> a column, change column type), and write it into a separate table (e.g.
> EVENTS). Then a process periodically reads this table to send schema change
> notification. However, the event trigger (https://www.postgresql.org/docs/current/plpgsql-trigger.html)
> (43.10.2. Triggers on Events) does not provide me info such as which table
> is altered, old and new schema. Am I missing something? Thanks very much for
> any hints.
You must use ddl_command_end event triggers[0] and call function
pg_event_trigger_ddl_commands[1] to get info such as altered table and column.
[0] https://www.postgresql.org/docs/current/event-trigger-definition.html
[1] https://www.postgresql.org/docs/current/functions-event-triggers.html
--
Erik
--
> On 31/05/2023 00:28 CEST Lian Jiang <jiangok2006@gmail.com> wrote: > > The info useful for me is command_tag, object_type, object_identity. > classid, objid is not useful since object_identity is more explicit. > objsubid is not useful because I don't need comment > (https://www.postgresql.org/message-id/Pine.LNX.4.33.0212091822050.15095-100000@leary.csoft.net) > information for schema change. You need objsubid to identify the column in pg_attribute to get its type, not just to get the comment from pg_description as the linked thread says. > Besides table name, I still need: > * which columns are added and their types. > * which columns have type change, the old and new types. > * which columns are dropped. > > Will command field provide this info? I don't have an example and decoding it > needs C code (https://www.postgresql.org/message-id/20190712222343.GA26924%40alvherre.pgsql). > If I cannot get such info from pg_event_trigger_ddl_commands, I may need to > maintain schema snapshots myself and diff the old and new snapshots upon an > alter table/view event. Which way should I go? Thanks a lot. Right off the bat, I would combine it with a ddl_command_start event trigger to record the necessary info (current columns and their types) in a temp table. Query this table in the ddl_command_end event trigger to figure out which columns have changes. This can be done entirely in plpgsql without using the command column. -- Erik