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 <alvhe...@alvh.no-ip.org> wrote: > 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/ >
