Re: [HACKERS] update_pg_pwd trigger does not work very well

Tom Lane writes:

> 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.

Wow, that implies that every trigger that contains non-database
side-effects is potentially bogus. That never occured to me. Perhaps (as a
future plan), it would be a good idea to have deferred triggers as well?
Now that I think of it, wasn't that the very reason Jan had to invent the
separate constraint triggers?

> 2. The trigger tries to grab AccessExclusiveLock on pg_shadow.

It doesn't actually need that exclusive lock, I think. A shared read lock
(i.e., none really) would suffice.

> 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.

If you think that this is okay (and not just a hack), then go for it. If
the above mentioned deferred triggers are at all in the near future I
wouldn't mind scrapping that trigger altogether. There isn't a good reason
to muck with pg_shadow.{usename|password|validuntil} anyway. And it is in
general not safe to muck with system catalogs period. (Try to rename a
table by updating pg_class.relname. ;)


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden