Re: Tracking role modification timestamps in pg_authid / pg_roles

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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/27666(dot)1461177383(at)sss(dot)pgh(dot)pa(dot)us#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/24270(dot)1239663969(at)sss(dot)pgh(dot)pa(dot)us#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