Skip site navigation (1) Skip section navigation (2)

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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: ERROR: deferredTriggerGetPreviousEvent: event for tuple (0,9) not found
Date: 2001-07-11 00:34:33
Message-ID: 200107110034.f6B0YX879575@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-odbcpgsql-sql
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


Responses

pgsql-odbc by date

Next:From: Edward ThomasDate: 2001-07-11 13:19:41
Subject: Data export using Microsoft Access
Previous:From: markMLl.pgsql-interfacesDate: 2001-07-10 20:55:46
Subject: Re: Getting info on "Max LongVarChar" under ODBC

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2001-07-11 04:57:08
Subject: Re: pg_ctl restart just appends to command line instead of regenerating original cmd
Previous:From: Tom LaneDate: 2001-07-10 22:28:21
Subject: Re: sql query cursor problem

pgsql-sql by date

Next:From: Robby SlaughterDate: 2001-07-11 02:51:39
Subject: RE: SQL question
Previous:From: Josh BerkusDate: 2001-07-11 00:26:40
Subject: Re: SQL question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group