Hi Tom, I think it's worth addressing these architectural points directly rather than continuing to narrow the patch's scope in response.
On Thu, 27 Aug 2026 at 15:07, Tom Lane <[email protected]> wrote: > I don't think that limiting this idea to global objects does one single > thing to address the problems with the concept, such as what are the > semantics for dump/restore. On dump/restore: pg_dump and pg_dumpall are unaffected by this patch and, structurally, could not carry this column either way. dumpRoles() in pg_dumpall.c selects an explicit column list matching the attributes settable via CREATE/ALTER ROLE, and there is no such clause for this timestamp, so it was never a candidate for inclusion. A restored cluster, therefore, gets the restoration time on every role, since pg_dumpall replays CREATE ROLE then ALTER ROLE rather than copying the tuple, and pg_upgrade goes through the same path. I'm not proposing to change that or extend the grammar to carry it through. The semantics I have in mind are strictly "the timestamp of the last CREATE or ALTER command executed against this object on this server": instance-local, live-cluster state, not object history. Under that definition, restamping on restore is correct, not a tolerated limitation. A role that comes back through a dump/restore is, from that server's point of view, a freshly written state, and there is nothing to preserve. > We've discussed modification timestamps for database objects many times in > the past, and rejected them every single time. What exactly will be > different this time? > On "what's different this time": I went looking rather than asking you to dig it up for me. I found a 2016 thread on pgsql-general proposing relcreated on pg_class, where your answer was almost word-for-word what you just told me: "it sounds trivial, until you start thinking about backup/restore/replication situations, and then you realize that the required semantics are far from clear. In practice, audit logs ... are a far better solution." ( https://www.postgresql.org/message-id/flat/[email protected]#75f96d35e6e8424d57492288aeaca870 ) I also found a 2009 hackers thread proposing created/altered columns on pg_proc and pg_class, where you leaned toward Josh Berkus's alternative of a generic side table keyed by classid/objectid instead of per-catalog columns, with pruning stale entries left as the open problem ( https://www.postgresql.org/message-id/flat/[email protected]#ab1f5c7afe259305b1d15cb5e3a70d67). If either is among the threads you had in mind, I'd rather engage with them directly than restate my own case blind. Two things stand out specifically from the 2016 thread. First, your answer there didn't turn on whether event triggers cover the object: pg_class already supports them, and you pointed to audit logging instead, not to event triggers. That's a real problem for the argument I was leaning on, that this proposal only touches objects that event triggers can't see, since it suggests the objection to a plain column isn't about coverage gaps at all. Second, if audit logging is the standard answer, I don't think it actually serves the declarative-reconciliation use case this patch targets, and I'd rather say that plainly than assume it's covered. An audit log is an append-only history that must be enabled, retained, and parsed. What a reconciler needs is a single indexed value it can compare against a cached one in a single SELECT, with the same shape as an HTTP ETag or a Kubernetes resourceVersion. That's a cheaper problem than "what happened here", and I don't think an audit log answers it any more cheaply than an event trigger plus a side table does, which my first mail already conceded works, just at the cost of extra machinery. So, is the objection that a plain catalog column is never the right shape for this class of problem, independent of what the alternative costs? If so, I'd like to hear that plainly, since it means withdrawing this rather than continuing to adjust its scope. Thanks, Gabriele -- Gabriele Bartolini VP, Chief Architect, Kubernetes enterprisedb.com / Melbourne, Australia
