pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (9+ messages)  latest in thread

Message-ID: <23687.994869627@sss.pgh.pa.us>
Permalink:  ../23687.994869627@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/23687.994869627@sss.pgh.pa.us

 · 

reply

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