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 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/