pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: pgsql-bugs@postgresql.org
To: pgsql-bugs@postgresql.org
Subject: ERROR:  deferredTriggerGetPreviousEvent: event for tuple (0, 9) not found
Date: Tue, 10 Jul 2001 20:34:33 -0400 (EDT)
Message-ID: <200107110034.f6B0YX879575@hub.org> (raw)

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 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




view thread (9+ messages)  latest in thread

Message-ID: <200107110034.f6B0YX879575@hub.org>
Permalink:  ../200107110034.f6B0YX879575@hub.org/
Also on:    postgresql.org/message-id/200107110034.f6B0YX879575@hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: pgsql-bugs@postgresql.org
  Subject: Re: ERROR:  deferredTriggerGetPreviousEvent: event for tuple (0, 9) not found
  In-Reply-To: <200107110034.f6B0YX879575@hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox