update_pg_pwd trigger does not work very well

I was looking at the trigger function that's been added to try to
update pg_pwd automatically if pg_shadow is updated via standard
SQL commands.  It's got some problems:

1. Since the trigger is executed as soon as a tuple is inserted/
updated/deleted, it will write pg_pwd before the transaction is
committed.  If you then abort the transaction, pg_pwd contains wrong
data.  Even if you don't abort, the postmaster may read and act on the
updated pg_pwd before you've committed, which could have bad
consequences (logging in a user who doesn't exist yet, for example).

2. The trigger tries to grab AccessExclusiveLock on pg_shadow.
Since this is being done in the middle of a transaction that has
previously grabbed some lower level of lock on pg_shadow, it's
very easy to create a deadlock situation.  All you need is two
different transactions modifying pg_shadow concurrently, and
it'll fail.

3. CREATE USER and friends refuse to run inside a transaction block
in the vain hope of making life safe for the trigger.  It's vain
since the above problems will occur anyway, if one simply alters
pg_shadow using ordinary SQL commands.  (And if we're not going to
support that, why bother with the trigger?)  I think this is a rather
unpleasant restriction, especially so when it isn't buying any
safety at all.


A possible solution for these problems is to have the trigger procedure
itself do nothing except set a flag variable.  The flag is examined
somewhere in xact.c after successful completion of a transaction,
and if it's set then we run a new transaction cycle in which we
read pg_shadow and write pg_pwd.  (A new transaction is needed so
that it's safe to demand AccessExclusiveLock on pg_shadow --- we
have to release all our old locks before we can do that.)  Note that
*only* this second transaction would need AccessExclusiveLock; CREATE
USER and friends would not.

I am not quite certain that this is completely bulletproof when there
are multiple backends concurrently updating pg_shadow, but I have not
been able to think of a case where it'd fail.  The worst possibility
is that a committed update in pg_shadow might not get propagated to
pg_pwd for a while because some other transaction is holding a lock on
pg_shadow.  (But pg_pwd updates can be delayed for that reason now,
so it's certainly no worse than before.)

Comments?
        regards, tom lane