Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-odbcpgsql-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

pgsql-odbc by date

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

pgsql-bugs by date

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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group