From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kristis Makris <kristis.makris@datasoft.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0, 9) not found
Date: Wed, 11 Jul 2001 12:40:27 -0400
Message-ID: <23687.994869627@sss.pgh.pa.us> (raw)
In-Reply-To: <200107111617.f6BGHWJ23570@sss.pgh.pa.us>
References: <200107110034.f6B0YX879575@hub.org>
<18395.994864895@sss.pgh.pa.us>
<200107111604.f6BG4xJ21783@sss.pgh.pa.us>
<21997.994867629@sss.pgh.pa.us>
<200107111617.f6BGHWJ23570@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, kristis.makris@datasoft.com, pgsql-bugs@postgresql.org
Subject: Re: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0, 9) not found
In-Reply-To: <23687.994869627@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox