Looking for context around which event triggers are permitted

Hi,

I was working on a project with event triggers and was wondering if there was any context from the developers around why some things make this list and others do not. Example: REVOKE/ GRANT are in the event trigger matrix [1] but REINDEX is not. Just wondering if there's a mailing list thread or a commit message that has more info. I can't seem to find anything in the postgres list archives. Thanks!

[1] https://www.postgresql.org/docs/15/event-trigger-matrix.html

Good question. My guess would be that no one really needed an event
trigger for REINDEX so far.

--
Best regards,
Aleksander Alekseev

On Mon, 17 Jul 2023 at 11:26, Aleksander Alekseev <aleksander@timescale.com>
wrote:

Hi,

I was working on a project with event triggers and was wondering if

there was any context from the developers around why some things make this
list and others do not. Example: REVOKE/ GRANT are in the event trigger
matrix [1] but REINDEX is not. Just wondering if there's a mailing list
thread or a commit message that has more info. I can't seem to find
anything in the postgres list archives. Thanks!

[1] https://www.postgresql.org/docs/15/event-trigger-matrix.html

Good question. My guess would be that no one really needed an event
trigger for REINDEX so far.

My answer is not authoritative, but I notice that ANALYZE and VACUUM are
also not there. Those, together with REINDEX, are maintenance commands,
which normally should not affect which queries you can run or their
results. If we think of the queries we can run and the objects we can run
them against as forming an abstraction with maintenance commands breaking
the abstraction, then we can think of event triggers as operating against
the abstraction layer, not the underlying maintenance layer.

On the other hand, the event triggers include tags related to indexes,
which themselves (except for enforcement of uniqueness) in some sense sit
below the abstraction: presence of an index can affect the query plan and
how efficient it is, but shouldn't change the result of a query or whether
it is a valid query. So this is not a fully satisfactory explanation.

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.

Hi,

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.

Sure, please feel free submitting the patch and we will see how it
goes. I don't foresee a strong push-back from the community, but this
being said you can never be certain.

Ideally the patch should include corresponding tests and changes to
the documentation. If you will experience difficulties with those,
that's fine, submit the patch as is. Somebody (me, perhaps) will add
them if necessary.

--
Best regards,
Aleksander Alekseev

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/

That makes sense and is similar to the problem I'm hoping to solve for our
team. We had a DB upgrade that corrupted a few indexes. Gitlab went through
something similar as part of their OS/ DB upgrade. We had to concurrently
reindex everything. This took a few days and just to make sure we completed
this, we reindexed again. If we had had a way to log the event to a table
for each index, it would have made our lives a lot easier.

At a more high level though, it really made me wish there was a way to
audit these things. Sounds like that is what event triggers were designed
for and adding a few more operations could prove useful. Example: You can
track Create/Alter/Drop of a table's lifecycle, capturing timestamps in a
table, but not indexes without REINDEX.

On Mon, Jul 17, 2023 at 10:31 AM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote: