Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6DGVaa47818 for ; Fri, 13 Jul 2001 12:31:36 -0400 (EDT) (envelope-from pgsql-bugs-owner@postgresql.org) Received: from datasoft.com (maniac.datasoft.com [63.228.155.187]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6BG4ka39360 for ; Wed, 11 Jul 2001 12:04:46 -0400 (EDT) (envelope-from kristis.makris@datasoft.com) Message-Id: <200107111604.f6BG4ka39360@postgresql.org> Received: (qmail 11679 invoked from network); 11 Jul 2001 16:04:45 -0000 Received: from dsco-user.datasoft.com (HELO leonidas.corp.datasoft.com) (63.228.155.190) by maniac.datasoft.com with SMTP; 11 Jul 2001 16:04:45 -0000 Subject: Re: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,9) not found From: Kristis Makris To: Tom Lane Cc: pgsql-bugs@postgresql.org In-Reply-To: <18395.994864895@sss.pgh.pa.us> References: <200107110034.f6B0YX879575@hub.org> <18395.994864895@sss.pgh.pa.us> Content-Type: text/plain X-Mailer: Evolution (0.9/+cvs.2001.03.14.20.47 - Preview Release) Date: 11 Jul 2001 09:04:45 -0700 Mime-Version: 1.0 X-Archive-Number: 200107/58 X-Sequence-Number: 1839 Hi Tom, thanks for your feedback on this. On 11 Jul 2001 11:21:35 -0400, Tom Lane wrote: > pgsql-bugs@postgresql.org writes: > > ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,9) not found > > Hmm, are you trying to create triggers on pg_shadow? It's hard to see > how that message could come from an "UPDATE pg_shadow" otherwise. I'm not trying to explicitly create triggers on pg_shadow. None of the triggers/tables/sequences/functions I've created so far try to reference the pg_shadow in any way, so I don't *believe* (but I could be wrong) that I've even implicitly created a trigger on pg_shadow; > Triggers on system catalogs don't work very well, because they won't > be invoked as a side effect of system-initiated operations. In this > situation, it appears that the trigger code sees that the pg_shadow row > was inserted in the current transaction, so it tries to look up the row > in the list of pending trigger events --- and doesn't find an entry, > because CREATE USER didn't invoke the trigger code. Before I came across this error, the approach I was following was: -Call the WATSUSer_iou() plpgsql function that contained "CREATE USER" statements to create the new user -Call a plpgsql function after that, that contained "UPDATE pg_shadow" statements to update the "valuntil" field from the pg_shadow table. This approach was working fine. I get the feeling that this approach had the effect of each function call being considered a separate transaction, thus there were no errors when the "UPDATE pg_shadow" statement was called (the "CREATE USER" transaction was commited by that time) > In a perfect world we'd support user-defined triggers on the system > catalogs, but given the potential circularity problems, I doubt it's > going to happen real soon. I'd suggest finding another approach. Sounds reasonable. I can stick to the old approach. In case you guys ever need to debug/ look into this I have a schema and a sequence of commands executed that I'll keep around. Thanks for your help. -Kristis