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