Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6DGWKa48006 for ; Fri, 13 Jul 2001 12:32:20 -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 f6BHpla87781 for ; Wed, 11 Jul 2001 13:51:47 -0400 (EDT) (envelope-from kristis.makris@datasoft.com) Message-Id: <200107111751.f6BHpla87781@postgresql.org> Received: (qmail 12879 invoked from network); 11 Jul 2001 17:51:46 -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 17:51:46 -0000 Subject: Re: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,9) not found From: Kristis Makris To: Tom Lane Cc: pgsql-bugs@postgresql.org, pgsql-sql@postgresql.org, pgsql-odbc@postgresql.org In-Reply-To: <23687.994869627@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> <23687.994869627@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 10:51:46 -0700 Mime-Version: 1.0 X-Archive-Number: 200107/59 X-Sequence-Number: 1837 > 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: I'm glad I could be of some help. I don't know if the following means anything to you (or the pgsql-odbc folks), but in the past (I don't recall if that was postgres 7.0.3, 7.1.1 or 7.1.2) I used to get the following error message while issuing a "BEGIN, ALTER USER ..., COMMIT" block, coming in through an ODBC connection using VC++ 6 with the 7.01.00.05 driver: ERROR: ALTER USER: may not be called in a transaction block Doing a BEGIN, ALTER USER.., COMMIT (not through ODBC, through the psql client) now on 7.1.2 *seems* to work fine though. Should I take it that ALTER USER may indeed be used in a transaction block? I don't know if this was actually fixed or bugfixes to this might have something to do with the system trigger on pg_shadow, but I just thought I'll mention it in case it rings any more bells. I'm sure you know best. > The workaround I'd recommend to you for now is to use ALTER USER, not > UPDATE, to perform the second step in your function. So, how would I go about using the ALTER USER statement to clear out the valuntil value from the pg_shadow table? If I want to set an expiration date for the user, I can use a statement such as: ALTER USER test VALID UNTIL '2001-07-11 10:23:23-07' but if I want to clear the valuntil completely, how should I issue the ALTER USER statement? I have tried (with no success) the following so far: ALTER USER test VALID UNTIL NULL; ALTER USER test VALID UNTIL 'NULL'; ALTER USER test VALID UNTIL ''; Me failing to use the "ALTER USER" command successfully caused be to use the "UPDATE pg_shadow" instead. Thanks, -Kristis