DML fails after updatable cursor is used with trigger returning function

From: "Dharmendra Goyal" <dharmendra(dot)goyal(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: DML fails after updatable cursor is used with trigger returning function
Date: 2007-10-31 09:58:34
Message-ID: f87e6d710710310258o76194ea5x1d7de09e44aa59cb@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I created one function which updates a table using updatable cursor. I wrote
one trigger also on the same table. When i execute the function it gives
expected results. But after that all DMLs fail.

CREATE TABLE test(i int, j int);
Drop trigger test_trig;
INSERT INTO test VALUES(1, 100);
INSERT INTO test VALUES(2, 200);

CREATE OR REPLACE FUNCTION test_func()
RETURNS TRIGGER
AS $$
DECLARE c CURSOR FOR SELECT i FROM test FOR UPDATE;
v_i numeric;
BEGIN
OPEN c;
FETCH c INTO v_i;
UPDATE test SET i=50 WHERE CURRENT OF c;
DELETE FROM test WHERE CURRENT OF c;
RETURN NULL;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER test_trig
AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE test_func();

Now when i execute test_func(), it gives error as expected:
SELECT test_func();
ERROR: cursor "c" already in use
CONTEXT: PL/pgSQL function "test_func" line 4 at open
SQL statement "UPDATE test SET i=50 WHERE CURRENT OF $1 "
PL/pgSQL function "test_func" line 6 at SQL statement

Above error is expected.

But after above if i execute any DML DELETE or UPDATE it fails:
DELETE FROM test;
ERROR: cursor "c" is not positioned on a row
CONTEXT: SQL statement "UPDATE test SET i=50 WHERE CURRENT OF $1 "
PL/pgSQL function "test_func" line 6 at SQL statement

OR
update test set i=i+1;
ERROR: cursor "c" already in use
CONTEXT: PL/pgSQL function "test_func" line 4 at open
SQL statement "UPDATE test SET i=50 WHERE CURRENT OF $1 "
PL/pgSQL function "test_func" line 6 at SQL statement

Comments..??

Thanks,
Dharmendra
www.enterprisedb.com

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Gokulakannan Somasundaram 2007-10-31 10:31:07 Clarification on a Time travel feature
Previous Message J. Andrew Rogers 2007-10-31 07:00:45 Re: Opportunity for a Radical Changes in Database Software