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

Kristis Makris (kristis(dot)makris(at)datasoft(dot)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 replicating the ?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