Re: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0, 9) not found

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Kristis Makris <kristis(dot)makris(at)datasoft(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0, 9) not found
Date: 2001-07-11 16:40:27
Message-ID: 23687.994869627@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-odbc pgsql-sql

Kristis Makris <kristis(dot)makris(at)datasoft(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2001-07-11 16:53:38 PL/pgSQL triggers with parameters don't work
Previous Message Kristis Makris 2001-07-11 16:17:27 Re: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,9) not found

Browse pgsql-odbc by date

  From Date Subject
Next Message Kristis Makris 2001-07-11 17:51:46 Re: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,9) not found
Previous Message Kristis Makris 2001-07-11 16:17:27 Re: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,9) not found

Browse pgsql-sql by date

  From Date Subject
Next Message Alex Pilosov 2001-07-11 17:51:20 RE: can we write to a flat file from Postgresql procedure
Previous Message Kristis Makris 2001-07-11 16:17:27 Re: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,9) not found