Обсуждение: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,9) not found

Kristis Makris (kristis.makris@datasoft.com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
ERROR:  deferredTriggerGetPreviousEvent: event for tuple (0,9) not found

Long Description
Using Postgres 7.1.2, I'm executing a plpgsql function that is expected to create a new user and modify the field
"valuntil"in the pg_shadow table. Instead, the user is not created at all and I get the error: 

ERROR:  deferredTriggerGetPreviousEvent: event for tuple (0,9) not found

I log into postgres as a user with administrative privileges (the "usesuper" field is set in the pg_shadow table).

I can provide a complete log of the sequence of commands executed and the schema used if anyone is interested in
replicatingthe ?bug? 



-- Here is the function declaration

CREATE FUNCTION WATSUser_iou (TEXT, INT4, INT4, BOOL, BOOL, BOOL, BOOL, BOOL, BOOL, BOOL, BOOL, BOOL,
                  BOOL, BOOL, BOOL, TEXT) RETURNS INT4 AS '
DECLARE
    lUsername ALIAS FOR $1;
    lUserType_ID ALIAS FOR $2;
    lContactInfo_ID ALIAS FOR $3;
    lCanLogin ALIAS FOR $4;
    lCanEnterTaxPayment ALIAS FOR $5;
    lCanEnterDeposit ALIAS FOR $6;
    lCanEnterAdjustment ALIAS FOR $7;
    lCanEnterWaterRequest ALIAS FOR $8;
    lCanEnterRefund ALIAS FOR $9;
    lCanEnterRefundRequest ALIAS FOR $10;
    lCanEnterWellPayment ALIAS FOR $11;
    lCanEnterWellPaymentRequest ALIAS FOR $12;
    lIsAdministrator ALIAS FOR $13;
    lCanEnterUsers ALIAS FOR $14;
    lCanDeleteUsers ALIAS FOR $15;
    lPassword ALIAS FOR $16;

    lTemp RECORD;
    lType TEXT;
    lExecStmt TEXT;
    lResetPermissions BOOL = FALSE;
    lCurrentUser TEXT;
    lIsSuperUser BOOL;
BEGIN

    --
    -- Identify the user that is calling the function
    --
    SELECT    CURRENT_USER
    INTO    lCurrentUser;

    --
    -- Check if the current user is a superuser
    --
    SELECT    IsSuperUser(lCurrentUser)
    INTO    lIsSuperUser;

    --
    -- Figure out if this is an INSERT or an UPDATE
    --
    SELECT *
    INTO   lTemp
    FROM   WATSUser
    WHERE  username = lUsername;

    IF NOT FOUND THEN
        lType = ''INSERT'';
    ELSE
        lType = ''UPDATE'';
    END IF;


RAISE NOTICE ''WATSUser_iou() - 1'';

    IF lType = ''INSERT'' THEN

         --
         -- Check that the user is allowed to enter users
         --
         SELECT          canenterusers
         INTO          lTemp
         FROM          WATSUser
         WHERE          username = lCurrentUser;

        IF NOT FOUND AND lIsSuperUser = FALSE THEN
               RAISE EXCEPTION ''User % does not have privileges to add any users.'', lCurrentUser;
               RETURN 1;
        ELSE
            -- Enter the watsuser
            INSERT INTO WATSUser
            VALUES (lUsername, lUserType_ID, lContactInfo_ID, CURRENT_TIMESTAMP, lCanLogin, lCanEnterTaxPayment,
                   lCanEnterDeposit, lCanEnterAdjustment, lCanEnterWaterRequest, lCanEnterRefund,
lCanEnterRefundRequest,
                   lCanEnterWellPayment, lCanEnterWellPaymentRequest, lIsAdministrator, lCanEnterUsers,
                   lCanDeleteUsers);

        END IF;

RAISE NOTICE ''WATSUser_iou() - 1.0.a'';

        -- Begin preparing a statement to be executed
        -- There is no escape from escaping single quotes to escape single quotes
        lExecStmt= ''CREATE USER '' || lUsername || '' WITH PASSWORD ''
               || '''''''' || lPassword || '''''''' || '' NOCREATEDB '';

RAISE NOTICE ''WATSUser_iou() - 1a'';

    ELSE

         --
         -- Check that the user is allowed to update user information
         --
         SELECT          canenterusers
         INTO          lTemp
         FROM          WATSUser
         WHERE          username = lCurrentUser;

        IF NOT FOUND AND lIsSuperUser = FALSE THEN
               RAISE EXCEPTION ''User % does not have privileges to update any user data.'', lCurrentUser;
               RETURN 2;
        ELSE

            --
            -- Check if the usertype has changed
            --
            SELECT    usertype_id
            INTO    lTemp
            FROM    WATSUser
            WHERE    username = lUsername;

            IF lTemp.usertype_id <> usertype_id THEN
               lResetPermissions = TRUE;
               PERFORM RevokePerms(lUsername);
            END IF;

RAISE NOTICE ''WATSUser_iou() - 1b'';

            UPDATE WATSUser
            SET    UserType_ID = lUserType_ID,
                   ContactInfo_ID = lContactInfo_ID,
                   CanLogin = lCanLogin,
                   CanEnterTaxPayment = lCanEnterTaxPayment,
                   CanEnterDeposit = lCanEnterDeposit,
                   CanEnterAdjustment = lCanEnterAdjustment,
                   CanEnterWaterRequest = lCanEnterWaterRequest,
                   CanEnterRefund = lCanEnterRefund,
                   CanEnterRefundRequest = lCanEnterRefundRequest,
                   CanEnterWellPayment = lCanEnterWellPayment,
                   CanEnterWellPaymentRequest = lCanEnterWellPaymentRequest,
                   IsAdministrator = lIsAdministrator,
                   CanEnterUsers = lCanEnterUsers,
                   CanDeleteUsers = lCanDeleteUsers
            WHERE  username    = lUsername;

            IF lResetPermissions = TRUE THEN
               PERFORM GrantPerms(lUsername);
            END IF;

            -- Begin preparing a statement to be executed
            -- There is no escape from escaping single quotes to escape single quotes
            lExecStmt= ''ALTER USER '' || lUsername || '' '';

        END IF;

    END IF;


RAISE NOTICE ''WATSUser_iou() - 2'';

    -- If the user is an administrator, she can create users
    IF lIsAdministrator = TRUE THEN
        lExecStmt = lExecStmt || ''CREATEUSER'';
    ELSE
        lExecStmt = lExecStmt || ''NOCREATEUSER'';
    END IF;

    -- Execute the prepared statement
    EXECUTE lExecStmt;

RAISE NOTICE ''WATSUser_iou() - 3'';

    -- If the user can login, her account does not expire
    IF lCanlogin = TRUE THEN
        UPDATE pg_shadow SET valuntil=NULL WHERE usename=lUsername::name;
    ELSE
        UPDATE pg_shadow SET valuntil=current_timestamp WHERE usename=lUsername::name;
    END IF;

RAISE NOTICE ''WATSUser_iou() - 4'';

    -- Change the password if you must
    IF lPassword IS NOT NULL AND lType = ''UPDATE'' THEN
        PERFORM ChangePassword(lUsername, lPassword);
    END IF;

    -- Grant all the necessary permissions to the user
    PERFORM GrantPerms(lUsername);

    RETURN 0;
END;
'    LANGUAGE 'plpgsql';






--
-- And here is the function call:
--
test=#  SELECT WATSUser_iou('gcc', 2, 1, 't', 'f', 'f', 'f', 'f', 'f', 'f', 'f', 'f', 'f', 'f', 'f', 'gcc');
NOTICE:  WATSUser_iou() - 1
NOTICE:  WATSUser_iou() - 1.0.a
NOTICE:  WATSUser_iou() - 1a
NOTICE:  WATSUser_iou() - 2
NOTICE:  WATSUser_iou() - 3
ERROR:  deferredTriggerGetPreviousEvent: event for tuple (0,9) not found
test=# select * from pg_user where usename='gcc';
 usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd | passwd | valuntil
---------+----------+-------------+----------+----------+-----------+--------+----------
(0 rows)


Sample Code


No file was uploaded with this report

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.

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.

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.

            regards, tom lane

Kristis Makris <kristis.makris@datasoft.com> writes:
> On 11 Jul 2001 11:21:35 -0400, Tom Lane wrote:
>> 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;

Maybe you made a table with a foreign key reference to pg_shadow?

            regards, tom lane

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

Kristis Makris <kristis.makris@datasoft.com> writes:
> but if I want to clear the valuntil completely, how should I issue the
> ALTER USER statement?

Offhand I don't believe ALTER USER can do that.  Feel free to submit
a patch ;-).

            regards, tom lane

> 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

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

On 11 Jul 2001 12:07:09 -0400, Tom Lane wrote:
> Kristis Makris <kristis.makris@datasoft.com> writes:
> > On 11 Jul 2001 11:21:35 -0400, Tom Lane wrote:
> >> 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;
>
> 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?

-Kristis