Kristis Makris <kristis.makris@datasoft.com> writes:
> On 11 Jul 2001 12:07:09 -0400, Tom Lane wrote:
>> Maybe you made a table with a foreign key reference to pg_shadow?
> I suspected that, grep'ed all my sql source for "pg_shadow" and the only
> place where I use the pg_shadow table is on the "UPDATE pg_shadow"
> statement. So if I haven't referenced the pg_shadow table, is there any
> other way I might have implicitly created a trigger for it?
Wait a minute, the light just dawned on me: there's a *system* defined
trigger on pg_shadow!
regression=# select * from pg_trigger where tgrelid = (select oid from pg_class where relname = 'pg_shadow');
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable
|tginitdeferred | tgnargs | tgattr | tgargs
---------+----------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+--------
1260 | pg_sync_pg_pwd | 1689 | 29 | t | f | | 0 | f
|f | 0 | |
(1 row)
So that raises this from an unimplemented feature to a real bug, or at
least higher priority in my eyes. You can't do a CREATE/ALTER USER
followed by an UPDATE on pg_shadow in a single transaction:
regression=# begin;
BEGIN
regression=# create user foo;
CREATE USER
regression=# update pg_shadow set passwd = 'z' where usename = 'foo';
ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,29) not found
regression=#
This says that CREATE/ALTER USER really ought to fire the trigger in the
normal fashion, rather than the special-purpose hack they use now.
The workaround I'd recommend to you for now is to use ALTER USER, not
UPDATE, to perform the second step in your function.
regards, tom lane
