Re: Looking for context around which event triggers are permitted

On 2023-Jul-17, Garrett Thornburg wrote:

> That's a good point, Isaac. Select into, security label, comment, etc are
> all maintenance style commands but are already added to the matrix. I do
> think there's a good case to include other maintenance related commands as
> event triggers. Suppose you want to know the last time a table was vacuumed
> or the last time a table was reindexed. If you can trigger off of these
> maintenance commands, there's a lot you could build on top of postgres to
> make the maintenance experience easier. Seems like a positive thing.
>
> The code exists but they are disabled at the moment. Happy to enable those
> with a patch if it's as Aleksander said. Meaning, no real reason they were
> disabled other than someone thought folks wouldn't need them.

Yeah, as I recall, initially there were two use cases considered for
event triggers:

1. DDL replication. For this, you need to capture commands that somehow
modify the set of objects that exist in the database. So creating an
index or COMMENT are important, but reindexing one isn't.

2. DDL auditing. Pretty much the same as above. You don't really care
when vacuuming occurs, but if a table changes ownership or a security
label is modified, that needs to be kept track of.

Later, a further use case was added to enable people avoid long-running
table locking behavior: you only want to let your devs run ALTER TABLE
in production if it's going to finish really quick. So table_rewriting
appeared and allowed some further options. (As for SELECT INTO, it may
be that it is only there because it's very close in implementation to
CREATE TABLE AS, which naturally needs to be logged for auditing
purposes ... but I'm not sure.)

I'm wondering why you want REINDEX reported to an event trigger. What's
your use case?

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/