Re: Looking for context around which event triggers are permitted

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(at)alvh(dot)no-ip(dot)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/
>