I'm looking for best-practice advice.
PL/Java is an extension that manages some objects (jar files, which
users can tell PL/Java to load, drop, or replace). The objects have
owners (have had since PL/Java 1.1.0 anyway).
When the owner tracking was added for 1.1.0 it recorded the owner oid.
In 2006, before 1.3.0, it was changed to keep the owner name instead
of the oid, in response to a bug report 1506 that involved the wrong
owner name being shown after dump/restore into another db where the
user oids were different.
Neither approach seems perfect to me (in fact, they both strike me
as having complementary sides of the same weakness, which dump/restore
just happens to expose). I am also wondering whether PL/Java ought to
create references, or a trigger, on pg_authid to clean up if the user
goes away; it currently doesn't.
Before I spend too many cycles on this, is there a most favored
design pattern that has already emerged for this kind of thing?
-Chap