Kristis Makris ([EMAIL PROTECTED]) 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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
